Page 1 of 1

order by asc

Posted: Thu Sep 01, 2005 3:05 pm
by SidewinderX
Hello. I read up on some info relating to sorting information by ascending and descending values. But im still unsure how to do what i want.

Here is my code

Code: Select all

<?php
$db = mysql_connect("", "", ""); 
mysql_select_db("TOP50",$db);

$result = mysql_query("SELECT * FROM stats", $db);
echo "<table width=\"75%\" border=\"1\">
  <tr> 
    <td width=\"12%\"><div align=\"center\"><strong>Player</strong></div></td>
    <td width=\"12%\"><div align=\"center\"><strong>Exp. Points</strong></div></td>
  </tr>";
while ($row = mysql_fetch_assoc($result))
{
extract($row);
echo "  <tr>
    <td>$player</td>
    <td>$team_experience_points</td>
	    </tr>";
}
echo "</table>";
?>
Now what i want to do is link "Player" and "Exp. Points" so when a user clicks on it, the data will sort itself by ascending / descending values. Could someone please give me an example?

Thankyou

Burrito: Please use php tags when Posting PHP Code In The Forums
SidewinderX: I usually do, i was just in a rush 8O

Posted: Thu Sep 01, 2005 3:21 pm
by Burrito
you coudl try somethign like the following:

note this assumes your page name is page.php:

Code: Select all

PHP: 
<?php 
$db = mysql_connect("", "", ""); 
mysql_select_db("TOP50",$db); 
$ordr = (isset($_GET['ordr']) ? $_GET['ordr'] : "asc");

$result = mysql_query("SELECT * FROM stats order by player $ordr", $db); 
echo "<table width=\"75%\" border=\"1\"> 
  <tr> 
    <td width=\"12%\"><div align=\"center\"><strong><a href=\"page.php?ordr=".(isset($_GET['ordr']) && $_GET['ordr'] == "asc" ? "desc" : "asc")."\">Player</a></strong></div></td> 
    <td width=\"12%\"><div align=\"center\"><strong>Exp. Points</strong></div></td> 
  </tr>"; 
while ($row = mysql_fetch_assoc($result)) 
{ 
extract($row); 
echo "  <tr> 
    <td>$player</td> 
    <td>$team_experience_points</td> 
        </tr>"; 
}  
echo "</table>"; 
?>

Posted: Thu Sep 01, 2005 3:31 pm
by raghavan20
anybody here can explain why we are using extract() here???
I have understood the basic concept of extract() to treat keys as variable names and values as variable values.
but why are we using it here :?

