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

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 ?

CodePudding user response：

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