...

View Full Version : Order by both numbers, not just first



thilss0o
03-14-2010, 07:57 AM
i have an order by thing on my results array and the default is set to be by the point total in descending order, but its sorting by the first number and not the two collective numbers.

e.g.

-78
-32
-2
-10

(10 should be above 2, but it orders it below because 1 comes after 2)

here is the code


<?php
// Assign the name of your script so it will work no matter what you named it.
$url=$_SERVER['PHP_SELF'];

// If a sort was requested, then process it.
if($_GET['action'] == "sort"){
$s=$_GET['s'];
$o=$_GET['o'];
$_SESSION['csort'] = $s;
$_SESSION['order'] = $o;
// after sessions are set, re-run the script.
header ("location: $url");
}

// set the default, in case session is not set.
$csort="sum_total";
$order="DESC";

// Read the SESSION variables (if they exist)
if(session_is_registered('csort')){
$csort=$_SESSION['csort'];
}
if(session_is_registered('order')){
$order=$_SESSION['order'];
}

// sanitize any variables used in the MySQL query (always sanitize the variables).
$csort = mysql_real_escape_string($csort);
$order = mysql_real_escape_string($order);
?>
<table border="0" cellspacing="0" style="border-top:solid 1px #333;">
<tr style="border-left:solid 1px #333;border-right:solid 1px #333; border-bottom:solid 1px #333; background-color:#CCCCCC;" >
<td style="width:200px;padding:2px 2px 2px 2px;"><p class="standing-header"><a href="<?=$url?>?action=sort&s=fullname&o=ASC">Entrant</a></p></td>
<td style="width:50px; padding:2px 2px 2px 2px;"><p class="standing-header"><a href="<?=$url?>?action=sort&s=sum_total&o=DESC">Points</a></p></td>
<td style="width:150px;padding:2px 2px 2px 2px;"><p class="standing-header"><a href="<?=$url?>?action=sort&s=pick6_1&o=ASC">Choosen Winner</a></p></td>
<td style="width:100px;padding:2px 2px 2px 2px;"><p class="standing-header"><a href="<?=$url?>?action=sort&s=ID&o=ASC">View Bracket</a></p></td>
</tr>
</table>
<table border="0" cellspacing="0" style="background-image:url(images/table-bg.png);border-bottom:solid 1px #333;">
<?php
// make connection
mysql_connect("localhost", "root", "moreland") or die('Could not connect');
mysql_select_db("ipool") or die('Could not connect to database');


// build query
$query = mysql_query("SELECT * FROM TestTable ORDER BY $csort $order");


// display results
while ($row = mysql_fetch_array($query))
{
echo
"<tr style='border-left:solid 1px #333;border-right:solid 1px #333;'>
<td style='width:200px;padding:2px 2px 2px 2px;'>" .$row['fullname']. "</td>
<td style='width:50px;padding:2px 2px 2px 2px;'>" .$row['sum_total']. "</td>
<td style='width:150px;padding:2px 2px 2px 2px;'>" .$row['pick6_1']. "</td>
<td style='width:100px;padding:2px 2px 2px 2px;'><p class='view-bracket-link'><a href='system/displays/display-bracket-id-".$row['ID'].".php'>View Bracket</a></p></td>
</tr>"

;}

?>
</table>
</div>


how do i fix this? thanks

SKDevelopment
03-14-2010, 08:24 AM
I think the field by which you are ordering has a string type (e.g. CHAR or VARCHAR). If you convert it to the proper numeric type (e.g. INT) the ordering should work.

Edit: Of course you could try to do something like


ORDER BY CAST(myfiled AS DECIMAL)

But it would be less effective. I would advise to use numeric fields for numeric data instead. Then ordering by such fields should work in the numeric (not string) way.

thilss0o
03-14-2010, 08:27 AM
ah, of course, im an idiot

masterofollies
03-14-2010, 03:10 PM
ah, of course, im an idiot

No you're not. We all spend so much time on our scripts hours after hours and start to mess up and find little errors. Happens to me all the time. Time for a coffee break! :thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum