2

I originally had an SQL statement, this:

SELECT *, COUNT(friend_one) AS pending_count , COUNT(friend_two) AS requests_sent   
FROM friends
WHERE friend_one OR friend_two = ?
AND status = ?

In which I assigned my parameters like :

$pending_friend_count_stmt->execute(array($user_id, $status_one));

However, the query was not getting the results I wanted. Someone showed me a different way of doing it, but it has the variable $user_id in it multiple times, so I do not know how to adjust the code to be able to use a parameter.

You can see the new query here:

http://rextester.com/KSM73595

Am I able to just do

SELECT COUNT(CASE WHEN `friend_one` = ? THEN 1 END) as `requests_count`,
       COUNT(CASE WHEN `friend_two` = ? THEN 1 END) as `pending_count`
FROM `friends`
WHERE ? IN ( `friend_one` , `friend_two` )
  AND `status` = ?

$pending_friend_count_stmt->execute(array($user_id, $user_id, $user_id $status_one));
Xorifelse
  • 7,878
  • 1
  • 27
  • 38
Paul
  • 3,348
  • 5
  • 32
  • 76

1 Answers1

7

Using PDO you have the ability to use named parameters, however in your question you want to use 1 parameters for multiple values and that means emulation has to be on:

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

Now you can do the following:

$stmt = $db->prepare("SELECT * FROM table WHERE userid = :userid AND userid = :userid");

$stmt->excecute([
  ':userid' => 1
]);

Resulting in:

"SELECT * FROM table WHERE userid = 1 AND userid = 1"
Xorifelse
  • 7,878
  • 1
  • 27
  • 38