Page 1 of 1

Alpha-numeric data sorting using php

Posted: Wed Aug 05, 2009 4:06 pm
by tawfiq
Hi guys,

I have got some products which are identified by unique alpha-numeric code.

How would you sort these product_codes in ascending order where both alha and nemuric values are sorted in order? At the moment, when I run this sql command I get the codes appear as follows.

select * from product_invertory order by Product_code asc


MS-1869
MS-1870
MS-265
MS-266
MT100
MT230

But I want to sort them as

MS-265
MS-266
MS-1869
MS-1870
MT100
MT230

Any idea how to do that?

Re: Alpha-numeric data sorting using php

Posted: Wed Aug 05, 2009 4:20 pm
by tr0gd0rr
Probably the most processor efficient way is to create a new column in the database called something like sortable_product_code. Then write a script in PHP that populates the columns something like this:

Code: Select all

$alphaNumCode = preg_replace('/\W/', '', $code); // strip anything that is not a letter or number
$alpha = substr($alphaNumCode, 0, 2); // get the letter prefix
$num = substr($alphaNumCode, 2); // get the number part
$sortableCode = sprintf('%s%04d', $alpha, $num); // zero-pad the number part to four digits
You might be able to use a complex regex in MySQL, but it would be really slow. Other ideas?

Re: Alpha-numeric data sorting using php

Posted: Wed Aug 05, 2009 4:25 pm
by aceconcepts
Hi,

Try this method:

Code: Select all

SELECT * FROM product_invertory ORDER BY Product_code + 0 ASC

Re: Alpha-numeric data sorting using php

Posted: Wed Aug 05, 2009 4:42 pm
by tawfiq
This didn't work

Code: Select all

SELECT * FROM product_invertory ORDER BY Product_code + 0 ASC
tr0gd0rr's solution is a bit complicated. I'd rather wait and see if someone has any simpler solution. But thanks a lot tr0gd0rr. I'll try your code.

Re: Alpha-numeric data sorting using php

Posted: Wed Aug 05, 2009 5:01 pm
by aceconcepts
How does it return?

Re: Alpha-numeric data sorting using php

Posted: Wed Aug 05, 2009 5:16 pm
by tawfiq
MS-1869
MS-1868
MS-265
MS-263
MT230
MT212

So, not sorted at all.

Re: Alpha-numeric data sorting using php

Posted: Wed Aug 05, 2009 5:30 pm
by aceconcepts
Try:

Code: Select all

SELECT * FROM product_invertory WHERE Product_code REGEXP '[[:alnum:]]' ORDER BY Product_code ASC

Re: Alpha-numeric data sorting using php

Posted: Wed Aug 05, 2009 5:45 pm
by Ollie Saunders

Re: Alpha-numeric data sorting using php

Posted: Mon Aug 17, 2009 4:13 am
by tawfiq
Thanks a lot. It did really work for an array. However, I am yet to find out how that should work with a database table.

I have tried this:

Code: Select all

 
$sql = "select * from products order by code asc";
$result = mysql_query($sql);
if(mysql_num_rows($result)>0){
echo '<table width="100%" border="0" cellspacing="0" cellpadding="0">';
    while($row = mysql_fetch_array($result)){
        $row = natcasesort($row);
        
        echo '
                <tr>
                    <td>'.$row['code'].'</td>
                    <td>'.$row['price'].'</td>
                    <td>'.$row['name'].'</td>
                </tr>
        
        ';
    }
    echo '</table>';
 
}
 
I am pretty sure I am doing it wrong by assigning the sorted array into an array variable like this. $row = natcasesort($row);

Anyone knows what am I missing here?

Many thanks.

Re: Alpha-numeric data sorting using php

Posted: Mon Aug 17, 2009 10:51 am
by Ollie Saunders

Code: Select all

function fetchAllRows($queryResource, $fetchType = 'assoc') {
  $accumulate = array();
  $fetchFn = "mysql_fetch_$fetchType";
  while ($accumulate[] = $fetchFn($queryResource));
  return $accumulate; }
function rowCodeNatCmp($a, $b) { return strnatcmp($a['code'], $b['code']); }
$rows = fetchAllRows(mysql_query($query));
usort($rows, 'rowCodeNatCmp');
foreach ($rows as $r) { echo "<tr><td>{$r['code']}</td><td>{$r['price']}</td><td>{$r['name']}</td></tr>"; }
PHP 5.3 users may pass the body of rowCodeNatCmp() directly into usort() as an anonymous function for additional win.

Re: Alpha-numeric data sorting using php

Posted: Tue Aug 18, 2009 10:43 am
by tawfiq
Thanks a ton.

This works fine for all uppercase or all lowercase product codes. But it doesn't seem to work with upper-lower mixture.

MS-265
ms-1890
MS-206
ms-1891

Although it already serves my purpose well enough, I am just being curious to know if I can achieve the end result using usort in case of mix cases.

Re: Alpha-numeric data sorting using php

Posted: Tue Aug 18, 2009 11:35 am
by Ollie Saunders