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

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
– Markus AO, Commented Jan 28, 2022 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
– Markus AO, Commented Jan 28, 2022 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
– Kyv, Commented Jan 28, 2022 at 16:30
You could also consider doing all in one query. Make a join with the two tables in question. — slaakso
– slaakso, Commented Jan 28, 2022 at 16:36

Bill Karwin · Accepted Answer · 2022-01-28 17:02:05Z

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, 2022 at 17:02

answered Jan 28, 2022 at 16:31

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kyv Over a year ago

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.

Bill Karwin Over a year ago

I edited the code above. I forgot that array_unshift() modifies its argument instead of returning a new array.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related