MySql prepared statements in a loop

Question

I wish to check for the occurrence of stored values of an array in a table. An array like this:

$myarray=array("122","123","124","125");

I don't want to implode the array in the query as it is not secure.

SELECT ledger FROM mytable WHERE ledger IN('".implode("','",$myarray)."')

I want to go for prepared statement for security. I tried to run the queries in a for loop, but it fails.

$not = sizeof($myarray);
for ($i = 0; $i < $not; $i++) {
    $qc = 'SELECT ledger FROM mytable WHERE ledger = ?';
    $st = $mysqli->prepare($qc);
    $st->bind_param("i", $myarray[$i]);
    $st->execute();
    $ro = $st->num_rows;
    if ($ro > 0){
        echo "number exists";
        break;
    }
}

This throws "Call to a member function bind_param() on a non-object" error. I am sure there is a better way to do this. Any suggestions?

Answer 1

This should give you a parameterized version of your original query.

$in = '';
$myarray = array('1', '2', '3');
foreach($myarray as $value) {
    $in .= '?, ';
}
//or $in = str_repeat("?, ", count($myarray)); in place of foreach
$query = 'SELECT ledger FROM mytable';
if(!empty($in)) {
    $in = '(' . rtrim($in, ', ') . ')';
    $query .= " where ledger IN $in";
}
echo $query;
//$st = $mysqli->prepare($query);
//$st->execute($myarray);
//$ro = $st->num_rows;

Output:

SELECT ledger FROM mytable where ledger IN (?, ?, ?)

Then you can do a fetch on the result and get all ledgers that were found.