"mysqli prepared statements with in operator [duplicate]" Answer’s

0

I've recently found the solution for my question. Maybe it's not the best way to do it, but it works nice! Prove me wrong:)

<?php
$lastnames = array('braun', 'piorkowski', 'mason', 'nash');
$arParams = array();

foreach($lastnames as $key => $value) //recreate an array with parameters explicitly passing every parameter by reference
    $arParams[] = &$lastnames[$key];

$count_params = count($arParams);

$int = str_repeat('i',$count_params); //add type for each variable (i,d,s,b); you can also determine type of the variable automatically (is_int, is_float, is_string) in loop, but i don't need it
array_unshift($arParams,$int); 

$q = array_fill(0,$count_params,'?'); //form string of question marks for statement
$params = implode(',',$q);

$data_res = $_DB->prepare('SELECT `id`, `name`, `age` FROM `users` WHERE `lastname` IN ('.$params.')');
call_user_func_array(array($data_res, 'bind_param'), $arParams);
$data_res->execute();
$result = $data_res->get_result();
while ($data = $result->fetch_array(MYSQLI_ASSOC)) {
    ...
}

$result->free();
$data_res->close();
?>
Wednesday, March 31, 2021
 
hakre
answered 10 Months ago
0

A slight variation on Your Common Sense's answer, and something I didn't realise you could do (although it makes sense in a way)...

$cat=1;
$lastNames = $ids;

$count_params = count($lastNames);
$int = str_repeat('i',$count_params+1);
$q = array_fill(0,$count_params,'?');
$params = implode(',',$q);
$qry1=$dblink->prepare("SELECT * FROM course_details WHERE category=? and cat_id IN ( $params )");

$qry1->bind_param( $int, $cat, ...$lastNames);
$qry1->execute();
$qry1_res=$qry1->get_result();
while($rowset1=$qry1_res->fetch_array()){
        print_r($rowset1);
}

The only real different is the call to bind_param, rather than merging the item into the array, just list it as another parameter before using the array fill (...) from PHP 5.6+.

Update: From the comment, the version of PHP doesn't support splat :(... so going back to original...

$cat=1;
$lastnames = $ids;
$arParams = array();

array_unshift($lastnames,$cat);
foreach($lastnames as $key => $value)   {
    $arParams[] = &$lastnames[$key];
}

$count_params = count($arParams);
$int = str_repeat('i',$count_params);
array_unshift($arParams,$int);
$q = array_fill(0,$count_params-1,'?');
$params = implode(',',$q);

$qry1=$dblink->prepare("SELECT * FROM course_details WHERE category=? and cat_id IN ( $params )");
call_user_func_array(array($qry1, 'bind_param'), $arParams);
$qry1->execute();
$qry1_res=$qry1->get_result();

while($rowset1=$qry1_res->fetch_array()){
    print_r($rowset1);
}

This adds the category into the list of items, but note the array_fill() uses count-1 as the ? for the cat is already there.

Saturday, May 29, 2021
 
fhonics
answered 8 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :