MySQL Prepared Statements With a Variable Size Variable List

MySQL Prepared statements with a variable size variable list

I can think of a couple solutions.

One solution might be to create a temporary table. Do an insert into the table for each parameter that you would have in the in clause. Then do a simple join against your temporary table.

Another method might be to do something like this.

$dbh=new PDO($dbConnect, $dbUser, $dbPass);
$parms=array(12, 45, 65, 33);
$parmcount=count($parms);   // = 4
$inclause=implode(',',array_fill(0,$parmcount,'?')); // = ?,?,?,?
$sql='SELECT age, name FROM people WHERE id IN (%s)';
$preparesql=sprintf($sql,$inclause);  // = example statement used in the question
$st=$dbh->prepare($preparesql);
$st->execute($parms);

I suspect, but have no proof, that the first solution might be better for larger lists, and the later would work for smaller lists.

---

To make @orrd happy here is a terse version.

$dbh=new PDO($dbConnect, $dbUser, $dbPass);
$parms=array(12, 45, 65, 33);
$st=$dbh->prepare(sprintf('SELECT age, name FROM people WHERE id IN (%s)',
                          implode(',',array_fill(0,count($parms),'?'))));
$st->execute($parms);

---

In Mysql Perpared statement increase the set variable length

It sounds like you are having an issue with the GROUP_CONCAT_MAX_LEN. You can adjust the length of this variable during your session.

The default length of this variable is 1024. You should be able to use:

set session group_concat_max_len = yourNewValue;

If you were to set this globally it would impact all other connections, that is why you might only want to set it for your session.

PreparedStatement with list of parameters in a IN clause

What I do is to add a "?" for each possible value.

var stmt = String.format("select * from test where field in (%s)",
                         values.stream()
                         .map(v -> "?")
                         .collect(Collectors.joining(", ")));

Alternative using StringBuilder (which was the original answer 10+ years ago)

List values = ... 
StringBuilder builder = new StringBuilder();

for( int i = 0 ; i < values.size(); i++ ) {
    builder.append("?,");
}

String placeHolders =  builder.deleteCharAt( builder.length() -1 ).toString();
String stmt = "select * from test where field in ("+ placeHolders + ")";
PreparedStatement pstmt = ... 

And then happily set the params

int index = 1;
for( Object o : values ) {
   pstmt.setObject(  index++, o ); // or whatever it applies 
}
   

   

Prepared Statement when not knowing how many parameters

A very good question. And the solution is very simple.

What you need is called argument unpacking operator. It will allow you to use an array of arbitrary length with bind_param. All you need is to collect accepted parameters into array, then create the types string dynamically and finally use the aforementioned operator:

$query = "SELECT name, id, age, address, phone_number from person_db WHERE 1=1 ";
$params = array();

if (isset($_REQUEST['name'])) {
   $query  .= " and name = ?";
   $params[] = $_REQUEST['name'];
}

if (isset($_REQUEST['age'])) {
   $query  .= " and age = ?";
   $params[] = $_REQUEST['age'];
}

if (isset($_REQUEST['address'])) {
   $query  .= " and address = ?";
   $params[] = $_REQUEST['address'];
}

if ($params)
    $stmt = $db->prepare($query);
    $types = str_repeat("s", count($params));
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $result = $stmt->get_result();
} else {
    $result = $db->query($query);
}

PHP MySQL Prepared Query using IN

There is no way to do that with a prepared statement. The only possibility is to do something like this:

$query = "SELECT * FROM fruitcart WHERE fruitname = ? OR fruitname = ? OR fruitname = ? ...

You can easily build a statement like this with an foreach loop.

But be aware that, since your array will probably have different amounts of values, this might cause some confusion in the database optimizer algorithms. For optimal performance you might want to prepare statements with for example 128, 64, 32, 16, 8, 4, 2, 1 slots and then use the biggest one you can fill until you got all your values from the database. That way the optimizer is able to deal with a much more limited amount of statement skeletons.

You can also use a temporary table for this. For example create a table that only contains the values (apple, banana, ...) and an id for your value set.

You can then insert the array of values into the database using a unique set-id (php offers a guid function for example) and then selecting them in a subquery:

$query = "SELECT * FROM fruitcart WHERE fruitname IN (SELECT fruitname FROM temptable WHERE setid = ?)"

That's easily preparable and will perform quite good.
You can use an in-memory table for the temptable so it will be very fast.

Multiple values in where with prepared SQL-Statements?

If you have an array of "something" that comes from the user, you can build a list of placeholders with array_fill, generate a string like "?, ?, ?, ..." by calling implode on the array. Alternatively you can make sure everything in the array is an integer (using intval, for example) and use it directly to build the query.

Use the IN directive to search with a prepared statement

The prepared statement has no parameters because you have interpolated the list into the statement before preparing it.

$array=array("item1","item2","item3","item4");
//This is dynamically filled, this is just an example
$in_list = "'".implode("','",$array)."'";//that's why i use implode

$stmt = $this->db->prepare('SELECT libelle,activite,adresse,tel,lat,lng FROM etablissements where type IN ('.$in_list.')');

At this point, the SQL statement you have created is:

SELECT libelle,activite,adresse,tel,lat,lng 
FROM etablissements where type IN ('item1','Item2','Item3','Item4')

Since the statement has no parameters, mysqli_stmt::bind_param fails. Instead of interpolating the items into the statement (which is vulnerable to injection), interpolate a string of parameters, then bind the values (which must be kept separate).

$array=array("item1","item2","item3","item4");

if (count($in_list) > 0) {
    $query = $this->db->prepare('SELECT libelle,activite,adresse,tel,lat,lng FROM etablissements WHERE type IN (' . str_repeat('?, ', count($in_list)-1) . '?)');

    $args = $in_list;
    array_unshift($args, str_repeat('s', count($in_list)));
    call_user_func_array(array($query, 'bind_param'), $args);
    $query->execute();
    $query->bind_result($libelle,$activite,$adresse,$tel,$lat,$lng);
}

PDO's interface for binding is more straightforward.

$array=array("item1","item2","item3","item4");

if (count($in_list) > 0) {
    $query = $this->db->prepare('SELECT libelle,activite,adresse,tel,lat,lng FROM etablissements WHERE type IN (' . str_repeat('?, ', count($in_list)-1) . '?)');

    foreach ($in_list as $i => $arg) {
        // query params are 1-based, so add 1 to the index
        // PDO::PARAM_STR is the default type, so no need to pass 3rd arg
        $query->bindValue($i+1, $arg);
    }
    $query->execute();
    // no need to bind the result
}

In fact, it can be even simpler with PDO, since PDOStatement::execute can take a list of parameter values:

$array=array("item1","item2","item3","item4");

if (count($in_list) > 0) {
    $query = $this->db->prepare('SELECT libelle,activite,adresse,tel,lat,lng FROM etablissements WHERE type IN (' . str_repeat('?, ', count($in_list)-1) . '?)');

    $query->execute($in_list);
}

PDO with WHERE... IN queries

Since you can't mix Values (the Numbers) with control flow logic (the commas) with prepared statements you need one placeholder per Value.

$idlist = array('260','201','221','216','217','169','210','212','213');

$questionmarks = str_repeat("?,", count($idlist)-1) . "?";

$stmt = $dbh->prepare("DELETE FROM `foo` WHERE `id` IN ($questionmarks)");

and loop to bind the parameters.

---

Related Topics