• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

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

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
afflik1923
Asked:
afflik1923
  • 3
  • 3
2 Solutions
 
psadacCommented:
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
 
afflik1923Author Commented:
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
 
psadacCommented:
have you removed the parameters in the execute() statement ?

$st->execute();
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
afflik1923Author Commented:
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
 
psadacCommented:
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
 
afflik1923Author Commented:
Great stuff. Worked perfect. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now