0

I'm working non-stop since too many hours (more than 14 -.-") and im falling asleep. Maybe this is the reason why I wasted more than 2 hours in trying to understand why my PDO query is not working. Here's the query:

$query = $mysql->prepare('SELECT * FROM table '.$where.'');
$query->execute($clauses);

Before it of course I define $where and $clauses:

$where["country"] = "country IN (:country)"; 
$clauses["country"] = "'".implode("','",array_keys($countries))."'"

Every row of the table has a 'country' column with country ISO code (e.g. FR, ES, IT, DE, US...). My $countries contains a key => value array like this one:

Array
(
    ES => Spain
    IT => Italy
    DE => Germany
)

In conclusion as you clearly understand this is my $where:

country IN (:country)

Meanwhile my $clauses is:

'ES','IT','DE'

In other words the complete query string is:

country IN ('ES','IT','DE')

And don't worry, of course there's the WHERE statement in front of it. The query explained is this one:

SELECT * FROM table WHERE country IN ('ES','IT','DE')

It works on PHPMyAdmin but PDO always returns an empty array and I'm 99% sure that it's due to the fact that inside IN() there's nothing. I know this because if I change IN() into NOT IN() the query returns all rows of database. NOT IN(null) - yes, it's pseudo code I know it - means "everything" to my eyes. At this point I'm shocked.

Please if you think that the script strange or if you find it stupid or if you think that the problem is somewhere else don't focus on this. It's just an example. What you see here is not the real code and no, there's nothing wrong in other places. I've already debugged everything. Thank you for your time.

user1274113
  • 436
  • 8
  • 21
  • if you have a way to get the final query, debug it and check, and of course turn on error reporting and turn on pdo errors as well – Kevin Mar 28 '15 at 04:21
  • 3
    The SQL that you are passing to `prepare()` concatenates into it the string value of `$where`, which is an array! Quite apart from that, you can't parameterise entire arrays with a single placeholder—each placeholder can only represent SQL literals, which are limited to basic data types. See [Can I bind an array to an IN() condition?](http://stackoverflow.com/q/920353) for possible solutions. – eggyal Mar 28 '15 at 04:22
  • @Ghost: nop, there's no error. The query runs fine. – user1274113 Mar 28 '15 at 04:29
  • @eggyal: omg -.-" true! Why I was doing it!? Daaamn I wasted hours on this stupid thing. Thank you. I really need to sleep. Thank you again. – user1274113 Mar 28 '15 at 04:29

1 Answers1

1

since you must use many placeholders not one, you can't use named ones in this case, since you do not know array values. You could use assoc array and build query in a loop that way using named parameters. But for simplicity IN clause in PDO works like this:

$quest = implode(',',array_fill(1,count($ar),'?'));

here we made needed number of unknown placeholders: ?,?,?,? ...

$col = 'cats';
$table = 'pets';
$sql = sprintf('DELETE FROM %s WHERE %s IN (%s)', $table, $col, $quest);

here we put together valid SQL for deletion using sprintf

then prepare & execute

return $this->handler->prepare($sql)->execute($ar);
animaacija
  • 170
  • 9
  • 25