Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Seeking ideas, how to index auto parts by model year


choosealogin

Recommended Posts

The site I'm working on deals in auto parts, for one particular make of car. What I would like to do eventually is be able to "search by year", on the front end mainly but maybe the backend too. I would like for this search to be available across all categories, and within categories too.

 

For the sake of clarity, there are 9 applicable model years for the cars in question (70, 71, 72, 73, 74, 75, 76, 77, 78)

 

This would be sort of like a manufacturer search, but some parts fit all 9 years, while some parts fit cars over a span of just 3 years, 2 years, or sometimes just one.

 

At this point, the years are buried within the products_name and products_description.

 

I could obviously just list the years within the descriptions, and the advanced search would pick that up, but I'm looking for something a little less ebay-ish and more efficient too.

 

I could duplicate a lot of categories, but that seems really cumbersome and inefficient.

 

My PHP / SQL knowledge is still fairly remedial. I've learned enough that I've had some success modifying existing code and queries, but making something up from scratch is a little over my head. :blink:

 

If I had a starting point, I think I could figure it out. Any thoughts on how I could accomplish this?

Link to comment
Share on other sites

install the categories/products extra fields contribution.

http://www.oscommerce.com/community/contributions,3173

Once installed create an extra field and bind it to products that need a year. (some products may not, like if you have car accessories). Now for each product that requires a "year" set the extra field accordingly.

1970, 1980 etc.

 

In the advanced search script retrieve the extra field that uses the year after the form is posted.

Create an array to lead the selections somewhere. Like,

 

$year_array = array()

$year_array[0]['min'] = 1970;

$year_array[0]['max'] = 1975;

$year_array[1]['min'] = 1976;

$year_array[1]['max'] = 1979;

etc.

 

Create the drop-down list from this array. And during search extract the min/max from the posted parameters and compare it with the extra fields values.

 

You could do many things to simplify it like modify the extra fields to use dates instead of integers/strings, also you can modify it to use category inheritance so you do not have to create fields for each category etc.

 

Many of the php array control functions assume a single dimension array. You may have to write your own functions to process multi-dimensional arrays that you probably gonna need for such an implementation.

Link to comment
Share on other sites

Thanks for the ideas, I really appreciate that. I ended up digging into this pretty deep the day before you posted, though...so I went a slightly different way. If I had it to over again, simplicity is definitely preferred, given my mediocre skills.

 

The thing that was hardest for me to comprehend was how to bind multiple years to a single product. Would using the extra fields contribution allow for that? Maybe you covered that and I misunderstood. Just asking because I'm not totally sure that what I did will really work...basically I'm always looking for the glitch that hasn't shown up yet >_<

 

With that in mind, I ended up adding a products_year column to the products_description table, using a set w/ the 9 possible values (years) that I put in my first post. From a technical and/or efficiency standpoint, I'm not sure if that is a good idea or not. Any thoughts? You can see what I came up w/ on the site that's in my profile.

 

Back to your original suggestion, I'm not sure I know what you mean by single and multiple dimension arrays. I guess I need to read up on that. :blush: Everything I know about coding, I learned here. Could be a compliment to the forums or it could be a scary thought. :P

 

Back to what I did...basically I did away with the advanced search page and put what I wanted in the search box. One thing I couldn't resolve were the error checking functions. I really only want to throw an error for an empty keyword box. Without the advanced search page, that complicated the redirect to advanced_search.php when a user submits w/ an empty search box. I almost had it working, with the pink error bar showing up inside the search box...but after testing it I found that it was throwing the error intermittently as I just browsed the catalog pages. I didn't struggle w/it long enough to figure out when it was or wasn't doing it ...but for now if you leave the box empty it just searches with keyword "%" :blink:

 

Sorry for the long post...and thanks for your guidance :)

Link to comment
Share on other sites

The thing that was hardest for me to comprehend was how to bind multiple years to a single product. Would using the extra fields contribution allow for that?

Yes because now you placed the year in the products_description table. You have products that relay to multiple car models and perhaps different years. So having it in the products description table allows you for a single entry per language. But there isn't a relationship between the language and the year (one that you need at least). As I understand it you have now 9 additional fields in the products_description table to cover for the year increasing the size of the sql table in a static way.

 

