order by asc

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
SidewinderX
Forum Contributor
Posts: 407
Joined: Fri Jul 16, 2004 9:04 pm
Location: NY

order by asc

Post 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
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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>"; 
?>
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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 :?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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.
SidewinderX
Forum Contributor
Posts: 407
Joined: Fri Jul 16, 2004 9:04 pm
Location: NY

Post 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?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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>"; 
?>
SidewinderX
Forum Contributor
Posts: 407
Joined: Fri Jul 16, 2004 9:04 pm
Location: NY

Post 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)
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

yeah sorry, slight oversight....try echoing $fn and see what its value is when you click points.
SidewinderX
Forum Contributor
Posts: 407
Joined: Fri Jul 16, 2004 9:04 pm
Location: NY

Post 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??
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
SidewinderX
Forum Contributor
Posts: 407
Joined: Fri Jul 16, 2004 9:04 pm
Location: NY

Post by SidewinderX »

i know why....in my database i have everything set to varchar....i need to change them to int

:D
SidewinderX
Forum Contributor
Posts: 407
Joined: Fri Jul 16, 2004 9:04 pm
Location: NY

Post by SidewinderX »

what type of data would '1.05' be? Would i store that as Double?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

double/float
Post Reply