5

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?

sridhar
  • 1,321
  • 5
  • 17
  • 26
  • chris85, I am looking if any of the values in the array exist in the table. If yes, which one. – sridhar Jan 05 '16 at 04:25
  • Did you try the bind_param with single-quotes, like `$st->bind_param('i', $myarray[$i]);` – vmachan Jan 05 '16 at 04:28
  • 1
    Don't do your `$qc = 'SELECT ...';` and `$st = $mysqli->prepare($qc);` inside the loop, as it defeats part of the purpose of doing a `->prepare()`. Only do the `->bind_param()`, `->execute()`, etc inside the loop. – Sean Jan 05 '16 at 04:34
  • possible duplicate.. http://stackoverflow.com/questions/6053239/mysqli-bind-param-with-an-array-for-in – check Jan 05 '16 at 04:41
  • @sean, I tried as suggested by you. There are records in the table matching the array values, yet num_rows outputs 0. Any idea why? – sridhar Jan 05 '16 at 05:11
  • So I assume you have resolved your *Call to a member function bind_param() on a non-object* error? Looking at the comments in the docs for `int $mysqli_result->num_rows;` -> http://php.net/manual/en/mysqli-result.num-rows.php#105289, have you tried doing `$st->store_result();` before `$ro = $st->num_rows;`? – Sean Jan 05 '16 at 05:22
  • Sean, thanks. That was it. store_result() solved the issue. – sridhar Jan 05 '16 at 05:29
  • Sean, in another such loop, only the first record gets inserted, remaining three fail. No errors are thrown up. What could be the issue? – sridhar Jan 05 '16 at 07:16

1 Answers1

4

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.

chris85
  • 23,846
  • 7
  • 34
  • 51