sorting numbers in a text colomn

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
mccommunity
Forum Commoner
Posts: 62
Joined: Mon Oct 07, 2002 8:55 am

sorting numbers in a text colomn

Post by mccommunity »

I am trying to correctly sort numbers in a text colum (1,2,3,4....,10,11) and it currently sorts them (1,10,100,101,2,20...) How do I do this?? My code is below. Thanks:

$strSQL = "SELECT item, min(amount), max(amount) FROM bidlog WHERE code='K' and auction='$short_auction_name' GROUP BY item ORDER BY item";
$result = pg_exec($con, $strSQL);
if(!$result)
{
print("<SCRIPT>alert('An unknown error occurred. The bidder information could not be retrieved, please try again. If it does not work contact AMS.')</SCRIPT>\n");
return "false";
}

echo '<table align=center cellpadding=5><tr><th bgcolor="#C0C0C0">Item</th><th bgcolor="#C0C0C0">Start Bid</th><th bgcolor="#C0C0C0">End Bid</th><th bgcolor="#C0C0C0">Value Added</th></tr> ';
for ($y=0;$y<pg_numrows($result);$y++){
$data = pg_fetch_array($result,$y);
$value_added = $data[2]-$data[1];
echo "<tr bgColor=#dddddd><th><font color=red>$data[0]</font></th><th>$$data[1]</th><th>$$data[2]</th><th><I>$$value_added</I></th></tr>";
}
echo '</table>';
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

if there are only numbers stored in that field why not make it a numerical field?
mccommunity
Forum Commoner
Posts: 62
Joined: Mon Oct 07, 2002 8:55 am

reply

Post by mccommunity »

Sometimes there is A001, A002 and these sort correctly.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

I had to do this quickly one day, and had no time to actually look into it, so this might not be the best way (might not even work with your data...).

But...
I use 'CAST(expression AS type)' to change the varchar field i had, into unsigned (integer) and the I could sort it abit better. Much like:

Code: Select all

"select foo from bar order by cast(otherfield as unsigned) desc"
...alltho I'm not sure that is precicely what I used at the time. Might be worth looking at tho.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

hm, then I'm clueless, sorry
Post Reply