Forum Moderators: coopster
I'm hoping that someone here may be able to point me in the right direction with a query I'm having trouble with. To start off with, I am using 4.0.25-standard and cannot upgrade (my webhost will not upgrade my account because they would have to buy a new version of CPanel).
In short, I am trying to get a vehicle database up and running on my automotive forum for timeslips, etc. I have the database schema working fine and can pull make/model information but I can't build the right year ranges. I want to prevent people from having pulldowns so they can add a timeslipe for their 1945 Chevrolet Camaro, etc.
I've accomplished this by passing variables to the URL and using a variable to pull the make/model down.
Here's my query. I suppose it would be easier in 4.1+ because of nested queries but I need to do this somehow using a left join or something.
Here is my db schema:
table_vehicles
- vehicle_id
- vehicle_make
- vehicles_model
table_vehicles_description
- vehicle_id
- vehicles_startyear
- vehicles_endyear
table_vehicle_years (is only a storage of vehicle years from 1950-2007)
- vehicle_year_id
- vehicle_year
The vehicle_id fields are relational. I intend to do an excel-type import later on down the road of a whole bunch of vehicles and use vehicle_id to keep the fields organized.
What I am trying to do is build a pulldown with the range of vehicle years for a given model. i.e. If you choose a Chevrolet / Camaro / 1985 you will get a page of results of Camaros from 1982-1992 (that's the range I define). Thus, if you added your 1993 Camaro to the list and searched for a 1993 Camaro the page would then give results of Camaros from 1993-2002 (those year ranges having been defined in the vehicles table).
Here's an example entry:
table_vehicles
- vehicle_id=1
- vehicle_make=Chevrolet
- vehicle_model=Camaro
table_vehicles_description
- vehicle_id=1
- vehicle_startyear=1982
- vehicle_endyear=1992
The query I am using is:
Quote:
$vehicle_query = tep_db_query(" select * from vehicle_years as y, vehicles as v, vehicles_description as vd where y.vehicle_year between vd.vehicle_startyear and vd.vehicle_endyear and v.vehicle_model='" . $HTTP_GET_VARS['model'] . "'");
Rather than get the year range in question it's returning multiples of 'all' years in the vehicle_years table. It should be making a pulldown with the years 1967-2002. ANY help at all would be much appreciated.
Could I also use a set of years for a vehicle? Like this example
vehicle_id=1
vehicle_make=Chevrolet
vehicle_model=Camaro
vehicle_years=1982,1983,1984,1985,1986,1987,1988,1989,1990,1991,1992
The thing is, there would be 4 rows for the Camaro as there were 4 generations. Does that make sense? The 4 row years would have to be collated, I suppose.
Take care,
Colin
table_makes
- id(auto-increment, primary)
- make_name
table_models
- id(auto-increment, primary)
- model_name(varchar)
- make_id(index)
table_generations
- id(auto-increment, primary)
- start_year
- end_year
- model_id(index)
Then, you can:
$vehicle_query = tep_db_query("select g.start_year, g.end_year from table_generations as g, table_models as m where g.model_id = m.id and m.model_name = '" . $HTTP_GET_VARS["model"] . "'");
By the way, welcome to Webmaster World!
