Jump to content

Tip: where 1 , where 0


Recommended Posts

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).

#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';

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.

select *
from table
where 1
and this='this'
and that='that'
and theOther='theOther';

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.

select * from users
where 0
OR userID='1'
OR userLastIP=''
OR userName='admin'

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

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
create table cloneUsers
select * from users
where 0

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

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.