MySQLi: searching for elements of array in a MySQL table

Question

I'm currently using the following code snippet to search though a DB

for($i=0; $i<sizeof($deleted_records);$i++)  { 

    if($stmt->prepare("SELECT `id`, `status` FROM `02-2012` WHERE `id` = ?")) {

    // Bind your variable to replace the ?
    $stmt->bind_param('s', $id);

    // Set your variable    
    $id = $deleted_records[$i];

    // Execute query
    $stmt->execute();

    // Bind your result columns to variables
    $stmt->bind_result($id_f, $stat_f);

    // Fetch the result of the query
    while($stmt->fetch()) {
        //echo $id_f . ' - ' . $stat_f . '<div>';
        array_push($hits, $id_f);

    }

}

where

$deleted_records

is a large array (basically trying to find all the occurrences of elements of the array in the '02-2012' table)

The problem with this approach is that it is veeeeery slow. I'm sure there are better/more elegant ways than this brute force approach.

Thanks for your time

Ofir Baruch · Accepted Answer · 2012-10-15T10:10:56.587

Instead of using a PHP loop and executing a query each time You can use IN clause and execute the query once.

The problem is we can't do something like:

if($stmt->prepare("SELECT `id`, `status` FROM `02-2012` WHERE `id` IN (?)")) {

we need to have a separate placeholder for every parameter you want to pass in Make sure that $deleted_records is an array. $qMarks is a string of placeholders ?,?,?,?,?.

$qMarks = str_repeat('?,', count($deleted_records) - 1) . '?';
//Suggested by @DonamiteIsTnt (http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition)

if($stmt->prepare("SELECT `id`, `status` FROM `02-2012` WHERE `id` IN ($qMarks)")) {

  $stmt->execute($deleted_records);
}

Didn't test.

MySQLi: searching for elements of array in a MySQL table

1 Answers1