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

possible duplicate of [PHP PDO: Can I bind an array to an IN() condition?](http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition) — MrCode, Feb 20 '13 at 08:44

score 6 · Answer 1 · edited May 23 '17 at 10:28

6

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.

edited May 23 '17 at 10:28

Community

1
1

answered Feb 20 '13 at 08:44

PeeHaa

71,436
58
190
262

2

+1 for pointing out illegal multiple usage of same parameter (:list) and for additional info on disabling emulated prepared statements. thanks PeeHaa – alds Jul 04 '14 at 03:22

score 4 · Accepted Answer · answered Feb 20 '13 at 09:23

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

This is what I'm looking for. A method to combine array and non-array parameters into one single associative array ($param) which can then be used with the execute() statement instead of using BindParam. Thanks NLZ! :) — alds, Jul 04 '14 at 03:17

Your Common Sense · Answer 3 · 2013-02-20T11:31:24.877

PDO is very weak with cases like this, so the task is going to be quite toilsome.
Like any other API, PDO is good for basic tasks from beginner's manual only, and offers no real help to developer for whatever real life issues.
So a developer have to adopt some sort of abstraction library to let it do all the dirty job.

So, I'll give you a safeMysql example which is better than PDO in any way:

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

$query = "SELECT game_id
        FROM table
        WHERE date_int >= ?i 
        AND (home_team = ?s OR away_team = ?s) 
        AND home_team IN(?a)
        AND away_team IN(?a)
        ORDER BY game_date_int ASC 
        LIMIT 1";               

$data = $db->getAll($query, $limit, $team, $team, $nba, $nba);

Look - this code is neat, concise and certain. It does only meaningful things, hiding all the dirty job of binding complex data inside. Unlike ugly codes you can get playing with some PHP and API functions this code is readable. This is important matter. You can tell what does this code do even after a year or so.

NLZ's answer is a perfect example - the code gets polluted with unnecessary and quite puzzling code.
When you're looking up your code, you're looking for the business logic in first place. And such useless code blocks, intended only to create a small part of SQL query, would make you dizzy, hiding the real matters from you.
In ought to be moved somewhere behind internals.

What does `$limit` do? Is there any way to use named paramaters considering you are talking about readability? Otherwise: the cake is a lie imho. — PeeHaa, Feb 20 '13 at 09:03

MySQL PDO with IN clause

3 Answers3