CODEWITHSUNDEEP
phpmysql
tradebel123
tradebel123

Reputation: 435

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

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

Upvotes: 7

Views: 17014

Answers (5)

Manish
Manish

Reputation: 11

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

Upvotes: 1

webblover
webblover

Reputation: 1196

/* 
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.

Upvotes: 2

dhaval
dhaval

Reputation: 176

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

Upvotes: 0

Burhan Khalid
Burhan Khalid

Reputation: 174614

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.

Upvotes: 7

Code Lღver
Code Lღver

Reputation: 15603

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.

Upvotes: 0

Related Questions