November 8th, 2012, 11:14 PM
Querying a zip code table to get approximate location
(I just need a rough proximity idea so don't care about precision with merging longitude lines etc. I am using roughly 50 miles per degree but this is all irrelevant. My problem is with the query)
I have a php script where I check a given zip code against a customer table with radius, and a huge zip code table IF the customer has a zip code specified in the zip code column.
I am having a hell of a time with this but I'm sure it's obvious and I'm just really tired. Query is getting complex as this is a CRM with new features being added daily.
First I query the zip code table to get longitude and latitude of the GIVEN zip.
[store longitude as $longitude etc.]
SELECT longitude, latitude, city FROM zipcodes WHERE zipcode = $zip
Now I'll try to find customers who meet the WHERE criteria below within long and lat (OR bypass geolocation if that customer zip is NULL) by querying the zip table for matches within the long/lat range. I know JOIN is not the right thing here, I'm sure this requires some kind of CASE statement but I can't work it out.
To summarize, the query should return customers within long/lat limits OR who do not have a zip specified.
, MAX(xferleads.timestamp)AS maxTimeStamp
, COUNT( CASE WHEN xferleads.timestamp >= CURRENT_DATE
AND xferleads.timestamp < CURRENT_DATE + INTERVAL 1 DAY
ELSE NULL END ) AS countdaily
LEFT JOIN xferleads ON xferleads.customer = customers.id
LEFT JOIN fulfillment ON fulfillment.id = customers.id
WHERE (customers.lead_type = '$prenon' OR customers.lead_type = 'ANY' )
AND $callerac IN (ac0,ac1,ac2,ac3,ac4)
AND active = 1
AND $dow = 1
AND (customers.zip IS NULL OR
(longitude-(radius/50)) AND (longitude+(radius/50)))
(latitude-(radius/50)) AND (latitude+(radius/50)))
HAVING countdaily < (daily * 1.5)
November 9th, 2012, 11:56 PM
November 10th, 2012, 01:14 AM
If you know how to write the query to retrieve zips within an area, and you know how to write the query for customers without zips, why not just UNION them together?
Oh, and is the extra overhead in working out the exact distance really significant. Seems to me that it makes your results much more useful and scale able!
Finally, please don't post in multiple forums without mentioning in one or other (or both) of the threads that that's what you're doing. People get insanely upset about that kind of thing.
November 10th, 2012, 03:33 PM
I'm still learning SQL and the syntax... i need an example of what to do so I can understand better.
Originally Posted by cafelatte