How to Use in Clauses with MySQLi Prepared Statements

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

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 statement : several WHERE clauses and WHERE IN (Array)

The important thing here is that you are calling bind_param() just once, with an array containing all of the parameters you'll need to bind, so your solution will be to just add the additional WHERE clause parameter onto your $array of values to bind. The IN() clause isn't a special case requiring call_user_func_array() separated from other parameters. You call it on all of them.

Something is missing though - bind_param()'s first parameter is a string of data types. All your types are i, so you'll need to use str_repeat() to create that.

// Eventually, this array will contain the other params too
$array = (1,2,3,4,5,6,7,8,9,10);

// This creates a string of ?,?,?,?... for the IN () clause    
$clause = implode(',', array_fill(0, count($array), '?'));

// Add the additional value onto the $array array
// so the last param is bound with the others.
$array[] = $this->getTime();

$types = str_repeat('i', count($array));

// The params passed to call_user_func_array() must have as references, each subsequent value. Add those in a loop, but start with the $types string
$params = array($types);
foreach ($array as $key => $value) {
   $params[] = &$array[$key];
}

if($request = $this->getConnexion()->prepare('SELECT col1, col2 FROM table WHERE col1 IN ('.$clause.') AND col2>=?') or die(mysqli_error($this->getConnexion()))) {

    // Then bind_param() is called on all parameters
    // using the $params array which includes types and param references
    call_user_func_array(array($request, 'bind_param'), $params);

    // Execute & fetch.
    $request->execute();
    $request->bind_result($col1, $col2);
    $request->store_result();

    // Following the code
}

How to use SQL LIKE clause with mysqli prepared statements

Try this:

$link = \mysqli_connect("127.0.0.1", "user", "password", "dbname");

if (!$link) {
    $error = \mysqli_connect_error();
    $errno = \mysqli_connect_errno();
    print "$errno: $error\n";
    exit();
}

$query = "SELECT * FROM tblSchools WHERE systemName LIKE ?";

$stmt = \mysqli_stmt_init($link);
if (!\mysqli_stmt_prepare($stmt, $query)) {
    print "Failed to prepare statement\n";
    exit;
} else {
    $sysName = 'sys%';
    \mysqli_stmt_bind_param($stmt, "s", $sysName);

    \mysqli_stmt_execute($stmt);
    $result = \mysqli_stmt_get_result($stmt);
    $row = mysqli_fetch_array($result);
    var_dump($row);
}

it works for me

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.

php mysqli prepared statements select

I told you to limit your select function to a simple primary key lookup. And now you opened a can of worms. As a result you are getting entangled implementation code and unreadable application code.

$table, $args, $sort, $order, $clause

What all these variables are for? How you're going to call this function - a list of gibberish SQL stubs in a random order instead of plain and simple SQL string? And how to designate a list of columns to select? How to use JOINS? SQL functions? Aliases? Why can't you just write a single SQL statement right away? You already have a function for selects, though without this barbaric error reporting code you added to it:

function prepared_query($mysqli, $sql, $params, $types = ""){
    $types = $types ?: str_repeat("s", count($params));
    $stmt = $mysqli->prepare($sql)) { 
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    return $stmt;
}

Just stick to it and it will serve you all right.

$sql = "SELECT * FROM `teste_table` WHERE id = ? AND username = ?";
$stmt = prepared_query($mysqli, $sql, [$id, $name]);
$row = $stmt->get_result()->fetch_assoc();

The only specific select function could be, again, a simple primary key lookup:

function crud_find($conn, $table, $id)
{
    $table = escape_mysql_identifier($table);
    $sql = "SELECT * FROM $table WHERE id=?";
    $stmt = prepared_query($conn, $sql, [$id], "i");
    return $stmt->get_result()->fetch_assoc();
}

And for the everything else just use a generic function with native SQL.

---

Related Topics