Posted: Thu Sep 01, 2005 3:51 pm
by Burrito
I used to use extract when I first started working with php especially when was returning a ton of results...probably just out of laziness (didn't want to write out the whole array every time I wanted to do something with values).

I always used the EXTR_OVERWRITE extract type and started running into issues with overwriting other vars so I started steering away from it. Now, out of habit, I just write out the whole array...but whatever works.

Posted: Thu Sep 01, 2005 6:08 pm
by SidewinderX
thanks, its almost exactley what i want. if i understand what is happening correctley, when you click on the link it passes asc / desc through the url and then in the sql query it takes $ordr and does what ever it is set to. My next question is how would i pass what it is to "ORDER BY" through the URL. When i was messing with the code you gave me i edited it so it looks like this now

Code: Select all

<?php
include "config.php";
include "language.php";

$ordr = (isset($_GET['ordr']) ? $_GET['ordr'] : "asc");
$result = mysql_query("SELECT * FROM stats ORDER BY player $ordr", $db);

echo "<center>
<table width=\"75%\" border=\"1\">
<tr>
<td width=\"12%\"><div align=\"center\"><strong><a href=\"raw.php?ordr=".(isset($_GET['ordr']) && $_GET['ordr'] == "asc" ? "desc" : "asc")."\">player</a></strong></div></td>
<td width=\"12%\"><div align=\"center\"><strong><a href=\"raw.php?ordr=".(isset($_GET['ordr']) && $_GET['ordr'] == "asc" ? "desc" : "asc")."\">points</a></strong></div></td>
</tr>";
while ($row = mysql_fetch_assoc($result))
{
extract($row);
echo "<tr>
<td>$player</td>
<td>$team_experience_points</td>
</tr>";
}  
echo "</table></center>";
?>
When i click on player it does what i want it to do. BUt when i click on points it still orders it by player because in the sql query it is set to player. My new question is how would i send player and points through the URL?

Posted: Thu Sep 01, 2005 6:13 pm
by Burrito
you need to pass the field name as well:

Code: Select all

<?php 
include "config.php"; 
include "language.php"; 

$ordr = (isset($_GET['ordr']) ? $_GET['ordr'] : "asc"); 
$fn = (isset($_GET['fn']) ? $_GET['fn'] : "player");
$result = mysql_query("SELECT * FROM stats ORDER BY $fn $ordr", $db); 

echo "<center> 
<table width=\"75%\" border=\"1\"> 
<tr> 
<td width=\"12%\"><div align=\"center\"><strong><a href=\"raw.php?ordr=".(isset($_GET['ordr']) && $_GET['ordr'] == "asc" ? "desc" : "asc")."\&fn=player">player</a></strong></div></td> 
<td width=\"12%\"><div align=\"center\"><strong><a href=\"raw.php?ordr=".(isset($_GET['ordr']) && $_GET['ordr'] == "asc" ? "desc" : "asc")."\&fn=team_experience_points">points</a></strong></div></td> 
</tr>"; 
while ($row = mysql_fetch_assoc($result)) 
{ 
extract($row); 
echo "<tr> 
<td>$player</td> 
<td>$team_experience_points</td> 
</tr>"; 
}   
echo "</table></center>"; 
?>

Posted: Thu Sep 01, 2005 7:06 pm
by SidewinderX
hm...when i click points it still orders it by players 8O

Code: Select all

<?php
include "config.php";
include "english.php";

$ordr = (isset($_GET['ordr']) ? $_GET['ordr'] : "asc");
$fn = (isset($_GET['fn']) ? $_GET['fn'] : "player");
$result = mysql_query("SELECT * FROM stats ORDER BY $fn $ordr", $db);

echo "<center>
<table width=\"75%\" border=\"1\">
<tr>
<td width=\"12%\"><div align=\"center\"><strong><a href=\"raw.php?ordr=".(isset($_GET['ordr']) && $_GET['ordr'] == "asc" ? "desc" : "asc")."&fn=player\">$_PLAYER</a></strong></div></td>
<td width=\"12%\"><div align=\"center\"><strong><a href=\"raw.php?ordr=".(isset($_GET['ordr']) && $_GET['ordr'] == "asc" ? "desc" : "asc")."&fn=team_experience_points\">$_EXPPOINTS</a></strong></div></td>
</tr>";
while ($row = mysql_fetch_assoc($result))
{
extract($row);
echo "<tr>
<td>$player</td>
<td>$team_experience_points</td>
</tr>";
}  
echo "</table></center>";
?>
(i changed 2 things on lines 12 and 13 in your code the \ wasnt in the right place...i think i fixed it anyway)

Posted: Thu Sep 01, 2005 7:48 pm
by Burrito
yeah sorry, slight oversight....try echoing $fn and see what its value is when you click points.

Posted: Thu Sep 01, 2005 8:30 pm
by SidewinderX
when i echo $fn and click points i get "team_experience_point"
theoretically it should be working..

i added another one to see if it was an error in my coding or something. it wasnt. except when i added the new one it didnt sort it by player...which leads me to belive it is sorting it properly.... however the data is incorrect....

here is Points Acending
120437
181255
456610
46389
and here is the new one Acending
1727
2422
3369
695
neither one is sorted properly according to their true value..however they are sorted properly according to the first 2 digits.... any ideas on that??

Posted: Thu Sep 01, 2005 8:34 pm
by feyd
is the field in the database a string? Then it is ordered as you requested. You can have MySQL convert them to numbers using "$fn+0 $ordr"

note, if there are other fields to be sorted that aren't numbers, you'll need to tweak the $fn value as it comes in.

Posted: Thu Sep 01, 2005 8:34 pm
by SidewinderX
i know why....in my database i have everything set to varchar....i need to change them to int

:D

Posted: Thu Sep 01, 2005 10:11 pm
by SidewinderX
what type of data would '1.05' be? Would i store that as Double?

Posted: Thu Sep 01, 2005 10:36 pm
by feyd
double/float