-3

I have the following error

SELECT * FROM Class_1_2024 WHERE `term` = 1 AND 'subjectid' = Science
Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Science' in 'where clause' in C:\xampp\htdocs\NGM\wre_incs\teacher_class.php:77 Stack trace: #0 C:\xampp\htdocs\NGM\wre_incs\teacher_class.php(77): PDOStatement->execute() #1 C:\xampp\htdocs\NGM\teacher\ges-grade.php(357): Teacher->ges_instructor_students('SELECT * FROM C...') #2 {main} thrown in C:\xampp\htdocs\NGM\wre_incs\teacher_class.php on line 77

This is the line of code that raises this error

"SELECT * FROM $tablename WHERE `term` = $term AND `subjectid` = $subjectid"

What I am confused about is that the error says unknown column "Science" but the column refrenced is the 'subjectid' column.

There is no error when the SQL query is changed to

"SELECT * FROM $tablename WHERE `term` = $term"

but of course the second part of the where clause is not accounted for and that is not what i want.

Im quite new to this too Thanks in advance for any assistance.

*** UPDATE *** I have been redirected to some links that suggest that my query is the same as those, this is not true for the following reasons

  1. All the links show clearly that the issues were caused and solved by adding and removing backticks respectively
  2. All the queries were related to inserting data into tables so the purpose of the queries in the links provided are not the same as the purpose

if all these mean nothing then it is worth mentioning that I have backticks in my code because I applied similar suggestions about putting column names and table names in backticks and it still did not work.

The emphasis of my issue is that the error says i am referenceing a column "science" when there is no column called science and the query clearly does not reference a column called "science" but rather a column called "subjectid".

Note that when the query works very fine even with backticks when it is modified like so...

"SELECT * FROM $tablename WHERE `term` = $term"

if it works this way even with backticks why does the second part AND `subjectid` = $subjectid" also with backtick not work and how are backticks a problem in the second part when they are clearly not a problem in the first part.

OVERALL the backtick solution I am being referred to is NOT solving the problem as I have tried all possible combinations of the code with or without backticks and in all instances it works when the second part is absent. Please the backtics dont seem to be the cause of the problem and when all the solutions from backtics are applied the problem still persists.

Alt-01
  • 7
  • 3
  • Use prepared statements with query parameters and you won't have to worry about manually maintaining quoting for string values. With the added bonus of correcting the SQL injection vulnerability that this code has. – David Aug 09 '23 at 14:35
  • SQL needs a way to distinguish between table/column names and literal text. That's single quotes. As said, do not inject free text into SQl and you won't have to care about this. – Álvaro González Aug 09 '23 at 14:45
  • *"`... AND 'subjectid' = Science`"* — In this query, `'subjectid'` is a string *value* and `Science` is a *column*. Because `'subjectid'` is in single quotes and `Science` is in no quotes. No quotes and backticks have the same effect here. What you want is `AND subjectid = 'Science'`. But what you _really_ want is to use parameterised queries, so you don't need to think about adding the correct quotes manually! – deceze Aug 09 '23 at 15:39

0 Answers0