Getting only filled fields?

mrhinman

One table in my database has over 350 fields. Granted the data in each field is less than 3 characters each, but it's a pretty big table nonetheless.

I'd like to write a script that only presents data on screen from not null fields. If the field has nothing in it (NULL), I do not want to output it. I would really like to write something that read every single column, then checks for nulls, and outputs only filled fields.

Anyone care to help this newbie?

Sxooter

I'm just gonna pseudo code this. You get to make it real code... 🆒

// select * from somebigoldtable;
$rowcount = db_num_rows();
for ($i=0;$i<$rowcount;$i++){
    $row = db_fetch_assoc($res,$i);
    foreach(array_keys($row))=> $k){
        if (db_fieldisnull($res,$i,$k)){
            print $k.":".$row[$k];
        }
    }
}

Made some changes, cause you need the row number...

mrhinman

Sxooter wrote:
I'm just gonna pseudo code this. You get to make it real code... 🆒
// select * from somebigoldtable;
while ($row = db_fetch_assoc()){ 
    foreach(array_keys($row))=> $k){
        if (db_fieldisnull($k)){
            print $row[$k];
        }
    }
}

Forgive my "newbieness" here...

Okay, I'm working in my own bits to make it "real". I'm confused on the "db_fieldisnull($K)" part, though.

Sxooter

No problem. Every database interface has a function like that (or better!) that reports if a field is null. For PostgreSQL it's pg_field_is_null

It checks to see if a field is null or not.

mrhinman

Sxooter wrote:
No problem. Every database interface has a function like that (or better!) that reports if a field is null. For PostgreSQL it's pg_field_is_null

It checks to see if a field is null or not.

So, $k would be the $result?

I'm getting a consistent error with the () in the foreach line of code. Isn't there an "AS" requirement there?

Sxooter

yep. there is. I said it was pseudo code ya know. 🙂

Yeah, it'd be foreach(array_keys($row) as $k){

mrhinman

So, what about the "AS"? MySQL is killing me on the "foreach" statement. Perhaps my syntax is off:

foreach ( (array_keys($row)) => $result )

Parentheses problems, right?

mrhinman

Hey, I realized that you edited your post, so my WHILE loop was busted.

It's about 11 here and I'm calling it a night. I'll check in tomorrow morning.

Thanks for all your help!

Roger_Ramjet

Lots of different ways you can do this, but keeping it simple


// get your query results and include all the validation error trapping
// then process them in an associative array 
while ($row = mysql_fetch_array($result)) {
   foreach ($row as $key=>$val) {
      if (!is_null($val)) echo $key . ' = ' . $val . '<br />';
   }
}

Get each field, check it is not null then output it.

Sxooter, there is no mysql function to test for NULL like there is for pg.

Sxooter

Sxooter, there is no mysql function to test for NULL like there is for pg.

Good lord! How did the api folks miss that one?!

Roger_Ramjet

I guess they just thought the generic is_null function was enough.

mrhinman

Thanks all! Roger's sample worked great. I "real-coded" it, and it comes out fine.

I appreciate the assistance!