Group Simular

[berrywoods]

I have been messing around using left, right and LD to achive this and still not sure wheres best to go, basicly on our webshop, we have simular products. For Example

Dog Collar Black 10cm
Dog Collar Black 20cm
Dog Collar Black 30cm
Dog Collar Black 40cm

Sometimes the variable is at the front of the description

40cm chokechain
50cm chokechain

sometimes midway

Dog Bed 20cm Black
Dog Bed 30cm Black

On our product page i have got php to group these together, if on say the Dog Collar Black 10cm page, a combo box will show the other 3 and link to them.

I want the listings page to only show 1 of these, remove the variable part. I plan to use php to change its price on the listing page to From £x.xx and the listing result would be basicly for every size avaliable.

So far i have used select distinct left(products_description,5)

this worked providing the variable is the first 5 letters. Is there any more efficent way of doing this. The list is sorted by the used based on price ect so i cant rely on php to do the leg work.

[LifeinAsia]

My suggestion would be to make a new field in your DB table for the variable you're trying to track. (Or better yet, use an INT field for Variable Type and create another table with all the various VariableTypes.) Then you don't have to mess around with parsing on the fly.

[rocknbil]

Right, if it's not too late, you'd be better off at least starting with something like this:

products
id|product_code|name|description (etc.)
1|1234|Dog Collar|Some description|...
2|4567|Choke Chain|Another description|....
3|6565|Dog Bed|Some other description|...

options
id|product_id|option_name|option_value
1|1234|Size|10cm
2|1234|Size|20cm
3|1234|Size|30cm
4|1234|Size|40cm
5|4567|Size|50cm
6|1234|Color|Black
7|6565|Size|20cm
8|6565|Size|30cm
9|6565|Color|Black
10|4567|Color|Silver
11|4567|Color|Gold

Even this is not fully normalized, note the multiple occurrences of Color and Size. Take it one step further in four tables:

option_names
id|option_name
1|Size
2|Color

option_values
id|option_id|value
1|1|10cm
2|1|20cm
3|1|30cm
4|1|40cm
5|1|50cm
6|2|Black
7|2|Silver
8|2|Gold

You tie those to products with the final table - the parentheses are just for notes here, not actual table data:

product_options
id|product_id|option_value_id
1|1234|1 (Collar Size 10cm)
2|1234|2 (Collar Size 20cm)
3|1234|3 (Collar Size 30cm)
4|1234|4 (Collar Size 40cm)
5|4567|5 (Choke Chain Size 50cm)
6|1234|6 (Collar Color Black)
7|6565|2 (Dog Bed Size 20cm)
8|6565|3 (Dog Bed Size 30cm)
9|6565|6 (Dog Bed Color Black)
10|4567|7 (Choke Chain Color Silver)
11|4567|8 (Choke Chain Color Gold)

Then you do your selections by a combination of group by and left joins. This my **seem* like a huge undertaking, but when you get through it, look at the advantages:

- Normalization - you optimize your tables by making them lean and mean, eliminating row after row of the same data, which will slow down all of your queries.

- selecting by integer data - when you do your joins, they will be on integer fields as you can see, and those will **always** be faster than textual selections.

- Simplifies your code - instead of figuring out complex textual pattern matches as you're attempting now, the data will be reliable and absolute, and will not change over time (what happens if you use "colour" instead of "color?")

- You can change the names of your options or even the values at any time without having to reprogram anything or mess with database entries. Using my example, this changes absolutely nothing in your database, everything will still work:

option_names
id|option_name
1|Size
2|Colour

....
6|2|Midnight Black
....

- Selecting and ordering becomes a breeze, you can now order by any of the textual values you want, depending on the situation. For example, when outputting a drop down list for say, the collar sizes, you can order it by option_value.value asc, which will go from 10cm up. No futzing around using up memory by stuffing values in an array and using programming sorting.

- Calculations - though it's not exemplified here, for a merchant, the potential will exist - and you will likely encounter - the need for price variants, for example, prices will vary for small, medium, and large. You can add a price field to your product options table. Leave it at zero for the base price of the product, but enter values and let these override the base price if present. An additional complication, what if it's an ADD ON?

Dog collar carrying case

You can add a field, call it "required" that defaults to 1. If it's a zero, this means you use the base price and if any priced options are selected they add to the price.

(In truth, for full normalization price info should likely be saved in another table, especially if only a few records will contain price variants.)

- And the biggest, baddest one of all, from the standpoint of a merchant: you will never have to modify your code to expand or apply this to virtually any product. As you need a new option, you add it:

id|option_name
1|Size
2|Color
3|Dog Size
4|Dog Breed

....

option_values
id|option_id|value
....
12|3|Small
13|3|Medium
14|3|Large
15|4|Golden Retriever
16|4|Black Lab
17|4|Mutt

This makes it expandable, meaning you can have a near infinite combination of option names and associated values - AND - look closely at the dog collar and dog bed colors. Note how they are distinctly different items but they are using the very same entries for "color." This is called normalization.

It's a hard concept to get your head around, the selects will drive you nuts at first, but it's well worth it.

[berrywoods]

thanks very much rocknbil. Ill have a play around with what you've suggested,

i think oscommerece uses a similar method already.

problem was trying to automate the process.

Currently, my wholesaler sends me a cd with images and a csv file with all product data. Theres no grouping on the csv, every product has a different code. i have a vb.net program which uploads the csv to a sql database, i have a table for products we stock in store, which then goes onto the till system, at about 3am, the program removes all products from the website, and then reuploads both whats on the in-store table and everything else on the wholesaler table.

i didnt want to use oscommerece's inbuilt attributes as this would mean splitting up all the products manually. although i may have a go at using vb.net to do this.

[berrywoods]

ok idea. im testing just a simple

SELECT DISTINCT SUBSTR(products_name, 1, 15)
FROM products_description

obviously gives me unique products with the first 15 characters. i also need the id of this product but when i put

SELECT DISTINCT SUBSTR(products_name, 1, 15),products_id
FROM products_description

it obviously doesnt give me a distinct recordset

id be quite happy if i could run the query, and just say give distinct records of first 15 characters
and
distinct records of last 15 characters

providing no records have the same id

in thoery this should from...

15kg james wellbeloved turkey and rice | 123
15kg james wellbeloved fish and rice | 124
15kg james wellbeloved duck and rice | 125
wagg complete dry beef 15kg | 126
wagg complete dry chicken 15kg | 127
wagg complete dry fish 15kg | 128

produce

15kg james wellb | 123
wagg complete d | 126

if this is even possible? im thinking more the way i want to do this it may be something php may have to handle from the entire recordset.

i already have a function that i use for the product info which does what i want the sql to do quite well.

at the moment when i go into say

10kg Adult Maintenance Duck & Rice Cat Food

the 10kg part is removed, the title is left as
Adult Maintenance Duck & Rice Cat Food

and a dropdown box allows users to choose between

225g Adult Maintenance Duck & Rice Cat Food
2kg Adult Maintenance Duck & Rice Cat Food
5kg Adult Maintenance Duck & Rice Cat Food
10kg Adult Maintenance Duck & Rice Cat Food

im still looking at rocknbil's method to see if vb.net can do the splitting and working out, generating a number to a table which php can then see what products match.