PHP code to execute multiple queries with a single mysql_query() call
mysql, php
Solution
From the manual:
`mysql_query()` sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.
Move to `mysqli`, which has support for multiple statements.
Problem
I am trying to execute the SQL code below from my PHP class, but when I do it gives an error. The below code runs perfectly in PHPMyAdmin's console, but not in PHP. ``` SET @columns := ( SELECT GROUP_CONCAT(column_name) FROM information_schema.columns WHERE table_schema = 'test' AND table_name = 'mytable' AND column_key <> 'PRI' ); SET @sql := ( SELECT CONCAT( 'INSERT INTO mytable (', @columns, ') ', 'SELECT ', @columns, ' FROM mytable ', 'WHERE id = 1;' ) ); PREPARE stmt FROM @sql; EXECUTE stmt; ``` This is how I'm doing it in PHP: ``` $sql=''; $sql.="SET @columns := ( SELECT GROUP_CONCAT(column_name) FROM information_schema.columns WHERE table_schema = 'test' AND table_name = 'mytable' AND column_key <> 'PRI' );"; $sql.="SET @sql := ( SELECT CONCAT( 'INSERT INTO mytable (', @columns, ') ', 'SELECT ', @columns, ' FROM mytable ', 'WHERE id = 1;' ) );"; $sql.="PREPARE stmt FROM @sql; EXECUTE stmt;"; $result = mysql_query($sql, $this->connection); ``` What am I doing wrong? see the error am getting:: ``` Database query failed: 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 'SET @sql := ( SELECT CONCAT( 'INSERT INTO mytable(', @colu' at line 9 ```