Tip: where 1 , where 0

[bibby]

Users of PhpMyAdmin might have wondered what the deal is with the default query textarea reading
[b]SELECT * from table WHERE 1[/b]

1 is TRUE
0 is FALSE

How does this help?

------------------
[b]WHERE 1[/b]

For starters, for data to be returned, it should at the very least exist.
[i]Where 1[/i] is a condition that demands that the data be true in order to be returned (without though, it is still implied).

[code]
#show me userIDs in users THAT EXIST and userID=1
select userID from users where 1 and userID='1';
# is the same as
select userID from users where userID='1';
[/code]

When building complicated conditionals in your php script, you may find it difficult at times to put all of your [b]AND[/b]s in the right place. Typically I see coders using
[b]WHERE this AND that AND theOther[/b]
But what happens if one day you have to elimate that first condition ('this')?
You may see an error near 'WHERE AND that'.

The way around that is to satisfy the first condition from the start, and simply add 'AND' to the beginning of everything else.

[code]
select *
from table
where 1
and this='this'
and that='that'
and theOther='theOther';
[/code]

If my conditions are php generated, and I have AND in front of each, I could eliminate any or all of them and still have my query run.

------------------
[b]WHERE 0[/b]

[code]select * from users where 0[/code]
This query instantly returns false. "[i]Show me everything that isn't there[/i]".

It's pretty funny, but it comes in handy if, by the same method above, you are constructing a series of [b]OR[/b]conditionals that may or may not be present at any given time.

[code]
select * from users
where 0
OR userID='1'
OR userLastIP='127.0.0.1'
OR userName='admin'

#show me everything in users that DOES NOT EXIST -> OR meets these criteria.
[/code]

At any point in my script, I could decide to use any or none of the OR conditions and the query still runs.

-----
Side benefits:

Copying table structure
[code]
create table cloneUsers
select * from users
where 0
[/code]

Copying structure and data
[code]
create table cloneUsers
select * from users
where 1
#where 1 is optional here
[/code]

These days, I'm using more and more multi-lined queries. Instead of my scripts eliminating single conditions, I'm cutting them by hand. Where 1 or 0 helps me by allowing me to cut whole lines or blocks of lines, and still have the query run.