Phpmyadmin shows errors, but this works fine

Pedroski55 · August 22, 2020, 2:59am

I have a little button on my webpage called “Show my score”. A student can click it, enter their class and student number and the term week. They can then see their attendance, their score this week, their total score so far and their overall score so far this term.

When I paste the sql in phpmyadmin, I see a nasty red button on the left. Unfortunately, when I move the mouse over it, the text is white and I cannot read it. However, the sql query works fine, does just what I want. I tried it in mysql in bash (no errors), I tried it in phpmyadmin and in Firefox (no errors in apache2 error.log). I get what I want, no problems

The tables involved are: 19BE1login, 19BE1_max_scores and 19BE_all_scores

Maybe one of you experts can make this better, or at least tidier:

SELECT 19BE1login.name, 19BE1login.number, 19BE1login.max_attendance, 19BE1login.attendance, 19BE1login.max_score AS your_total_score, 19BE_all_scores.Week1 AS your_score_this_week, 19BE1_max_scores.Week1 AS week_max, 19BE1_max_scores.total_score AS maximum_points FROM 19BE1login INNER JOIN 19BE_all_scores ON 19BE1login.number = 19BE_all_scores.number INNER JOIN 19BE1_max_scores ON 19BE1login.number = 19BE1_max_scores.number WHERE 19BE1login.number = 1234567890

I made this more flexible as below. It takes the $class, $studentnum and $weeknr from the input form:

$sql = 'SELECT ' . $class . 'login.name, ' . $class . 'login.number, ' . $class . 'login.max_attendance, ' 
. $class . 'login.attendance, ' . $class . 'login.max_score AS your_total_score, 19BE_all_scores.' . $weeknr . ' AS your_score_this_week, ' 
. $class . '_max_scores.' . $weeknr . ' AS week_max, ' . $class . '_max_scores.total_score AS maximum_points FROM ' 
. $class . 'login INNER JOIN 19BE_all_scores ON ' . $class . 'login.number = 19BE_all_scores.number INNER JOIN ' 
. $class . '_max_scores ON ' . $class . 'login.number = ' 
. $class . '_max_scores.number WHERE ' . $class . 'login.number =  ' . $studentnum;

This works fine.
Thanks for any tips!

jimbopp · August 23, 2020, 1:35pm

It looks like you’re not using PDO?

Pedroski55 · August 24, 2020, 1:25am

Let me clarify:

I have phpmyadmin open in Firefox on my laptop.

I am looking at the table 19BE1login.

I paste this in the phpmyadmin sql tab:

SELECT 19BE1login.name, 19BE1login.number, 19BE1login.max_attendance, 19BE1login.attendance, 19BE1login.max_score AS your_total_score, 19BE_all_scores.Week1 AS your_score_this_week, 19BE1_max_scores.Week1 AS week_max, 19BE1_max_scores.total_score AS maximum_points FROM 19BE1login INNER JOIN 19BE_all_scores ON 19BE1login.number = 19BE_all_scores.number INNER JOIN 19BE1_max_scores ON 19BE1login.number = 19BE1_max_scores.number WHERE 19BE1login.number = 1234567890

I see a nasty red button on the left. I move the mouse over it. I see various error messages, but they are in white text on a light background. I cannot read them.

Notwithstanding, this query works perfectly, does just what I want.

So I am just wondering whether you experts can see something wrong in this query, maybe improve it.

This query works on my laptop localhost and it works on my webpage. It displays a student’s attendance, scores and overall score in little html tables.