Passing multiple values using multiple IN statements using PDO

Question

I have had no trouble passing multiple values into a single IN statement, but when I have multiple IN statements in a single statement it is where I am struggling.

Below is how I am doing it for a single IN statement:

$sql = "SELECT *
FROM x
INNER JOIN y
ON x.id = y.d_id";

if (isset($_GET['c'])) {

    $catVals = explode(',', $_GET['c']);
    $catValsPlaceholder = implode(',', array_fill(0, count($catVals), '?'));

    $sql .= " WHERE y.category IN ($catValsPlaceholder)";

} else {

}

$sql .= " GROUP BY y.d_id
ORDER BY x.updated ASC
LIMIT 1000";

$sth = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

if(isset($_GET['c'])) {

    $sth->execute($catVals);
}

$result = $sth->fetchAll(PDO::FETCH_ASSOC);

But when I add another IN query I am unsure how to execute both using execute(). Some context, I pass vars from my App so c = a,b,c,d or d = value,value2,value3

if (isset($_GET['c']) && isset($_GET['d'])) {

    $catVals = explode(',', $_GET['c']);
    $catValsPlaceholder = implode(',', array_fill(0, count($catVals), '?'));

    $typeVals= explode(',', $_GET['d']);
    $typePlaceholder= implode(',', array_fill(0, count($typeVals), '?'));

    $sql .= " WHERE y.category IN ($catValsPlaceholder)";
    $sql .= " AND y.type IN ($typePlaceholder)";

}

I don't see how this is a PHP problem. It is rather an SQL problem. Also, keep in mind IN does not allow 0 elements, unless using a subquery. — oxygen, May 19 '16 at 10:08
The IN statements are built dynamically, so there will always be a value - I have emulated all my statements using pure SQL before hand and have no issues thus far - just when out putting in PHP — Dan Whiteside, May 19 '16 at 10:09

Gerald Schneider · Accepted Answer · 2016-05-19T10:21:27.860

To get this completely dynamic I'd do it like this:

$where = array();
$values = array();

if (isset($_GET['c'])) {
    $catVals = explode(',', $_GET['c']);
    $catValsPlaceholder = implode(',', array_fill(0, count($catVals), '?'));
    $where[] = "y.category IN ($catValsPlaceholder)";
    $values = array_merge( $values, $catVals );
}

if (isset($_GET['d'])) {
    $typeVals= explode(',', $_GET['d']);
    $typePlaceholder= implode(',', array_fill(0, count($typeVals), '?'));
    $where[] = "y.type IN ($typePlaceholder)";
    $values = array_merge( $values, $typeVals );
}

// add more conditions

if (sizeof($where > 0))
    $sql .= " WHERE " . implode( " AND ", $where );
}

// then, later:
$sth->execute( $values );

Passing multiple values using multiple IN statements using PDO

1 Answers1