Stumped with how to pull multiple variables..

[iBuddy]

Alright I have a database set up for a website for car accessories. Basically all the items are pulled thru a database...the column names are:

• item
  make
  model
  year
  price
  description

Okay everything works great...however my client wants a way for a product to be spread over a few years. For example say they got a product that fits a acura integra for 1990-1994....I have the search pull up so that it searches for the variable column name "year" but I cant have it search a string of years....this is kinda hard to explain..anyone get what im saying?

[tsinka]

Hi,

to be honest I didn't get exactly what you want to do but if you want to have a range of years you might want to add a second year column. Then use one column for the beginning of the date range and one for the end of the daterange. You can then search with a query like e.g.

SELECT * FROM thetable WHERE item='someitem' AND year>='datetocheck' AND year2<='datetocheck'

item
make
model
year
price
description

Hope this goes into the right direction ...

Thomas

[NeoGeo]

You could also take the year column out of that table and create a new table that has two columns

ItemID, Year

Then you could use a join query on the ItemID with the ID column of your Item table (assuming that's what its called) and select between date1 and date2.

I'm sure the join query isn't as fast as the plain select query - but are there any cases where the part item might be (for example) from 90-92 AND 94-95? In that case you would NEED to do it the way I just listed.

[Roger_Ramjet]

You can have a query either search for multiple years using OR , or search for a year in a string of years by using LIKE.

However, the best solution is, as NeoGeo says, to put your year column into a seperate table. I would go further and say, Normalise your data properly. I would guess that it is possible for an item to fit more than 1 make, or more than 1 model, as well as more than 1 year. You should generalise your table designs to allow for this. It will impress the client when you point out that you have done so.