Update two table on button click at once

Question

I am trying to update two tables which are tblbooking and tblvehicle upon clicking a button here is my code;

if(isset($_REQUEST['aeid']))
{       
    $aeid=intval($_GET['aeid']);
    $status="1";
    $availability="1";

    $sql = "UPDATE tblbooking 
            JOIN tblvehicles 
            SET tblbooking.Status=:status,
                tblvehicles.Availability=:availability 
            WHERE tblbooking.id=:aeid";

    $query = $dbh->prepare($sql);
    $query -> bindParam(':status',$status, PDO::PARAM_STR);
    $query -> bindParam(':availability',$availability, PDO::PARAM_STR);
    $query-> bindParam(':aeid',$aeid, PDO::PARAM_STR);
    $query -> execute();

    $msg="Success!";
    }

I ran the code above and it updated the tblbooking.Status table BUT it updated all tblvehicles.Availability to 1 on tblvehicles table, it should only update the assigned vehicle. I tried editing it but I am not going anywhere.

You may wanna read up on join conditions... https://www.w3schools.com/sql/sql_join.asp — Dorvalla, Apr 22 '20 at 11:36
@Dorvalla, I did It! I added JOIN tblvehicles ON tblbooking.VehicleId=tblvehicles.id — AZURE KNIGHT, Apr 22 '20 at 11:49

score 0 · Answer 1 · answered Apr 22 '20 at 11:45

I'd suggest doing two different updates:

$sql = "UPDATE tblbooking 
        SET tblbooking.Status=:status
        WHERE tblbooking.id=:aeid";

$query = $dbh->prepare($sql);
$query -> bindParam(':status',$status, PDO::PARAM_STR);
$query -> bindParam(':aeid',$aeid, PDO::PARAM_STR);
$query -> execute();

$sql = "UPDATE tblvehicles 
        SET tblvehicles.Availability=:availability 
        WHERE ...your condition...";

$query = $dbh->prepare($sql);
$query -> bindParam(':availability',$availability, PDO::PARAM_STR);
$query -> bindParam(....your condition ...);
$query -> execute();

Otherwise I suggest to do check the comments or read https://www.mysqltutorial.org/mysql-update-join/

Your join is missing the "ON" condition, example:

UPDATE tblbooking JOIN tblvehicles on tblbooking.vehicleid = tblvehicles.id

Also check th following solution: How to do 3 table JOIN in UPDATE query?

Update two table on button click at once

1 Answers1