5 replies to this topic

[docmonkey]

Hey all,

I'm attempting to select all fields in a table with a particular ID, but exclude the fields that have no data.

SELECT * 

FROM managers

WHERE id=19;

So this query would return all fields for the record with an id of 19, but i'd like to exclude the fields that are NULL.

Thanks

[WingedPanther]

SQL doesn't work that way. You can usually test whether a field was null after you return it, but in advance, the database doesn't know whether your query will return 1 record or 100. There is nothing about the statement itself to indicate it either way. If it returns 100 records, then do you want each record to contain different fields, based on which are not null for that record, or just the ones that are not null for ALL records.

[docmonkey]

WingedPanther said:

SQL doesn't work that way. You can usually test whether a field was null after you return it, but in advance, the database doesn't know whether your query will return 1 record or 100. There is nothing about the statement itself to indicate it either way. If it returns 100 records, then do you want each record to contain different fields, based on which are not null for that record, or just the ones that are not null for ALL records.

Thanks for that help. What is the best way to do this? Basically I have some PHP code that displays all the fields on a page and the values next to them. For example "Location: USA" with location being the field name and USA being value. I want to show all the fields, but exclude the ones that are empty or null. What is the best method to do this?

Thanks

[WingedPanther]

Do a test for null before displaying each value. If the field is null, display a non-breaking space instead ( )

PHP: is_null - Manual

[docmonkey]

So this is my PHP code

echo "<b>Nationality:</b> ".$info['nationality'] . " <br>";

And what I want to do is show all of this if the field 'nationality' has data in it, but show none of it if it is empty or null. Sorry this is sort of switching over to PHP.

[docmonkey]

Figured it out!