return 0 if no record

[StevenOliver]

What is the most efficient way to return results in the same order as I used in my Select Statement, and return "0" if no record is found.

Example:
"SELECT fruit,color from table where fruit = 'grape' or fruit = 'apple' or fruit = 'banana' or fruit = 'pomegranite';

Current Result:
| banana |     yellow |
| grape |     purple |

Desired Result:
| grape |     purple |
| apple |    0 |
| banana |     yellow |
| pomegranite |     0 |

Thank you.

[Barand]

Isn't that like addressing a meeting and asking "Hands up anyone who isn't here?"

To do it with a query only you would need a table with four rows

fruit table
--+-------------
id| name
--+-------------
1 | grape
2 | apple
3 | banana
4 | pomegranate

then

SELECT f.name
     , COALESCE(t.color, 0) as color
FROM fruit f 
     LEFT JOIN table t ON f.name = t.fruit

Alternatively you can do it in your code with an array with fruit name as the key.

Either way it needs to know what fruits to expect. Psychic mysql is a few versions away yet.

[benanamen]

Op, could you tell us why you want to do this?

[StevenOliver]

Barand: Thank you (I like the "Hands up" analogy) 😀
I cannot change the table structure (my table has 2 columns "fruit" and "color"), and your table doesn't have colors, and I tried but couldn't figure out how to modify your suggestions.

Benanamen: Yes, I can explain. I have an excel spreadsheet, I select the entire "fruit" column, open my text editor and quickly create a mySQL query "Select fruit,color from mytable where fruit = 'apple' or fruit = 'banana' or fruit = 'orange'.... 1000 more fruits." Then I copy-and-paste the mySQL result into excel and I can see which ones match. They never match because every 10 fruits or so, one is missing from my mySQL table...

I know I could take a full 2 days off and hack out some PHP code to take a .csv file, parse it for fruit names, create an array, create PHP/mySQL script, etc., I just hoped there would be a way for mySQL to at least do this via SSH command line result using an ifnull or coalesce or some mysterious acronym....
| grape |     purple |
| apple |    0 ERROR NOT HERE |
| banana |     yellow |
| pomegranite |     0 ERROR NOT HERE |

 

Edited September 15, 2019 by StevenOliver

[Barand]

My table is in addition to your table containing the colors (if you read the query there is a JOIN between your table and mine)

SELECT f.name
     , COALESCE(t.color, 0) as color
FROM fruit f                                      -- extra table "fruit" defining fruits required
     LEFT JOIN table t ON f.name = t.fruit        -- your existing table "table" containing the color

The moral is -  to get information out of a database it has to be in there

[StevenOliver]

Perfect! I see it now.

I ended up following your advice and created an fgetcsv PHP script (which only took me an hour, not the 2 days I anticipated :-)

Now instead of the hassle of opening the file in excel, copy-and-pasting into text editor, creating a mySQL lookup, formatting the data to paste back into Excel, etc., all I have to do is open up SSH and type "php my_new_script.php" and voila.