4

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.

salep
  • 1,332
  • 9
  • 44
  • 93
Danielle
  • 61
  • 4

3 Answers3

2
$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();
Simone Nigro
  • 4,717
  • 2
  • 37
  • 72
  • 1
    Your proposal is to switch to named place-holders? Could you please elaborate on how that will make a difference? – Álvaro González Dec 27 '14 at 20:29
  • Unfortunately neither this solution nor the one proposed by Peter Darmis seems to do the trick. I have also tried exporting my app to a LAMP-server (PHP: 5.4.4, MySQL: 5.5.38), which resulted in an error 1060: Duplicate column name '?'. I'm not sure how to interpret this correctly but it seems to be in some way related to the strange insert values on the WAMP-server. The double ? in the insert is in some way jumbled in the prepared statement parsing, which gives an error on the LAMP and strange insert values on the WAMP. Still not sure how to solve it, though. – Danielle Dec 27 '14 at 20:50
  • 1
    Please use 'bindValue' rather than 'bindParam' as you can then use 'calculated' values. Only use 'bindParam' for 'large objects such as 'blob'. If you use 'bindParam' then you **must** pass a PHP variable with the required value in it. Has this changed since PHP 5.3? – Ryan Vincent Dec 28 '14 at 00:40
  • 1
    Does this even work? $stmt->bindParam(':forum_id1', 2, PDO::PARAM_INT) when it is run? I expect it to throw an error as you do not pass PHP variables to the 'bind'. Please tell me what version of PHP it works in. – Ryan Vincent Dec 28 '14 at 01:35
2

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.

Danielle
  • 61
  • 4
1

Change this

$values = Array(2, 1, 2, 1); // Normally it's variables here
$stmt->execute($values);

with this

$values = Array(2, 1, 2, 1);
for($i=0;$i<count($values);$i++) {
$stmt->bindParam(($i+1),$values[$i]); 
}
$stmt->execute();