Update two tables in one query

Question

I can't update two table in one query. Is there any other way to do it? below is an example of my code.

 $id = $_GET['idnum'];
    $txtEditUsername=$_GET['txtEditUsername'];
    $txtlname=$_GET['txtlname'];
    $txtfname=$_GET['txtfname'];
    $txtgender=$_GET['txtgender'];
    $txtbdate=$_GET['txtbdate'];
    $txtnationality=$_GET['txtnationality'];
    $txtcnum=$_GET['txtcnum'];
    $txtaddress=$_GET['txtaddress'];

    $sql = "UPDATE users SET u_usernamee = '$txtEditUsername' WHERE u_uid = '$id'";
    $sql = "UPDATE people SET ppl_lname = '$txtlname', ppl_fname = '$txtfname', ppl_gender = '$txtgender', ppl_bdate = '$txtbdate', ppl_nationality = '$txtnationality', ppl_cnum = '$txtcnum', ppl_address = '$txtaddress' WHERE ppl_id = '$id'";

    if (mysqli_query($conn, $sql)) {
    } else {
        echo "Error ".mysqli_error($conn);
    }

You're wide open to SQL injection attacks with that code, you should be using prepared statements, no matter what the source of the data. Never trust any user submitted input, no mater whether you trust the user or not — SpacePhoenix, Apr 18 '19 at 08:33

score 1 · Accepted Answer · answered Apr 18 '19 at 08:42

You can use mysqli_multi_query like this

$sql = "UPDATE users SET u_usernamee = '$txtEditUsername' WHERE u_uid = '$id'";
$sql .= "UPDATE people SET ppl_lname = '$txtlname', ppl_fname = '$txtfname', 

ppl_gender = '$txtgender', ppl_bdate = '$txtbdate', ppl_nationality = '$txtnationality', ppl_cnum = '$txtcnum', ppl_address = '$txtaddress' WHERE ppl_id = '$id'";

if (mysqli_multi_query($conn, $query)) {
    do {
        /* sStockage du premier résultat */
        if ($result = mysqli_store_result($conn)) {
            while ($row = mysqli_fetch_row($result)) {
                printf("%s\n", $row[0]);
            }
            mysqli_free_result($result);
        }
        /* Affichage d'une séparation */
        if (mysqli_more_results($conn)) {
            printf("-----------------\n");
        }
    } while (mysqli_next_result($conn));
}

And here you can know more about it https://www.php.net/manual/en/mysqli.multi-query.php

score 0 · Answer 2 · answered Apr 18 '19 at 08:35

You should use different name for variables like $sql and $sql2, but you can even use one query, try this:

UPDATE users, people
SET users.u_username = '$txtEditUsername',
    people.ppl_lname = '$txtlname',
    people.ppl_fname = '$txtfname',
    // AND SO ON
WHERE
    users.u_uid = '$id'
AND people.ppl_id = '$id';

EDIT

However, like someone else said, you can be victim of sql injection. Try to use prepared statement with PDO, give a look here

score 0 · Answer 3 · answered Apr 18 '19 at 08:49

The problem is here:

$sql = "UPDATE users ...";
$sql = "UPDATE people ...";

You are using the same variable to execute two different queries. The second statement overrides the first one, erasing it. That's why the users table isn't updated.

$usersSQL = "UPDATE users SET u_usernamee = '$txtEditUsername' WHERE u_uid = '$id'";

if (!mysqli_query($conn, $usersSQL)) {
    echo "Error while updating users table: ".mysqli_error($conn);
    // Eventually, consider to exit the function..
}

$peopleSQL = "UPDATE people SET ppl_lname = '$txtlname', ppl_fname = '$txtfname', ppl_gender = '$txtgender', ppl_bdate = '$txtbdate', ppl_nationality = '$txtnationality', ppl_cnum = '$txtcnum', ppl_address = '$txtaddress' WHERE ppl_id = '$id'";

if (!mysqli_query($conn, $peopleSQL)) {
    echo "Error while updating people table: ".mysqli_error($conn);
}

Last but not least

Never ever use unsanitized data. Always filter and validate user's data. Adding validation, you'll avoid passing invalid values to the query (example: ppl_lname's length is maximum 50 chars, and user sends 51 chars).

And most important, NEVER use user's data directly to a SQL query, because you are exposing your database to a serious risk.

Give a read to this link, or this one, they will explain what's the problem when using unsanitized data.

Update two tables in one query

3 Answers3

Last but not least