How can I update two tables in one statement with $qryUpdate

Question

From my editclient.php (controller) I want to update 2 tables in my database at the same time with $qryUpdate, but I have no idea how to do that.

I have looked at this How to update two tables in one statement in SQL Server 2005? but I couldn't get that to work.

if (strtolower($_SESSION['role'])=='admin')
    {
        //$conn=bekend door include db.php in header
        $qryCreate="INSERT INTO USER
                    (username, password, email, role_id)
                    VALUES('$nwUsername', '$nwPassword', '$nwEmail', '$nwRole')";
        //controleren of gebruiker al bestaat...
        if (getExistUsername($conn, $nwUsername))
        {
            echo "Gebruiker $nwUsername bestaat al, gebruiker wordt niet aangemaakt...<br>";
            header('refresh: 5; url=index.blade.php');
            exit;
        }
//query uitvoeren met error afhandeling
        if (mysqli_query($conn, $qryCreate))
        {

            echo "Gebruiker $nwUsername is aangemaakt!";
            header('refresh: 5; url=edit.blade.php?name='.$nwUsername.'&action=edit');
        }
        else
        {
            echo "Gebruiker $nwUsername is NIET aangemaakt!<br>
                      Geef deze foutmelding door aan uw beheerder: ".mysqli_error($conn);
            header('refresh: 5; url=index.blade.php');
        }

I want to execute the next query at the same time as the other one

UPDATE client
                SET name='$name', street='$street', number='$number', postalcode='$postalcode', location='$location', phonenumber='$phonenumber'
                WHERE user_id='$user_id';"

At this moment i have this:

                $qryUpdate =<<<SQL
            BEGIN;
              UPDATE user
              SET username='$username', password='$password'
                WHERE username='$nwUsername';
              UPDATE client 
              SET name='$name', street='$street', number='$number', postalcode='$postalcode', location='$location', phonenumber='$phonenumber'
              WHERE user_id=' .$user_id. ';
              COMMIT;
            SQL;

             var_dump($nwUsername);
            //hieronder worden de gegevens opgeslagen....
            mysqli_query($conn, $qryUpdate);
            if (true) {
                echo "Gebruiker $nwUsername is aangepast naar $username!<br> 
                      Client $name is aangepast.<br> $qryUpdate";
            } //    header('refresh: 2; url=index.blade.php');

            else {
                echo "Wijzigingen zijn NIET doorgevoerd!<br>

        foutmelding: " . mysqli_error($conn) . "<br>Query:" . $qryUpdate;
            }
            }```

But the problem now is that i don't know how to close the query and that the echo shows the query with the correct values but, nothing changes in my database.

id recommend you create a stored procedure with your logic in it...pass in your parameters...and run it as transactional — Ctznkane525, Apr 25 '19 at 14:37
Possible duplicate of [Executing multiple SQL queries in one statement with PHP](https://stackoverflow.com/questions/13980803/executing-multiple-sql-queries-in-one-statement-with-php) — Epodax, Apr 25 '19 at 14:38
@Epodax Except ignore the use of `mysql_` API all the way through that answer:) — RiggsFolly, Apr 25 '19 at 14:40
@RiggsFolly Of course, but it's relative easy to use the same code with mysqli or PDO :) — Epodax, Apr 25 '19 at 14:41
hmm also sounds very dangerous to allow multiple SQL queries in PHP even in MySQLi or PDO @Epodax .. it's asking for trouble. — Raymond Nijland, Apr 25 '19 at 14:41
I was really looking for https://www.php.net/manual/en/mysqli.quickstart.multiple-statement.php but couldn't find a proper question for it so I chose the above one because it contained a link to the mysqli multi query execution. — Epodax, Apr 25 '19 at 14:44
Actually looking at the code here, you dont seem to actually execute the INSERT query anywhere!!! — RiggsFolly, Apr 25 '19 at 14:44
I think if you were to do a little research on TRANSACTIONS and how to start a transaction and then issue 2 or more update queries followed by either a COMMIT if all successful or a ROLLBACK if any of them failed, you would soon understand how to do this — RiggsFolly, Apr 25 '19 at 14:49

score 1 · Accepted Answer · answered Apr 25 '19 at 14:49

You can do this with PDO. I've truncated your queries here to illustrate what the code would look like.

$sql = <<<SQL
   START TRANSACTION;
   INSERT INTO user (...) VALUES (...);
   UPDATE client SET ... WHERE ...;
   COMMIT;
SQL;

$dsn = "mysql:dbname=dbname;host=127.0.0.1";
$username = "mysqluser";
$password = "mysqluser-secret-password";

$pdo = new PDO($dsn, $username, $password); // update for your server
$statement = $pdo->prepare($sql);
//
// bind the parameters to avoid SQL injection 
//
$statement->execute();

Learn more about transactions in the MySQL documentation. Also be sure to read about PDO which can work with multiple relational database engines, not just MySQL.

How can I update two tables in one statement with $qryUpdate

1 Answers1