Help -- Want to exclude rows with three fields empty

bbeban

Been searching Google, searching this site, but can't seem to find an answer: How do I show only rows that have values for all three non-required fileds empty?

Example: I have a form that users fill out, and the fields "title" "author" and "year" are optional. Many records are missing one or two of these, but only certain ones are missing all three. I want to show the data in a table but exclude any instance where ALL THREE are blank, because that configuration classifies the row of data as a different set which I'm displaying on a different page.

I have tried

 SELECT FROM table WHERE title !='' AND author !='' AND year !='' ";

But this does not work, I think for obvious reasons. I have tried many, many forms of this but everything I do either shows nothing or excludes any row where any one of the three items is blank.

Can someone boot me into the right direction?

Many thanks -- Bob

LordShryku

Are the fields blank or null?

bbeban

I imported old stuff -- in those cases the fields are blank. As for anything added from here forward, the fields are null. In either event, nulls are allowed in those three fields.

LordShryku

Well then, it sounds like you need to check for blanks and nulls....

You also need to specify what you're selecting. In this case, I'll use *

SELECT *
  FROM table 
 WHERE title != ''
   AND title IS NOT NULL
   AND author != ''
   AND author IS NOT NULL
   AND year !=''
   AND year IS NOT NULL

bbeban

Tried:

SELECT * FROM table WHERE title != ''
   AND title IS NOT NULL
   AND author != ''
   AND author IS NOT NULL
   AND year !=''
   AND year IS NOT NULL
ORDER BY $column $sort";

and I also tried

SELECT * FROM table WHERE title != '' AND title IS NOT NULL AND author != '' AND author IS NOT NULL AND year !='' AND year IS NOT NULL ORDER BY $column $sort";

and I also tried

SELECT * FROM table WHERE title != '' AND title = NOT NULL AND author != '' AND author = NOT NULL AND year !='' AND year = NOT NULL ORDER BY $column $sort";

and in every case I got this error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Can you tell me where I went wrong? This is on Windows by the way... don't know if that makes a difference...

bbeban

P.S. I can't see at this point how your suggestion, once working, is going to turn up different results than the example I gave in my oiginal posting... but I am willing to wait and see if it is different in some way...

Bottom line, I need only results where ALL THREE fields are empty to be skipped... if one is there, or two are there, that row should show up. Make sense?