0

I've already read Passing an array to a query using a WHERE clause which is basically what I am doing, but with strings instead of integers. I am having an issue with the WHERE clause.

$codes_imp = "'" . implode("','", $codes) . "'";
$passwords_imp = "'" . implode("','", $passwords) . "'";
$comments_imp = "'" . implode("','", $comments) . "'";

$set_pass_query = "INSERT INTO users (password, comments) VALUES ($passwords_imp, $comments_imp) WHERE Code IN ($codes_imp)";

When executed, the query looks like this:

INSERT INTO users (password, comments) 
VALUES ('password1', 'password2', 'password3', 'comment1', 'comment2', 'comment3') 
WHERE Code IN ('code1', 'code2', 'code3')

All columns in the table are of type VARCHAR. Clearly I have a syntax error (as it is telling me), but I am not sure how to construct this properly.

Community
  • 1
  • 1
Kevin_TA
  • 4,575
  • 13
  • 48
  • 77
  • Try looking [here](http://stackoverflow.com/questions/485039/mysql-insert-where-query). This discussion should answer your question. – zysoft Sep 20 '12 at 17:53
  • 1
    Someone needs to read up on [SQL injection handling](http://bobby-tables.com/php). – tadman Sep 20 '12 at 18:21

2 Answers2

3

You are trying to constrain an INSERT query with a WHERE clause. That's a big no-no.

Either you want to UPDATE, or you need to drop the WHERE

What exactly are you trying to accomplish?

Mike Mackintosh
  • 13,917
  • 6
  • 60
  • 87
  • Right, UPDATE. Sorry, it's been a while since I've done much mysql. I'm trying to add passwords from an array to existing entries in a database. – Kevin_TA Sep 20 '12 at 17:55
  • Even then, your query doesn't add up. You have 2 fields, and then 6 values and 3 constraints. Can you do a `describe users` and post the output so I can get a better idea? – Mike Mackintosh Sep 20 '12 at 18:02
  • @sixeightzero, The scenario seems fine, now that we know he wants to update. Added a possible answer for that. – saji89 Sep 20 '12 at 18:10
  • I am just trying to update the password and comment of a user with Code = 'code1'. So 'password1' and 'comment1' will be assigned to columns `password` and `comments` respectively. The same would be done for the user with Code = 'code2' and the user with Code = 'code3'. – Kevin_TA Sep 20 '12 at 18:11
  • @Kevin_TA, I have added the answer for you. – saji89 Sep 20 '12 at 18:13
2

You need to use UPDATE and not INSERT, to alter existing db rows. You could use a loop construct like the foreach loop. E.g: 

foreach($codes_imp as $key=>$code){
   $query="UPDATE users SET password='$passwords_imp[$key]', comments='$comments_imp[$key]' WHERE Code='$code'"
   [Code to execute the query goes here]
}

N.B: We need to use a loop construct because UPDATE queries can run only on one db row at a time.

Alternative method:

Instead of using loops you could use the CASE WHEN ELSE END syntax in Mysql.
To know how to use this, please refer:

Community
  • 1
  • 1
saji89
  • 2,093
  • 4
  • 27
  • 49
  • If I understand this correctly, this will (for this example) run 3 queries. I wanted to avoid running queries in a loop as it is inefficient and in reality, this code will be updating some 13,000 rows. I used 3 here for a simple example. – Kevin_TA Sep 20 '12 at 18:14
  • @Kevin_TA, An update query can run only on one row at a time. – saji89 Sep 20 '12 at 18:15
  • Ahhh. I was not aware of that. Bummer. Ok well thanks for the help. – Kevin_TA Sep 20 '12 at 18:19
  • I have added an alternative method that might be of interest to you. – saji89 Sep 20 '12 at 18:22
  • You're welcome @Kevin_TA. If this solved the problem for you, please don't forget to mark your question as solved. – saji89 Sep 20 '12 at 18:23