mySQL query, but return values NOT in table

[ChenXiu]

Table name:
"prettyflowers"
+--------+--------+
| flower | color  |
+--------+--------+
| daisy  | yellow |
| rose    | red      |
| orchid | purple |
| lily       | pink     |
+--------+--------+

Query:
SELECT color FROM prettyflowers WHERE
flower = 'rose'
UNION SELECT color from prettyflowers WHERE
flower = 'carnation'
UNION SELECT color from prettyflowers WHERE
flower = 'orchid'
UNION SELECT color from prettyflowers WHERE
flower = 'tulip';

Question:
My query requests data on 4 flowers, but the table only has data on 2 of the flowers.
(Notice that "carnation" and "tulip" are absent from the table.)

When running a Union Select query like the one above, is there a way for PHP/mySQL to return a list of what's NOT present in the table?

Here's a non-working "pseudocode" to illustrate what I'm looking for

instead of:
while ($results = $query->fetch_assoc()) { ......
maybe there's something simple like
while ($results != $query->fetch_assoc()) { $array_of_things_not_in_table[ ]=

Thanks!

 

[requinix]

Your query will return two colors. There's no way for you to know which color belonged to which flower. Or whether there were multiple rows for a given flower.

You're starting with a list of flowers, right?

SELECT flower, color FROM prettyflowers WHERE flower IN ('rose', 'carnation', 'orchid', 'tulip');

That will give you the list of colors but also their matching flowers.

I don't know what you want to do after this, but while you go the that set of results, you can remove the flower from the list you started with. So you'll find a row of rose/red and orchid/purple, remove those from your list of flowers, and end up with carnation and tulip.

[ChenXiu]

The code I provided was just a silly example to articulate my question:

How to capture list of what keys are not in a given table.

If a given table has the keys 1, 2, 3, 4, 5 in it, and my query is
select blah from table where key = 2, 3, 8, and 9
Obviously "8" and "9" are not in the table, and that's what I want captured by PHP.

PHP would give me this result:
$not_in_table = array(
'8',
'9'
);

So while I'm doing my queries and retrieving valid results, etc.,  I am also wanting PHP to capture/keep-track-of keys not in the table.

[Barand]

There is no way php or mysql can know what isn't in the table unless you tell it what could potentially be there.

Create a table "flowers" containing all the varieties you could be storing in "pretty_flowers"

table: flowers           table: prettyflowers
+-----------+            +--------+--------+
| name      |            | flower | color  |
+-----------+            +--------+--------+
| carnation |            | daisy  | yellow |
| daffodil  |            | rose   | red    |
| daisy     |            | orchid | purple |
| fresia    |            | lily   | pink   | 
| lily      |            +--------+--------+ 
| orchid    |
| rose      |
| tulip     |
+-----------+

Now you can join the tables using a LEFT JOIN to see which are missing

SELECT f.name
     , p.color
FROM flowers f
     LEFT JOIN
     prettyflowers p ON f.name = p.flower;

Where there is no match, the color will be NULL

+-----------+--------+
| name      | color  |
+-----------+--------+
| carnation |        |
| daffodil  |        |
| daisy     | yellow |
| fresia    |        | 
| lily      | pink   |
| orchid    | purple |
| rose      | red    |
| tulip     |        |
+-----------+--------+

If you only want to know the missing flowers, add "WHERE p.color IS NULL" to the query

[ChenXiu]

Barand, thank you, but in your answer you used the phrase, "...unless you tell it what could potentially be there."

I was hoping that the query itself tells PHP what could potentially be there.
My query here demonstrates that "rose" and "carnation" could potentially be there:

SELECT * FROM prettyflowers WHERE flower = 'rose' UNION SELECT * from prettyflowers WHERE flower = 'carnation' ...etc., etc.

That's why I thought PHP could somehow keep track of what's not in the table ( "carnation" in this example).

Currently my only solution is to do TWO separate queries:
• "check and see if it is there" query, and
• the main query

Like this:
 

$not_in_table = array(); // CAPTURES WHAT DOESN'T EXIST IN TABLE
foreach($flowers as $foo) { //NOTE: "$flowers" would be list of flowers input by visitor
	if ($result = $db->query("select exists(select * from prettyflowers where flower = '$foo') as c")->fetch_assoc()) {
		if(!$result['c']) {
		$not_in_table[] = $foo;
		}
	}
}

// NOW I RUN MY MAIN QUERY
// (using "UNION SELECT" because customer will input 100 flowers, and we want to keep the same order as input by customer)
$main_query = "select * from prettyflowers where flower = 'rose' UNION SELECT * FROM prettyflowers WHERE flower = 'carnation' ";
$flowerList = $db->query("$main_query");
while ($flower = $flowerList->fetch_assoc()) {
echo $flower["color"];
echo '<BR>';
}

// AND THEN I CAN LET VISITOR KNOW WHICH ONES WEREN'T IN TABLE
echo 'Sorry, these were not in database<BR>';
foreach ($not_in_table as $absent) {
echo "$absent <BR>";
}

 

 

[Barand]

Instead of my permanent "flowers" table, create a temporary "flowers" table for the user storing the ist of (100) flowers that they enter then use that in the query I gave earlier.

The temporary table will disappear when the script ends and the connection is closed.

[kicken]

If you're using mysql 8.0.19 or newer you can generate your temporary table inline like so:

select userList.flower
from (values 
  row('rose')
  , row('carnation')
) userList (flower)
left join pretty_flowers on pretty_flowers.flower=userList.flower
where
    pretty_flowers.flower is null

see example.

Edited November 1, 2022 by kicken