CODEWITHSUNDEEP
phpmysqlphpmyadmin
Rickos
Rickos

Reputation: 45

How can I use array values in WHERE clauses?

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.

Upvotes: 1

Views: 2251

Answers (4)

rwhite
rwhite

Reputation: 430

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.

Upvotes: -1

dan-lee
dan-lee

Reputation: 14492

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)

Upvotes: 4

Mike Brant
Mike Brant

Reputation: 71384

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.

Upvotes: 1

Ed Manet
Ed Manet

Reputation: 3178

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

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

Upvotes: 0

Related Questions