Geek Answers Handbook

MySQL PDO with IN clause

I am in the process of switching all my queries to the PDO format, and I am having problems with one, in particular, with 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)));
+4
source share
3 answers

You can solve it as follows:

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

Not tested, but I think you know what I'm trying

+4
source

IN cannot be parameterized like other values . So you just need to use implode placeholders and values. For some time I was thinking about trying to implement it in PHP. However, I never thought about him.

I also see that you have the same parameter ( :list ) twice in your request. It is also not possible if you use real prepared statements. Please note that when you use the mysql driver and PDO, you need to disable emulated prepared statements .

+6
source

PDO is very weak with such cases, so the task will be quite difficult.
Like any other API, PDO is good for basic tasks only from a beginner's guide and does not offer real developer assistance for any problems in real life.
Therefore, the developer must accept some kind of abstraction library to allow it to do all the dirty work.

So, I will give you a safeMysql example that 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);

Take a look - this code is neat, concise, and specific. It does only meaningful things, hiding all the dirty work of linking complex data inside. Unlike ugly codes, you can play with some PHP and API functions, this code is readable . It is important. You can tell what this code does even after a year or so.

NLZ's answer is a great example - the code is polluted with unnecessary and rather cryptic code.
When you are looking for your code, you are looking for business logic in the first place. And such useless code blocks, designed only to create a small part of the SQL query, will make you dizzy, hiding real questions from you.
In should move somewhere behind.

0
source

More articles:


All Articles