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]
Ok. Here goes. This are all of the tables that I need to reference for my results and the structure that holds them.
[b]CarParts Table[/b]
[i]This holds the MAKE ID and its corresponding VALUE[/i]
[syntax="sql"]
CREATE TABLE `carparts` (
`CIDCARP` char(2) NOT NULL default '',
`CCATEGORY` char(12) NOT NULL default '',
`CDESCRIPTION` char(25) NOT NULL default '',
KEY `CIDCARP` (`CIDCARP`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
LotData Table
This holds all of the dealer information for all participating dealers across the country
Code: Select all
CREATE TABLE `lotdata` (
`CIDLOTD` varchar(5) NOT NULL default '',
`CIDLICE` varchar(5) NOT NULL default '',
`CLOTNUMBER` varchar(10) NOT NULL default '',
`CNAME` varchar(30) NOT NULL default '',
`CADDRESS1` varchar(30) NOT NULL default '',
`CADDRESS2` varchar(30) NOT NULL default '',
`CCITY` varchar(30) NOT NULL default '',
`CSTATE` char(2) NOT NULL default '',
`CZIP` varchar(5) NOT NULL default '',
`CFIPS` varchar(5) NOT NULL default '',
`CPHONE` varchar(35) NOT NULL default '',
`CMAINTAINI` char(1) NOT NULL default '',
`CWARRANTYT` char(1) NOT NULL default '',
`CPC_LABOR` char(3) NOT NULL default '',
`CPC_PARTS` char(3) NOT NULL default '',
`CWARRMONTH` char(2) NOT NULL default '',
`IWARRMILES` varchar(5) NOT NULL default '',
`CSERVICEAG` char(1) NOT NULL default '',
`CPRINTCONT` varchar(30) NOT NULL default '',
`CPRICEPERC` varchar(5) NOT NULL default '',
`WEBPAGE1` varchar(60) NOT NULL default '',
`WEBPAGE2` varchar(60) NOT NULL default '',
`WEBPAGE3` varchar(60) NOT NULL default '',
`WEBPAGE4` varchar(60) NOT NULL default '',
`WEBPAGE5` varchar(60) NOT NULL default '',
`WEBPAGE6` varchar(60) NOT NULL default '',
`WEBPAGE7` varchar(60) NOT NULL default '',
`WEBPAGE8` varchar(60) NOT NULL default '',
`LOTSTATUS` varchar(10) NOT NULL default '',
`LOTURL` varchar(30) NOT NULL default '',
`LASTUPDATE` varchar(8) NOT NULL default '',
`CONTACT` varchar(30) NOT NULL default '',
`FAXNUM` varchar(12) NOT NULL default '',
`LOTSTATUS2` varchar(12) NOT NULL default '',
`MAXCARSININVENTORY` smallint(2) NOT NULL default '0',
`CARFAXID` varchar(10) NOT NULL default '',
`MILEAGECHECK` varchar(8) NOT NULL default '',
`ISAUTONA` char(1) NOT NULL default '',
`USEDCARMGRNAME` varchar(30) NOT NULL default '',
`LOTGROUPID` varchar(10) NOT NULL default '',
`LASTUPDATEDATE` datetime NOT NULL default '0000-00-00 00:00:00',
`LASTTRANSMISSIONDATE` datetime NOT NULL default '0000-00-00 00:00:00',
`NADAREGION` datetime NOT NULL default '0000-00-00 00:00:00',
`FRANCHISESREPRESENTED` varchar(30) NOT NULL default '',
KEY `CIDLOTD` (`CIDLOTD`),
KEY `CZIP` (`CZIP`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
PremiumDealers Table
This table holds a short list of DEALER IDs that we've set as premium (STATUS = 2), featured (STATUS =1) or basic/non-listed (STATUS = 0/NULL)
Code: Select all
CREATE TABLE `premiumdealers` (
`LOTID` varchar(5) NOT NULL default '',
`STATUS` char(1) NOT NULL default '',
KEY `LOTID` (`LOTID`),
KEY `STATUS` (`STATUS`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
VehicleData Table
This table holds every bit of information about each one of the vehicles at dealers lots across the country
Code: Select all
CREATE TABLE `vehicles-temp` (
`CIDLOTD` varchar(5) default NULL,
`CVIN` varchar(17) NOT NULL default '',
`CSTOCKNUM` varchar(15) default NULL,
`CDISPOSITI` char(1) default NULL,
`CMAKE` char(2) default NULL,
`CMODEL` varchar(25) default NULL,
`CYEAR` int(4) default NULL,
`CMILEAGE` int(10) default NULL,
`CBODYTYPE` char(2) default NULL,
`CENGINE` char(2) default NULL,
`CENGINESIZ` varchar(5) default NULL,
`CINDUCTION` char(2) default NULL,
`CTRANSMISS` char(2) default NULL,
`CCOLOR` varchar(19) default NULL,
`CPRICE` int(25) default NULL,
`CCOST` varchar(10) default NULL,
`CWARRANTY` char(1) default NULL,
`CWARRANTYT` char(1) default NULL,
`FWARRANTY` char(2) default NULL,
`FWARRANTYT` char(1) default NULL,
`FWARRANTYC` varchar(4) default NULL,
`CPC_LABOR` char(3) default NULL,
`CPC_PARTS` char(3) default NULL,
`CWARRMONTH` char(2) default NULL,
`CWARRMILES` varchar(5) default NULL,
`CMANUF_MON` char(2) default NULL,
`CMANUF_YEA` varchar(4) default NULL,
`CSERVICEAG` char(1) default NULL,
`DDATE_IN` date default '0000-00-00',
`DDATE_REMO` date default '0000-00-00',
`O1` char(2) default NULL,
`O2` char(2) default NULL,
`O3` char(2) default NULL,
`O4` char(2) default NULL,
`O5` char(2) default NULL,
`O6` char(2) default NULL,
`O7` char(2) default NULL,
`O8` char(2) default NULL,
`O9` char(2) default NULL,
`O10` char(2) default NULL,
`O11` char(2) default NULL,
`O12` char(2) default NULL,
`O13` char(2) default NULL,
`O14` char(2) default NULL,
`O15` char(2) default NULL,
`O16` char(2) default NULL,
`O17` char(2) default NULL,
`O18` char(2) default NULL,
`O19` char(2) default NULL,
`O20` char(2) default NULL,
`O21` char(2) default NULL,
`O22` char(2) default NULL,
`O23` char(2) default NULL,
`O24` char(2) default NULL,
`O25` char(2) default NULL,
`O26` char(2) default NULL,
`O27` char(2) default NULL,
`O28` char(2) default NULL,
`O29` char(2) default NULL,
`O30` char(2) default NULL,
`O31` char(2) default NULL,
`O32` char(2) default NULL,
`O33` char(2) default NULL,
`O34` char(2) default NULL,
`O35` char(2) default NULL,
`DUPDATED` datetime default '0000-00-00 00:00:00',
`DCREATED` datetime default '0000-00-00 00:00:00',
`CPRICING` varchar(8) default NULL,
`DATEONLOT` datetime default '0000-00-00 00:00:00',
`FREEHANDTEXT` longtext,
`RECORDFLAGS` varchar(80) default NULL,
`MAKETXT` varchar(25) default NULL,
`MODELTXT` varchar(25) default NULL,
`BODYTXT` varchar(25) default NULL,
`ENGINETXT` varchar(25) default NULL,
`INDUCTIONTXT` varchar(25) default NULL,
`TRANSTXT` varchar(25) default NULL,
`EXTERNALCOLOR` varchar(50) default NULL,
`INTERNALCOLOR` varchar(50) default NULL,
`LABORCOST` varchar(8) default NULL,
`LABORPRICE` varchar(8) default NULL,
`INTERNETCOST` varchar(8) default NULL,
`INTERNETPRICE` varchar(8) default NULL,
`MANFACTUREDATE` datetime default '0000-00-00 00:00:00',
`DATEFIRSTSEEN` datetime default '0000-00-00 00:00:00',
`NADAID` varchar(8) default NULL,
`CERTIFICATIONNUM` varchar(20) default NULL,
`CERTIFICATIONDATE` datetime default '0000-00-00 00:00:00',
`INSERVICEDATE` datetime default '0000-00-00 00:00:00',
`TRIMLEVEL` varchar(15) default NULL,
`SYSTEMWARRANTYNUM` smallint(2) default '0',
`LASTINVENTORYDATE` datetime default '0000-00-00 00:00:00',
`OPTIONS` longtext,
`ADDENDUM` longtext,
`CUSTOPT` longtext,
`KBBDATA` varchar(60) default NULL,
`CARMEMO` varchar(255) default NULL,
`CARNUMBER` varchar(255) default NULL,
`STYLEID` int(4) default '0',
`F100` varchar(255) NOT NULL default '',
`F101` varchar(255) NOT NULL default '',
PRIMARY KEY (`CVIN`),
KEY `CIDLOTD` (`CIDLOTD`),
KEY `CYEAR` (`CYEAR`),
KEY `CCOLOR` (`CCOLOR`),
KEY `CCOST` (`CCOST`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
ZipCode Table
This holds all of the zipcodes and their corresponding latitude/longitude coordinates
Code: Select all
CREATE TABLE `zipcodes` (
`detail_code` char(1) NOT NULL default '',
`zip_code` varchar(5) NOT NULL default '',
`city_state_key` varchar(6) NOT NULL default '',
`zip_code_class` char(1) NOT NULL default '',
`city_state_name` varchar(28) NOT NULL default '',
`city_abbr` varchar(13) NOT NULL default '',
`facility_code` char(1) NOT NULL default '',
`mail_name` char(1) NOT NULL default '',
`city_key` varchar(6) NOT NULL default '',
`city` varchar(28) NOT NULL default '',
`delivery` char(1) NOT NULL default '',
`cart_rate` char(1) NOT NULL default '',
`unique_zip` char(1) NOT NULL default '',
`finance_number` varchar(6) NOT NULL default '',
`state` char(2) NOT NULL default '',
`fips_code` char(3) NOT NULL default '',
`county` varchar(25) NOT NULL default '',
`latitude` varchar(7) NOT NULL default '',
`longitude` varchar(8) NOT NULL default '',
`area_code` varchar(15) NOT NULL default '',
`time_zone` char(2) NOT NULL default '',
`elevation` varchar(5) NOT NULL default '',
`persons_per_household` varchar(4) NOT NULL default '',
`population` varchar(8) NOT NULL default '',
`square_miles` varchar(6) NOT NULL default '',
`households_per_zipcode` varchar(8) NOT NULL default '',
`population_white` varchar(8) NOT NULL default '',
`population_black` varchar(8) NOT NULL default '',
`population_hispanic` varchar(8) NOT NULL default '',
`income_per_household` varchar(8) NOT NULL default '',
`avg_house_value` varchar(8) NOT NULL default '',
KEY `zip_code` (`zip_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Record Count
CarParts Table: 400
LotData Table: 8,000
PremiumDealers Table: 10
VehicleData Table: 600,000
ZipCode Table: 80,000
Final Results
The final results need to display: Year, Make, Model, Color, Cost, Odometer, Distance From Given Zip Code
Parameters
The user is providing a year range, make, model, zipcode and radius around zipcode. I need to be able to find all of the dealers within the radius of the given zipcode and display all of the vehicles on each of their lots. I also need the user to be able to sort the results by any one of the displayed fields. ie: they can sort price highest to lowest or closest to farthest distance from them, ect.
If you need anymore info, please let me know! I greatly appreciate the help!
feyd | Please use[/syntax]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]