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

As far as I know, the deprecated legacy mysql_... extension does not support multiple statement execution. You'll have to run them one by one. — Álvaro González, Jul 01 '13 at 11:30
Why aren't you using [PDO](http://php.net/manual/en/book.pdo.php)? — Bojangles, Jul 01 '13 at 11:31
@ÁlvaroG.Vicario i tried in that way also still same result....what to do..the statement is breaking when it reaches @columns...!! — tradebel123, Jul 01 '13 at 11:32
Whenever you say "it gives me an error" you should also provide the exact error message you're seeing. Along with the point in the code where the error is thrown, if possible. — Marcello Romani, Jul 01 '13 at 11:34
@MarcelloRomani thanx for your comment i have updated my question with errror...thanxxx — tradebel123, Jul 01 '13 at 11:46
Might be worth a shot to check the encoding of the file. Try UTF8 without BOM. — OptimusCrime, Jul 01 '13 at 12:09
See ? The error points to the beginning of the second query. That goes along with what others have pointed out: the `mysql_*` API doesn't support multiple queries. So you have to either issue one query at a time (i.e. one per mysql_query() call) or move to a more recent and featureful API (like `mysqli_*`). — Marcello Romani, Jul 01 '13 at 12:35
Pass 65536 as mysql_connect's 5th parameter. Check Source: http://stackoverflow.com/questions/13980803/executing-multiple-sql-queries-in-one-statement-with-phps — Bakly, Nov 08 '13 at 22:00

score 7 · Accepted Answer · answered Jul 01 '13 at 12:04

7

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.

answered Jul 01 '13 at 12:04

Burhan Khalid

169,990
18
245
284

thanx man __ you came with a better answer __ i foud using PDO is simpler than migrating to mysqli___what is your opinion..?? waiting for your comment__!! – tradebel123 Jul 01 '13 at 13:32
Either will be better than what you have now. I would give slight preference to mysqli since its part of PHP proper. – Burhan Khalid Jul 01 '13 at 14:01

score 2 · Answer 2 · answered Jan 19 '14 at 13:33

/* 
Author: Jack Mason 
website:  volunteer @http://www.osipage.com , web access application and bookmarking tool. 
Language: PHP, Mysql 
This script is free and can  be used anywhere, no attribution required. 
*/ 

ini_set('display_errors', 0); 
error_reporting(0); 
// SET MYSQL CONFIGURATION 
$serverName = 'localhost'; 
$username   = 'root'; 
$password   = ''; 
$database   = 'test_delete'; 

// SET THE SQL FILE PATH OR DIRECTLY GIVE ALL SQL STATEMENTS INSIDE QUOTES 
$query = file_get_contents('file.sql'); 

//OR  to execute multiple SQL statements directly, set "$query" variable as follows: 

$query = 'CREATE TABLE IF NOT EXISTS `employee_attendances` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `attendance_date` date DEFAULT NULL, 
  `employee_id` int(11) DEFAULT NULL, 
  `employee_leave_type_id` int(11) DEFAULT NULL, 
  `reason` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, 
  `is_half_day` tinyint(1) DEFAULT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; 
CREATE TABLE IF NOT EXISTS `items_product` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `product_name` TEXT DEFAULT NULL, 
  `price` DOUBLE DEFAULT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; 
'; 


// Establishing connection with mysqli database 
$con = new mysqli($serverName, $username, $password, $database); 
/* check connection */ 
if(mysqli_connect_errno()) { 
    printf("Connect failed: %s\n", mysqli_connect_error()); 
    exit(); 
} 

/* execute multi query */ 
if($con->multi_query($query))  
{ 
    do { 
        /* store first result set */ 
        if($resultSet = $con->store_result())  
        { 
            while($row = $resultSet->fetch_row())  
            { 
                printf("%s\n", $row[0]); 
            } 
            $resultSet->free(); 
        } 

         //print divider 
        if($con->more_results())  
        { 
     $loadArray = array("Creating tables....", "please wait..", "stay tuned while all table definitions are dumped..."); 
     $upperLimit = count($loadArray) - 1; 
           $randNumb = rand(0, $upperLimit); 
           echo $loadArray[$randNumb]; echo '<br/>'; 
           $loadArray = array();  
        } 
    } while ($con->next_result()); 

    echo 'All tables have been successfully copied/created to given database!'; 
/* close connection */ 
} 
$con->close();

This code works both with .SQL file or directly executing multiple SQL queries. Tested by executing upto 200 tables successully at once. Taken from this phpsnips page.

score 1 · Answer 3 · answered Apr 13 '14 at 17:53

1

Simply run the query "set names 'utf8' " against the MySQL DB and your output should appear correct.

answered Apr 13 '14 at 17:53

Manish

11
1

Code Lღver · Answer 4 · 2013-07-01T11:50:58.780

0

Use the below code as example:

$sql.= <<<EOF
   SET @sql := (
  SELECT CONCAT(
    'INSERT INTO mytable (', @columns, ') ',
    'SELECT ', @columns, ' FROM mytable ',
    'WHERE id = 1;'
  )
)
EOF;

EDITED:

$sql.= <<<EOF
  SET @columns := (
   SELECT
    GROUP_CONCAT(column_name)
    FROM information_schema.columns
    WHERE table_schema = 'test'
    AND table_name = 'mytable'
    AND column_key <> 'PRI'
    );
EOF;

Use the EOF to make such statement.

edited Jul 01 '13 at 11:50

answered Jul 01 '13 at 11:32

Code Lღver

15,573
16
56
75

@tradebel123 I got it I have edited the answer. now make change in edited statement also and then check. – Code Lღver Jul 01 '13 at 11:51
same result i am getting...statement is breaking near @colu...?? – tradebel123 Jul 01 '13 at 11:56

score 0 · Answer 5 · answered Jul 01 '13 at 12:07

0

As Burhan pointed out the fact that multiple queries aren't supported anymore with mysql+php.The reason might be SQL Injection

answered Jul 01 '13 at 12:07

dhaval

176
1
1
7

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

5 Answers5