Alpha-numeric data sorting using php
Moderator: General Moderators
Alpha-numeric data sorting using php
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?
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
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:
You might be able to use a complex regex in MySQL, but it would be really slow. Other ideas?
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- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: Alpha-numeric data sorting using php
This didn't work
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.
Code: Select all
SELECT * FROM product_invertory ORDER BY Product_code + 0 ASC- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: Alpha-numeric data sorting using php
How does it return?
Re: Alpha-numeric data sorting using php
MS-1869
MS-1868
MS-265
MS-263
MT230
MT212
So, not sorted at all.
MS-1868
MS-265
MS-263
MT230
MT212
So, not sorted at all.
- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: Alpha-numeric data sorting using php
Try:
Code: Select all
SELECT * FROM product_invertory WHERE Product_code REGEXP '[[:alnum:]]' ORDER BY Product_code ASC- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
Re: Alpha-numeric data sorting using php
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:
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.
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>';
}
Anyone knows what am I missing here?
Many thanks.
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
Re: Alpha-numeric data sorting using php
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>"; }Re: Alpha-numeric data sorting using php
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.
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.
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK