...

View Full Version : Quick question on an optional WHERE clause



flexillu
04-13-2011, 05:46 PM
The below code seems to be working but i just wanted to check its working because its correct, and not a fluke!



SELECT * FROM TABLE WHERE
i.gender = '$gender'
AND c.catName = '$catName'
AND t.typeName = '$typeName'
OR v.varName = '$varName'";

I want this statement to be able to handle queries that may or may not contain a varName.

So if the $varName field is null it will just disregard it, but if there is a $varName it will be used in the query.

So have i done this right? Thanks

Old Pedant
04-13-2011, 09:16 PM
???

No, I don't think so.

If $varName is blank (or null...same thing in PHP when you then try to use the value as part of a longer string), the query becomes:


SELECT * FROM i,c,t,v WHERE
i.gender = 'M'
AND c.catName = 'framitz'
AND t.typeName = 'zoogle'
OR v.varName = ''

And so it will only match those records where v.varName is blank.

There's an easy answer.



if ( $varName == "" ) $varName = "%";

$sql = "SELECT ... WHERE v.varName LIKE '$varName' ";

It won't give you the best performance (better is to have your PHP code simply not include that condition in the SQL at all), but if you aren't talking tens of thousands of records it should be fine.

Old Pedant
04-13-2011, 09:22 PM
DOH on me.

Yes, in the *EXACT* query you showed, it will work!!!

That's because of the *PRECEDENCE* of the AND and OR operators!!!

Your query is *REALLY* doing


SELECT * FROM i,c,t,v
WHERE
( i.gender = 'M' AND c.catName = 'framitz' AND t.typeName = 'zoogle' )
OR
v.varName = ''

So what if the v.varName = '' is never true???

If the other three conditions are true, it NEVER matters what v.varName is!!!

Remember: AND conditions have a higher precedence than OR conditions.

*IF* that OR in the query was an AND, or maybe if the query was intended to be


SELECT * FROM i,c,t,v
WHERE
i.gender = 'M'
AND
c.catName = 'framitz'
AND
( t.typeName = 'zoogle' OR v.varName = '' )

then of course it wouldn't work.

So...it just happens to work in this combination of ANDs and ORs.

flexillu
04-14-2011, 09:48 AM
Thanks for your help on this.

It seems i was wrong, its not working actually. I'll just check whether $varName is null first in the script.

Then i could

A) set varName to "None", because the records do actually have None i they don't have a variation. (doh)

Or

B) Perform query x if varName is not there and perform query y if varName is there.

Which would you advise as the best method?

Old Pedant
04-14-2011, 08:03 PM
Best results is to not use the condition if you don't need to. Less work for the DB.

But that's pretty easy:


$sql = "SELECT * FROM i,c,t,v WHERE i.gender = '$gender' AND c.catName = '$catName' AND t.typeName = '$typeName' ";

if ( $varName != "" ) $sql .= "OR v.varName = '$varName'";

...



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum