Jump to content

mySQL query, but return values NOT in table


ChenXiu

Recommended Posts

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!


 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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>";
}

 

 

Link to comment
Share on other sites

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 by kicken
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.