Sorting by table headers

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Spiguy
Forum Newbie
Posts: 2
Joined: Tue May 14, 2002 6:12 am

Sorting by table headers

Post by Spiguy »

peoples,
I am new to PHP and sql so be kind. I have a php script that displays my data from a database but I want to sort it by the column header (i.e. the column header would have a link that would sort the table by that column).
Is it possible to do this all on one page or do I need to build seperate pages for the different columns? I also could not get the ORDER BY function to work, it did not recognize that they numbers were double digits (i.e. 8 was at the top and 36 near the bottom because of sorting on the first nuimber only). Could you look at this and tell me what I am doing wrong? Thanks
George

<?php

$linkID = @mysql_connect("localhost","","");
mysql_select_db("misc", $linkID);

$resultID = mysql_query("SELECT * FROM qb ORDER BY Pass_TDs DESC", $linkID);

print "<table border=1><tr><th>Position</th>";
print "<th>Name</th><th>Team</th>";
print "<th>Pass Comp</th><th>Pass Att</th>";
print "<th>Pass Yds</th><th>Pass TDs</th>";
print "<th>Int</th><th>Rush Att</th><th>Rush Yds</th><th>Rush TDs</th></tr>";

while ($row = mysql_fetch_row($resultID))
{
print "<tr>";
foreach ($row as $field)
{
print "<td>$field</td>";
}
print "</tr>";
}

print "</table>";

mysql_close($linkID);

?>
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post by jason »

Code: Select all

SELECT * FROM qb ORDER BY Pass_TDs DESC
Change that to

Code: Select all

SELECT * FROM qb ORDER BY Pass_TDs ASC
and that should put 36 at the top and 8 near the bottom.

Anyways....onto your question.

Yes, you can build it so at the top of the page, in each header, you can have somone click on something (the header itself?) and have the results Ordered by that. It is actually very simple.

Code: Select all

<?php 

$linkID = @mysql_connect("localhost","",""); 
mysql_select_db("misc", $linkID); 

$resultID = mysql_query("SELECT * FROM qb ORDER BY ".(isset($_GET&#1111;'order_by'] ? $_GET&#1111;'order_by'] : 'Pass_TDs' )." DESC", $linkID); 

print "<table border=1><tr><th><a href="$PHP_SELF?order_by=position">Position</a></th>"; 
print "<th>Name</th><th>Team</th>"; 
print "<th>Pass Comp</th><th>Pass Att</th>"; 
print "<th>Pass Yds</th><th>Pass TDs</th>"; 
print "<th>Int</th><th>Rush Att</th><th>Rush Yds</th><th>Rush TDs</th></tr>"; 

while ($row = mysql_fetch_row($resultID)) 
{ 
        print "<tr>"; 
        foreach ($row as $field) 
        { 
                print "<td>$field</td>"; 
        } 
        print "</tr>"; 
} 

print "</table>"; 

mysql_close($linkID); 

?>
If you look at the Position link, you will notice the link sends along to the same page, the value for order_by. In the sql query, we have an if statement that says if $_GET['order_by'] exists, use it, otherwise, use the normal value.

You can easily just add links for the other headers in the same manner, and the IF statement handles them all equally.
Spiguy
Forum Newbie
Posts: 2
Joined: Tue May 14, 2002 6:12 am

Post by Spiguy »

OK gave that a shot but get a parse error:
Parse error: parse error, expecting `','' or `')'' in /home/virtual/site48/fst/var/www/html/test/phpinfo.php on line 6
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post by jason »

Please, try and debug stuff like this on your own. :D
Change:

Code: Select all

$resultID = mysql_query("SELECT * FROM qb ORDER BY ".(isset($_GET&#1111;'order_by'] ? $_GET&#1111;'order_by'] : 'Pass_TDs' )." DESC", $linkID);
to:

Code: Select all

$resultID = mysql_query("SELECT * FROM qb ORDER BY ".(isset($_GET&#1111;'order_by']) ? $_GET&#1111;'order_by'] : 'Pass_TDs' )." DESC", $linkID);
Post Reply