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. Also, to support multi-queries, you need to make sure the PDO::ATTR_EMULATE_PREPARES attribute is set to true — Phil
– Phil, Commented Jan 12, 2014 at 22:39
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
– Reactgular, Commented Jan 12, 2014 at 22:53
@MathewFoscarini I think you can do multi-queries with PDO_MYSQLND and ATTR_EMULATE_PREPARES set to true. — Phil
– Phil, Commented Jan 12, 2014 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
– Reactgular, Commented Jan 12, 2014 at 23:28

Félix Adriyel Gagnon-Grenier · Accepted Answer · 2014-06-13 20:30:33Z

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 )));
      ';

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related