Php: Multiple SQL Queries in One MySQL_Query Statement

How do you do multiple SQL statements in one mysql_query?

I've never tried this, but I think you can use mysqli::multi_query. One of the good things about mysql_query rejecting multiple statements is that it immediately rules out some of the more common SQL injection attacks, such as adding '; DELETE FROM ... # to a statement. You might therefore want to be careful with multiple statements.

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

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.

PHP: multiple SQL queries in one mysql_query statement

You have more problem cases than just semicolons within strings.

• Script builtin commands that cannot be executed by mysql_query(), like USE.
• Statements that are not terminated with a semicolon, like DELIMITER.
• Statements that contain semicolons, but not inside quotes, like CREATE PROCEDURE.

I don't know of an easy way to handle this task, without shelling out to the mysql command-line client. I realize you said you can't rely on that client being present, but without that client, you need a large amount of PHP code to parse the script and execute statements appropriately.

You may be able to find such code inside the phpMyAdmin product. However, that product is licensed under the GPL, so if you use any of the code, you must also license your own project under the GPL.

---

See also my answers to these related questions:

• Running MySQL *.sql files in PHP
• Loading .sql files from within PHP
• is it possible to call a sql script from a stored procedure in another sql script?

Executing multiple SQL queries in one statement with PHP

Pass 65536 to mysql_connect as 5th parameter.

Example:

$conn = mysql_connect('localhost','username','password', true, 65536 /* here! */) 
    or die("cannot connect");
