Update in two tables at the same time

Question

Is there a way to update two tables at the same time? I have a table food and table food_r

This is the code with which I insert into food

$rest_id = null;
if ( !empty($_GET['rest_id'])) 
{
    $rest_id = $_REQUEST['rest_id'];
}
if ( null==$rest_id ) 
{
    echo "null==$rest_id";
}
if(isSet($_POST['submit'])) 
{
    // keep track post values
    $food_name = $_POST['food_name'];
    $food_description = $_POST['food_description'];
    $food_menu = $rest_id;
    $usertype = $_SESSION['usertype'];

     // update data
     $pdo = Database::connect();
     $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     $sql = $pdo->prepare("INSERT INTO food ( food_name, food_description, food_menu, usertype ) 
              VALUES (:food_name, :food_description, :food_menu, :usertype)");

                    $sql->execute(array(
                    ':food_name'    => $food_name,
                    ':food_description' => $food_description,
                    ':food_menu'    => $food_id,
                    ':usertype'     => $_SESSION['usertype']                        
                    ));
                    Database::disconnect();
                    echo "Product added!";
            }

Now If I want to be visible the inserted product I must insert in table food_r value of food_menu and the value of usertype. How can I do this?

Update: It's working that way. Thank's to @JonathonWisnoski for pointing me to transactions..

  $pdo->beginTransaction();
    $sql = $pdo->prepare("INSERT INTO food ( food_name, food_description, food_menu, usertype ) 
                VALUES (:food_name, :food_description, :food_menu, :usertype)");

        $sql->execute(array(
        ':food_name'    => $food_name,
        ':food_description' => $food_description,
        ':food_menu'    => $rest_id,
        ':usertype'     => $_SESSION['usertype']                        
        ));
        $lastInsertID = $pdo->lastInsertId();
    $sql = $pdo->prepare("INSERT INTO food_r (food_id, usertype) 
                    VALUES (:rest_id, :usertype)");
        $sql->execute(array(
        ':rest_id'      => $lastInsertID,
        ':usertype' => $rest_id
        ));
        $pdo->commit();

This is something you would do with transactions. The general idea behind transactions is to ready a number of insersions/etc. And to commit them all at once. — Jonathon, Dec 06 '14 at 17:17
See: http://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization — Jonathon, Dec 06 '14 at 17:18
You can UPDATE two tables at the same time. You cannot INSERT into two tables at the same time. I'm unclear as to what it is you're actually trying to do. — Strawberry, Dec 06 '14 at 17:25
When I hit button `submit` on the form I insert new row(record) in table `food`. The product is still not visible because I must also insert the values from `food_menu` and `usertype` into the table `food_r`. — Jason Paddle, Dec 06 '14 at 17:30
I have updated my question with how I've made it and it's working. — Jason Paddle, Dec 06 '14 at 18:25
@JasonPaddle do NOT update question by adding answer. If you solved your problem post normal **answer** and when SO allows, approve it — Marcin Orlowski, Dec 06 '14 at 18:27
@MarcinOrlowski, thank's for your comment. I will post this working sniped like answer. — Jason Paddle, Dec 06 '14 at 18:29

score 1 · Accepted Answer · answered Dec 06 '14 at 18:34

Update: It's working that way. Thank's to @JonathonWisnoski for pointing me to transactions..

I've also put try{}catch{} block for any errors.

 try 
 {
   $pdo->beginTransaction();
     $sql = $pdo->prepare("INSERT INTO food ( food_name, food_description, food_menu, usertype ) 
                VALUES (:food_name, :food_description, :food_menu, :usertype)");

        $sql->execute(array(
        ':food_name'    => $food_name,
        ':food_description' => $food_description,
        ':food_menu'    => $rest_id,
        ':usertype'     => $_SESSION['usertype']                        
        ));
        $lastInsertID = $pdo->lastInsertId();
    $sql = $pdo->prepare("INSERT INTO food_r (food_id, usertype) 
                    VALUES (:rest_id, :usertype)");
        $sql->execute(array(
        ':rest_id'      => $lastInsertID,
        ':usertype' => $rest_id
        ));
        $pdo->commit();
   }                
   // any errors from the above database queries will be catched
   catch (PDOException $e)
   {
        // roll back transaction
        $pdo->rollback();
        // log any errors to file
        ExceptionErrorHandler($e);
        exit;
    }

Update in two tables at the same time

1 Answers1