Re: dealer locator - my mini tutorial

Ok... here's what I would do. Say you get a geographical list of zips. First make a dealer table. The table will have all of the information you want about the dealer. FOr this example I'll only use the column dealer_name. This example will use 2 columns. dealer id (DID) and dealer_name. DID | dealer_name DID will be auto_incrment primary key Use place all of the zips in another table. This table should have two columns. zipid | zipcode zipid will be auto_increment primary key zipcode will have each zipcode. the zipid will reference the zip. Now you need a table to reference the zips in the geographical area. you may have to do this manually, which will be time consuming if you're doing the entire nation. one of our gov't agencies may have been so nice to actually allocate some of our tax dollars for something useful and already have a database/table that is setup similarly. zone | refzipid will be the 2 columns. refzipid is the same as zipid. i usually make a unique name for the columns in dirrefent tables (even though the represent the same thing) to avoid having to do zipid.tablename and such. zone will be the general area or city, it will be identified by a number. there will be many zips that belong to each zone. ie- zone | refzipid 1 1 1 6 1 3 2 12 2 18 2 14 and so on.... now you need a lookup table. lookDID | lookZone now place the DID in lookDID column and lookZone would be the zone in which the dealer resides. now... you can find a dealer by the zip code or the all dealers in a limited zone/area. query: "SELECT dealer_name FROM dealers WHERE zipcode='$zip'" That would directly find all the dealers from a certain zip only. Obviously we would not need to go through all this trouble if we only wanted to search by zip. So... say that yeilds no results and they want a small regional listing. "SELECT zipid FROM zips WHERE zipcode='$zip'" That get us our id which will be the basis for the relational database technique. Lets say it gave us "1" as the result for the zip codes's id "SELECT dealer_name FROM dealers WHERE refzipid=1 and zone=lookZone and DID=lookDID" Thats going to say --> start where the refzipid = 1, find the value of zone that is exists in the same row (which is 1 in this case), then goto the lookup table and find where lookZone = 1, find the values of lookDID that exists on the same row and find out which DIDs it matches with in the dealers table. Now, i'm thinking this up on the fly, so there may be quite a bit of room for optimization of the database structure and how it flows. But this should give you an idea how to start thinking. Everything must be relational and be able to be linked from table to table, mainly through the use of IDs. Of course there's another way to g about this. Thats using Long/Lat coordinates to calculate everything on the fly based on radius and such. However, I have no experience doing that type of thing. I think this will be fine, it will just take some time to set it up, god knows how many zips you'll have to plug into this DB. Jason ----- Original Message ----- From: Jerry Lake <jerryl@europa.com> To: <php-general@lists.php.net> Sent: Friday, December 15, 2000 9:04 PM Subject: [PHP] dealer locator > I know this has been on the list before, > sorry for the redundancy. > but I am trying to make a dealer locator > and I'm getting a little confused about using > multiple tables i.e. the dealer table and the > zipcodes table, has anyone done any tutorials > on this, I have some resources, but apparently not > enough. if anyone has any reference material I > would really appreciate it. > > Jerry Lake > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net > For additional commands, e-mail: php-general-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net > >