0

I want to use queries like that: 

$sql = "SELECT `name` FROM `table` WHERE `id` IN (?)";

and bind array of values

$sth = $pdo->prepare($sql);
$sth->execute(array(array('1', '2', '4')));

So, I must wrap PDO. How to implement this behavior and consider all it pitfalls?

It must consider:

IN(?) with array values;
IN(?) with empty array;
NOT IN(?) with array values;
NOT IN(?) with empty array;
NOT (expr IN (?)) with array values;
NOT (expr IN (?)) with empty.

The problem becomes what we have an empty array. with simple in i can replace in this situation wildcard to null. But what i must to do if i have NOT IN(?) and empty array?

The hot point is using NOT IN (?) with empty array.

sectus
  • 15,605
  • 5
  • 55
  • 97
  • @Aiias, answers not full enough, and i cannot clarify the original question. – sectus Mar 30 '13 at 16:28
  • You have to construct a query string for each value in the array. If your array is empty, that should be taken care of before it passes any data to the query. – Aiias Mar 30 '13 at 16:31
  • The answers suggested are very comprehensive. You need to explain how they are deficient. What do you mean by: "i cannot clarify the original question."? – Captain Payalytic Mar 30 '13 at 16:31
  • @Aiias, i must throw an exception with empty array? – sectus Mar 30 '13 at 16:33
  • @Aiias it is a simple query. Imagine that the request would be more complicated. – sectus Mar 30 '13 at 16:41
  • You should always process the logic behind your parameters before passing them to SQL if they are potentially unnecessary. Otherwise it makes bugs very hard to find when you're dealing with several arguments to a query. – Aiias Mar 30 '13 at 16:42
  • @Aiias it will not. Neither of these 2 queries will return what you expect. – Your Common Sense Mar 30 '13 at 16:42
  • @Aiias, i want to use array as type of data. No one string can bring error to query logic. I want it with array too. – sectus Mar 30 '13 at 16:46
  • @CaptainPayalytic, look at YourCommonSense answer. He describe my problem more clear. – sectus Mar 30 '13 at 16:53

1 Answers1

2

The problem becomes what we have an empty array.

That's quite interesting problem that took me some time to investigate.

After some extremely interesting debates with help of my friends I came to conclusion to pass NULL into IN() statement in case if array is empty. It makes pretty sensible behavior and doesn't make query to fail.

However, one issue is still open:

Although IN(NULL) returns FALSE and it seems quite sensible, NOT IN(NULL) returns... FALSE again! So, you have to be careful wit this kind of statement.

For my own implementation I am using conditional query building as a workaround, adding whole NOT IN() statement only in case of non-empty array. It makes the logic solid, though at the cost of the extra code:

$in = '';
if ($array) {
    $in = $db->parse(" AND col NOT IN(?a)", $array);
}
$data = $db->getAll("SELECT * FROM t WHERE name=?s ?p", $name, $in);

(compare it with concise statement in case of straight IN):

$data = $db->getAll("SELECT * FROM t WHERE name=?s AND col IN(?a)", $name, $in);

which will return no rows if array is empty.

So, I am still open to suggestions on how to solve the NOT IN problem more elegant way.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • will "SELECT `name` FROM `table` WHERE `id` NOT IN (null);" give me correct result? – sectus Mar 30 '13 at 16:36
  • depends on what you call 'correct'. I was in hurry writing this answer before it got closed. Now it's pretty finished. – Your Common Sense Mar 30 '13 at 16:38
  • "only in case of non-empty array" -- so, it can bring error in logic. I want to avoid it at all. On prepared statement layer. – sectus Mar 30 '13 at 16:43
  • @sectus quite contrary, it will make the logic solid, at the cost of extra code in the business logic layer, instead of prepared statement layer. If you find a better solution, please let me know. – Your Common Sense Mar 30 '13 at 16:52