query example question

[thejayjay]

Thanks a ton for your help guys, the solutions you spoke of achieved a large portion of my work! I was able to get rid of a lot of nasty code that was handling what is now handled with about half as many lines! =]

 

I have run into a snag though as I am now doing a really complicated query to do some serious \"trimming\" to find EXACTLY what i need from a database.

 

let me explain the situation

 

a customer selects a make and year from a drop down list such as this

 

make --> acura

year --> 1998

 

i then take a look in the db to see what \"models\" of acura existed in 1998.

 

the user then selects

model --> cl

 

upon submission of that page another query is run on the \"vehiclegroups\" table that pulls down the various vehicle settings like so, let me post a query w/ results as an example

 

mysql> select * from vehiclegroups where descriptor like \'%acura% %cl% %1998%\';

 

+------------------------------------+---------+--------+----+-----------+

| descriptor | bolt | offset | id | wheelsize |

+------------------------------------+---------+--------+----+-----------+

| Acura CL 1996 1997 1998 1999 2000 | 4x114.3 | 35-40 | 79 | 15x6.5 |

| Acura CL 1996 1997 1998 1999 2000 | 4x114.3 | 35-45 | 80 | 16x7 |

| Acura CL 1996 1997 1998 1999 2000 | 4x114.3 | 38-42 | 81 | 17x7 |

| Acura CL 1996 1997 1998 1999 2000 | 4x114.3 | 38-42 | 82 | 17x7.5 |

| Acura CL 1996 1997 1998 1999 2000 | 4x114.3 | 38-45 | 83 | 18x7 |

| Acura CL 1996 1997 1998 1999 2000 | 4x114.3 | 38-45 | 84 | 18x7.5 |

| Acura CL 1996 1997 1998 1999 2000 | 4x114.3 | 38-45 | 85 | 18x8 |

| Acura CL 1996 1997 1998 1999 2000 | 4x114.3 | 38-45 | 86 | 19x7 |

| Acura CL 1996 1997 1998 1999 2000 | 4x114.3 | 38-45 | 87 | 19x7.5 |

+------------------------------------+---------+--------+----+-----------+

9 rows in set (0.01 sec)

 

that gives me a \"start\" to determine the necessary information if a \"wheel\" that we carry will fit a vehicle that the customer is shopping for.

 

here is how the \"fitting\" works, the wheel\'s offset must be in the range of what the vehicle accpets and the wheelsize MUST MATCH exactly. so the offset is a range and the wheel must be directly equal.

a wheel offset is usually a number so i just make sure it is bigger than the smallest and bigger than the largest.

 

my problem is this.

 

i can\'t figure out a way to \"traverse\" the database with a proper search algorithmn without generating duplicate results.

 

if you guys need more code / query stuff posted let me know.

 

thanks

[pallevillesen]

I guess it would be nice to see a small part of the table you want to search for wheels (my guess is, that it\'s called wheels ?)

 

wheelsize is the width of the wheel and offset is on the diameter ?

 

My problem is that if I have an Acura CL 1997 then it looks like 9 different wheels will fit or what ?

 

I do NOT understand how the same car can be in 9 different vehicle groups ? Please clarify.

 

If I come with a car and a make you should have the exact requirements (diameter range and exact size) and then search the wheels table using this, right ?

 

P., denmark

[thejayjay]

I have solved a lot of problems since I initially posted, mostly because the customer wanted a different layout of the output. =]

Thank goodness it made the work easier.

 

To answer your question about the vast number of results for \"one\" caris that as the tire size increases the offset of the wheel also begins to differ. I have solved this problem with various solutions that required some weird nested if statements and otherwise. Unfortunately I discovered in the end that the offsets aren\'t really important because our client can figure out what offset to buy \"on the fly\" and he intended to do it all himself anyway.... grrr.

 

Oh well, at least I tried...

 

Thanks for all your help guys =]