Page 1 of 1
Multiple Tables
Posted: Tue Dec 05, 2006 4:12 pm
by WanamakerStudios
Maybe someone can help. I know a bit about PHP and MySQL, but seem to have undertaken a project that might go beyond my knowledge. I currently have a few tables within a database that I need to cross reference against in order to get the result set that I need. Currently I have a zip code table so I can do a radius limit, a dealer lot table with all of the dealer's information and a vehicle table with all of the inventory for each of the dealers in the dealer table. Basically, what I need to be able to do is provide the script with a given zip code and radius. The script must then find the coordinates of that zipcode, do mathematical calculations to determine all of the zipcodes within the radius of the given zipcode, find all of the dealers within matching zipcodes and then display each of the vehicles that are on their lot. I also need the user to be able to sort the results by year, make, model, color, price and distance. Its your basic vehicle search as you would find on Automotive.Com or Cars.Com, but we need to build something from the ground up around a data feed we're receiving. I currently have everything written out in multiple SELECT requests, but after searching the entire US worth of zip codes, 8,000+ dealers and 600,000+ vehicles ... the search tends to take a bit longer than it should. Any help would be greatly appreciated!
Posted: Tue Dec 05, 2006 5:24 pm
by feyd
Sounds like a complicated, but potentially time saving JOIN query. It may be more straight forward to use subqueries, but they can sometimes take more time than a JOIN would.
Posted: Wed Dec 06, 2006 9:13 am
by WanamakerStudios
Any recommendations on the best way to JOIN these tables so I can do my search properly?
Posted: Wed Dec 06, 2006 9:21 am
by CoderGoblin
Without knowing your database structue it is difficult to assist.. Can you show the relevant tables/columns (you can strip out any unneeded columns. Also state the database you are using. I know postgres has geographic functions which can be of help, not sure about mysql.
Posted: Wed Dec 06, 2006 9:28 am
by WanamakerStudios
Let me know if this is ok ...
UsedCars Database -
ZipCode Table:
-zip_code
-latitude
-longitude
LotData Table:
-lotid
-name
-zipcode
Vehicles Table:
-lotid
-vin
-year
-make
-model
-color
-price
Posted: Wed Dec 06, 2006 9:49 am
by CoderGoblin
OK as stated I use postgres and have done something similar in the past (but not using US data).
My method of solving this problem was a script which took lat/long and calculated the distance x km,y km from a known point,
Great Circle and storing this as a 'point' type within the database table. This was only performed once.
I could then use the postgres radius and distance functions within postgres to get the radius/distance without having to calculate the great circle distance again. This sped the query up considerably. The actual joins between the tables as shown should be straight forward.
Remember distances on a map are not simply pythagarus (or however you spell it).
Don't know about mysql geographic functions...
Could you also provide an example of your current query...
Posted: Wed Dec 06, 2006 9:56 am
by WanamakerStudios
feyd | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
This is the old coding that I was using to figure out everything ...
Code: Select all
## COORDINATES OF REQUESTED ZIP
$coordinates_query = "SELECT DISTINCT latitude, longitude FROM zipcodes WHERE zip_code = '". $searchzip ."'";
$coordinates_query = mysql_query($coordinates_query);
$coordinates = mysql_fetch_array($coordinates_query);
$latitude = $coordinates['latitude'];
$longitude = $coordinates['longitude'];
## GET PREMIUM DEALERS
$pdealer_query = "SELECT DISTINCT LOTID FROM premiumdealers WHERE STATUS IN (2, 3)";
$pdealer_query = mysql_query($pdealer_query);
while($pdealer = mysql_fetch_array($pdealer_query)) {
if($i!=0) {$pdealers .= ", ";}
$pdealers .= "'". $pdealer['LOTID'] ."'";
$i++;
}
unset ($i);
## GET DEALER LOT CODES FOR GIVEN AREA
## $radius_query = "SELECT DISTINCT zip_code, city, round(3958*3.1415926*sqrt((latitude-". $latitude .")*(latitude-". $latitude .") + cos(latitude/57.29578)*cos(". $latitude ."/57.29578)*(longitude-". $longitude .")*(longitude-". $longitude ."))/180) AS distance FROM zipcodes HAVING distance <= (". $searchradius ." +1) ORDER BY distance ASC;";
$radius_query = "SELECT DISTINCT zip_code, city, round(69.1 * sqrt(((latitude-". $latitude .") * (latitude-". $latitude .")) + .6 * ((longitude-". $longitude .") * (longitude-". $longitude .")))) AS distance FROM zipcodes HAVING distance <= (". $searchradius ." +1) ORDER BY distance ASC;";
$radius_query = mysql_query($radius_query);
while($radius = mysql_fetch_array($radius_query)) {
$dealer_query = "SELECT DISTINCT CIDLOTD FROM `lotdata-temp` WHERE CZIP = '". $radius['zip_code'] ."'";
$dealer_query = mysql_query($dealer_query);
while($dealer = mysql_fetch_array($dealer_query)) {
array_push($dealer_code, $dealer['CIDLOTD']);
array_push($dealer_miles, $radius['distance']);
if($i!=0) {$dealers .= ", ";}
$dealers .= "'". $dealer['CIDLOTD'] ."'";
$i++;
}
}
global $radius;
unset ($i);
I can figure out the distance thing but never thought about putting it all into a DB and referencing it that way. I guess I am just trying to figure out how to make MySQL do all of the work so it saves some time in returning the results.
feyd | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Posted: Wed Dec 06, 2006 11:02 am
by feyd
If memory serves, Astions had a bunch of posts/threads on the great circle calculation from within MySQL.
Posted: Wed Dec 06, 2006 11:05 am
by WanamakerStudios
How could I run all of this as one giant MySQL statement?
Posted: Wed Dec 06, 2006 11:16 am
by feyd
As I said before, a somewhat complicated JOIN. I'd like to see a couple of tries so we have a baseline to nudge you from.
Posted: Wed Dec 06, 2006 12:04 pm
by WanamakerStudios
Let me finish up some of the coding I have and I'll paste it in later on ... so you can see more of a complete picture ...