CODEWITHSUNDEEP
phpmysql
greenboxgoolu
greenboxgoolu

Reputation: 139

PHP Query - Exclude if exist in table

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 ?

Upvotes: 1

Views: 41

Answers (2)

Chalid Ade Rahman
Chalid Ade Rahman

Reputation: 1

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

?>

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

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

Upvotes: 1

Related Questions