How can I use array values in WHERE clauses?

Question

I'm trying to figure out how to use a array variable with a where clause.

when I echo $deader, I get 23,25,43,56,31,24,64,34,ect.. these are id numbers i want Updated

$sql = mysql_query("UPDATE users SET dead='DEAD' WHERE userID ='(".$deader.")' ");

The Array$deader has multiple values of id numbers, it only works and updates the first id# in the $deader Array.

I'm reading that Implode is what I need, but don't know how to get it into a functional format.

Have you tried echoing out the SQL you're generating and running it straight in the database? You should also look at moving away from `mysql_*` functions, as they're being deprecated. — andrewsi, Sep 12 '12 at 19:42
Assuming that `$deader` is an array, you can `implode()` the array using `,` as the separator and use the `IN` clause. — Palladium, Sep 12 '12 at 19:43
possible duplicate of [PHP/MYSQL using an array in WHERE clause](http://stackoverflow.com/questions/907806/php-mysql-using-an-array-in-where-clause) — Adarsh M Pallickal, Apr 24 '14 at 12:33

score 4 · Answer 1 · edited May 23 '17 at 11:58

4

Use WHERE ... IN

$sql = mysql_query("UPDATE users SET dead='DEAD' WHERE userID IN (".$deader.")");

Where $deader is in comma separated format. (for example: $deader = '143, 554, 32')

If it is an array you can use $deader = implode(',', $deader); to make it comma separated.

Note: Please stop using mysql_* functions for new code. The functions aren't maintained anymore and the community has begun the deprecation process. See here for more info about converting this to PDO: How do I convert a dynamically constructed ext/mysql query to a PDO prepared statement? (thanks to PeeHaa)

edited May 23 '17 at 11:58

Community

1
1

answered Sep 12 '12 at 19:43

dan-lee

14,365
5
52
77

If you're going to mention PDO, please use placeholders like [Mike Brant](http://stackoverflow.com/users/1529853/mike-brant)'s answer did. Including things like `$deader` in the query is reckless and shouldn't be considered a valid answer. – tadman Sep 12 '12 at 19:59
@tadman That isn't an example of PDO, but the link he provided contains information about converting the old mysql_ to PDO as stated. – PeeHaa Sep 12 '12 at 20:03
1

@tadman It would require some extra string work to get it done with `WHERE .. IN` statements in PDO. Here's an example how to do it: [PDO with “WHERE… IN” queries](http://stackoverflow.com/a/2373756/612202). You're free to edit :) – dan-lee Sep 12 '12 at 20:05
One extra line of code is a small price to pay. That link is a great example. – tadman Sep 13 '12 at 05:45

score 1 · Answer 2 · answered Sep 12 '12 at 19:45

If $deader is some sort of string of values, you will need to use MySQL IN() condition. Like this

UPDATE users SET dead = 'DEAD" WHERE userID IN ('?', '?', '?')

Where ? are your values. If userID as an INTEGER field, you can omit the single quotes around the values, if it is a string field, they would be required.

score 0 · Answer 3 · answered Sep 12 '12 at 19:44

0

I think what you're looking for is the IN keyword in SQL.

UPDATE users set dead='DEAD' where userID in (100,101,102)

answered Sep 12 '12 at 19:44

Ed Manet

3,118
3
20
23

rwhite · Answer 4 · 2012-09-13T18:56:36.760

Using MySQLi instead of mysql_*

require_once('.dbase'); //contains db constants DB_NAME, DB_USER etc
//using PHP built in connection class mysqli

$mysqli = new mysqli(DB_HOST,DB_UNAME,DB_UPWORD,DB_NAME);

if ($mysqli->connect_errno){
    $err = urlencode("Failed to open database connection: ".$mysqli->connect_error);
    header("Location: error.php?err=$err");
exit();
    }
$deader=implode(',',$deader); //assumes array, sting "143,554,32"
if ($stmt = $mysqli->prepare("UPDATE users SET dead='DEAD' WHERE userID IN (?)"){
    //bind variable to statement object
    $stmt->bind_param('s',$deader) //var type[string],var to bind
    //execute query
    $stmt->execute();
    //feedback
    $rowsAffected = $stmt->affected_rows(); //update doesn't return a result set.
    //close statement object
    $stmt->close();
}
$mysqli->close();

You guys are hammering on Rickos for using mysql_* but not explaining how to do it otherwise, my point was simply showing how to use mysqli. A prepared statement isn't necessary, but since you marked my comment down (peehaa) for not showing it as a prepared statement, here it is edited as a prepared statement. And it does answer his questions.

mysqli isn't a silver bullet. I.e. it's not like I add an `i` and it is good. The statement should be changed to a prepared statement to be any safer than what OP currently has. And besides this doesn't answer OP. — PeeHaa, Sep 12 '12 at 20:03

How can I use array values in WHERE clauses?

4 Answers4