Sorting strings both alphabetically and numerically

[chris_p]

Hi there, I was wondering if anybody could think of an easy way to sort a bunch of strings both alphabetically and numerically - for example this is the intended final sort order of a sample string:

 

A8

A9

A10

A11

A12

 

However when sorted alphabetically, the outputted result order is:

 

A10

A11

A12

A8

A9

 

I think a mix of alphabetic and numeric sorting would be what is needed, does anybody have an idea of how to do this?

 

Many thanks, Chris.

[MadTechie]

untested but this is the idea..

<?php
$string = "A1\nA2\netc";
$array = explode("\n",$string);

#sort 1
sort($array);
echo "<pre>";
print_r($array);

#sort 2
natsort($array);
print_r($array);
?>

[chris_p]

Excellent, thanks - didn't know natsort() exists. Do you know of a way to implement this in a mySQL query at all? For example

SELECT * FROM something ORDER BY ????

 

Thanks

[MadTechie]

Natsort in mysql would look something like this

SELECT *,
CASE WHEN ASCII(RIGHT(`field`, 1)) > 47 AND ASCII(RIGHT(`field`, 1)) < 58
THEN LPAD(CONCAT(`field`,  '-'), 5, '0')
ELSE LPAD(`field`, 5, '0') END AS `vsort`
FROM table ORDER BY `vsort`;

 

replace field and table

[chris_p]

Cool, thanks for taking the time to write that code, would you possibly be able to let me know whereabouts the SQL you wrote goes in the following statement? I tried putting it in a few different places but couldn't seem to get it right (kept coming back with mySQL errors:

 

        $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

 

Many thanks, Chris

[MadTechie]

whats the table & field you wish to sort ?

 

[chris_p]

The table is called 'products' and the field is 'products_model'. Thanks.

[MadTechie]

Humm without testing it myself its hard to say but try this

$listing_sql = "select $select_column_list p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price, CASE WHEN ASCII(RIGHT(p.products_model, 1)) > 47 AND ASCII(RIGHT(p.products_model, 1)) < 58 THEN LPAD(CONCAT(p.products_model,  '-'), 5, '0') ELSE LPAD(p.products_model, 5, '0') END AS `vsort` from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p  left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id  left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c  where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "' ORDER BY `vsort`";

[chris_p]

Thanks for the reply - I think it almost works, but not quite. Currently the outputted list is:

 

A1

A2

BK8

BK9

BK16

C3

A3

A4

A5 to A16

 

So it is counting them from A5 to A16 correctly now, but any ideas why BK8 to C3 are in between the rest of the A's? (eg: from A3 to A16)

 

Thanks.

[StormTheGates]

You could use PHP natsort()?

[MadTechie]

thanks StormTheGates, i said that in the 2nd post

 

anyway to be trueful it might be easier to pull them out into an array and sort them, i know why you have the problem but can't think how to solve it..

 

this is from the first record and its joins

A1

A2

BK8

BK9

BK16

C3

the rest is from the next record and its joins, etc etc