how to select only non null value fields

donoe

Hi, i've a problem with a mysql query but i'm not sure if it mysql can do it, or i can only handle it with php.

i've a table that looks like this

id | s1 | s2 | s3 | s4 | s5 |

7 | NULL| 1 | NULL| NULL | 1 |

i need to do a search, that only returns fields s2 and s5, because i do not need fields that have NULL values in them. should this be done in sql query, or i should rely on php to parse e results for me? (i've got many s* fields, so this may take a while)

a short ques: if i want to search two tables, can i do this? select * from tbl1,tbl2 where tbl1field=1,tbl2field=2 ?

would appreciate any help! thank you!

Kudose

You're wanting something like:

mysql_query("SELECT * FROM table WHERE id IS NOT NULL");

For joins, you'll want something like:

mysql_query("SELECT * FROM table1 AS t1, table2 AS t2 WHERE t1.s2=t2.s5");

Not exactly the queries you need, but something to base your needs from.

donoe

thank you kudose for your reply 🙂
i've solved e problem after changing my table structure to stutterbug's recommended one.

i understand when you say where id is not null, however what if i have 3 fields, say field1(null value), field2, field3 (null value).

is there a command for it to detect field2 as a valid field only during a select? (leaving out field1 and field3 in the $result because i wouldnt need them..

Roger_Ramjet

There are the control flow functions , but I'm sure that they do not apply in your case. Basically with SQL, you get the columns you ask for in every row in the results: you can not get fields 1,3,5 for one row and fields 1,3 only for another row. You have to test for and discard null values in your php script after the query is run. The control flow functions would allow you to test for and substitute values, but not to remove fields.