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