Jump to content
Sign in to follow this  
d.aspo

Automotive filter function

Recommended Posts

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:

 

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:

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?

Share this post


Link to post
Share on other sites

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.

 

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:

 

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:

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.

Share this post


Link to post
Share on other sites

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

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

 

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.

Share this post


Link to post
Share on other sites

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:

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?

Edited by d.aspo

Share this post


Link to post
Share on other sites

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.

Edited by d.aspo

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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. ????????

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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.


Follow the community build:

BS3 to osCommerce Responsive from the Get Go!

Check out the new construction:

Admin Gone to Total BS!

Share this post


Link to post
Share on other sites

HI All, "Dennis" you almost read my mind when creating your version of this filtering (Actually I should state I channelled your mind when the idea for new tables hit me ) Before i ever saw your work I developed an almost carbon copy of your DB structure and ideas on how to implement, I have also created a backend for entering the filtering data and have all but finished with an infobox to boot and a results page that will be like the advanced_search_results mostly. i have a demo going now, that is unfinished mind you, at http : // 62b2b (dot) prositedemos (dot) com. where you can see how i'm beginning to get it going.

 

I decided against using vehicle specific naming to allow for a much more broad range of uses, and to keep it less confusing when adapting to other industry specific products like clothing, shoes, vehicles whatever. I do see a couple small ideas you have that may wind up being VERY beneficial to the one I'm Building and would love to trade some more ideas chats phone conversations on how it would go later on. Anyway just ran across this post for the first time tonight and will be back with any updates :)


-------------------------------------------------

Best Regards,

 

ggbig (maestro)

Share this post


Link to post
Share on other sites

OK so does anyone have a version of this working and can it be ported to work for CRE Loaded site?

 

I am looking for something like this to use on an automotive website so the users can just see what they want to see and their aut info be saved so when they come back they do not have to reload the basic info unless they so desire!

Share this post


Link to post
Share on other sites

HI All, "Dennis" you almost read my mind when creating your version of this filtering (Actually I should state I channelled your mind when the idea for new tables hit me ) Before i ever saw your work I developed an almost carbon copy of your DB structure and ideas on how to implement, I have also created a backend for entering the filtering data and have all but finished with an infobox to boot and a results page that will be like the advanced_search_results mostly. i have a demo going now, that is unfinished mind you, at http : // 62b2b (dot) prositedemos (dot) com. where you can see how i'm beginning to get it going.

 

I decided against using vehicle specific naming to allow for a much more broad range of uses, and to keep it less confusing when adapting to other industry specific products like clothing, shoes, vehicles whatever. I do see a couple small ideas you have that may wind up being VERY beneficial to the one I'm Building and would love to trade some more ideas chats phone conversations on how it would go later on. Anyway just ran across this post for the first time tonight and will be back with any updates :)

 

Hello,

Do you have any updates? And if you do can you please share us the contributions on? :)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×