MySQL PDO with IN clause

Question

I'm in the process of switching all of my queries to PDO format and I'm having problems with one in particular that involves the IN() clause.

$nba[0] = "Boston Celtics";
$nba[1] = "New York Knicks";
$nba[2] = "Houston Rockets";

$query = "SELECT game_id
        FROM table
        WHERE date_int >= :date_int 
        AND (home_team = :team OR away_team = :team) 
        AND home_team IN(:list)
        AND away_team IN(:list)
        ORDER BY game_date_int ASC 
        LIMIT 1";               
$stmt = $db->prepare($query);
$stmt->execute(array(':date_int' => $limit, ':team' => $team, ':list' => implode(',', $nba)));

Answer 1

IN cannot be parameterized like other values. So just have to use implode the placeholders and the values. I have been thinking about trying to implement it in PHP for some while now though. However I never got further than thinking about it.

I also see you have the same named parameter (:list) twice in your query. This is also not possible if you use real prepared statements. Note that when you are using the mysql driver and PDO you have to disable emulated prepared statements.

Answer 2

You could solve this like this:

$nba = array();
$nba[0] = "Boston Celtics";
$nba[1] = "New York Knicks";
$nba[2] = "Houston Rockets";

$params = array(':date_int' => $limit, ':team' => $team);
$nba_teams = array();
for($i=0;$i<count($nba);$i++){
    $nba_teams[] = ':list' . $i;
    $params[':list' . $i] = $nba[$i];
}

$query = "SELECT game_id
    FROM table
    WHERE date_int >= :date_int 
    AND (home_team = :team OR away_team = :team) 
    AND home_team IN(".implode(',', $nba_teams).")
    AND away_team IN(".implode(',', $nba_teams).")
    ORDER BY game_date_int ASC 
    LIMIT 1";            

$stmt = $db->prepare($query, $params);
$stmt->execute();

Haven't tested it yet, but I think you know what I'm trying