0

I have a mysql query 

   SELECT jss_products . * , jss_extrafields_values.content as test_weight
   FROM jss_products_tree
   INNER JOIN jss_products ON jss_products.productID = jss_products_tree.productID
   INNER JOIN jss_extrafields_values ON jss_extrafields_values.productID = jss_products.productID   AND extraFieldID = 5
   WHERE sectionID = 1
   ORDER by test_weight

It returns me different products with their options and other things, as well as their weight. Now the thing about weight, it is a varchar field. And currently weight is not only an integer, but it can be anything like

18.8 Gram
20 Gram
20g
18.5gr
18.5g
18.5 gr
Style 4 18 gram
Style B 18gram
1081A 22gram
1081B 22 gr
16 gram Knurled (k)

and similar.

Basicly what all have in common, the weight might be lets say 10-40gram. most of the time it's double digit number but it might be decimal as well.

Now what I need to do, is to sort products by weight. I was trying regexp, abs, cast and other functions which returns the field as integer but none of them worked as expected.

Any thoughts on this? What should I use? Is it even possible to do it? Please do ask if I could provide more information. Thanks.

php_nub_qq
  • 15,199
  • 21
  • 74
  • 144
  • I think you need a preg_match, which MySQL doesn't support 'out-of-the-box' - although I think someone wrote an extension. It might be simplest to parse the whole result to some application code (PHP?), extract all the numbers and feed those back to the table in some normalised form (weight DECIMAL(5,2),unit VARCHAR/ENUM, etc.) - They do all appear to be grams, which may make things slightly simpler – Strawberry Sep 02 '14 at 12:03
  • Consider re-thinking your database design (if you have the authority to do so!) to constrict your values to something sensible and workable. E.g. numeric data and (optional) unit description, or some kind of coefficient for you to normalise the unit against an SI measurement, such as kg. – ne1410s Sep 02 '14 at 12:04
  • A partial solution: Change jss_extrafields_values.content to 0+jss_extrafields_values.content. This will convert those values that start with digits correctly. Sadly, it will be completely wrong for '1081B 22 gr' and will produce zero for 'Style 4 18 gram'. You will have a very hard time making a regex in php that actually works well here, I expect, but doing the regex in php seems to be the way to go – Chris Lear Sep 02 '14 at 12:08
  • Take look at: [MySQL - Return matching pattern in REGEXP query](http://stackoverflow.com/questions/5361457/mysql-return-matching-pattern-in-regexp-query) – Rimas Sep 02 '14 at 12:10
  • What you need to do is [normalize your database](http://en.wikipedia.org/wiki/Database_normalization) – php_nub_qq Sep 02 '14 at 13:07

0 Answers0