[SOLVED] Retrieving all fields that are NOT NULL....

[galvin]

I have a database with 22 colums (listid, listname, listitem1, listitem2 all the way through listitem20).  Listid and listname will always be NOT NULL but the listitems may or may not be null. 

 

How do I format the MySQL query to bring back the listid, listname and all the listitems that are NOT NULL.  I tried the following but it didn't like it...

 

$sql = "SELECT * 
    FROM listtest
    WHERE listid = 1 
    AND * IS NOT NULL";

 

In plain English, I want the query to say..."Along with the listid and listname, also give me all the listitems that have something filled in."

Any idea what I'm doing wrong?

 

[fenway]

Well, this is why you need to normalize your table design.

 

Why not handle this in php?

[galvin]

FYI, you had previously sent me a link tutorial on "normalizing a database" and I swear I read it but it's becoming obvious I need to read it over a few more times

 

I will eventually have over 1,000 unique lists (some with 20 list items, some with up to 40 list items).  So we're talking at least 20,000 list items overall and since every single one is different, it seems to me like there is no simpler way to store the items in a database rather than having those columns for listitem1, listitem2, listitem3, etc. 

 

When you say "why not handle this in PHP?" do you mean don't use a database at all?  Because my plan was to retrieve the info from the database using MySQL and then as usual, I would create a PHP array of the results and go from there.  Are you saying to cut out the database completely and just create the PHP array with the list info?

 

 

 

 

[fenway]

You need 3 tables -- one of lists, one of items, and one that links lists to items.  That's it.

 

As for "in php", i mean the logic for if it's null or not.  But if you normalize, you won't EVER have that issue, and you can do it all very efficiently in the db.

[galvin]

Great, I'll try that out.  Thanks!!!