Dave,
I did something similar and I came up with an interesting way of
approaching it:
Assign IDs to each car (obviously).
Assign an ID to each option.
Match up your car IDs and option IDs in a seperate table.
Here's some table defs:
create table cars (
carid int auto_increment,
name varchar(255)
);
create table options (
optid int auto_increment,
name varchar(255)
);
create table caroptions (
carid int,
optid int
);
(if my sql is off, don't flame me, you at least get the idea)
Here's the search:
$words should be a comma delimited list if the options the user chose.
select count(o.carid) as cnt, o.carid as id, c.name from caroptions as o,
cars as c where and o.optid in ({$words}) and o.carid = c.carid group by
o.carid, c.name order by cnt DESC
This would give you a list of cars ordered by the best match to worst match.
again - this is all off the top of my head, I'm sure it's not word for word
correct.
--
Rich Cavanaugh
-----Original Message-----
From: Dave Mariner [mailto:[EMAIL PROTECTED]]
Sent: Monday, June 25, 2001 3:54 PM
To: [EMAIL PROTECTED]
Subject: [PHP] [OT-ish] Optional Extras.
Please excuse me if you consider this to be off-topic, but this is the best
place I can think of to ask the (slightly long-winded) question.
Imagine you have a car database (MySQL driven). Different models have
different optional extras (air-con, central locking, immobiliser etc). I
need to store the optional extras in a searchable form - i.e. the customer
may have a wish-list of electric windows, aircon, and power steering.
However the optional extras list is not and will not be finalised when the
system goes live (probably will never be finalised!). Therefore I cannot do
the quick-and-dirty hack of putting all the options as binary fields in my
car database, so must come up with a more elegant solution. I've thought of
storing e.g. 10 tuples car.option1->aircon code, car.option2->powersteering
code. etc. and also going down the header-detail route.
My current quandry is to which is going to be better for the search
aspect, considering I'd also like to give them a "best fit" option. Would it
be to create a cursor on my fixed criterion (price, age etc) and then
iterate through each of those manually in my php script (see - it isn't
entirely off topic ;0) ) counting the matches for that record in the
optional-extra detail table? Or would it be to do a select where
(optionalextra1=mychoice1 or optionalextra2 = mychoice1 ......) and
(optionalextra2=mychoice2 or optionalextra2 = mychoice2...... ) and .... etc
etc (yeuch!).
I have a sneaking suspicion that there's a more elegant way than either
of these, but can't think of it at the moment.
If you come up with the solution there's a beer in it for you the next
time you're in Paphos, Cyprus!
Thanks in advance,
Dave.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
