CODEWITHSUNDEEP
phpmysql
Jason Paddle
Jason Paddle

Reputation: 1103

Update in two tables at the same time

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();

Upvotes: 0

Views: 307

Answers (1)

Jason Paddle
Jason Paddle

Reputation: 1103

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;
    }

Upvotes: 1

Related Questions