AND OR code inspection

[grudz]

Hi,

i have this code

SELECT *
FROM listing
WHERE type = 'coltype' OR 
name LIKE '%quicksearch%' OR  
(type = 'coltype' AND name LIKE 'letter%') OR  
(address = 'estaddress' AND type = 'esttype' AND location = 'estlocation') OR   (address = 'estaddress' AND type = 'esttype' AND location = 'estlocation' AND name LIKE 'letter%') OR
  (location = 'locationbutton' AND type = 'locationtype') OR  (location = 'locationbutton' AND type = 'locationtype' AND name LIKE 'letter%') OR  
(name LIKE '%partialname%' AND type = 'partialtype') OR   
(name LIKE '%partialname%' AND type = 'partialtype' AND name LIKE 'letter%') OR 
 (type LIKE '%advtype%' AND music LIKE '%advmusic%' AND location LIKE '%advlocation%' AND events LIKE '%advevents%' AND food LIKE '%advfood%') OR 
 (type LIKE '%advtype%' AND music LIKE '%advmusic%' AND location LIKE '%advlocation%' AND events LIKE '%advevents%' AND food LIKE '%advfood%' AND name LIKE 'letter%')
ORDER BY sqlorderby 

the reason there are some that are almost duplicates (except the name LIKE 'letter%') is because this page is a search results page for about 5 options, above those 5 options, i want to be able to narrow the records by letter (hence the name LIKE 'letter%') anyway.....back to my question

lets say i only put this

(type = 'coltype' AND name LIKE 'letter%') 

it'll correctly show my records for an url like this: www.site.com/page.php?type=value&letter=value

HOWEVER

if i have it the way i have it now

type = 'coltype' OR (type = 'coltype' AND name LIKE 'letter%') 

the same url wont work, but the type would?

any ideas why?

[raf]

I don't understand the question

the same url wont work, but the type would?

but

type = 'coltype' OR (type = 'coltype' AND name LIKE 'letter%') 

is simply pointless. I mean, just fill in actual values
--> if type=5 or (type=5 and name like 'test%')

so if type = 5 --> True. Regardless the name/letter-value
So basiccally it's the same as

type = 'coltype' 

And it's the same with your other and/or checks in the where clause --> half of them our pointless.

[grudz]

im sorry.....the coltype is a $_GET['type']....they mean the same....

the same url wont work, but the type would?

and what i mean by this is the the same url will only work if its something like this
www.site.com/page.php?type=value..... but if i add a type=value&letter=value......it wont work.......

its a long story why i didnt write name = 'coltype' and not name = '$_GET['type']......

also, why i write this

type = 'coltype' OR (type = 'coltype' AND name LIKE 'letter%') 

is because there can be 2 searches.....
a) only for type
b) type + letter

[raf]

also, why i write this


PHP:--------------------------------------------------------------------------------
type = 'coltype' OR (type = 'coltype' AND name LIKE 'letter%')

--------------------------------------------------------------------------------


is because there can be 2 searches.....
a) only for type
b) type + letter

Like i told you, the "OR (type = 'coltype' AND name LIKE 'letter%')" has no effect whatshovever

example

5=5 OR (5=5 and 10=10) --> will return True, because its True OR (True AND True)

5=6 OR (5=6 and 10=10) --> will return False, bacause its False OR (False AND True)

5=5 OR (5=5 and 10=11) --> will return True, because its True OR (True AND False)

So it will only evaluate the "type = 'coltype'" and the OR clause is pointless since it has no effect on the evaluation.


If you can have 2 possible search-conditions (like your a and b) then you need to dynamically build the querystring. Like

$codition= '1=1' ;   // makes it easier, because you don't need to check what the first real  condition is
if (strlen($_GET['type']) >= 1){
   $codition .= " AND type = ' " . $_GET['type'] . "'"
}
if (strlen($_GET['letter']) >= 1){
   $codition .= " AND name LIKE ' " . $_GET['letter'] . "%'"
}
...
$sql="SELECT ... WHERE " . $condition ; 

So you dynamically build the where clause and then add it to your query.