Jump to content
StevenOliver

return 0 if no record

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by StevenOliver

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

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.