PHP code to execute multiple queries with a single mysql_query() call

Question

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 

Answer 1

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.