MySQL Update Syntax Using Parentheses

Question

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?

Community · Accepted Answer · 2017-05-23 12:27:10Z

3

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.

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Jun 17, 2014 at 19:37

John Conde

220k100 gold badges464 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3745602 Over a year ago

THe problem with this is that the $keyresult variable contains multiple columns (column 1, column 2, column 3) and the $valueresult has corresponding values (value 1, value 2, value 3). Running it like this gives a syntax error as well. And yes. They are escaped with mysqli_escape_string

John Conde Over a year ago

Then you need to generate your query differently. The way you are approaching it won't work. A simple loop would make this simple.

Collectives™ on Stack Overflow

MySQL Update Syntax Using Parentheses

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related