2

I feel like I'm losing my mind on this one. I have three simple tables. A user table, a roles table, and a role_user table that joins the user and roles in a many to many relationship.

I have the following code to the roles for a user:

$query = $pdo->prepare('select roles.* from roles inner join role_user on roles.id = role_user.role_id where role_user.user_id = ?');
$query->execute(array('1'));
die(var_dump($query->fetchAll()));

This returns an empty array. No results. However, if I change the code to this, I will get the user's roles:

$query = $pdo->prepare('select roles.* from roles inner join role_user on roles.id = role_user.role_id where role_user.user_id = 1');
$query->execute();
die(var_dump($query->fetchAll()));

Am I missing something totally obvious? Is there something about my SQL that is messing up the bindings? Why doesn't the example with bindings work?

TaylorOtwell
  • 7,177
  • 7
  • 32
  • 42
  • What if you say `array(1)`? The difference between a string and a number shouldn't matter with SQLite but... – mu is too short May 18 '11 at 05:08
  • @mu - Tried that. No results. This is really blowing my mind! I've done tons of PDO work before and I've never seen anything like this. – TaylorOtwell May 18 '11 at 05:15
  • 1
    What happens if you use [`bindParam`](http://www.php.net/manual/en/pdostatement.bindparam.php)to fill in the placeholders instead of supplying them to `execute`? – mu is too short May 18 '11 at 05:27
  • Man, if that makes a difference, I'm gonna eat a hat. `execute(array(...))` *should* be entirely identical to calling `bindParam`/`bindValue`. Hm. Have you [switched PDO to exception-throwing mode](http://us2.php.net/manual/en/pdo.error-handling.php)? Maybe there's another error there that's being hidden. – Charles May 18 '11 at 05:41
  • According to http://php.net/pdostatement.execute , if you pass an array of values to `execute`, all params will be treated as `PDO::PARAM_STR`. In MySQL it doesn't make a difference if you quote an int like you would quote a string. Maybe in SQLite it is different? What's the output if you quote your `id="1"` in the last example? – Carlos Campderrós May 18 '11 at 09:42
  • SOLVED: The data type in the SQLite table was not an integer, I guess it was a VARCHAR for whatever reason. Still don't know exactly why passing a string 1 didn't work. Changing the data type to Integer worked. – TaylorOtwell May 18 '11 at 13:10
  • 1
    @TaylorOtwell then publish your comment as answer and accept it to help future viewers – Carlos Campderrós May 18 '11 at 14:56

1 Answers1

2

This is a bug in PDO: http://bugs.php.net/bug.php?id=45259

As a workaround, the following code, though heavier, should work in PHP5.3:

$query = $pdo->prepare(
    'select roles.* from roles inner join role_user on roles.id = role_user.role_id '
    . 'where role_user.user_id = :id'
);
$query->bindValue(':id', 1, PDO::PARAM_INT);
$query->execute();
die(var_dump($query->fetchAll()));

The latest versions of SQLite have native prepared statements, but I don't think PDO can use them yet (IIRC, PDO's code has no real maintainer, so it does not evolve much). It probably won't change anything, but you could still try to disable the emulation with $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);