Query Breaks When Field Has No Value

trying to do a query that finds property near landmarks

[old_expat]

Hello,

I'm hoping that someone can help with a query problem. I've spent some time looking around the internet but can't find any good examples (which is the way I'm trying to learn).

My basic query looks something like this:

$server="myserver.com";
$user="me";
$password="password";
$database="database";
mysql_connect('localhost',$user,$password);
@MYSQL_SELECT_DB($database) or die("warning");
$query="SELECT * FROM database WHERE thisfield='this' OR thisfield='that' OR thisfield='foo1' OR thisfield='foo2'";
$query .= " ORDER BY ar DESC";
$result=mysql_query($query);
$numrow=mysql_num_rows($result);

It works just fine so long as "thisfield" has some value in the database. But when "thisfield" has no value/is empty, it breaks.

By way of explanation, "thisfield" is for property landmark location codes aka LAX, SFO, ORD and not every property has a location code, so that field is empty in my database.

I want to keep the database flexible enough so that landmark location codes are not necessarily required, but I would rather not go in and hand edit 990 records.

Thanks

[dreamcatcher]

Can you not just pull all entries and then filter afterwards?

$query="SELECT * FROM database ORDER BY ar DESC";
$result=mysql_query($query);
$numrow=mysql_num_rows($result);

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

switch ($row['thisfield'])
{

case 'foo':
//do something;
break;

case 'bar':
//do something;

default:
//empty field. do something
break;

}

}

dc

[old_expat]

I'm not exactly sure what you mean.

Basically, the query is to dispay a list of properties. When there is an empty field, there is no output i.e. no listing of properties.

[wheelie34]

If its similar to a site of mine, a variable $num is passed to display that item, BUT, when people get to that page without $num they get nothing, so what I did was give an alternative

if ($_REQUEST[num]){
do something}

or

if ($_REQUEST[num] == ''){
do something else}

In the do something else bit I chose to randomly display, in your case "one or more properties" by using

$sql="select * from TABLENAME ORDER BY RAND() LIMIT 3";

I use that or something similar many times just random selections.

Hope that helps

[DrDoc]

Can you just add:

"... OR ( thisfield IS NULL OR thisfield='' )"

[old_expat]

"OR ( thisfield IS NULL OR thisfield='' )"

DrDoc .. thanks big time, Pard. Works great .. even makes sense to this old creaky brain.:)