Convert MySQL to PDO - Query

Question

I have a query I wish to convert to be using PDO instead of mysql_:

$checkLikes = $sdb->query(sprintf("SELECT * FROM `likes`,`users`
                                  WHERE `likes`.`by` = `users`.`idu` 
                                  AND `likes`.`by` IN (%s) 
                                  ORDER BY `id` DESC 
                                  LIMIT %s", $subscriptions, 25));

The above query is using MySQL to get executed. I am trying to convert it to PDO:

$checkLikes = $sdb->prepare("SELECT * FROM `likes`,`users` 
                             WHERE `likes`.`by` = `users`.`idu` 
                             AND `likes`.`by` IN :subscriptions 
                             ORDER BY `id` DESC 
                             LIMIT :subscriptions", $subscriptions, 25);
$checkLikes->bindParam(":subscriptions",$subscriptions);
$checkLikes->execute();

The above code doesn't work, as I get these errors:

Warning: PDO::prepare() expects at most 2 parameters, 3 given in /home/user/public_html/header.php on line 411

Fatal error: Call to a member function bindParam() on a non-object in /home/user/public_html/header.php on line 412

What am I doing wrong? I've used bindParam() to bind the variable to the PDO query. I can't see the error.

What is not clear here?? `PDO::prepare() expects at most 2 parameters, 3 given` see the documentation, you'll understand. — walther, Oct 25 '14 at 14:47
http://stackoverflow.com/questions/1457131/php-pdo-prepared-statements — Ken, Oct 25 '14 at 14:47
No, `3 given` means `You gave 3`! Only need 1 in your case which is the query. Check my answer. — Kypros, Oct 25 '14 at 14:49

Kypros · Accepted Answer · 2014-10-25T15:09:11.837

1

First remove the second and third parameter you are sending to the ->prepare statement as only the query is needed:

$checkLikes = $sdb->prepare("SELECT * FROM `likes`,`users` WHERE `likes`.`by` = `users`.`idu` AND `likes`.`by` IN (:subscriptions) ORDER BY `id` DESC LIMIT :subscriptions_limit");

Then bind your two parameters and execute the query:

$checkLikes->bindParam(":subscriptions",$subscriptions, PDO::PARAM_STR);
$subscription_limit = 25;
$checkLikes->bindParam(":subscriptions_limit",$subscription_limit, PDO::PARAM_INT);
$checkLikes->execute();

and since you are using an IN you should also make sure that $subscriptions is a string representation of the array values and not an array in bindParam()

edited Oct 25 '14 at 15:09

answered Oct 25 '14 at 14:48

Kypros

2,997
5
21
27

1

Beat me to clicking the posting button by ~2 seconds. +1 – Daryl Gill Oct 25 '14 at 14:50
This gives me: Fatal error: Cannot pass parameter 2 by reference in 413 (The second bindParam()) – oliverbj Oct 25 '14 at 14:54
`bindParam` binds the parameter as a `reference` so we cannot specify the value directly, I updated the answer so that the value is passed through a variable `$subscription_limit`. – Kypros Oct 25 '14 at 15:01
Ah, ok got it! But the query has an error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''11,13' ORDER BY `id` DESC LIMIT 25' at line 1' in – oliverbj Oct 25 '14 at 15:01
That's probably because you are giving `$subscriptions` as an array i presume, since it is not shown in your code – Kypros Oct 25 '14 at 15:02
$subscriptions are printed out like this: return implode(',', $subscriptions); – oliverbj Oct 25 '14 at 15:06
Updated the answer so that the query now has `IN (:subscriptions)` since your string doesn't have `parenthesis` after the IN and tries to execute `IN 1,2,3` instead of `IN (1,2,3)` – Kypros Oct 25 '14 at 15:11
Problem with this answer is, that it won't work for a `WHERE ... IN ()` clause if you're using prepared (and not emulated) statements. – hakre Oct 25 '14 at 16:23

Convert MySQL to PDO - Query

1 Answers1