Passing multiple values as parameter to mysql query

Question

I have a database, and I want to get the names of multiple users in the same query by passing in an array of user_ids. Sadly, I cannot get this to work.

I have this query:

$stmt = $this->db->prepare('SELECT name FROM users WHERE user_id=?');

Where the parameter is an array:

$stmt->bind_param('i', $user_ids);

The user_ids array looks like this {1, 2}. Basically, I want to get the name of user 1 and user 2, without having to query the database more than one time.

When I have this code, I only seem to get the name of the first user, not the others:

$stmt->bind_result($name);
while ($stmt->fetch()) {
    array_push($names, $name);
}

Keep in mind that I have initialized $names like this $names = array();

How could I solve this?

Any help would be appreciated!

Mulan · Accepted Answer · 2014-04-06 08:58:28Z

1

You should use IN statement like this :

<?php
//Your array
$user_ids= array(1, 2);

$inQuery = implode(',', array_fill(0, count($ids), '?'));

$db = new PDO(...);
$stmt = $db->prepare('SELECT name FROM users WHERE user_id IN(' . $inQuery . ')');

// bindvalue is 1-indexed, so $k+1
foreach ($user_ids as $k => $id)
    $stmt->bindValue(($k+1), $id);

$stmt->execute();

Inspired by Can I bind an array to an IN() condition?

From the PHP docs

<?php
/* Execute a prepared statement using an array of values for an IN clause */
$params = array(1, 21, 63, 171);
/* Create a string for the parameter placeholders filled to the number of params */
$place_holders = implode(',', array_fill(0, count($params), '?'));

/*
    This prepares the statement with enough unnamed placeholders for every value
    in our $params array. The values of the $params array are then bound to the
    placeholders in the prepared statement when the statement is executed.
    This is not the same thing as using PDOStatement::bindParam() since this
    requires a reference to the variable. PDOStatement::execute() only binds
    by value instead.
*/
$sth = $dbh->prepare("SELECT id, name FROM contacts WHERE id IN ($place_holders)");
$sth->execute($params);
?>

edited Apr 6, 2014 at 8:58

Mulan

136k35 gold badges240 silver badges276 bronze badges

answered Apr 6, 2014 at 8:38

sdespont

14.1k9 gold badges61 silver badges99 bronze badges

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

3 Comments

Mulan Over a year ago

You don't have to bind the params with the foreach, You can just use $stmt->execute($user_ids);

Aleksander Over a year ago

When I bind the parameters in $stmt->execute($user_ids) I get this error mysqli_stmt::execute() expects exactly 0 parameters, 1 given

sdespont Over a year ago

@Alekplay, try the approach first and echo $inQuery, you should see "?,?" to declare that 2 parameters are expected.

Collectives™ on Stack Overflow

Passing multiple values as parameter to mysql query

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related