PHP - Issue with PDO when using concat

Question

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

This can be useful: stackoverflow.com/questions/4155873/find-in-set-vs-in — Lajos Veres
– Lajos Veres, Commented Jan 10, 2014 at 10:16
Well, there is no link with PDO ? I've tried to hard code the variable and it works. — ena
– ena, Commented Jan 10, 2014 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
– mishu, Commented Jan 10, 2014 at 10:27

mishu · Accepted Answer · 2014-01-10 10:22:37Z

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

answered Jan 10, 2014 at 10:22

mishu

5,3971 gold badge24 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

ena Over a year ago

Oh, i see. Do you know any way to get over it ?

mishu Over a year ago

If you are absolutely sure that the string is "clean" and it has only elements in the format <int>-<int> 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

ena Over a year ago

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 !

mishu Over a year ago

I made a google search for "pdo bind list of elements" and I found this SO thread: 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

ena Over a year ago

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.

|

Collectives™ on Stack Overflow

PHP - Issue with PDO when using concat

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related