MySql prepared statements in a loop

Question

I want to check for the presence of stored array values ​​in a table. An array like this:

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

I do not want to embed the array in the request because it is not safe.

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

I want a prepared expression for security. I tried running 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 calls "Calling the member function bind_param () on an error other than an object." I am sure there is a better way to do this. Any suggestions?

Answer 1

This should give you a parameterized version of the 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 as a result and get all the ledgers that were found.