Mysqli Prepared Statements with in Operator

MySQLi prepared statements with IN operator

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();
?>

mysqli prepared statements with IN operator and one more placeholder

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.

How do you use IN clauses with mysqli prepared statements

Look at the answer to a similar question that has been asked here before (second code sample):

I have an array of integers, how do I use each one in a mysql query (in php)?

It boils down to:

• create the SQL string with the right amount of question marks
• use call_user_func_array() to bind your array to the query string

Use an array in a mysqli prepared statement: `WHERE .. IN(..)` query

you could do it this way:

$ids = array(1,5,18,25);

// creates a string containing ?,?,? 
$clause = implode(',', array_fill(0, count($ids), '?'));

$stmt = $mysqli->prepare('SELECT * FROM somewhere WHERE `id` IN (' . $clause . ') ORDER BY `name`;');

call_user_func_array(array($stmt, 'bind_param'), $ids);
$stmt->execute();

// loop through results

Using this you're calling bind_param for each id and you have sorting done by mysql.

Building Multiple LIKE Operator Prepared Statement at run-time

Consider building your LIKE expression portion of prepared SQL statement using implode. Then build a param arguments to be run with call_user_func_array().

$terms = explode(",", str_replace(",", " ,", $_POST['txtD']));

// PREPARED STATEMENT BUILD
$likes = [];
foreach($terms as $t) {
    $likes[] = "col3 LIKE CONCAT('%', ?, '%')";
}

$expr = implode(" or ", $likes);
$sql = "select col1, col2, col3 from tbl ".
       "where col1=? and col2 between ? and ? and (". $expr .")";

// PARAM ARG BUILD
$type = 'iii' . str_repeat("s", count($terms));
$sql_params = array_merge(array($stmt, $type, $param1, $param2, $param3), $terms);

// PREPARE AND EXECUTE QUERY
$stmt = mysqli_prepare($con, $sql);
call_user_func_array('mysqli_stmt_bind_param', sql_params);    
mysqli_stmt_execute($stmt); 
mysqli_stmt_close($stmt);

SQL and Param Build Demo

---

Alternatively, consider MySQL's REGEXP for a regex expression using pipes to denote OR logic:

// REPACE COMMAS BY PIPE
$terms = str_replace(",", "|", str_replace(",", " ,", $_POST['txtD']));

$sql = "select col1, col2, col3 from tbl " .
       "where col1=? and col2 between ? and ? and col3 regexp ?";

// PREPARE AND EXECUTE QUERY
$stmt = mysqli_prepare($con);    
mysqli_stmt_bind_param($stmt, "iii", $param1, $param2, $param3, $terms);
mysqli_stmt_execute($stmt); 
mysqli_stmt_close($stmt);

Do note as mentioned here REGEXP is slower in execution than the equivalent LIKE expression.

using mysqli prepared stmt within if condition with || and && operator

Instead of doing so much complex code, use single query to do your job

//validating email
if(!filter_var($user_email, FILTER_VALIDATE_EMAIL) === true) {
    echo "invalid email";
    exit();
}
else{

    $stmt = mysqli_prepare($conn, "SELECT * FROM users WHERE user_name = ? OR user_email = ?");
    mysqli_stmt_bind_param($stmt, 'ss', $user_name, $user_email);

   if( !mysqli_stmt_execute($stmt)){
       echo "stmt execution failed";
       exit();

   }else{

        $result_name = mysqli_stmt_store_result($stmt_name);
        $result_email = mysqli_stmt_store_result($stmt_email);
        //rows
        $row_name = mysqli_stmt_num_rows($stmt_name);
        $row_email = mysqli_stmt_num_rows($stmt_email);
        echo $row_name;
        echo $row_email;
   }
}

Issue with LIKE/OR operator in mysqli select statement in a prepared statement

While I'm not very familiar with parameter binding, shouldn't you be specifying a type for each argument, i.e.

$stmt->bind_param('ss', $var1, $var2);

Correct way to use LIKE '%{$var}%' with prepared statements?

Try this

$likeVar = "%" . $yourParam . "%";
$stmt = $mysqli->prepare("SELECT * FROM REGISTRY where name LIKE ?");
$stmt->bind_param("s", $likeVar);
$stmt->execute();

you need to prepare the query using simply ? then you bind the param using bind_param.

Combine LIKE and IN in PHP MySQLi prepared statement

The like requires wildcards to have loose matching.

e.g.

select * from table where a like 'b'

is the same as:

select * from table where a = 'b'

so a record of b would be found but abc would not.

From the manual:

With LIKE you can use the following two wildcard characters in the pattern:

% matches any number of characters, even zero characters.

_ matches exactly one character.

So to find abc you'd use:

select * from table where a like '%b%'

For prepared statements the wildcards get appended to the variable, or in the binding, NOT in the query itself. Example 6 on the PDO manual page shows this. http://php.net/manual/en/pdo.prepared-statements.php#example-991 (after the comment // placeholder must be used in the place of the whole value)

---

Related Topics