mySQL SELECT question

cmason22

this is probably pretty simple but i've not yet been able to figure out.

i have a table with structure:

some of the fields do not have values (not sure if they are NULL or empty....)

I want to select all where ID=value and FIELD has a value. in other words, i want to ignore all the fields without values. is there is simple command for this?

SELECT from tablename where LENGTH(any field*) > 0

something along those lines.

thanks!

AstroTeg

SELECT * FROM tablename WHERE myfield NOT NULL

MySQL considers an empty field as null.

cmason22

yes, but wouldn't that require me to specify the fields? or is 'myfield' a catch-all? i'm trying to avoid having to specify each field since it's a very big table.

Thanks!

cmason22

sorry, i think i need to clarify...

the results will be returning a SINGLE ROW.

what i want is to only return the fields from that row that contain data.

AstroTeg

Well, what's your column names?

If you're saying you have 2 columns and one may contain the string "field" and the 2nd column will contain null or data, then the query might look like:

SELECT * FROM mytable WHERE column1 = 'Field' AND column2 IS NOT NULL AND column2 != ''

Note: I goofed twice:

I forgot to include the "IS" in "IS NOT NULL"
NULL and "" have 2 different meanings. NULL means there was no data. "" means there was data and it was blank.
More details here: http://www.mysql.com/doc/en/Problems_with_NULL.html

cmason22

i know this is hard to translate on message board.

in "plain english" what i want the SQL to say is:

"select all columns that have a value from TABLENAME where ID=$id"

AstroTeg

By definition:

select all columns that have a value from TABLENAME where ID=$id

Is:

SELECT * FROM tablename WHERE ID = $id

AstroTeg

If you want to only get all ROWS where the column(s)s that have a value, then you'd have to include them all in the WHERE clause.

But there isn't any easy way to bring back all "columns that have a value" without bringing back the entire row (or at least the columns you're interested in).

I'm wondering if you're bumping up against a database/table design issue. If you're table design was tweaked, you might find the query to be a lot easier to handle. But I'm not sure what you're trying to go after. Can you post a table design (with column a names and a few rows of data?) Then post the columns and data you're expecting back. That would help me out...

keith73

Originally posted by cmason22
i know this is hard to translate on message board.

in "plain english" what i want the SQL to say is:

"select all columns that have a value from TABLENAME where ID=$id"

what you aren't realizing is that if you have a record in the database, every field, or column, has a value. that value could be null or could have length > 0 so mysql will not simply tell you that a row has data in it unless you ask it to. that means if you want it to tell you that 3 rows have a value for column1,column2 or column3, you need to tell it so.

The second thing is, why would you have a record with no data in any field, except for the ID. That looks like you aren't testing your data before inserting it and can be a bad thing.

keith

cmason22

Originally posted by keith73

The second thing is, why would you have a record with no data in any field, except for the ID. That looks like you aren't testing your data before inserting it and can be a bad thing.

keith [/B]

optional fields on a form will sometimes not have data. there will be data in many of the fields for each row (sorry if i gave the impression that rows could contain on ID data). but there will be many optional (empty) fields that i want to ignore.

orionblue

Hi there -

Did anyone resolve this? I'm having similar issues, and I can't wrap my head around it. Each row has an 'id' column, and there are 20 other rows that hold optional info - the desired effect would be -

select all columns from table where row id = x

display:
col 1 value
col 3 value
col 4 value
col 7 value

(another row might contain a different combination of values, based on input from checkboxes).

There's got to be a simple way to do this, any suggestions?

thanks...

cmason22

orionblue:

i figured out a way to solve my problem. not sure exactly what you are doing, but you might try my technique. there certainly might be a better way but....

what i did was select * and then loop thru the results and only print when there's a value.

example:

$query="SELECT * FROM tablename";
$results=mysql_db_query ($db , $query, $connection);

while($Row=mysql_fetch_array($result)){

for($n=0 ; $n < sizeof($Row) ; $n++){

if(strlen($Row[$n]) > 0){ print "value is: $Row[$n]";

}

orionblue

holy crap cmason22 it worked, in seconds! Thank you so much, I've been trying to figure out a way to do this (and still have editable check boxes in a form) on and off for weeks, or longer. I just didn't grasp the proper concept.

thanks again, I truly appreciate your help.

😃

cmason22

cool. glad i could help.