osCommerce Community Support Forums > Automotive filter function

d.aspo

Feb 26 2007, 07:54 AM

I'm making this thread because this one was locked:
http://forums.oscommerce.com/index.php?sho...p;hl=automotive

Now to begin with, I'm a complete newbie when it comes to JS, PHP and MySQL. I program somewhat ok in old regular ASP though. This is how I've planned to make the general modification of OSCommerce. I looked at using the product attributes but no thats clearly not going to work like I want it. So making new fields for my own purposes seems to be the only solution. First I decided to make new tables, each for each attribute I want to filter by:

QUOTE

table_vehicle_make
id
make

table_vehicle_year
id
model

table_vehicle_year
id
year

Then in the product table you'd have these extra fields:

QUOTE

vehicle_make
vehicle_model
vehicle_year

Now as I imagine this, you would then link these tables and use the values in extra fields to filter the results, which I so far assume is done via an SQL string(i'm really, really new to OSC). This way you can combine make/model/year in any way you like and I think would allow you to make it "smart" as well.

I haven't done anything yet, I'm just posting my thoughts first since I'm such a beginner. Any thoughts on the issue?

d.aspo

Feb 26 2007, 08:43 AM

An addendum. There will also be boats and such as well, so I'm thinking maybe there should be another table as well, so you can have cars/boats/whatever. vehicle_type perhaps. Now i realized we also need another field in the tables "further down" in the hierarchy so they know what their parents are.

QUOTE

table_vehicle_type
id
type_name

table_vehicle_make
id
parent
make

table_vehicle_year
id
parent
model

table_vehicle_year
id
parent
year

So it should look something like this:

QUOTE

table_vehicle_type
id: type1
typename: cars

table_vehicle_make
id: make1
parent: type1
make: Pontiac

table_vehicle_model
id: model1
parent: make1
make: Firebird

table_vehicle_year
id: year1
parent: model1
make: 1991

And in the product table the product that fits with this would have these fields filled in like this:

QUOTE

vehicle_type: type1
vehicle_make: make1
vehicle_model: model1
vehicle_year: year1

This sounds me like a workable solution. I am not sure about how elegant it is. Another issue that crops up is if you can have multiple values (one part for a car fan fit several different model years) for one product in a single field... Or would it require duplicate products? Thats not really desireable to me. I would also like to try keep products with these values as null as "universal" and they should always be seen irregardless of filtering.

Am I making any sense? I only slept three hours last night.

d.aspo

Feb 26 2007, 12:27 PM

Well I've created the tables so far, here's the code I used:

QUOTE

CREATE TABLE vehicle_type (
vehicle_type_id int NOT NULL auto_increment,
vehicle_type_name int(4) NOT NULL,
PRIMARY KEY (vehicle_type_id),
KEY vehicle_type_id (vehicle_type_id)
);

CREATE TABLE vehicle_make (
vehicle_make_id int NOT NULL auto_increment,
vehicle_make_name int(4) NOT NULL,
vehicle_make_parent int(4) NOT NULL,
PRIMARY KEY (vehicle_make_id),
KEY vehicle_make_id (vehicle_make_id)
);

CREATE TABLE vehicle_model (
vehicle_model_id int NOT NULL auto_increment,
vehicle_model_name int(4) NOT NULL,
vehicle_model_parent int(4) NOT NULL,
PRIMARY KEY (vehicle_model_id),
KEY vehicle_model_id (vehicle_model_id)
);

CREATE TABLE vehicle_year (
vehicle_year_id int NOT NULL auto_increment,
vehicle_year_name int(4) NOT NULL,
vehicle_year_parent int(4) NOT NULL,
PRIMARY KEY (vehicle_year_id),
KEY vehicle_year_id (vehicle_year_id)
);

And the code for the fields in products

QUOTE

ALTER TABLE products ADD vehicle_year int(7) AFTER products_ordered;
ALTER TABLE products ADD vehicle_model int(7) AFTER products_ordered;
ALTER TABLE products ADD vehicle_make int(7) AFTER products_ordered;
ALTER TABLE products ADD vehicle_type int(7) AFTER products_ordered;

So, now I got the easy part done. Bloody hell php code is mind boggling compared to the old asp code I am used to.

d.aspo

Feb 26 2007, 02:02 PM

Redid the tables after I noticed I did alot of things wrong, like the field types for names, but as I said I'm new to this:

QUOTE

CREATE TABLE vehicle_type (
vehicle_type_id int NOT NULL auto_increment,
vehicle_type_name varchar(100) NOT NULL,
PRIMARY KEY (vehicle_type_id),
KEY vehicle_type_id (vehicle_type_id)
);

CREATE TABLE vehicle_make (
vehicle_make_id int NOT NULL auto_increment,
vehicle_make_name varchar(100) NOT NULL,
vehicle_make_parent int(7) NOT NULL,
PRIMARY KEY (vehicle_make_id),
KEY vehicle_make_id (vehicle_make_id)
);

CREATE TABLE vehicle_model (
vehicle_model_id int NOT NULL auto_increment,
vehicle_model_name varchar(100) NOT NULL,
vehicle_model_parent int(7) NOT NULL,
PRIMARY KEY (vehicle_model_id),
KEY vehicle_model_id (vehicle_model_id)
);

CREATE TABLE vehicle_year (
vehicle_year_id int NOT NULL auto_increment,
vehicle_year_name varchar(100) NOT NULL,
vehicle_year_parent int(7) NOT NULL,
PRIMARY KEY (vehicle_year_id),
KEY vehicle_year_id (vehicle_year_id)
);

ALTER TABLE products ADD vehicle_year int(7) AFTER products_ordered;
ALTER TABLE products ADD vehicle_model int(7) AFTER products_ordered;
ALTER TABLE products ADD vehicle_make int(7) AFTER products_ordered;
ALTER TABLE products ADD vehicle_type int(7) AFTER products_ordered;

I've fed the type, make and model tables with vehicle models now. I need to create some kind of form then to configure products and to create yearmodels. But I'm still not sure how to solve the problem of multiple values for one product, comma separated values in a field?

d.aspo

Mar 1 2007, 09:16 PM

Progress report on this. So far I've been able to create the code itself that lets you choose vehicles, store them(using cookies, tried sessions but it didn't work) and then allow you to remove it as well. What is left is to code the admin interface so people can add makes and models and also modify products. Also what needs to be done is the actual filtering of products.

