ORDER BY, Problem.

[smithmr8]

Hi,

I am using the below code, to display people with the most money on hand and in the bank by order of the amount in descending order, but its not quite working properly.

 

 

<?php
$mostmoney = mysql_query("SELECT * FROM `users` ORDER BY `money` DESC LIMIT 10");
$mostbank = mysql_query("SELECT * FROM `users` ORDER BY `bank` DESC LIMIT 10");
echo "<table cellpadding=0 cellspacing=0 width=476 height=227>";
echo "	<tr>";
echo "	<td valign=top width=230>";
echo "<b>Most Money (In Hand)</b><br>";
  while ($money = mysql_fetch_array($mostmoney)) {
  echo "#. ".$money['username']." (£".$money['money'].")<br>";
  }
  echo "</td>";
echo "	<td width=16> </td>";
echo "	<td valign=top height=227 width=230>";	
  echo "<b>Most Money (Bank)</b><br>";
  while ($bank = mysql_fetch_array($mostbank)) {
  echo "#. ".$bank['username']." (£".$bank['bank'].")<br>";
  }
  echo "</td>";
echo "</tr>";
echo "</table>";
?>

 

Thanks

[aschk]

That looks right to me, as I expect you're storing the values as strings (VARCHAR) in your database aren't you...

[tinker]

this seems odd, but have you tried removing the quote marks from around the variable name, it seems to be treating it like a string (it is a numeric field I hope), e.g.

$mostmoney = mysql_query("SELECT * FROM `users` ORDER BY money DESC LIMIT 10");

[revraz]

Do not store or treat numbers as strings and it will sort.

[Chris92]

Make sure your money field type is INT but be honest I don't see any reason why it shouldn't work. Try cleaning up your html a little

<?php
$mostmoney = mysql_query("SELECT * FROM `users` ORDER BY `money` DESC LIMIT 10");
$mostbank = mysql_query("SELECT * FROM `users` ORDER BY `bank` DESC LIMIT 10");
echo "<table cellpadding=\"0\" cellspacing=\"0\" width=\"476\" height=\"227\">";
echo "	<tr>";
echo "	<td valign=\"top\" width=\"230\">";
echo "<strong>Most Money (In Hand)</strong><br />";
 while ($cash = mysql_fetch_array($mostmoney)) {
   echo "#. {$cash['username']} (£{$cash['money']})<br />";
 }
 echo "</td>";
 echo "	<td width=\"16\"> </td>";
 echo "	<td valign=\"top\" height=\"227\" width=\"230\">";	
 echo "<strong>Most Money (Bank)</strong><br />";
 while ($account = mysql_fetch_array($mostbank)) {
   echo "#. {$bank['username']} (£{$bank['bank']})<br />";
 }
 echo "</td>";
echo "</tr>";
echo "</table>";
?>

[smithmr8]

Oh.. I never thought of checking that . It's currently VARCHAR.

 

Thanks

[Sulman]

Make sure your money field type is INT...

 

Surely you meant to say FLOAT right (99.99 etc)...?

[smithmr8]

The way money is configured, everything is in whole numbers. £1, £1954, £19405 ect..

 

Also,

for ($num=1; $num <= 10; $num++ ){
  while ($money = mysql_fetch_array($mostmoney)) {
    echo $num.". ".$money['username']." (£".$money['money'].")<br>";
}
}

 

Im trying to get it to list them and replace the '#' shown above with the corresponding number. This code is making them all '1'. Any ideas ?

 

Thanks

[tinker]

I thought there was a currency data type, but supposedly not, here's how I define it at install, 'price float (8,2)'...

[Sulman]

try this:

 

<?php
$num=1;
while ($money = mysql_fetch_array($mostmoney)) {
    echo $num.". ".$money['username']." (£".$money['money'].")<br>";
    $num++;
}
?>

[smithmr8]

Perfect ! Thanks.