Checking for empty fields in mysql table

Question

I have a table with 12 columns and 200 rows. I want to efficiently check for fields that are empty/null in this table using php/mysql. eg. "(col 3 row 30) is empty". Is there a function that can do that? In brief: SELECT * FROM TABLE_PRODUCTS WHERE ANY COLUMN HAS EMPTY FIELDS.

there are no "row 30" in your table. it's a heap, not ordered list. — Your Common Sense, Jan 06 '11 at 17:53

score 4 · Answer 1 · answered Jan 06 '11 at 12:33

4

empty != null

select * from table_products where column is null or column='';

answered Jan 06 '11 at 12:33

ajreal

46,720
11
89
119

score 1 · Answer 2 · answered Jan 06 '11 at 12:29

1

SELECT * FROM table WHERE COLUMN IS NULL

answered Jan 06 '11 at 12:29

Sarfraz

377,238
77
533
578

score 1 · Answer 3 · answered Jan 06 '11 at 12:38

1

As far as I know there's no function to check every column in MySQL, I guess you'll have to loop through the columns something like this...

$columns = array('column1','column2','column3');
foreach($columns as $column){
  $where .= "$column = '' AND ";
}
$where = substr($where, 0, -4);
$result = mysql_query("SELECT * FROM table WHERE $where",$database_connection);
//do something with $result;

The = '' will get the empty fields for you.

answered Jan 06 '11 at 12:38

Will

1,893
4
29
42

Hello, thanks. instead of listing the columns manually, is there a way of listing all columns at once? – karto Jan 06 '11 at 15:52

score 1 · Answer 4 · answered Jan 07 '11 at 15:16

you could always try this approach:

//do connection stuff beforehand

$tableName = "foo";

$q1 = <<<SQL
SELECT
  CONCAT(
    "SELECT * FROM $tableName WHERE" ,
    GROUP_CONCAT(
      '(' ,
      '`' ,
      column_name,
      '`' ,
      ' is NULL OR ',
      '`' ,
      column_name ,
      '`',
      ' = ""' , ')'
      SEPARATOR ' OR ')
    ) AS foo
FROM
information_schema.columns
WHERE
table_name = "$tableName"

SQL;

$rows = mysql_query($q1);

if ($rows)
{
    $row = mysql_fetch_array($rows);
    $q2 = $row[0];
}

$null_blank_rows = mysql_query($q2);

// process the null / blank rows..

score 1 · Answer 5 · answered Feb 10 '12 at 21:20

<?php
    set_time_limit(1000);

    $schematable = "schema.table";

    $desc = mysql_query('describe '.$schematable) or die(mysql_error());

    while ($row = mysql_fetch_array($desc)){

        $field = $row['Field'];
        $result = mysql_query('select * from '.$schematable.' where `'.$field.'` is not null or `'.$field.'` != ""');

        if (mysql_num_rows($result) == 0){
            echo $field.' has no data <br/>';
        }

    }
?>

rahulcs754 · Answer 6 · 2016-09-17T11:23:38.337

0

Check this code for empty field

$sql = "SELECT * FROM tablename WHERE condition";

$res = mysql_query($sql);

while ($row = mysql_fetch_assoc($res)) {

    foreach($row as $key => $field) { 
        echo "<br>";
        if(empty($row[$key])){
            echo $key." : empty field :"."<br>";        
        }else{
            echo $key." =" . $field."<br>";     1
        }
    }

}

edited Sep 17 '16 at 11:23

answered Sep 17 '16 at 09:41

rahulcs754

39
1
1
5

score 0 · Answer 7 · answered Jan 06 '11 at 12:41

$sql = "SELECT * FROM TABLE_PRODUCTS";
$res = mysql_query($sql);

$emptyFields = array();

while ($row = mysql_fetch_array($res)) {
    foreach($row as $key => $field) {
        if(empty($field)) || is_null($field) {
            $emptyFields[] = sprintf('Field "%s" on entry "%d" is empty/null', $key, $row['table_primary_key']);
        }
    }
}

print_r($emptyFields);

Not tested so it might have typos but that's the main idea. That's if you want to know exactly which column is empty or NULL.

Also it's not a very effective way to do it on a very big table, but should be fast with a 200 row long table. Perhaps there are neater solutions for handling your empty/null fields in your application that don't involve having to explicitly detect them like that but that depends on what you want to do :)

THANKS pal, It works like magic now. just have to format it a bit. Saved me lots of time. — karto, Jan 06 '11 at 17:04

score 0 · Answer 8 · answered Jul 24 '18 at 14:45

Here i'm using a table with name words

$show_lang = $db_conx -> query("SHOW COLUMNS FROM words");
        while ($col = $show_lang -> fetch_assoc()) {
            $field = $col['Field'];
            $sel_lan = $db_conx -> query("SELECT * FROM words WHERE $field = '' ");
            $word_count = mysqli_num_rows($sel_lan);
            echo "the field ".$field." is empty at:";
            if ($word_count != 0) {
              while($fetch = $sel_lan -> fetch_array()){
                echo "<br>id = ".$fetch['id']; //hope you have the field id...
              }
            }
        }

score 0 · Answer 9 · answered Jul 24 '18 at 15:23

0

There is no function like that but if other languages are allowed, you can extract the structure of a table and use that to generate the query. If you only need this for a single table with 30 columns, it would be faster to write the query by hand...

answered Jul 24 '18 at 15:23

DataVader

740
1
5
19

Checking for empty fields in mysql table

9 Answers9