Wednesday 5 September 2018

The prepared PDO state does not give the same result as the manual query

I'm using PHP and PDO prepared statements to access a database. I have this prepared query which I want to execute (multiple times through a foreach-loop, but I don't see that affecting this really):

insert into forum_access (forum_id, user_id) select * from (select ?, ?)
 as tmp where not exists (select * from forum_access
 where forum_id = ? and user_id = ?) limit 1

Then I'm using PDO execute with variables in an array to execute this statement, like this:
$values = Array(2, 1, 2, 1); // Normally it's variables here
$stmt->execute($values);

This executes, but the strange thing is that it inserts a row with values (2, 2) into forum_access. The really strange thing is that when I run the SQL query with variables manually inserted, like this:
insert into forum_access (forum_id, user_id) select * from (select 2, 1)
 as tmp where not exists (select * from forum_access
 where forum_id = 2 and user_id = 1) limit 1

it correctly inserts a row with values (2, 1).
I expect this to have something to do with the way that PDO/MySQL treats prepared statements. I'm more or less a novice at prepared statements and have no idea what is going wrong here. Hopefully someone else can shed some light on this.
Notes: I have reasons to use the quite complicated insert... select query instead of insert... on duplicate key. Maybe not perfect reasons, but good enough to not be interested in suggestions to fundamentally change the query.
Using PHP 5.3 and MySQL 5.0 on WAMP server.

I sort of solved the problem myself. It seems MySQL gets confused by the insert values from the select statement and mixes these up. I tried to name these values, like this:
insert into forum_access (forum_id, user_id) select * from
 (select ? as forum_id, ? as user_id) as tmp
 where not exists (select * from forum_access
 where forum_id = ? and user_id = ?) limit 1

and lo and behold, this actually works.
I'm still not certain why it should work. But my immediate concern is getting a working app, so for the moment I'm content.
Thanks for all your help and sorry to bother with something that I could and did solve myself.

0 comments:

Post a Comment