Using last inserted id in mysql multi query

Question

I am trying to use last inserted id in MySQL muli_query but I don't know what I missed

this is my code :

$query = "INSERT INTO posts 
                    (nparc,id_chauffeur,id_camion,
                     lot_de_bord,triangle,pelle,balai,date)
            values('$nparc','$id_chauffeur','$id_camion',
                    '$lot_de_bord','$triangle', '$pelle', 
                    '$balai','$get_datetime');";

$query .= "INSERT INTO photos
                        (post_id,64_image1, 64_image2, 64_image3, 
                         64_image4 ,date_upload)
                values('$mysqli->insert_id','$imsrc1','$imsrc2',
                       '$imsrc3','$imsrc4','$get_datetime');";

$result = mysqli_multi_query($connection, $query) ;

Answer 1

Do not use mysqli_multi_query(). It is never recommended to use.

What you are probably looking for are transactions. You can execute both statements as prepared statements inside of a transaction.

try {
    $connection->begin_transaction();

    $stmt = $connection->prepare('INSERT INTO posts 
    (nparc,id_chauffeur,id_camion,lot_de_bord,triangle,pelle,balai,date)
    values(?,?,?,?,?,?,?,?)');
    $stmt->bind_param('ssssssss', $nparc, $id_chauffeur, $id_camion, $lot_de_bord, $triangle, $pelle, $balai, $get_datetime);
    $stmt->execute();

    $stmt = $connection->prepare('INSERT INTO photos
    (post_id, 64_image1, 64_image2, 64_image3, 64_image4 ,date_upload)
    values(?,?,?,?,?,?)');
    $stmt->bind_param('ssssss', $connection->insert_id, $imsrc1, $imsrc2, $imsrc3, $imsrc4, $get_datetime);
    $stmt->execute();

    $connection->commit();
} catch (\Throwable $e) {
    $connection->rollback();
}

Make sure that you have error reporting enabled otherwise your code won't work. You must put this line before new mysqli()

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);