PDO prepared a state not giving the same result as manual request
I am using PHP and PDO instructions to access the database. I have this prepared query that I want to execute (multiple times through a foreach loop, but I don't see if it really affects this):
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 use PDO execute with variables in an array to execute that statement, e.g .:
$values = Array(2, 1, 2, 1); // Normally it variables here
$stmt->execute($values);
It does, but the weird thing is that it inserts a line with values (2, 2) into forum_access. The weird thing is that when I run the SQL query with variables manually, like:
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 the row with the values (2, 1).
I expect it to have something to do with how PDO / MySQL handles prepared statements. I am more or less new to prepared statements and have no idea what is going on here. Hopefully someone else can shed some light on this.
Notes: I have reasons to use a rather complex insert ... select query instead of insert ... on a duplicate key. These may not be ideal reasons, but good enough that you are not interested in proposals to fundamentally change the query.
Using PHP 5.3 and MySQL 5.0 on a WAMP server.
source to share
I solved the problem myself. MySQL seems to get confused by the insert values from the select statement and mix them up. I tried to call 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 so it really works.
I'm still not sure why it should work. But my immediate concern is to get a working app, so I'm happy for now.
Thanks for your help and sorry to disturb what I could and decided myself.
source to share
$SQL = 'insert into forum_access (forum_id, user_id)
select *
from (select :forum_id1, :user_id1) as tmp
where not exists (
select *
from forum_access
where forum_id = :forum_id2 and user_id = :user_id2)
limit 1';
$forum_id1 = 2;
$user_id1 = 1;
$forum_id2 = 2;
$user_id2 = 1;
$stmt = $dbh->prepare($SQL);
$stmt->bindParam(':forum_id1', $forum_id1, PDO::PARAM_INT);
$stmt->bindParam(':user_id1', $user_id1, PDO::PARAM_INT);
$stmt->bindParam(':forum_id2', $forum_id2, PDO::PARAM_INT);
$stmt->bindParam(':user_id2', $user_id2, PDO::PARAM_INT);
$stmt->execute();
source to share