?
Solved

PHP PDO mysql - cannot get the prepare statement to work - what's wrong?

Posted on 2009-02-08
6
Medium Priority
?
455 Views
Last Modified: 2013-12-12
Hi,
I'm experimenting with starting to use PDO which seems great. However I cannot get the prepare statment to work.
I have shown a snippet of code below which does not return any results when I use the variable substitution feature but when I replace the "?" characters with actual values it works fine.

I cannot see what I'm doing wrong but I'm sure it is something obvious. Can anyone see?

Note that this code comes from wihtin a class hence the use of $this in the example.

Hope this is clear and thanks in advance.
//$sql= 'SELECT * FROM mytable LIMIT 50 OFFSET 0'; # works if I use this
$sql= 'SELECT * FROM mytable LIMIT ? OFFSET ?';
    try {
        $st = $this->db->prepare($sql);#
        $st->execute(array(50, 0));
    } catch (PDOException $e) {
        die($e->getMessage()); 
    }

Open in new window

0
Comment
Question by:afflik1923
  • 3
  • 3
6 Comments
 
LVL 14

Accepted Solution

by:
psadac earned 2000 total points
ID: 23585001
you have to specify a data type using bindValue() or bindParam()
    $sql= 'SELECT * FROM mytable LIMIT ? OFFSET ?';
    try {
        $st = $this->db->prepare($sql);#
        $st->bindValue(1, 50, PDO::PARAM_INT);
        $st->bindValue(2, 0, PDO::PARAM_INT);
        $st->execute();
    } catch (PDOException $e) {
        die($e->getMessage());
    }

Open in new window

0
 

Author Comment

by:afflik1923
ID: 23585195
I'm very confused. I did not know I had to use the bind value as not mentioend on the PHP page
http://uk2.php.net/manual/en/pdo.prepare.php

However even when I tried adding the two lines of code it still does not work.

Also I intilly tried binding the value using varibles
:limit :offset in place of the quesion marks and then passing the associative array bu still no luck.

Is there some external setting which can stop this working. Is it a problem that I'm using MySQL 4.1 (but am using PHP5)?
thanks
0
 
LVL 14

Expert Comment

by:psadac
ID: 23585248
have you removed the parameters in the execute() statement ?

$st->execute();
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:afflik1923
ID: 23585404
Ahh, OK yes that has worked. Thank you. Now the ideall thing would be to get the bind with named variables working.

Therefore:
$sql= 'SELECT * FROM mytable LIMIT :rowlimit OFFSET :rowoffset ';

Then make the execute work. Is there another step I have to do when using the above statement.

Thanks again!

0
 
LVL 14

Assisted Solution

by:psadac
psadac earned 2000 total points
ID: 23585468
the code below should do the trick :
$st->bindValue('rowlimit', 50, PDO::PARAM_INT);
$st->bindValue('rowoffset', 0, PDO::PARAM_INT);
$st->execute();

Open in new window

0
 

Author Closing Comment

by:afflik1923
ID: 31544272
Great stuff. Worked perfect. Thanks!
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month16 days, 12 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question