Empty field in row

Sharif

Is there a way in PHP to check if a mysql field is empty, meaning it has no data in it?

Say for row 1, the fields NAME, TYPE, STATUS, PIC are there.

NAME = BLAH
TYPE = 2
STATUS = 5
PIC = <blank>

How can I check if the PIC field is blank?

LordShryku

SELECT * FROM table WHERE field IS NULL

onyx

Well you can either do it via MySQL and hence use a where clause to search if the field is null or empty.

select * from table where pic='';

or you could just select everything and then when going through the rows determine which rows are empty.

So:

select * from table;


//mysql stuff setting $query
while($row = mysql_fetch_array($query))
{
if($row['pic'] != "")
{
//do something
}
else
{
//do something else
}
}

LordShryku

Doing it through SQL, you need to realize there is a difference between a NULL value and ""

Sharif

I was trying to do:

if($row['pic'] == '' || empty($row['pic']))
{
     echo 'No pic available';
}
else
{
     $p = $row['pic'];
}

onyx

Yes it is important to note that if you are searching to determine if a field contains nothing, ie an empty string. or if they are null are actually two different things.

It is possible to set things as empty strings in MySQL which is not the same as setting them as null. If you want to discover if a field is null or empty then you will have to search on both.

ie.

select * from table where pic=null or pic='';

Sharif

THAT WORKED!!!

Any bugs in that?

Sharif

Ah ok,

$sql = "SELECT pic FROM table WHERE pic = NULL or pic =";

if(mysql_query($sql))
{
    echo 'There is a pic';
}
else
{
    echo 'No Pic';
}

That should work as well right?

onyx

Well that will check to see if the value was null or an empty string.

However I do not think what you are doing with the php isn't correct for what you want. The function mysql_query(SQL) returns false only when the SQL itself was incorrect.

http://uk2.php.net/manual/en/function.mysql-query.php

You need something more like:


$sql = "select * from table where pic=null or pic=''";
$query = mysql_query($sql);

while($row = mysql_fetch_array($query))
{
//see my previous post
}

LordShryku

The syntax used should be "is null".

$sql = "SELECT * 
        FROM   table 
        WHERE  pic IS NULL
        OR     pic=''";