mysql_select_db('database_name') or die("cannot use database");
mysql_query("
    INSERT INTO table1 (field1,field2) VALUES(1,2);

    INSERT INTO table2 (field3,field4,field5) VALUES(3,4,5);

    DELETE FROM table3 WHERE field6 = 6;

    UPDATE table4 SET field7 = 7 WHERE field8 = 8;

    INSERT INTO table5
       SELECT t6.field11, t6.field12, t7.field13
       FROM table6 t6
       INNER JOIN table7 t7 ON t7.field9 = t6.field10;

    -- etc
");

When you are working with mysql_fetch_* or mysql_num_rows, or mysql_affected_rows, only the first statement is valid.

For example, the following codes, the first statement is INSERT, you cannot execute mysql_num_rows and mysql_fetch_*.
It is okay to use mysql_affected_rows to return how many rows inserted.

$conn = mysql_connect('localhost','username','password', true, 65536) or die("cannot connect");
mysql_select_db('database_name') or die("cannot use database");
mysql_query("
    INSERT INTO table1 (field1,field2) VALUES(1,2);
    SELECT * FROM table2;
");

Another example, the following codes, the first statement is SELECT, you cannot execute mysql_affected_rows. But you can execute mysql_fetch_assoc to get a key-value pair of row resulted from the first SELECT statement, or you can execute mysql_num_rows to get number of rows based on the first SELECT statement.

$conn = mysql_connect('localhost','username','password', true, 65536) or die("cannot connect");
mysql_select_db('database_name') or die("cannot use database");
mysql_query("
    SELECT * FROM table2;
    INSERT INTO table1 (field1,field2) VALUES(1,2);
");

PHP multiple MYSQL commands in one mysql_query() query

I think you need this?? http://us2.php.net/manual/en/mysqli.multi-query.php

perform multiple sql queries when clicking a submit button on PHP Page

I think that this is likely to be your problem

while($rows=mysql_fetch_array($result)){

Should be

while($rows=mysql_fetch_assoc($result)){

To refer to them as

echo $rows['dispatcharea'];

EDIT:

You also need to split both of your queries up in to two separate queries, because you cannot run two queries in one mysqli_query() tag.

You will need to split them as shown below:

 // First update query
 $sql1="update loaddetails set loadid= (select max(loadid)+1 from loadcounterid) where loadid=0";

// Second update query
$sql2="update loadcounterid set loadid= (select max(loadid) from loaddetails) where loadid>0";

// Run both queries independently
$final_query1 = mysql_query($sql1);
$final_query2 = mysql_query($sql2);

// Check for query success
if ($final_query1 && $final_query2)
{
   // Success running the queries
}
else
{
   // Unsuccessful running the queries
}

Execute multiple queries at once in Mysql using PHP

What you are looking for is TRANSACTIONS assuming you are not using MyISAM since it does not supports Transactions.

The concept of transactions is that either all the queries will execute or no query would execute at all.

In simple words all-or-nothing is what Transactions do

This is a basic example using mysqli

mysqli_query("START TRANSACTION");

$query1 = mysqli_query("INSERT INTO TABLE1(id) VALUES(2)");
$query2 = mysqli_query("INSERT INTO TABLE2(id) VALUES(3)");

if ($query1 and $query2) {
   mysqli_query("COMMIT"); //Commits the current transaction
} else {        
   mysqli_query("ROLLBACK");//Even if any one of the query fails, the changes will be undone
}

NOTE: This was just a simple example.It would better if you implement using try and catch blocks handling then exceptions properly.

Take a look at PHP DOCS

How to use multiple queries in one php function

The other answer might have stated that you can use MySQLi's multiquery option, and also as I commented you can use *_query() once for the first statement and second time for the second statement. However, you are braking the single responsibility principle of the method. If you want to chain to actions within one, separate them in different methods and call inwithin.

public function addNewMediaRecord($Subject, $Section, $Principle, $Title, $Blurb, $Uniq_id) { 
       $sql = "INSERT INTO media (media_id, subject_id, section_id, principle_id, title, blurb, verified, media_uniqid) 
               VALUES ('NULL', '".$Subject."', '".$Section."', '".$Principle."', '".$Title."', '".$Blurb."', '0', '".$Uniq_id."')";
       $this->addMediaCrossRef($Job, $Career, $Uniq_id); // You don't have this vars initilized in the current method?
       return yourQueryFunction($sql, $this->conn); 
}

public function addMediaCrossRef($Job, $Career, $Uniq_id) {
    $sql = "INSERT INTO media_career_crossref (media_id, jobrole_id, career_id, verified, media_uniqid) 
         VALUES (NULL, '".$Job."', '".$Career."', '0', '".$Uniq_id."')";
    return yourQueryFunction($sql, $this->conn);
}

And normally methods do not return the query() return value, especially when they are insert/update

I would prefer:

public function addNewMediaRecord($all_the_params) {
    $sql = ".....";
    yourQueryFunction($sql);
    $this->chainedMethod($params);
    return yourFunctionforAFFECTED_ROWS() > 0;
}

So it will return boolean if affected rows are more than zero (successful insert).

---

Also you'd better wrap the database functions in order to have easier switch between libraries, when you need to.

E.g.:

class Database {

    private $_host;
    private $_user;
    private $_pass;
    private $_db;

    private $_conn;

    public function __construct($host, $user, $pass, $db) {
        $this->_host = $host;
        $this->_user = $user;
        $this->_pass = $pass;
        $this->_db = $db;

        $this->connect();
    }

    private function connect() {
        $this->_conn = mysqli_connect($this->_host, $this->_user, $this->_pass, $this->_db)
                or die(mysqli_error($this->_conn));
    }

    public function query($query) {
        return mysqli_query($this->_conn, $query);
    }

    public function affectedRows() {
        return mysqli_affected_rows($this->_conn);
    }

---

class TheClassWhereYourMethodsYouHaveShownAre {

    protected $_db;    

    public function __construct() {
        $this->_db = new Database('host', 'user', 'pass', 'db');
    }

    public function addNewMediaRecord($Subject, $Section, $Principle, $Title, $Blurb, $Uniq_id) { 
           $sql = "INSERT INTO media (media_id, subject_id, section_id, principle_id, title, blurb, verified, media_uniqid) 
               VALUES ('NULL', '".$Subject."', '".$Section."', '".$Principle."', '".$Title."', '".$Blurb."', '0', '".$Uniq_id."')";
           $this->addMediaCrossRef($Job, $Career, $Uniq_id); // You don't have this vars initilized in the current method?
           $this->_db->query($sql);
           return $this->_db->affectedRows > 0;
    }

executing multi query in mysql_query() function

There are many delusions in your question.

Let's sort them out.

1. mysql_query() doesn't support multiple queries execution.
   
   (so, it is useless to delete anything)
2. dropping tables in the separate query is not the only way of the SQL injection.
   
   (so, it is useless to delete anything again)
3. To protect your query you have to follow some well-known techniques, not some handmade inventions of doubtful efficiency.

php/mysql with multiple queries

Thanks to php.net I've come up with a solution : you have to use (mysqli_multi_query($link, $query)) to run multiple concatenated queries.

 /* create sql connection*/
$link = mysqli_connect("server", "user", "password", "database");

$query = "SQL STATEMENTS;"; /*  first query : Notice the 2 semicolons at the end ! */
$query .= "SQL STATEMENTS;"; /* Notice the dot before = and the 2 semicolons at the end ! */
$query .= "SQL STATEMENTS;"; /* Notice the dot before = and the 2 semicolons at the end ! */
$query .= "SQL STATEMENTS"; /* last query : Notice the dot before = at the end ! */

/* Execute queries */

if (mysqli_multi_query($link, $query)) {
do {
    /* store first result set */
    if ($result = mysqli_store_result($link)) {
        while ($row = mysqli_fetch_array($result)) 

/* print your results */    
{
echo $row['column1'];
echo $row['column2'];
}
mysqli_free_result($result);
}   
} while (mysqli_next_result($link));
}

EDIT - The solution above works if you really want to do one big query but it's also possible to execute as many queries as you wish and execute them separately.

$query1 = "Create temporary table A select c1 from t1"; 
$result1 = mysqli_query($link, $query1) or die(mysqli_error());

$query2 = "select c1 from A"; 
$result2 = mysqli_query($link, $query2) or die(mysqli_error());

while($row = mysqli_fetch_array($result2)) {

echo $row['c1'];
    }  

---

Related Topics