[RESOLVED] Proper order sequence for my client that sells boxes

perpetualshaun

I'm developing a CS-Cart for a client who sells boxes online. Since CS-Cart doesn't allow you to import the Position field and there are hundreds of products here, I'm looking to write my own MySQL queries where I can order the products in the correct sequence, and then I'll manually update the Position field with the appropriate value with another query. However, I can't quite seem to get it in the right sequence to begin with. Here's the query I have at the moment:

	SELECT
		products.*,
		descriptions.product AS product_description
	FROM
		cscart_products products
	LEFT JOIN
		cscart_products_categories categories ON categories.product_id=products.product_id
	LEFT JOIN
		cscart_product_descriptions descriptions ON descriptions.product_id=products.product_id
	WHERE
		categories.category_id='31'
	ORDER BY
		CAST(product_description AS SIGNED) ASC

I've attached a screen shot of the results I'm getting in the product_description column. I'm looking for a way to revise this query so they will appear in this order for example:

2 x 2 x 2
3 x 2 x 2
3 x 3 x 1
3 x 3 x 2
3 x 3 x 3

Instead of how it's currently appearing:

2 x 2 x 2
3 x 3 x 3
3 x 3 x 2
3 x 3 x 1
3 x 2 x 2

There are also some boxes that have fractions in them. So ideally, the fractions would appear in the correct sequence also. For example, the box that is sized 6 3/8 x 5 3/8 x 2 1/2 should come after anything that starts out w/ 6 x ...

Any ideas?

Shaun Worcester | senior webologist

design chemistry, llc | next generation web site design
p. 800 640 0424 x 105 | http://www.designchemistry.com/

Connect to me: http://www.google.com/profiles/shaun.daytonwebheads
Follow us on Twitter: http://twitter.com/daytonwebdesign
Become a fan on Facebook: http://www.facebook.com/daytonwebdesign

dagon

i would use a height width depth column store as decimal then format the fractions and add the x for display. Then sorting would be a snap.

perpetualshaun

That sure would be nice, but I'm afraid that's not an option for my system. I can't do anything with that field. Maybe some kind of regular expression inside the SQL query? Seems like that would be an option, but I have no idea how to write it.

--
Shaun Worcester | senior webologist

design chemistry, llc | next generation web site design
p. 800 640 0424 x 105 | http://www.designchemistry.com/

Connect to me: http://www.google.com/profiles/shaun.daytonwebheads
Follow us on Twitter: http://twitter.com/daytonwebdesign
Become a fan on Facebook: http://www.facebook.com/daytonwebdesign

tomhath

It might be easiest to read the rows into a 3-dimensional associative array. Keys to the array would be the product_description exploded on the 'x'. Then loop through the array and create the HTML table as desired. Performance might be an issue if you have a very large number of sizes in a category_id but it's worth a try.

dagon

well that's the problem with third party scripts :_)