Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using IF in a select query 1

Status
Not open for further replies.

sinbadly

Technical User
Mar 16, 2006
126
GB
I want to say in mysql that if a field is NULL, then ignore it … in a select query statement like this -

$result = mysql_query("SELECT
id,
head,
IF(blurb IS NULL, SUBSTRING(story, 1, (locate( ',', story, 50)))) AS blurb, etc etc

Is there a way, please, Experts?
 
You can't just ignore a field in a record. The nearest you can come is to ignore the record (with "WHERE blurb IS NOT NULL"). If you want to use a default value when a field is null, then you can use IFNULL(blurb,'defval') or COALESCE(blurb,'defval').
 
Thanks very much, Tony. I searched through my books for this and found nothing. Very grateful for you sharing your knowledge.
 
Tony, do I have to declare a default value somewhere, or if a default value isn't declared, is it taken to be, well, null.

I want to use it in a select query that populates a webpage thanks to php. Some fields are null, and where I can't find a way for php to deal with an empty field, it would be handy to do that in mysql.

For example, if "intro" is empty, then just ignore it. Cheers.
 
It's not very clear what you're looking for.

If you just want to stop null values being passed to your program, then you could say something like SELECT IFNULL(blurb,'empty') which would return either the field's value or the value "empty" if it is null.

Remember that the number of columns in the result set is fixed, determined by the number of fields in your select list. If there are 6 fields in your list, there will always be 6 in the result set; it's up to your program to decide which of those fields to process or ignore.
 
Sorry. Say the select query provides the fields 'head', 'story', and 'byline', there are times when there won't be a byline. So I am looking for a way to say 'if byline is empty, just ignore it and continue.'

However, I think you are saying that if mysql provides six fields from the database, then php will expect to be handling six fields.

So - if that's right - I suppose I am looking for an answer in the wrong place. I have to find a way round the problem with php. Your help, yet again, is appreciated, Tony.
 
That's exactly it.

A query result set is like a (unformatted) spreadsheet with column headings; the number of cells in each line is the same; you can't make a cell "disappear" even if it's empty. You just have to adjust your processing to cope with the empty cells.
 
Cheers, Tony, all noted. Thanks very much for the explanation. All the best.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top