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
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.
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.
Right, if it's not too late, you'd be better off at least starting with something like this:
1|1234|Dog Collar|Some description|...
2|4567|Choke Chain|Another description|....
3|6565|Dog Bed|Some other description|...
Even this is not fully normalized, note the multiple occurrences of Color and Size. Take it one step further in four tables:
You tie those to products with the final table - the parentheses are just for notes here, not actual table data:
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:
- 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:
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.
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.
ok idea. im testing just a simple
SELECT DISTINCT SUBSTR(products_name, 1, 15)
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
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
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
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.