add new session product - PHP

Question

When a user is buying a product (lets say they have 3 products) and they have typed their information, the product automatically goes into database with a status = 0.

If the user is going away from the basket, lets assume into product overview(and buying an extra product, now they have 4 products in basket).

I have to find out how to add the new product into database instead of inserting all products + the new one, because 3 of the products is already inserted.

$_SESSION['inserted_ids'] = array();
   $id = ''; 
        if( in_array( $id, $_SESSION['inserted_ids']) ){
             return;
        }else{

            $id = $db->Insert("INSERT INTO orders SET 
                       name    = '". $_SESSION['information']['name'] ."',
                       address = '". $_SESSION['information']['address'] ."',
                       date    = '". Database::Now() ."',
                       phone   = '". (isset($_SESSION['information']['phone']) ? $_SESSION['information']['phone']:'') ."',
                       email   = '".  $_SESSION['information']['email'] ."',
                       city    = '".  $_SESSION['information']['city'] ."',
                       zipcode = '".  $_SESSION['information']['zipcode']."'
            ");

            $_SESSION['inserted_ids'][] = (int) $id;


              # lists products
              $list = '';
              $grand_total = '';
              $res = $db->Execute("sql with product_id");
          while($row = $res->GetNext()){


                    if( $row['product_id'] == $_SESSION['inserted_ids'] ){
                         $db->Execute("INSERT INTO order_lines SET 

                                          price         = '$round_price', 
                                          order_id      =  $id,
                                          product_id    =  $product_id, 
                                          product_name  = '$product_name',
                                          units         = '$grand_units', 
                                          status        = '0',
                                          date          = '".Database::Now()."', 
                                          item_num      = '".$item_num."';

                                          ON DUPLICATE KEY UPDATE SET 
                                              units = '$grand_units' 

                                          WHERE order_id = $id
                                          LIMIT 1;
                                      ");
                    }


                  }
        }

Answer 1

Why don't you add a flag key to your product array that denotes whether the product was inserted or not, then before you insert the product, check the flag.

If you can't even do that, then create another session array ( call it inserted_ids ) that holds the product ids that were inserted, and check if the product_id is in the inserted_ids array.

( You can just use in_array, you don't need to do a whole new loop for that ).

This should be your code:

$_SESSION[ 'inserted_ids' ] = array(); // execute this line before everything else.

if( in_array( $id, $_SESSION[ 'inserted_ids' ] ) {
     return;
}
$id = $db->Insert("INSERT INTO orders SET 
               name    = '". $_SESSION['information']['name'] ."',
               address = '". $_SESSION['information']['address'] ."',
               date    = '". Database::Now() ."',
               phone   = '". (isset($_SESSION['information']['phone']) ? $_SESSION['information']['phone']:'') ."',
               email   = '".  $_SESSION['information']['email'] ."',
               city    = '".  $_SESSION['information']['city'] ."',
               zipcode = '".  $_SESSION['information']['zipcode']."'
   ");

$_SESSION['inserted_ids'][] = $id;

      $res = $db->Execute("sql statment");
while($row = $res->GetNext()){

 $db->Insert("INSERT INTO order_lines SET 

                          price         = '$round_price', 
                          order_id      = '".$id."',
                          product_id    = '$product_id', 
                          product_name  = '$product_name',
                          units         = '$grand_units', 
                          status        = '0',
                          date          = '".Database::Now()."', 
                          item_num      = '".$item_num."'
                ");
          }

Answer 2

Add a UNIQUE constraint to the order_lines table and then update that line when a duplicate is found:

ALTER TABLE order_lines ADD UNIQUE(order_id, product_id)

Then, when adding an order_line use the ON DUPLICATE KEY UPDATE statement:

INSERT INTO order_lines SET
 price= 2.22,
 order_id= 2,
 product_id= 3,
 status = 0,
 ... other columns ...
ON DUPLICATE KEY UPDATE SET
 price= 2.22, 
 grand_unit= 4,
 ... other columns ...