PDO not working as expected

Question

I have the follow PDO:

// Connect to PDO / $this->link = PDO
$this->statement = $this->link->prepare("
    SELECT p.*, pm.*
      FROM dev_permissions p, dev_permissions_menu pm
     WHERE pm.id = p.permission_menu_id
       AND pm.module = 'account'
       AND pm.controller IN('login', '*')
       AND pm.action IN('index', '*')
");
// Execute and verify number of rows

Which works, returning one row from $this->statement->rowCount().

But, trying to bind params does not work as expected:

// Connect to PDO / $this->link = PDO
$this->statement = $this->link->prepare("
    SELECT p.*, pm.*
      FROM dev_permissions p, dev_permissions_menu pm
     WHERE pm.id = p.permission_menu_id
       AND pm.module = :module
       AND pm.controller IN(:controller, '*')
       AND pm.action IN(:action, '*')
",

$this->statement->bindParam(':module', 'account', PDO::PARAM_STR);
$this->statement->bindParam(':controller', 'login', PDO::PARAM_STR);
$this->statement->bindParam(':action', 'index', PDO::PARAM_STR);
// Execute and verify number of rows

Thanks!

UPDATE:

Changed PDO::FETCH_ASSOC to PDO::PARAM_STR because CTRL + C/CTRL + V issues, and #__ to reflect correct database prefix.

Tested with the follow code:

$pdo = new \PDO('mysql:dbname=database;host=localhost', 'user', 'password');
$statement = $pdo->prepare("
    SELECT p.*, pm.*
      FROM dev_permissions p, dev_permissions_menu pm
     WHERE pm.id = p.permission_menu_id
       AND pm.module = :module
       AND pm.controller IN(:controller, '*')
       AND pm.action IN(:action, '*')
");
$statement->bindParam(':module', $request['module'], \PDO::PARAM_STR);
$statement->bindParam(':controller', $request['controller'], \PDO::PARAM_STR);
$statement->bindParam(':action', $request['action'], \PDO::PARAM_STR);
$statement->execute();

new \Core\Debug($statement->fetchAll());

And all works correctly. I can't find the problem with the other way..

It happens that `PDO::FETCH_ASSOC` and `PDO::PARAM_STR` both have the integer value 2, but you you ought to be using the correct constants. — Michael Berkowski, Jun 10 '12 at 01:31
I dont know both use same id, but, from my code, I have `PDO::PARAM_STR` — Gabriel Santos, Jun 10 '12 at 01:32
@Michael Thanks for help! Solved my problem changing bindParam to bindValue, because I am binding from a loop, so, bindParam allow only by reference values, which is not the cause inside a foreach. — Gabriel Santos, Jun 10 '12 at 03:01

keithhatfield · Answer 1 · 2012-06-10T01:42:26.573

1

The variable names are a little confusing in your example ... prepare is a method of the PDO object and it returns a PDOStatement Object ... try:

$sth = $this->statement->prepare("
    SELECT p.*, pm.*
      FROM #__permissions p, #__permissions_menu pm
    WHERE pm.id = p.permission_menu_id
     AND pm.module = :module
     AND pm.controller IN(:controller, '*')
     AND pm.action IN(:action, '*')
",

$sth->bindParam(':module', 'account', PDO::PARAM_STR);
$sth->bindParam(':controller', 'login', PDO::PARAM_STR);
$sth->bindParam(':action', 'index', PDO::PARAM_STR);

//execute the prepared statement
$sth->execute();

As a note ... rowCount on a select statement doesn't issue you're query ... it performs a COUNT(*) query with the same info as your select ... see example #2 at http://www.php.net/manual/en/pdostatement.rowcount.php

edited Jun 10 '12 at 01:42

answered Jun 10 '12 at 01:36

keithhatfield

3,273
1
17
24

Sorry, because I need to "minify" my code, to avoid confusions, I have made another confusion. Updated my revised code, to reflect my actual development code xD – Gabriel Santos Jun 10 '12 at 01:44
In that case, are you doing any error checking? Any errors being returned or exceptions being thrown? – keithhatfield Jun 10 '12 at 01:47
`$this->statement->errorInfo()` return nothing more then sucess (error code `00000`) – Gabriel Santos Jun 10 '12 at 01:49
`var_dump($this->_statement->fetchAll());` return empty array: `array empty` – Gabriel Santos Jun 10 '12 at 01:52
Thanks for help! Solved my problem changing bindParam to bindValue, because I am binding from a loop, so, bindParam allow only by reference values, which is not the cause inside a foreach. – Gabriel Santos Jun 10 '12 at 03:01

PDO not working as expected

1 Answers1