where FALSE vs. where 0=1

blackhorse

In the past, I was working with several database server in different projects, such as sql server, access, interbase, and mysql.

When I need to use "Where FALSE" in the sql, sometimes it didn't work. So I use "where 0=1" instead, which alwasy worked.

Now I am working with mysql mostly. Should I use "where FALSE", to me it seemd more meaningful? or should I keep on using "where 0=1"?

Thanks!

Piranha

I may be stupid, but I can't understand why you would need to use where false in a SQL statement, it simply would not do anything. Can you please show a statement where you have used it?

blackhorse

I need to create a complete sql statement, based on many search criteria.

So it would be

$sql="select * from products where onsale=1 ".$sql1.$sql2.$sql3;

for example, based on the situations these $sql1.$sql2.$sql3 would be assigned different values. such as

$sql1=" AND city_id=1 ";

$sql2=" AND store_id=34 "

...

Also there could be cases that in certain situation I will ask the sql return nothing

For example, in certain case, I want the $sql1 or $sql2 situation cause the whole statement return no results, I will assign the value like this

$sql1= " AND FALSE ";

$sql2= " AND FALSE ";

...

So $sql="select * from products ".$sql1.$sql2.$sql3; will return no results.

Thanks!

Piranha

Why do you ask sql to return nothing? It would be better not to ask it at all. Your way of doing this seems like a very strange solution, just use an if-statement instead.

blackhorse

it is more like that the where clause is created on the fly, there are too many situations to predict the where clause and to preset the if statement to cover all the cases.

Piranha

Then try something like this:

// First do a variable that will indicate if the query should be run at all
// In this case 0 = not decided yet, > 0 = run query, < 0 = Don't run query
$runquery = 0;
// Then create the basic query
$query = "select rows from table where";
if ($var1 == 'abc') {
   query .= " row1 = " . $var1 . " and"
   $runquery = 1;
} else {
   $runquery = -1;
}
if (runquery > -1 && $var2 == 12) {
   $query .= " row2 = true and";
   $runquery = 1;
}
// And so on for all variables you already use in the query
if ($runquery > 0) {
   $query = substring($query, 0, length($query) -3);
   $result = ...

Then you handle all this in the PHP code and don't have to invent strange solutions for the database.