PHP Query - Exclude if exist in table

Question

orderfood
orderfood_id   food_id   total_amount



foodcancel
foodcancel_id   food_id  status

$query = $this->db->query("SELECT * FROM order_food of LEFT JOIN `foodcancel` fc ON of.food_id = fc.food_id WHERE of.orderfood_id = '" . (int)$orderfood_id . "'");
    $order_foods = $query->rows;

above is my query, what i wanted is that if there food_id inside foodcancel table , exclude it from rows, possbile to do it ?

ScaisEdge · Accepted Answer · 2020-07-23 08:46:41Z

1

For exclude the existing values you could try checking null for corresponding matching value

    SELECT * 
    FROM order_food of 
    LEFT JOIN foodcancel fc ON of.food_id = fc.food_id 
         and  of.food_id  = your_value  

    WHERE fc.orderfood_id is  null

anyway you should not php var in your sql code because in this way you are are risk for sqlinjection for avoid this you should take a look at prepared statement and binding param

edited Jul 23, 2020 at 8:46

answered Jul 23, 2020 at 8:36

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

greenboxgoolu Over a year ago

thanks , by following the code you provided , managed to get what i wanted

Chalid Ade Rahman · Accepted Answer · 2020-07-23 08:47:48Z

It's very possible to do. In my logic. first, you must get all food_id on food_cancel table. Then save it into variabel and use it when you show orderFood table with adding NOT IN condition.

I've write code for you,

<?php

// Get Food Id From Cancel 
$orderCancel   = mysqli_query($mysqli, "SELECT * FROM `foodcancel`");
$cancelId      = "";
while ($cancel = mysqli_fetch_array($orderCancel)) {
  $cancelId   .= $cancel["food_id"].",";
};
$cancelId      = substr($cancelId, 0, -1);

// Put Food Id on Cancel Table into NOT IN Condition Database
$orderFood     = mysqli_query($mysqli, "SELECT * FROM `orderfood` WHERE food_id NOT IN ($cancelId)");
while ($order  = mysqli_fetch_assoc($orderFood)) {
  $food[]      = $order;
};

echo json_encode($food);

?>

Collectives™ on Stack Overflow

PHP Query - Exclude if exist in table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related