I would love to know all the places I should start altering the SQL commands, and does anyone know a good way to allow for multiple years? I figured you could add multiple years by going 1992,1993,1994,1995 in the relevant productfield and then use wildcards in the SQL string.

Also that whole making it smart thing, I gave up on that, too hard for a newbie to begin with. But maybe once I get this working I can start on adding smart-capability to it. So far I've only really edited header.php and made two new files. I also need to add multi-language support.

lloydfz

Aug 10 2007, 12:50 AM

Hey man.. when you will finish this contribution? I really want to have this contribution, and test it out. Please...

gravanatuning

Dec 13 2007, 11:46 PM

QUOTE (d.aspo @ Mar 1 2007, 01:16 PM)

Progress report on this. So far I've been able to create the code itself that lets you choose vehicles, store them(using cookies, tried sessions but it didn't work) and then allow you to remove it as well. What is left is to code the admin interface so people can add makes and models and also modify products. Also what needs to be done is the actual filtering of products.

I would love to know all the places I should start altering the SQL commands, and does anyone know a good way to allow for multiple years? I figured you could add multiple years by going 1992,1993,1994,1995 in the relevant productfield and then use wildcards in the SQL string.

Also that whole making it smart thing, I gave up on that, too hard for a newbie to begin with. But maybe once I get this working I can start on adding smart-capability to it. So far I've only really edited header.php and made two new files. I also need to add multi-language support.

Great work. Do you have it operating live yet? If so, what is the URL?

Jim

vividracing

Dec 27 2007, 10:40 PM

already got it

email me marketing@vividracing.com

www.vividracing.com

gravanatuning

Dec 28 2007, 01:28 AM

QUOTE (vividracing @ Dec 27 2007, 02:40 PM)

already got it

email me marketing@vividracing.com

www.vividracing.com

Thank you very much for the link. That is excellent!!! I have been trying to find something dynamic enough to handle a variety of vehicle attributes. For example:

Year
Make
Model
Engine
Submodel 1
Submodel 2
Etc.

A great example of my problem that you can relate to would be how to map products to this new truck:

Year: 2007
Make: Chevrolet
Model: Silverado 2500
Engine: 6.0
Submodel 1: 2WD
Submodel 2: Crew Cab
Submodel 2: Long Bed

I have yet to find anyone doing that in osC. In fact, most sites get you into products with Year, Make & Model and then you have to scroll through a bunch of products that may not fit your specific vehicle. One site (truckperformance.com) does a great job with their YMM selection. They get your make, then year, then model, then, after only 3 clicks, you see products. When you click on the products, it then asks you to supply additional attribute info such as engine, 2WD or 4WD, bed, cab, etc. That way, the customer isn't frustrated with drilling down 6-8 clicks at the beginning. I like it!

