How to pass an array to a MySQL query using multiple WHERE clauses

Question

I am querying data from MySQL database.

$tag = "category";
$sql = "SELECT tagsID FROM `tags` WHERE tag=:tag";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(":tag", $tag, PDO::PARAM_INT);
$stmt->execute();
$query1 = $stmt->fetchAll(PDO::FETCH_OBJ);

The first query gives the following result:

$arrTags = array(5) { [0]=> array(1) { ["tagsID"]=> string(2) "12" } [1]=> array(1) { ["tagsID"]=> string(2) "14" } [2]=> array(1) { ["tagsID"]=> string(2) "20" } [3]=> array(1) { ["tagsID"]=> string(2) "51" } [4]=> array(1) { ["tagsID"]=> string(2) "53" } }

The next query from another table is based on the tagsID of the previous query.
I tried to do that based on this post
1- Make an array with tagsID

$arrList = array();
foreach($arrTags as $t){
   array_push($arrList, $t['tagsID']);
}

output:

$arrList = array(5) { [0]=> string(2) "12" [1]=> string(2) "14" [2]=> string(2) "20" [3]=> string(2) "51" [4]=> string(2) "53" }

2- Query 2 (what actually does not work)

$active = 1;
$in = join(',', array_fill(0, count($arrList), '?'));
$sql = "SELECT * FROM `table` WHERE active=:active                 
                AND postID IN ($in)
                ORDER BY postID DESC";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(":active", $active, PDO::PARAM_INT);
$stmt->execute($arrList);
$output = $stmt->fetchAll(PDO::FETCH_OBJ);

Unfortunately, it does not work. What I am doing wrong?
I get the error

PDO: Invalid parameter number: mixed named and positional parameters

Is it possible to do it in a better way ?

Your `:active` is a named and your `?` are positional parameters, is what causes the error. You can't mix them. Choose one or the other, and then modify your code to match. — Markus AO, Jan 28 '22 at 16:11
I like to use named all the way. Just build named keys. Here's a copy-paste from a SQL query builder for WHERE clauses, see if you get the idea and can adapt. No time for a more thorough answer right now. ```foreach($data as $val) { $ctr++; $bind = ":w__{$ctr}"; $binds[$bind] = $val; } $bind_vars = array_keys($binds);``` ... this creates the named bind keys (as `$bind_vars`), which you put in your prepared statement, and the `$binds` contains the actual data. The `$ctr` is `static $ctr = 0;` at the start of the builder function, avoiding collisions in case of multiple calls per query. — Markus AO, Jan 28 '22 at 16:16
Thank you @MarkusAO for your reply. Suppose I use positional parameters everywhere. `$sql = "SELECT * FROM `table` WHERE active=? AND postID IN ($in) ORDER BY postID DESC";` . Do you have any idea of how I could combine them before the `$stmt->execute()` line ? — Kyv, Jan 28 '22 at 16:30
You could also consider doing all in one query. Make a join with the two tables in question. — slaakso, Jan 28 '22 at 16:36

Bill Karwin · Accepted Answer · 2022-01-28T17:02:05.123

2

Here's a solution that uses only positional parameters:

$active = 1;
$in = join(',', array_fill(0, count($arrList), '?'));
$sql = "SELECT * FROM `table` WHERE active=?                 
                AND postID IN ($in)
                ORDER BY postID DESC";
$stmt = $pdo->prepare($sql);
// make an ordinal array for all the positional parameters
// with $active as the first element.
$params = array_values($arrList);
array_unshift($params, $active);
$stmt->execute($params);
$output = $stmt->fetchAll(PDO::FETCH_OBJ);

edited Jan 28 '22 at 17:02

answered Jan 28 '22 at 16:31

Bill Karwin

538,548
86
673
828

Thank you for your answer @Bill. I get the following error on line `$params = array_unshift(array_values($arrList), $active);`: `Notice : Only variables should be passed by reference`, and on line `$stmt->execute($params);`, I get the error `Warning : PDOStatement::execute() expects parameter 1 to be array, int given in`. – Kyv Jan 28 '22 at 16:49
I edited the code above. I forgot that `array_unshift()` modifies its argument instead of returning a new array. – Bill Karwin Jan 28 '22 at 17:03

How to pass an array to a MySQL query using multiple WHERE clauses

1 Answers1