Execute multiple MySQL queries

Question

I have 2 sql queries to execute, but I want it to execute all or if error in one query then dont execute any. I'm using php. I used to use try and catch in .NET but I'm new to php.

Below is the code which i was trying to do:

function Registration($UserFirstname,$UserLastname){
$sql="INSERT INTO table1 (fieldname1,fieldname2) VALUES ('$UserFirstname','$UserLastname')";
$res=mysql_query($sql) or die(mysql_error());
$sql="INSERT INTO table2 (fieldname1,fieldname2) VALUES ('$UserFirstname','$UserLastname')";
$res=mysql_query($sql) or die(mysql_error());}

Answer 1

The problem you're probably facing with try...catch is that PHP has two different error handling mechanisms: error reporting and exceptions. You cannot catch exceptions unless the underlying code throws them and good old mysql_query() will trigger warnings rather than throwing exceptions. There're several workarounds but, if you are interested in writing good object-oriented code, I suggest you switch to PDO.

In any case, if you want to stick to good old MySQL library, your code should basically work:

$res=mysql_query($sql) or die(mysql_error());

The explanation:

• mysql_query() returns FALSE if the query fails (e.g., you get a duplicate key)
• The right side of the or expression will only execute if the left side is FALSE
• die() aborts the script, thus preventing the next queries to be executed

However, I presume that you don't want to abort in the middle of nowhere. If we add some missing bits (such as proper SQL generation and code indentation) we get this:

function Registration($UserFirstname,$UserLastname){
    $sql = sprintf("INSERT INTO table1 (fieldname1,fieldname2) VALUES ('%s','%s')";
        mysql_real_escape_string($UserFirstname),
        mysql_real_escape_string($UserLastname)
    );
    $res = mysql_query($sql);
    if( !$res ){
        return FALSE;
    }


    $sql = sprintf("INSERT INTO table2 (fieldname1,fieldname2) VALUES ('%s','%s')";
        mysql_real_escape_string($UserFirstname),
        mysql_real_escape_string($UserLastname)
    );
    $res = mysql_query($sql);
    if( !$res ){
        return FALSE;
    }


    return TRUE;
}

About transactions

Please note that you still need to use transactions if there's a chance that the second query fails. Transactions are not particularly difficult to use, the only requirements are:

1. Define the involved tables as InnoDB
2. Run a START TRANSACTION query on top of the function
3. Run a COMMIT query at the end of the function

Answer 2

You need to be using transactions. These allow you to wrap a set of queries in a block that says "Either all these queries execute successfully or none of them do". This means that no matter what happens in the transaction block, you can be sure that the integrity of your database has been maintained.

NOTE: Transactions don't work with MyISAM tables, you have to use InnoDB tables.

mysql_query ('BEGIN TRANSACTION;', $db);
mysql_query ("INSERT INTO sometable (some, columns) VALUES ($some, $values)", $db);
if ($errMsg = mysql_error ($db))
{
    mysql_query ('ROLLBACK;', $db);
    die ($errMsg);
}
mysql_query ("INSERT INTO someothertable (some, other, columns) VALUES ($some, $other, $values)", $db);
if ($errMsg = mysql_error ($db))
{
    mysql_query ('ROLLBACK;', $db);
    die ($errMsg);
}
mysql_query ('COMMIT', $db);

Answer 3

For executing two queries as one, you have to use mysql extension, my below code works well

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query  = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";

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

/* close connection */
$mysqli->close();
?>

Answer 4

You can try to use mysqli_multi_query
Or you can change your DB schema to InnoDB..