Mysql variables and multiple queries in PDO. UPDATE query doesn't work

Question

I select data from tab1 to a variable, and use it in another query, which updates tab1. Target column target is of type INT, so I round it before its set.

It works well in mysql workbench, also getting no error from php (error_reporting set to -1). It just doesn't really update the target column - it stays zero.

Any idea why?

$q = 'SELECT @tmp = ( SELECT t
                      FROM tab1
                      WHERE a_id = :id1 );
      UPDATE tab1
      SET target = ( SELECT ROUND( @tmp / ( SELECT COUNT(*)
                                            FROM tab2
                                            WHERE b_id = :id2 )));
      ';

$sth = $dbh->prepare( $q );
$sth->execute( array( ':id1' => $id, ':id2' => $id ) );

[*You cannot use a named parameter marker of the same name twice in a prepared statement*](http://php.net/manual/pdo.prepare.php). Also, to support multi-queries, you need to make sure the `PDO::ATTR_EMULATE_PREPARES` attribute is set to `true` — Phil, Jan 12 '14 at 22:39
`PDO::ATTR_EMULATE_PREPARES` is set to `1`. Also, just tried to rename named params, and still no success. Thanks a lot for your time though! — notnull, Jan 12 '14 at 22:44
Please reflect any code changes in your question above. Also, what error mode is your PDO connection using? — Phil, Jan 12 '14 at 22:45
I'd go with *exception* mode, just in case any warnings are being swallowed somehow — Phil, Jan 12 '14 at 22:52
By default user variables in MySql are disabled. You have to allow them in your connection. I'll see if I can find how in google. — Reactgular, Jan 12 '14 at 22:53
http://stackoverflow.com/questions/4683110/how-to-execute-mysql-script-with-variables-using-phppdo — Reactgular, Jan 12 '14 at 22:54
@Phil I switched to `PDO::ERRMODE_EXCEPTION`, but don't get anything at all. @MathewFoscarini I believe PDO of php 5.3+ should allow you to use multiple queries by default — notnull, Jan 12 '14 at 22:58
@MathewFoscarini I think you can do multi-queries with PDO_MYSQLND and `ATTR_EMULATE_PREPARES` set to `true`. — Phil, Jan 12 '14 at 23:04
User variables for MySQL.NET connector are disabled by default, but I can not confirm this is true for PDO/PHP. You'd have to run a simple `SET @t = 1; SELECT @t;` test. — Reactgular, Jan 12 '14 at 23:28
@Phil `You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.` your first comment is almost true. — Félix Adriyel Gagnon-Grenier, Apr 28 '14 at 02:21

Félix Adriyel Gagnon-Grenier · Answer 1 · 2014-06-13T20:30:33.663

When using SELECT with user variables, we must use a different assignation, which is :. be sure to have emulate prepares set to true.

$q = 'SELECT @tmp:= ( SELECT t
                  FROM tab1
                  WHERE a_id = :id1 );
      UPDATE tab1
      SET target = ( SELECT ROUND( @tmp / ( SELECT COUNT(*)
                                        FROM tab2
                                        WHERE b_id = :id2 )));
     ';
$sth = DB::query( $q );
var_dump($sth->fetch());
$sth->nextRowset();
var_dump($sth);

but mysql reference http://dev.mysql.com/doc/refman/5.0/en/user-variables.html states that we should set user-defined variables with SET

so in your case:

$q = 'SET @tmp = ( SELECT t
                      FROM tab1
                      WHERE a_id = :id1 );
      UPDATE tab1
      SET target = ( SELECT ROUND( @tmp / ( SELECT COUNT(*)
                                            FROM tab2
                                            WHERE b_id = :id2 )));
      ';

Mysql variables and multiple queries in PDO. UPDATE query doesn't work

1 Answers1