David Joor Posted September 8, 2009 Share Posted September 8, 2009 I sell ball bearings and the sizes are relatively close to each other. The issue I am having is that I would like them sorted smallest to largest however just a standard ORDER BY places "10x15x4 Metal" before "2x6x3 Metal" because of the 1. My previous solution was "ORDER BY 0 + products.title ASC". Works decently but items that start with the same first digit just get randomly sorted. Here are my examples of what is happening. ex1: ORDER BY products.title ASC 10x15x4 Metal 5x10x4 Ceramic Metal 5x10x4 Flanged Rubber 5x10x4 Metal 5x10x4 Revolutions 5x11x4 Rubber 5x12x4 Revolution 5x13x4 Revolution 5x8x2.5 Ceramic Revolu... 5x8x2.5 Flanged Rubber 5x8x2.5 Rubber 5x9x3 Metal 5x9x3 Revolutions ex2: ORDER BY 0 + products.title ASC 5x13x4 Revolution 5x9x3 Revolutions 5x8x2.5 Ceramic Revolu... 5x11x4 Rubber 5x9x3 Metal 5x10x4 Flanged Rubber 5x10x4 Revolutions 5x8x2.5 Flanged Rubber 5x10x4 Metal 5x8x2.5 Rubber 5x10x4 Ceramic Metal 5x12x4 Revolution 10x15x4 Metal ex3: What I'm trying to accomplish 5x8x2.5 Ceramic Revolu... 5x8x2.5 Flanged Rubber 5x8x2.5 Rubber 5x9x3 Metal 5x9x3 Revolutions 5x10x4 Ceramic Metal 5x10x4 Flanged Rubber 5x10x4 Metal 5x10x4 Revolutions 5x11x4 Rubber 5x12x4 Revolution 5x13x4 Revolution 10x15x4 Metal I wouldn't even mind if they were slightly out of sequence like ex1 as long as they are grouped together and 10x15x4 was below a 5x9x3. Any ideas, this is so driving crazy! Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted September 8, 2009 Share Posted September 8, 2009 You can break the string into the three size fields, using msyql functions in the query, cast the values as numbers, then ORDER BY field1,field2,field3 (time permitting someone can probably post a query to do this) or you could add a column that you could put values into and use in the ORDER BY (probably not a good choice if you have a lot of existing items.) Quote Link to comment Share on other sites More sharing options...
David Joor Posted September 8, 2009 Author Share Posted September 8, 2009 Sounds great, over my head though... I thought about just setting a column for ordering but we have too many items to manage over time even if we did increments of 10. So far that is my only alternative though. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted September 8, 2009 Share Posted September 8, 2009 Partially tested only, should work - SELECT *, SUBSTRING_INDEX(SUBSTRING_INDEX(products.title, ' ', 1), 'x', 1) + 0 as f1, SUBSTRING_INDEX(SUBSTRING_INDEX(products.title, 'x', 2), 'x', -1) + 0 as f2, SUBSTRING_INDEX(SUBSTRING_INDEX(products.title, ' ', 1), 'x', -1) + 0 as f3 FROM your_table ORDER BY f1,f2,f3 Quote Link to comment Share on other sites More sharing options...
David Joor Posted September 8, 2009 Author Share Posted September 8, 2009 OH MY GOSH!!! I have absolutely no idea what that did but it worked flawlessly. I will go research SUBSTRING_INDEX now. I can't thank you enough. I would have never gotten this! Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted September 8, 2009 Share Posted September 8, 2009 Each of three lines of the query using SUBSTRING_INDEX() (the first one can actually be simplified) gets a separate 'n' field out of the 'nxnxn text' string. The 1st one gets everything up to the first x. The 2nd one gets everything up to the second x, then gets just the number back to the previous x. The 3rd one gets everything up to the first space, then gets just the number back to the previous x. Quote Link to comment Share on other sites More sharing options...
David Joor Posted September 8, 2009 Author Share Posted September 8, 2009 I worked it out, for 5x10x4 Revolution f1 = 5 f2 = 10 f3 = 4 But what is the + 0 for? Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted September 8, 2009 Share Posted September 8, 2009 SUBSTRING_INDEX() produces a string. When you sort stings that contain numeric digits, you get things like 10 is less than 9 because the first character in the string 10 is less than the first character in the string 9. The + 0 forces the string to be cast as a number. Quote Link to comment Share on other sites More sharing options...
David Joor Posted September 8, 2009 Author Share Posted September 8, 2009 Ahh, I see. For some reason I thought it was adding it to the end of the value like. f1 = 50 f2 = 100 f3 = 40 Obviously this isn't the case. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.