[RESOLVED] MySQL substitute/alternate string for empty field?

Joseph_Sliker

Hi;

Not sure if this is possible. Is there a statement that will fill an "empty cell" in the results of a MySQL query with alternative text? I know how to do this with if...else type statements in php code, but I'm trying to make my code more efficient by letting the MySQL query do more of the work.

something along the lines of ....

SELECT STUDENT, ITEM, IF(RESPONSE, ELSE 'NORESPONSE') AS REPONSESTRING FROM TESTDATA

so that the result would look like:

STUDENT, ITEM, RESPONSESTRING
KID1, 12, Columbus
KID2, 12, NORESPONSE
KID3, 12, Magellan
KID4, 12, Columbus

I know the example query above is not correct....just trying to get the concept across...

can't seem to find a clear reference to this in the online MySQL manual.

Thanks.

Joseph_Sliker

Man, I'm too impatient. Finally figured it out. Maybe this will help someone else.

SELECT STUDENT, ITEM, IFNULL(RESPONSE, 'NORESPONSE') AS REPONSESTRING FROM TESTDATA

The MySQL IFNULL(field, 'substitute string') function or whatever you call it, seems to do what I need.

Joseph_Sliker

K-RAP!

It does not seem to work in a GROUP BY query!

Wish I could Unresolve this thread!

Joseph_Sliker

I was wrong...it DOES work in GROUP BY queries: I had empty string values in the table I was searching rather than NULLs....once I corrected that, IFNULL() worked fine even in a GROUP BY situation.

So, I really, really think this issue is resolved (for me anyway).

Thanks, me!

liquorvicar

I'm not quite sure of your context but could you not just set the DEFAULT for the relevant column so 'NORESPONSE' is added to your table when the row is inserted?

Joseph_Sliker

Thanks for the suggestion, but the original tables in the database, and the forms that feed them were designed a few years ago when I knew even less than I know now (it hardly seems possible...) Anyway, there are hundreds of thousands of records, and a number of pages (which I have forgotten about, but users haven't) that will still feed cells with nulls &/or empty strings. I'd love to start all over again, from the ground up, and make the thing really efficient, but people keep urging me on to the next project.