1

I have the following code which does not work and i don't understand why.

Could anybody help me with PDO's subtleties ?

<?php

$stmt = $con->prepare("SELECT DISTINCT title FROM articles WHERE concat(id,'-',value) IN (?)");
$stmt->bindParam(1, $concat);

//Works
$concat = "1-4";
$stmt->execute();
$results = $stmt->fetchAll();
var_dump($results);

//Does not work
$concat = "'1-4','1-5'";
$stmt->execute();
$results = $stmt->fetchAll();
var_dump($results);

Thank you :)

ena
  • 85
  • 7
  • This can be useful: http://stackoverflow.com/questions/4155873/find-in-set-vs-in – Lajos Veres Jan 10 '14 at 10:16
  • Well, there is no link with PDO ? I've tried to hard code the variable and it works. – ena Jan 10 '14 at 10:22
  • That's because the query is correct but it isn't the one you would expect. You are in fact comparing with a list of one element that has the value `'1-4', '1-5'`. Please check the answer I posted – mishu Jan 10 '14 at 10:27

1 Answers1

1

The value you bind is used as a single string parameter. So the second time you are not providing a list, you are providing a string that has a comma inside.

If you would "translate" the queries that get executed at a database level you would have:

SELECT DISTINCT title FROM articles WHERE concat(id, '-', value) IN ("1-4")

and this is a valid SQL query. But the second time you would have something similar to:

SELECT DISTINCT title FROM articles WHERE concat(id, '-', value) IN ("'1-4', '1-5'")

and since no concatenation is equal to the only element in your list ('1-4', '1-5') you won't get any results

mishu
  • 5,347
  • 1
  • 21
  • 39
  • Oh, i see. Do you know any way to get over it ? – ena Jan 10 '14 at 10:28
  • If you are absolutely sure that the string is "clean" and it has only elements in the format - you might concat it to the query. Or maybe the best/safest solution would be to add a variable number of placeholders (? signs) and iterate an array of elements and bind each one – mishu Jan 10 '14 at 10:30
  • In fact, these 1-4, 1-5 etc come from a CSV file. I was concating it before sending it to the query. I think your variable number of placeholders might be the best solution, except i don't know how to use it. :p But i'll search it, thank you for your quick reply ! – ena Jan 10 '14 at 10:33
  • I made a google search for "pdo bind list of elements" and I found this SO thread: http://stackoverflow.com/q/920353/521598 it seems that you might find some help there.. the answer added by DonamiteIsTnt is very similar to what I was suggesting – mishu Jan 10 '14 at 10:37
  • Oh. The problem is, I prepare the query once for all. I mean, i have several lines in my CSV file, and i apply my script on each, and each line has a variable of 'concat' values. – ena Jan 10 '14 at 10:40
  • Maybe if i put a 'maximal' number of '?' ? I mean, i don't think it's gonna be more than 10 values per line, so maybe if i put 10 '?' it will work ? But is it syntaxly correct, and what about the performances ? – ena Jan 10 '14 at 10:44
  • @ena I guess the performance depends a lot on different things in your script. But having the same PDOStatement to run every time might be an improvement. I guess that if you know the maximum number of elements it might be an idea to add that number of placeholders and fill in the remaining places for shorter arrays with dummy elements that will never match (empty string or anythying without a minus).. but I'm just guessing, you should test yourself – mishu Jan 10 '14 at 10:49
  • Performance shouldn't be a 'big' problem. I test it right now ! – ena Jan 10 '14 at 10:53
  • Well. It 'works', but i am limited to a finite amount of parameters. Worse, i'm obliged to fill all the parameters (i repeat the first given value). That is definitely not the solution i'm looking for :/ – ena Jan 10 '14 at 11:13