MySQL Update Syntax Using Parentheses

问题:

In the following code $keyresult and $valueresult are comma separated lists of columns in my db and the values I want to put into them in the identified row. The problem is, the code isn't doing what I hoped it would and is returning a syntax error in the query.

$q3 = "UPDATE post SET ($keyresult) VALUES ('$valueresult') WHERE user_id='$user_id' AND post_id='$post_id' AND post_status='active'";

How can I fix the syntax of this?

回答1:

You are mixing INSERT and UPDATE syntax.

$q3 = "UPDATE `post` SET `$keyresult` = '$valueresult' 
       WHERE user_id='$user_id' AND post_id='$post_id' AND post_status='active'";

I am assuming you are properly escaping $valueresult, $user_id, and $post_id before you are executing your query. If not, and these are user-supplied values, you are wide open to SQL injections. I recommend looking into prepared statements to eliminate this risk.