Not Returning Records with NULL value

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
theoph
Forum Commoner
Posts: 47
Joined: Wed Jul 30, 2003 5:26 pm
Location: Lexington, KY USA

Not Returning Records with NULL value

Post by theoph »

The following code:

Code: Select all

SELECT laos.id, laos.oikos_id, laos.last, laos.first, laos.directory, laos.sex, laos.race, laos.status, laos.member, laos.p_cell, laos.s_cell, DATE_FORMAT(laos.birthday, '%c/%d/%Y') AS birthday, DATE_FORMAT(laos.aniversary, '%c/%d/%Y') AS aniversary, UNIX_TIMESTAMP(DATE_ADD(laos.datestamp, INTERVAL 3 hour)) AS datestamp
FROM laos
WHERE laos.last LIKE 'xLast%' AND laos.sex LIKE '%xSex%' AND laos.status LIKE '%xStatus%' AND laos.race LIKE '%xRace%' AND laos.p_cell LIKE 'xPG%'
ORDER BY laos.last ASC, laos.position, laos.birthday
The records which are found are the records that have a value in laos.p_cell. I want to be able to return all records if that column has any values in it or not.

In other words, if laos.p_cell has a NULL value that record is skipped.

I don't want it skipped unless of course a value has been entered by the client through entering data in the search form.

Any suggestions?

-Dave
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

IS NOT NULL ?

Code: Select all

SELECT laos.id, laos.oikos_id, laos.last, laos.first, laos.directory, laos.sex, laos.race, laos.status, laos.member, laos.p_cell, laos.s_cell, DATE_FORMAT(laos.birthday, '%c/%d/%Y') AS birthday, DATE_FORMAT(laos.aniversary, '%c/%d/%Y') AS aniversary, UNIX_TIMESTAMP(DATE_ADD(laos.datestamp, INTERVAL 3 hour)) AS datestamp 
FROM laos 
WHERE laos.last LIKE 'xLast%' AND laos.sex LIKE '%xSex%' AND laos.status LIKE '%xStatus%' AND laos.race LIKE '%xRace%' AND laos.p_cell LIKE 'xPG%' AND laos.p_cell IS NOT NULL
ORDER BY laos.last ASC, laos.position, laos.birthday
theoph
Forum Commoner
Posts: 47
Joined: Wed Jul 30, 2003 5:26 pm
Location: Lexington, KY USA

Worked!!

Post by theoph »

When I included the following:

Code: Select all

(laos.p_cell LIKE '%xPG%' OR IS NULL)

in the MySQL script it worked!

Thanks :D
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Duh!

"I don't want it skipped" you wrote... I was reading it upside down...
Post Reply