Updating mysql table dynamically

Question

I am trying to update the table dynamically here

$QUERY = "UPDATE `internshala`.`student` SET `High_School` = \'$High_School\', `HS_Percentage` = \'$HS_Percentage\', `Intermediate` = \'$Intermediate\', `I_Percentage` = \'$I_Percentage\', `Graduation` = \'$Graduation\', `G_Score` = \'$G_Score\', `G_Year` = \'$G_Year\', `PG_Year` = \'$PG_Year\', `PostGraduation` = \'$PostGraduation\', `PG_Score` = \'$PG_Score\' WHERE `student`.`id` = '$_SESSION['user_id'];";

It throws the error syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING)

I can't figure out the correct syntax for this, the above used syntax is something I deduced from phpmyadmin.

PS: All the variables being used in the update statement are set to a not null value.

score 2 · Accepted Answer · edited May 23 '17 at 12:28

2

There are lots of issues with your query. You are escaping single quotes while using double quotes to define your string. You don't need this.

SET `High_School` = \'$High_School\',

should read

SET `High_School` = '$High_School',

Also, you are using a session variable at the end and not closing the quote

WHERE `student`.`id` = '$_SESSION['user_id'];";

You need to enclose the array item in curly braces and close the single quote:

WHERE `student`.`id` = '{$_SESSION['user_id']}';";

Your full query should look like this

$QUERY = "UPDATE `internshala`.`student` SET 
  `High_School` = '$High_School', 
  `HS_Percentage` = '$HS_Percentage', 
  `Intermediate` = '$Intermediate',
  `I_Percentage` = '$I_Percentage',
  `Graduation` = '$Graduation',
  `G_Score` = '$G_Score', 
  `G_Year` = '$G_Year', 
  `PG_Year` = '$PG_Year', 
  `PostGraduation` = '$PostGraduation', 
  `PG_Score` = '$PG_Score' 
 WHERE `student`.`id` = '{$_SESSION['user_id']}';";

Also don't use this method. You are open to sql injection. You really should use PDO or mysqli with prepared statements. Take a look at this post: How can I prevent SQL injection in PHP?

edited May 23 '17 at 12:28

Community

1
1

answered Feb 02 '15 at 22:59

Robbert

6,286
5
30
58

Now its You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\'Dr. VSEC\', `HS_Percentage` = \'12\', `Intermediate` = \'Dr. Vsec\', `I_Percen' at line 1s message – Chaitanya Feb 02 '15 at 23:04
The values are being used from variables and yet this error? – Chaitanya Feb 02 '15 at 23:05
I am passing each input through this function clean_input($data) { $data = stripslashes($data); $data = trim($data); $data = htmlspecialchars($data); return $data; } Is SQL Injection still possible? – Chaitanya Feb 02 '15 at 23:11
Admittedly, your code does a good job of cleaing up the values. However, you're better off using the built in functions to minimize this possibility. It's just good coding practice. – Robbert Feb 02 '15 at 23:17

Updating mysql table dynamically

1 Answers1