In my SQL database I have a table namend "orders". As you can see the people ordered "cat", "dog" and "frog":
id | name
---------
1 | cat
2 | dog
3 | frog
I can print my orders like this:
$pdo = Database::connect();
$sql = 'SELECT * FROM orders ORDER BY id DESC';
foreach ($pdo->query($sql) as $row) {
echo($row['name'].' ');
}
and get the result: cat dog frog
Now I have another table with my inventory named "inventory":
id | name
---------
1 | cat
2 | dog
3 | frog
4 | duck
I have a selectbox on my website, which shows my inventory
<select name="animals">
<?php
$sql = 'SELECT * FROM inventory ORDER BY id DESC';
foreach ($pdo->query($sql) as $row) {
echo('<option value="'.$row['name'].'">'.$row['name'].'</option>');
}
?>
</select>
What I want to do now is inside my select box show ONLY the inventory which is not in my orders table. That means in this case my select box should only have the option "duck".
How do I have to change my selectbox to achieve what I need.
Add subselect to your query:
SELECT * FROM inventory WHERE name NOT IN (SELECT name FROM orders) ORDER BY id DESC
An other solution would be to use UNION ALL and check which names just appear once
SELECT name
FROM
(
SELECT tableOrdered.name
FROM tableOrdered
UNION ALL
SELECT TableInventory.name
FROM TableInventory
)t
GROUP BY name
HAVING COUNT(*) = 1
ORDER BY name