Sortable Categories in PHP

cmccomas

I have a page, commit.php that has one bit of php in it. The query is as follows:

$query = "SELECT * FROM fb_recruit WHERE pr_muinterest='Committed' ORDER BY pr_rivals DESC";

It gathers the data and puts it into a table for me. I have headings on the table and I want to have the page sortable by the headings.

I know it's done in ASP, but http://sports.espn.go.com/nfl/statistics?stat=team&sort=ypg&pos=off&league=afc&year=2004&season=2 has it done this year.

One of my headings is "WR" and I want it to show all the results on commit.php where the position field in the table is WR.

Understand?

Roger_Ramjet

Understand?

No.

Roger_Ramjet

What ESPN page does is not specific to ASP. Basic html where the table headings are a link that relaods the page with an additional parameter added to the base query. The new param just adds 'ORDER BY colname' to resort the output.

I want it to show all the results on commit.php where the position field in the table is WR.

Add "WHERE position='WR'" to the query.

cmccomas

Originally posted by Roger Ramjet
What ESPN page does is not specific to ASP. Basic html where the table headings are a link that relaods the page with an additional parameter added to the base query. The new param just adds 'ORDER BY colname' to resort the output.

Add "WHERE position='WR'" to the query.

I have several categories though QB, RB, WR, TE (for example sake)

They're listed on a table, I want it to sort by any of the 4 so I can't include it in the WHERE clause, right?

Roger_Ramjet

Right. But that is not what you asked.

To sort by a column use ORDER BY , and you can enter multiple columns to sort on.

To Restrict results to a column data value use WHERE.

cmccomas

Let me try it this way, I've got four columns:

Name | Position | Height | Weight

On a page called commit.php. When you load the page the data is ordered by name ascending. Position, Height, and Weight are links, if you click on Position I want it to sort the data by position, but not have to do a new page for each heading.

Roger_Ramjet

As I said, the ESPN page you refer to does do a call to the server and reload the page with the new sort order. If you want client-side sorting then you are into javascript, VBscript, or the like. There are some very good solutions around that will do client-side sorting of html tables: heres one to look at sortable tables or search google for 'smart tables' (I've lost the link).

cmccomas

Thanks,

What I'd like is what you mentioned, but I'm new and having trouble understanding how to set it up.

I want the page to stay commit.php how do I do the first bit, and do a page reload, sorted by the column head?

Roger_Ramjet

More or less like this

$query = "SELECT * FROM fb_recruit 
          WHERE pr_muinterest='Committed'"; 

if (isset($_GET['sort']) {
    $query .= " ORDER BY " . $_GET['sort'];
}
else {
	$query .= " ORDER BY pr_rivals DESC";
}

$result = mysql_query($query);

echo '<table><tr>
      <th><a href="commit.php?sort=name">Name</a></th>
      <th><a href="commit.php?sort=position">Position</a></th>
      <th><a href="commit.php?sort=height">Height</a></th>
      <th><a href="commit.php?sort=weight">Weight</a></th></tr>';

While // etc

And read my signature

cmccomas

Roger - Thanks, here's what I've got. One thing I'd like to figure it is the top two links are to years, I'd like when 2005 is clicked for it to show the commit.php where pr_year=2005, is that possible?

<tr>
					<td bgcolor="#18481d" align="right" colspan="5" class="textwhitehead"><a href="commit.php?sort=XXXXe">2005</a> | <a href="commit.php?sort=XXXX">2006</a></td>
				  </tr>
<tr>
					<td bgcolor="#18481d" align="left" class="textwhitehead"><a href="commit.php?sort=pr_name">NAME</a></td>
					<td bgcolor="#18481d" align="center" class="textwhitehead"><a href="commit.php?sort=pr_position">POS</a></td>
					<td bgcolor="#18481d" align="center" class="textwhitehead"><a href="commit.php?sort=pr_rating">RATING</a></td>
					<td bgcolor="#18481d" align="center" class="textwhitehead"><a href="commit.php?sort=pr_offered">OFFERED</a></td>
					<td bgcolor="#18481d" align="center" class="textwhitehead"><a href="commit.php?sort=pr_interest">INTEREST</a></td>
				  </tr>


			<? 
						// includes 
						include("../conf.php");

						// open database connection 
						$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!"); 

						// select database 
						mysql_select_db($db) or die ("Unable to select database!"); 

						// generate and execute query 
						$query = "SELECT * FROM fb_recruit ORDER BY pr_rating DESC"; 
						if (isset($_GET['sort']) { 
							$query .= " ORDER BY " . $_GET['sort']; 
							} 
						else { 
							$query .= " ORDER BY pr_rivals DESC"; 
							} 

						$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error()); 

						// if records present
						if (mysql_num_rows($result) > 0)
						{
							// iterate through resultset
							// print article titles
							while($row = mysql_fetch_object($result))
							{
							?>
							<tr>
								<td bgcolor="#DDDDDD" align="left" valign="middle"><font class="textbody"><a href="prospect.php?prospect=<? echo $row->prospect; ?>"><b><? echo $row->pr_fname; ?> <? echo $row->pr_lname; ?></b></a><br /></font><font class="textsmall"><? echo $row->pr_height; ?> / <? echo $row->pr_weight; ?> . <? echo $row->pr_hometown; ?>, <? echo $row->pr_state; ?></font></td>
								<td bgcolor="#DDDDDD" align="center" class="textbody" valign="middle"><? echo $row->pr_position; ?></td>
								<td bgcolor="#DDDDDD" align="center" class="textbody" valign="middle"><img src="../images/stars/<? echo $row->pr_rating; ?>.gif"></td>
								<td bgcolor="#DDDDDD" align="center" class="textbody" valign="middle"><? echo $row->pr_muoffer; ?></td>
								<td bgcolor="#DDDDDD" align="center" class="textbody" valign="middle"><strong><? echo $row->pr_muinterest; ?></strong></td>
							</tr>
							 <?
							}
						}
						// if no records present
						// display message
						else
						{
						?>
							<font class="textbody">No News Items Available</font>
						<?
						}

						// close database connection
						mysql_close($connection);
						?>

It's not working, I've tried several things...