update or insert based on a condition in php-CodePudding

I'm learning php, I work on a small project for restaurant. In servants page when I try to take order, first I check the existence of the food in the order list if the food is already exist in the table so the quantity of the food will update otherwise if the food is not exist in the order list so it will be add to (tbl_temp_order),but it gives me error. how can I fix it? I appreciate your help.

if (isset($_POST[$row['fid']])) {
    if (isset($_SESSION['tableId'])) {
        $table = $_SESSION['tableId'];
        $foodId = $row['fid'];
        $returnQTY = checkQty($foodId);
        //If the quantity was bigger than 0 in (order table) it just increases the quantity and updates the record
        $state = 0;
        if ($returnQTY) {
            $qty = '';
            $qty  ;
            $updateQty = "update tbl_temp_order set qty='$qty' where fId='$foodId'";
            mysqli_query(getConnection(), $updateQty);
        } else {
            //but if the quantity for this food in (order table) was '0' so it will insert new record.
            $qty = 1;
            $insert = "insert into tbl_temp_order(fid,tid,qty,state)value ('$foodId','$table','$qty','$state')";
            mysqli_query(getConnection(), $insert);
        }
    }
}

CodePudding user response：

The better solution for this - use ON DUPLICATE UPDATE query. You need to create/update table tbl_temp_order with primary/unique index fid:

CREATE TABLE `tbl_temp_order` (
    fid int primary key,
    tid int,
    qty int,
    state int
);

and use next query:

insert into `tbl_temp_order` (
    `fid`, `tid`, `qty`, `state`
) values (
    ?, ?, 1, 0  /*`qty` = 1, `state` = 0 by default*/
) ON DUPLICATE KEY UPDATE `qty` = `qty`   1;