Re: [PHP] [OT-ish] Optional Extras.
I don't see how this is weighted. The count(car_id) and group_by should give a score for each car_id. I neglected to use count(car_id) as score and to add order by score desc though. -- 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]
Re: [PHP] [OT-ish] Optional Extras.
I don't see how this is weighted.
I would suggest doing it the easy way and treating the options as
keywords and just putting them all in a text field with a fulltext
index. this will give you the weighting you want and it will be a lot
easier to deal with, but you will have to watch out for things like
'power locks' partially matching 'power doors' (just take out the
spaces I guess)
On Wed, 27 Jun 2001 00:41:37 -0500, Richard Lynch ([EMAIL PROTECTED])
wrote:
How about solving both problems at once? :-)
Yes, go with the N:N (the technical term for that car_option table)
relation, *AND* give yourself a weighted search engine to boot!
create table car (car_id auto_increment...);
create table option (option_id auto_increment...);
create table car_option(car_id int4, option_id int4,
key(option_id));
insert into car(name) values('Chevy Nova');
insert into car(name) values('Lexus');
insert into option(name) values('a/c');
insert into option(name) values('power window');
insert into option(name) values('power locks');
insert into car_option(car_id, option_id) values(1, 1);
insert into car_option(car_id, option_id) values(2, 1);
insert into car_option(car_id, option_id) values(2, 2);
insert into car_option(car_id, option_id) values(2, 3);
NOTE: The Lexus has all those options (at that price it had better!
) and
the Nova, well... It has a/c! :-)
Now, assume the user wants everything, so you have:
$options[1]
$options[2]
$options[3]
all set to some value by your checkbox.
#Untested code.
$query = select count(car_id), car.name from car, car_option ;
$query .= where car.car_id = car_option.car_id
$query .=and (1 = 0;
# 1 = 0 is a starter yeast for the following:
while (list($option_id) = each($options)){
$query .= or option_id = $option_id ;
}
# finish off our options OR list...
$query .= );
$query .= group by car_id ;
--
WARNING [EMAIL PROTECTED] address is an endangered species -- Use
[EMAIL PROTECTED]
Wanna help me out? Like Music? Buy a CD: http://l-i-
e.com/artists.htm
Volunteer a little time: http://chatmusic.com/volunteer.htm
--
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: php-list-
[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]
Re: [PHP] [OT-ish] Optional Extras.
How about solving both problems at once? :-)
Yes, go with the N:N (the technical term for that car_option table)
relation, *AND* give yourself a weighted search engine to boot!
create table car (car_id auto_increment...);
create table option (option_id auto_increment...);
create table car_option(car_id int4, option_id int4, key(option_id));
insert into car(name) values('Chevy Nova');
insert into car(name) values('Lexus');
insert into option(name) values('a/c');
insert into option(name) values('power window');
insert into option(name) values('power locks');
insert into car_option(car_id, option_id) values(1, 1);
insert into car_option(car_id, option_id) values(2, 1);
insert into car_option(car_id, option_id) values(2, 2);
insert into car_option(car_id, option_id) values(2, 3);
NOTE: The Lexus has all those options (at that price it had better!) and
the Nova, well... It has a/c! :-)
Now, assume the user wants everything, so you have:
$options[1]
$options[2]
$options[3]
all set to some value by your checkbox.
#Untested code.
$query = select count(car_id), car.name from car, car_option ;
$query .= where car.car_id = car_option.car_id
$query .=and (1 = 0;
# 1 = 0 is a starter yeast for the following:
while (list($option_id) = each($options)){
$query .= or option_id = $option_id ;
}
# finish off our options OR list...
$query .= );
$query .= group by car_id ;
--
WARNING [EMAIL PROTECTED] address is an endangered species -- Use
[EMAIL PROTECTED]
Wanna help me out? Like Music? Buy a CD: http://l-i-e.com/artists.htm
Volunteer a little time: http://chatmusic.com/volunteer.htm
--
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]
RE: [PHP] [OT-ish] Optional Extras.
off the top of my head, you could try using a lookup table to define the extra options... ford_spec table: id desc --- --- 1 air conditioning 2 whatever ford_cars table: car extra1 extra2 -- --- - 'contour' 1 0 'probe' 0 1 contour would have aircond but no whatever probe would have no aircond but would have whatever so to add more options specific to a manufacturer, you could make an entry into ford_spec and then create an extra3 field in ford_cars... this system, at least, would enable you to have as many extras as you wanted for each manufacturer, and have different options available for different manufacturers. i dont know how efficient this would be, and there are probably a bunch of better ways to do it, but i've seen this method used before by someone who developed a batteries catalog and needed to store different manufacturers with different options for their batteries. -Original Message- From: Dave Mariner [mailto:[EMAIL PROTECTED]] Sent: Monday, June 25, 2001 12:54 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]
Re: [PHP] [OT-ish] Optional Extras.
The problem with doing it this way is that it is not extensible. A manufacturer cannot add, say, MP3 players to the list at a later date without breaking the car table. Also, in time, there could be quite an extensive list of features - which would lead to an inefficient storage system. Dave - Original Message - From: ..s.c.o.t.t.. [EMAIL PROTECTED] To: Php-General [EMAIL PROTECTED] Sent: Tuesday, June 26, 2001 7:35 AM Subject: RE: [PHP] [OT-ish] Optional Extras. off the top of my head, you could try using a lookup table to define the extra options... ford_spec table: id desc --- --- 1 air conditioning 2 whatever ford_cars table: car extra1 extra2 -- --- - 'contour' 1 0 'probe' 0 1 contour would have aircond but no whatever probe would have no aircond but would have whatever so to add more options specific to a manufacturer, you could make an entry into ford_spec and then create an extra3 field in ford_cars... this system, at least, would enable you to have as many extras as you wanted for each manufacturer, and have different options available for different manufacturers. i dont know how efficient this would be, and there are probably a bunch of better ways to do it, but i've seen this method used before by someone who developed a batteries catalog and needed to store different manufacturers with different options for their batteries. -Original Message- From: Dave Mariner [mailto:[EMAIL PROTECTED]] Sent: Monday, June 25, 2001 12:54 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] -- 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]
Re: [PHP] [OT-ish] Optional Extras.
- Original Message - From: David Robley [EMAIL PROTECTED] To: Dave Mariner [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, June 26, 2001 4:32 AM Subject: Re: [PHP] [OT-ish] Optional Extras. SNIP Why not have a table that contains carid and optionlink, where carid is the identifier for a unique entry of car model and optionlink is ditto for a particular option. So you have something like Table car carid (autoincrement unique key) carname .. Table options optid (autoincrement unique key) optdescription Table optlink carid optid Then you can do a select like select car.carid, options.description from car, options, optlink where car.carid = $whatever and car.carid = optlink.carid and (optlink.optid = x or optlink.optid = y ...) where you build the latter part from the list of options selected - maybe from a dropdown list that you can dynamically build from table options. Yeah, this was the header-detail route that I was considering - the problem is that the select above will come back with any car that has any of the options specified. So if, for example, the options are Manual,Electric Windows, and for the sake of argument all automatic cars have e/w, the query will return all cars. I know that's kind of artificial, but you get what I'm driving at (if you'll excuse the pun). = [EMAIL PROTECTED] www.medlab-group.com you didn't see what it was, you saw what you wanted to see. -- David Robley Techno-JoaT, Web Maintainer, Mail List Admin, etc CENTRE FOR INJURY STUDIES Flinders University, SOUTH AUSTRALIA This is a sick bird, said Tom illegally. -- 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]
RE: [PHP] [OT-ish] Optional Extras.
i disagree with the first point. adding extra options (like an MP3 player) will not break the table if you code the system with knowledge of how the underlying database is set-up. a friend of mine setup a catalog system this way. the catalogs would usually have ~5-10 custom options, so his solution was fast and simple and efficient. i agree with the second point, that having hundreds or thousands of options would definately degrade performance. -Original Message- From: Dave Mariner [mailto:[EMAIL PROTECTED]] Subject: Re: [PHP] [OT-ish] Optional Extras. The problem with doing it this way is that it is not extensible. A manufacturer cannot add, say, MP3 players to the list at a later date without breaking the car table. Also, in time, there could be quite an extensive list of features - which would lead to an inefficient storage system. Dave - Original Message - From: ..s.c.o.t.t.. [EMAIL PROTECTED] Subject: RE: [PHP] [OT-ish] Optional Extras. off the top of my head, you could try using a lookup table to define the extra options... ford_spec table: id desc --- --- 1 air conditioning 2 whatever ford_cars table: car extra1 extra2 -- --- - 'contour' 1 0 'probe' 0 1 contour would have aircond but no whatever probe would have no aircond but would have whatever so to add more options specific to a manufacturer, you could make an entry into ford_spec and then create an extra3 field in ford_cars... this system, at least, would enable you to have as many extras as you wanted for each manufacturer, and have different options available for different manufacturers. i dont know how efficient this would be, and there are probably a bunch of better ways to do it, but i've seen this method used before by someone who developed a batteries catalog and needed to store different manufacturers with different options for their batteries. -- 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]
Re: [PHP] [OT-ish] Optional Extras.
- Original Message -
From: Rich Cavanaugh [EMAIL PROTECTED]
To: Dave Mariner [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, June 26, 2001 1:53 AM
Subject: RE: [PHP] [OT-ish] Optional Extras.
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.
Bingo!! Looks about right. What I was missing was the IN and a cdl of the
options. If it works (as it looks like it should), pop into Paphos I'll
buy you that pint!!
Cheers,
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]
RE: [PHP] [OT-ish] Optional Extras.
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,
namevarchar(255)
);
create table options (
optid int auto_increment,
namevarchar(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]
