1

I would like to convert an array if IDs, into a string of comma separated values, to use in a MySQL UPDATE query. How would I do this?

Improve this question
7
  • be very, very careful of SQL injection... if you're building query strings like this, you've probably made a design error. Commented Nov 18, 2008 at 3:10
  • i disagree, rmeador. if you know that the contents of the array is safe (eg: all integers), then there's no harm in this style at all. Commented Nov 18, 2008 at 4:10
  • @nickf True but I've still run into cases where there wasn't enough type checking on id's. Some extra checking and sanitization is easy and safer in the long run. Commented Nov 18, 2008 at 4:14
  • I don't think that there is any question, you can never know what happens, what if you make an error, or if the data is corrupt, or... whatever, ALWAYS sanitize your SQL. Commented Nov 18, 2008 at 5:00
  • 1
    well yes, you can answer with "sanitise your SQL". You could have just as easily said "check your code into version control", "save often", or "call your mum and tell her you love her"... all of these things are good advice, but are extraneous to the question. :) Commented Nov 18, 2008 at 12:39

5 Answers 5

Reset to default
13

Remember to escape values:

'"' . implode('","', array_map('mysql_real_escape_string', $data)) . '"'
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

13 
implode(',', $array);
Improve this answer

4 Comments

just don't forget to check that the array isn't empty first - otherwise your SQL will look like "WHERE id IN ()" which is an error.
This is a dangerous answer given that the stated use of the string is in an SQL statement. See troelskn's answer which demonstrates how you can escape all the array elements.
We don't know if he didn't get the data already sanitized. But it's true that somebody asking something that simple may need some advices on code security too ;-)
Read the OP comment. I only answered the question, no need to make needless assumptions
2

Make sure you pass the results through mysql_real_escape_string() before executing your query. This should prevent sql injection if you use implode() as others suggest.

And as nickf mentions, always check to make sure the array isn't empty or null first, and handle those cases. Since you are only dealing with int's, it wouldn't hurt to put some type checking in your assignments, otherwise you'll get sql errors if a string slips in somehow.

Improve this answer

Comments

1

Often this type of situation is people building an array from another table for use in a 2nd query.. If this is the case you can use a subquery to accomplish this.

Eg. UPDATE Table SET Column = Value WHERE ID IN ( SELECT ID FROM Table2 WHERE CONDITIONS )

Improve this answer

Comments

0

This is probably better if all ids should be numerical. Check that it consists of at least one integer with 

$ids = array_filter($ids, 'is_int');
if (!$ids) {
    //no valid ids returned.
    die('or something');
}
$sql .= '(' . implode(',', $ids) . ')';
Improve this answer

Comments