The other half of my problem is that I want to enter more vehicle info in the back-end so I can better manage and group things. For example, the vehicle category info would look more like this in the back end:

Year:
Make:
Model:
Engine:
Submodel 1:
Submodel 2:
Submodel 2:
Fuel Type: (Gas, Diesel)
Transmission: (Auto, Manual)
Vehicle Type 1: (Truck, Car, SUV, RV, Van, Etc.)
Vehicle Type 2: (Coupe, Sedan, Convertible, Etc.)
Vehicle Type 3:
Vehicle Type 4:
Vehicle Type 5:

This way, I can map to an individual vehicle, groups of vehicles, all vehicles, etc. I also need a Universal option for things that are truly for all vehicles. (air fresheners, brake fluid, detailing products, etc. I doubt that a person that owns a WRX would want to see a mud flap set with a "Pro Rodeo" logo on it. Something like that could be mapped to "All Truck", "All SUV", "All RV" but not to any cars.

Conversely, not many truck owners would be buying Sparco race pedals, so I would only map those to cars.

Does what you have allow for something this comprehensive? If not, have you seen anything close to what I need?

Thanks!

Jim

performancedeals

Feb 8 2008, 04:00 AM

QUOTE (gravanatuning @ Dec 27 2007, 07:28 PM)

Thank you very much for the link. That is excellent!!! I have been trying to find something dynamic enough to handle a variety of vehicle attributes. For example:

Year
Make
Model
Engine
Submodel 1
Submodel 2
Etc.

A great example of my problem that you can relate to would be how to map products to this new truck:

Year: 2007
Make: Chevrolet
Model: Silverado 2500
Engine: 6.0
Submodel 1: 2WD
Submodel 2: Crew Cab
Submodel 2: Long Bed

I have yet to find anyone doing that in osC. In fact, most sites get you into products with Year, Make & Model and then you have to scroll through a bunch of products that may not fit your specific vehicle. One site (truckperformance.com) does a great job with their YMM selection. They get your make, then year, then model, then, after only 3 clicks, you see products. When you click on the products, it then asks you to supply additional attribute info such as engine, 2WD or 4WD, bed, cab, etc. That way, the customer isn't frustrated with drilling down 6-8 clicks at the beginning. I like it!

The other half of my problem is that I want to enter more vehicle info in the back-end so I can better manage and group things. For example, the vehicle category info would look more like this in the back end:

Year:
Make:
Model:
Engine:
Submodel 1:
Submodel 2:
Submodel 2:
Fuel Type: (Gas, Diesel)
Transmission: (Auto, Manual)
Vehicle Type 1: (Truck, Car, SUV, RV, Van, Etc.)
Vehicle Type 2: (Coupe, Sedan, Convertible, Etc.)
Vehicle Type 3:
Vehicle Type 4:
Vehicle Type 5:

This way, I can map to an individual vehicle, groups of vehicles, all vehicles, etc. I also need a Universal option for things that are truly for all vehicles. (air fresheners, brake fluid, detailing products, etc. I doubt that a person that owns a WRX would want to see a mud flap set with a "Pro Rodeo" logo on it. Something like that could be mapped to "All Truck", "All SUV", "All RV" but not to any cars.

Conversely, not many truck owners would be buying Sparco race pedals, so I would only map those to cars.

Does what you have allow for something this comprehensive? If not, have you seen anything close to what I need?

Thanks!

Jim

I highly doubt that anyone in the OS Commerce community can pull this off. Especially if you want detailed back-end control and quick-loading pages. Having stated that - - - If it ever does get programmed, any OSC site that sells items specific to a year make or model will benefit - cars, motorcycles, motorhomes, boats, airplanes, ATVs, snowmobiles, Jet Skis, dune buggies, tractors, trailers, etcetera.

I looked at your site and it looks like you already have the year make and model thing worked out. ????????

ken0306

May 25 2008, 02:06 AM

That is a very good function. are you willing to share the contribution?

Glcustoms

May 25 2008, 04:17 AM

QUOTE (performancedeals @ Feb 7 2008, 11:00 PM)

I highly doubt that anyone in the OS Commerce community can pull this off. Especially if you want detailed back-end control and quick-loading pages.

Under-estimate the community you do. The darkness has surely clouded your way young member.

silverkorn

May 26 2008, 01:58 AM

so is anyone willing to share their code or are they keeping this things to themselves?

OSCOMMERCE SUPPORT CALL 702-453-3332