Alpha-numeric data sorting using php

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
tawfiq
Forum Newbie
Posts: 21
Joined: Sun Jan 27, 2008 12:19 pm

Alpha-numeric data sorting using php

Post 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?
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: Alpha-numeric data sorting using php

Post 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?
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Alpha-numeric data sorting using php

Post by aceconcepts »

Hi,

Try this method:

Code: Select all

SELECT * FROM product_invertory ORDER BY Product_code + 0 ASC
tawfiq
Forum Newbie
Posts: 21
Joined: Sun Jan 27, 2008 12:19 pm

Re: Alpha-numeric data sorting using php

Post 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.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Alpha-numeric data sorting using php

Post by aceconcepts »

How does it return?
tawfiq
Forum Newbie
Posts: 21
Joined: Sun Jan 27, 2008 12:19 pm

Re: Alpha-numeric data sorting using php

Post by tawfiq »

MS-1869
MS-1868
MS-265
MS-263
MT230
MT212

So, not sorted at all.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Alpha-numeric data sorting using php

Post by aceconcepts »

Try:

Code: Select all

SELECT * FROM product_invertory WHERE Product_code REGEXP '[[:alnum:]]' ORDER BY Product_code ASC
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: Alpha-numeric data sorting using php

Post by Ollie Saunders »

tawfiq
Forum Newbie
Posts: 21
Joined: Sun Jan 27, 2008 12:19 pm

Re: Alpha-numeric data sorting using php

Post 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.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: Alpha-numeric data sorting using php

Post 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.
tawfiq
Forum Newbie
Posts: 21
Joined: Sun Jan 27, 2008 12:19 pm

Re: Alpha-numeric data sorting using php

Post 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.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: Alpha-numeric data sorting using php

Post by Ollie Saunders »

Post Reply