By ahmed_h on

How can i use mysql SET variables while using my PDO queries?

db_query('SET @x := 5,SET @y := 6, SELECT * from mytable')->fetchAll();

I have tried this and many variations of this statement with and without ':' etc, but it doesn't work.

⋅ Categories: Drupal 7.x, Drupal 8.x

Comments

mehul.shah’s picture

mehul.shah commented

Hi ahmed_h,

There are 2 points here.

  1. "SET @x := 5,SET @y := 6, SELECT * from mytable" should be "SET @x := 5,SET @y := 6; SELECT * from mytable" as "SET @x := 5,SET @y := 6;" and "SELECT * from mytable" are 2 separate MySQL statement
  2. You can not execute multiple statements together using db_query. Since the PHP functions mysql_query and mysqli_query can only execute one SQL statement, the db_query wrapper can also only execute one SQL statement

What you can do here is, divide that into two part and run it.

  1. db_query('SET @x := 5,SET @y := 6;')->execute();

  2. db_query('SELECT * from mytable')->fetchAll();