php - create multiple temporary tables in laravel

Question

I'm trying to run a raw SQL queries, it makes multiple temporary tables. this works when running it in the database but not in code.

public function test(){
            $sql ='
  CREATE TEMPORARY TABLE test AS SELECT SUM(11) ;
  CREATE TEMPORARY TABLE test_3 AS SELECT SUM(11);
  select test.* FROM  test ;
  DROP TABLE IF EXISTS test, test_3;
            ';
    
            $sth = DB::select(DB::raw($sql));
    
}


SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TEMPORARY TABLE test_3 AS SELECT SUM(11) AS  bar' at line 2 (SQL:  CREATE TEMPORARY TABLE test AS SELECT SUM(11) AS  foo; CREATE TEMPORARY TABLE test_3 AS SELECT SUM(11) AS  bar;        )

however, making just one temporary table works.

public function testJustOne($user){
        $sql ='
 CREATE TEMPORARY TABLE test AS SELECT SUM(11) AS  foo;
        ';

        $sth = DB::select(DB::raw($sql));

}

there must be a way of just running the query as is.

Answer 1

This is an unprepared query that executes multiple statements in laravel.

IlluminateDatabaseConnection::unprepared

$sql ='
CREATE TEMPORARY TABLE test AS SELECT SUM(11) ;
CREATE TEMPORARY TABLE test_3 AS SELECT SUM(11);
select test.* FROM  test ;
DROP TABLE IF EXISTS test, test_3;
';
$result = DB::unprepared($sql);

MySQL general query log

It's just like running it from the MySQL console.

Query   CREATE TEMPORARY TABLE test AS SELECT SUM(11) ;
CREATE TEMPORARY TABLE test_3 AS SELECT SUM(11);
select test.* FROM  test ;
DROP TABLE IF EXISTS test, test_3

Error exception

You can only detect errors for the first query.
The following example returns $result = true without error.

$sql ='
CREATE TEMPORARY TABLE test AS SELECT SUM(11) ;
CREATE TEMPORARY TABLE test_3 AS SELECT SUM(11);
select test.* FROM  test ;
DROP TABLE IF EXISTS test, test_3;

select error_force_test() from dual;
';
$result = DB::unprepared($sql);

Security risk

Since it is not a prepared query, be careful of sql injection when receiving input values.