You have accessories that require the 1974-1978. Or 1970-1973. So although now you have just 4 or 5 entries to cover you still use 9 (times the number of languages).

 

For the empty user input you could simply check the length on the advanced search results and if it is less than 3 or 4 characters you redirect to an error page or signale the error.

 

However it really depends how the products are arranged and how many products you have with your store. (around 200 in your case)

So for the number of extra columns you now have 200 x no_of_languages x no_of_different_years. Probably you have just one language that gives 1800 extra cells in the sql tables. With the extra fields this figure is dynamic.

 

For the arrays I was refering to the case where you wanted to go by ranges. If I have a product line with some sort of field say between 1-100 then I need a step higher than 1. Otherwise the drop-down list will be extremely long and the search complicated. But with ranges I could define them

1-10

11-20

21-30

etc.

and have just 10 options. In this case I could set the fields with a min/max. So for the first entry the min would be 1 and the max 10. And so on. If the user selects 1-10 I could bring the products related with those fields only (1-10). Also I could have multiple fields. One is 1-100 the other A1-Z9 and so on.

Link to comment
Share on other sites

I'll have to work on my knowledge of how the arrays work. Thanks for the explanation.

 

I didn't explain myself very well. I actually only added one field to the products_description table.

 

Looking at the structure of products_description in phpmyadmin, the field I added looks like this

 

field:products_year

type:set('70', '71', '72', '73', '74', '75', '76', '77', '78')

 

and browsing to look at one part in the table looks something like this

 

(products_name)Tool Box Lid, 71.5-76 Z

(products_year) 71,72,73,74,75,76 <---those 6 values are all in one field

 

Then I added the year to the query

 

$where_str .= " and pd.products_year like '%" . $year . "%'";

 

Despite the fact that I only added one field, I did put in hidden fields for search in description and search subcategories. The query looks kind of long and messy. So if I do a search for the word "bumper", year 1977, category exterior body & trim , this is the query it spits out

 

select distinct p.products_image, m.manufacturers_id, p.products_id, pd.products_name, pd.products_year, pd.products_description, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products p left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id, products_description pd, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and (p2c.categories_id = '31' or p2c.categories_id = '42' or p2c.categories_id = '37' or p2c.categories_id = '41' or p2c.categories_id = '45' or p2c.categories_id = '49' or p2c.categories_id = '50') and pd.products_year like '%77%' and ((pd.products_name like '%bumper%' or p.products_model like '%bumper%' or m.manufacturers_name like '%bumper%' or pd.products_description like '%bumper%') ) order by pd.products_name

 

I guess my follow up question is have I cluttered this up unnecessarily and will it still be OK w/ 10 times the products...and since I made it sound like I added 9 fields to products_description...I wanted to clear that up and see if that makes a difference. Unless I'm misunderstanding and using the data type of "SET" is just as bad as adding 9 fields.

 

I only have one language, and I haven't found a need for the manufacturer's field...so there are some things I could clean up. At some point (hopefully soon) I'll have a lot more products listed so if I need to scratch this idea and start again doing the year a different way, then I will.

 

I read those posts all the time where people plead for advice, go against it and then ask for more. That's not my intention...I just made a big project out of this before you posted...so now it's one of those "I hope I don't have to turn back, but I will if I have to" things. :huh:

 

Thanks for your help.

Link to comment
Share on other sites

I see ok so it is one field and it is some sort of text or varchar type. I thought you implemented separate fields.

The query is ok the only thing you could minimize instead of using the or operators:

 
(p2c.categories_id = '31' or p2c.categories_id = '42' or p2c.categories_id = '37' or p2c.categories_id = '41' or p2c.categories_id = '45' or p2c.categories_id = '49' or p2c.categories_id = '50')

 

you could also do

 

and p2c.categories_id in (31,42,37,41........)

 

Now as for the extra fields really depends on the products you carry and how you set them up. If say there were products that had no relationship to the year of manufacturing (ex: gift certificates) you increase the length of the sql table because this field goes for every product (and subsequently the query). While with the extra fields you have a separate table therefore more control on when you need to pull info from it.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...