davisondraft
03-24-2008, 09:41 AM
Hi all - I'm a newbie PHP/MySQL developer and working on my first site. As part of the site, users will need to search the database for "projects" near them, as posted by other users. I have a table that basically looks like this:
---------------------------------------
proj_name | desc | category | lat | lon
---------------------------------------
| data | data | data |data |data
| data | data | data |data |data
| data | data | data |data |data
---------------------------------------
When a user searches (either by keyword against the description column, or selecting by category) I'd like to be able to automatically determine which entries are within a certain amount of miles from them and display only that list.
I have a stored procedure that calculates distance given two different lat and lon entries (the searcher's, and those assigned to the project) and returns it as a column 'distance'. No problems there, it works fine. (I'm also geocoding from zip code, so users don't have to know their lat/lon).
If I could use stored functions, i'd do this: select * from projects where desc like %keyword% and GetDistance(lat1, lon1, lat2, lon2) < 50. However, I can't use stored functions, as my hosting provider (Go Daddy) doesn't allow it.
So, here's my current thought for a solution...and I want to know if there's a better, more efficient way of doing this.
Query 1 - select * from projects where desc like %keyword%. <-- this query finds all the projects containing the search parameters, narrowing the list down. I'd want this to then output the results to another temporary table, where I'd run...
Query 2 - run the stored procedure on the temporary table, having it append a dynamic column onto the table with the distance calculated in miles for each record.
Query 3 - select * from temp_table where distance < 50.
That's three queries to return one set of results. Is this a terrible way to set this up? It feels really clunky, but then I don't really know what MySQL's performance limitations really are. This may be a piece of cake for it.
Thoughts? Sorry this is so long!
---------------------------------------
proj_name | desc | category | lat | lon
---------------------------------------
| data | data | data |data |data
| data | data | data |data |data
| data | data | data |data |data
---------------------------------------
When a user searches (either by keyword against the description column, or selecting by category) I'd like to be able to automatically determine which entries are within a certain amount of miles from them and display only that list.
I have a stored procedure that calculates distance given two different lat and lon entries (the searcher's, and those assigned to the project) and returns it as a column 'distance'. No problems there, it works fine. (I'm also geocoding from zip code, so users don't have to know their lat/lon).
If I could use stored functions, i'd do this: select * from projects where desc like %keyword% and GetDistance(lat1, lon1, lat2, lon2) < 50. However, I can't use stored functions, as my hosting provider (Go Daddy) doesn't allow it.
So, here's my current thought for a solution...and I want to know if there's a better, more efficient way of doing this.
Query 1 - select * from projects where desc like %keyword%. <-- this query finds all the projects containing the search parameters, narrowing the list down. I'd want this to then output the results to another temporary table, where I'd run...
Query 2 - run the stored procedure on the temporary table, having it append a dynamic column onto the table with the distance calculated in miles for each record.
Query 3 - select * from temp_table where distance < 50.
That's three queries to return one set of results. Is this a terrible way to set this up? It feels really clunky, but then I don't really know what MySQL's performance limitations really are. This may be a piece of cake for it.
Thoughts? Sorry this is so long!