Cant incorporate OR operator in search query.

[justride]

Hello,

I have 2 tables, one with restaurants and one with US centroids, latitudes longitudes, zipcodes for varius central cities in the us.

I have a query that I got working, that uses these 2 tables, it selects all restaurant data from table Restaurants city/state/zip... where the restaurant zipcodes are within the dynamic radius specified of the zipcode(centroid) specified.

This works fine, It also returns the distance as well.

I would also like to select restaurants records regardless if the the distance between zipcodes only IF the Restaurant City matched the CIty I pass to the query. I tried adding an OR operator but my query hung.

Here is my query:

SELECT R.RESTID,R.RESTNAME,R.RESTADDRESS1,R.RESTCITY,R.RESTSTATE,R.RESTPHONE1,R.RESTPHONE2,R.RESTPHONE3,R.RESTZIP1,R.GENRE,R.SERVICE,
			     TRUNCATE (acos( (sin( Z1.LATITUDE * 0.017453293 ) * sin( Z2.LATITUDE * 0.017453293 ) ) + (cos( Z1.LATITUDE * 0.017453293 ) * cos( Z2.LATITUDE * 0.017453293 ) * cos( (Z2.LONGITUDE * 0.017453293) - ( Z1.LONGITUDE * 0.017453293 ) ) )) *3956,1) AS DISTANCE 
			     FROM RESTAURANTS R, DINEZIPCODES Z1,DINEZIPCODES Z2 
			     WHERE Z1.ZIPCODE = '$centroid'  AND acos( (sin( Z1.LATITUDE * 0.017453293 ) * sin( Z2.LATITUDE * 0.017453293 ) ) + (cos( Z1.LATITUDE * 0.017453293 ) * cos( Z2.LATITUDE * 0.017453293 ) * cos( (Z2.LONGITUDE * 0.017453293) - ( Z1.LONGITUDE * 0.017453293 ) ) )) *3956 
			     BETWEEN 0 AND '$radius' AND R.RESTZIP1 = Z2.ZIPCODE ORDER BY DISTANCE ASC

If I add to the where clause OR R.RESTCITY = '$CITY' it will hang.
Any suggestions?

THANKS SO MUCH!!!

 

[ericbrunson]

It hangs if you paste it into mysql with real values instead of $variables or it hangs in whatever scripting language you're using?

 

[justride]

im using php, when i paste into mysql I use real values and it still hangs. Im not sure I have the query right. ANy suggestions

 

[TonyGroves]

I can see a problem in your query (there should not be a space between TRUNCATE and the following bracket), which would cause a MySQL syntax error. Is that what you mean?

 

[r937]

it will also hang if you just append the OR condition by itself without taking care that the 3 tables in the FROM clause are properly joined

you have this --

  FROM RESTAURANTS R
     , DINEZIPCODES Z1
     , DINEZIPCODES Z2 
 WHERE Z1.ZIPCODE = '$centroid'  
   AND [i]expression involving Z1 and Z2[/i]
            BETWEEN 0 AND '$radius' 
   AND R.RESTZIP1 = Z2.ZIPCODE

and if you just add the OR, you will basically have two conditions in the WHRE clause --

  FROM RESTAURANTS R
     , DINEZIPCODES Z1
     , DINEZIPCODES Z2 
 WHERE [b][COLOR=red]Z1.ZIPCODE = '$centroid'  
   AND [i]expression involving Z1 and Z2[/i]
            BETWEEN 0 AND '$radius' 
   AND R.RESTZIP1 = Z2.ZIPCODE[/color][/b]
    OR [b][COLOR=green]R.RESTCITY = '$CITY'[/color][/b]

whenever the 2nd condition (the OR) is true, that particular row of R is joined to every row of Z1 and every row of Z2 -- a massive cross join, which effectively hangs the server while it's retrieving all the Z1 times Z2 combinations


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[justride]

Thanks for your resposnes, are you showing me a soution or showing me that my idea wont work? Im sorry, im just a little confused.
I appreciate all your help though!
Thanks,
Chris

 

[r937]

i was showing you why your query wasn't working

i have no idea how to correct it, because i don't know how you want R.RESTCITY='$CITY' to be combined with all the other conditions in the WHERE clause

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[Bastien]

echo out the query before execution and ru it thru the mysql gui of your choice to let it show you where the errors might be

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[r937]

bastien, if you would read the thread, you will see that there is no syntax error

the query runs, it just hangs, that's all

;-)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[justride]

If I make RESTCITY <> '$city' it runs, but if I make it RESTCITY = '$city' it hangs. Im going to try running 2 queries and unioning the two record sets.

 

[justride]

using UNION worked for my needs, thanks for eveybodys time and effort!