CODEWITHSUNDEEP
phpmysqlpdo
Anders Fejerskov
Anders Fejerskov

Reputation: 55

PDO bindParam not working as expected

This has been bugging for a long time, and I still can't figure out what i'm doing wrong. In the code I want to select a few users with a comma separated string. The string will always be legit and valid.

In the first example, the one I would like to use, uses bindParam to assign the value of $postId to the SQL query. I have been using bindParam() for lots of other calls, but in this specific case, it fails.

$postId = "1,2,3";

$stm = $this->db->prepare('SELECT * FROM posts WHERE find_in_set(userId, "?") ORDER BY id DESC');
$stm->bindParam(1, $postId, PDO::PARAM_STR);
$stm->setFetchMode(PDO::FETCH_ASSOC);
$stm->execute();
$results = $stm->fetchAll();   
return print_r($results,true);

This code returns:

array (
)

In this other code which I really wouldn't like to use, I just pass the value of $postId right into the sql query.

$stm = $this->db->prepare('SELECT * FROM posts WHERE find_in_set(userId, "'.$postId.'") ORDER BY id DESC');
$stm->setFetchMode(PDO::FETCH_ASSOC);
$stm->execute();
$results = $stm->fetchAll();   
return print_r($results,true);

This code returns all the rows it is supposed to retrieve.

My question is; What is the specific problem and how can I avoid doing this again?

Upvotes: 1

Views: 1038

Answers (2)

AlexP
AlexP

Reputation: 9857

You don't need to add the double quotes "?" when referencing the value 

'SELECT * FROM posts WHERE find_in_set(userId, "?") ORDER BY id DESC'

Should be

'SELECT * FROM posts WHERE find_in_set(userId, ?) ORDER BY id DESC'

Upvotes: 1

Fluffeh
Fluffeh

Reputation: 33502

You shouldn't have quotes around the placeholder in yout query:

$stm = $this->db->prepare('SELECT * FROM posts WHERE find_in_set(userId, ?) ORDER BY id DESC');

See additional docs here.

Although it's not directly related to the question, it's also a handy habit to get into to use named params. When you have only one param to pass, it's not too bad, but when you start getting five or so question marks in the query, it's MUCH easier to actually read if you used named params:

SELECT * FROM posts WHERE find_in_set(userId, :someID) ORDER BY id DESC

Then you bind them as named params in your code:

$sth->bindParam(':someID', $postId, PDO::PARAM_STR);

Upvotes: 4

Related Questions