asked on

Sorting a Table By Clicking on the Header

Working on what I picked up from this link....

https://www.dougv.com/2009/06/13/sorting-your-mysql-results-set-in-php-using-jquery-and-a-more-traditional-approach/

I have two versions, aone that sorts and the other that doesn't.
Both tables populate.

Of course I want to use the non-sorting one, only because it's easier for me to manipulate what I have to do. Any help in getting the non-sorting one to sort is appreciated.

The sorting table.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <title>TableTalk</title>
        <link href="../demo.css" rel="stylesheet" type="text/css" />
        <script type="text/javascript" src="../Tables/jquery-1.3.1.min.js"></script>
        <script type="text/javascript" src="../Tables/jquery.tablesorter.min.js"></script>
        <script type="text/javascript">
			$(document).ready(function() {
				$("#sortedtable").tablesorter({ sortlist: [0,0] });
			});
		</script>
        <style type="text/css">
			#sortedtable thead th {
				color: #00f;
				font-weight: bold;
				text-decoration: underline;
			}
		</style>
    </head>
    <body>
    	<?php
			include("config.php");
				if(!$rs = mysql_query("SELECT tblRestaurants.RestName, tblLocations.CityID,
				tblLocations.AreaID, tblLocations.CuisineID, 		
				CONCAT(tblLocations.StreetNumber,' ', tblLocations.Street) Address,
				tblLocations.Phone, tblDetails.Price, tblDetails.Rating
				FROM tblRestaurants INNER JOIN (tblLocations LEFT JOIN tblDetails
				ON tblLocations.LocationID = tblDetails.LocationID) 				
				ON tblRestaurants.RestID = tblLocations.RestID
				WHERE tblLocations.CityID='16'
				AND tblLocations.AreaID='131'
				AND tblLocations.CuisineID='3'
				ORDER BY tblRestaurants.RestName ASC")) {
					echo "Cannot parse query";
				}
				elseif(mysql_num_rows($rs) == 0) {
					echo "No records found";
				}
				else {
					echo "<table id=\"sortedtable\" class=\"bordered\" cellspacing=\"0\">\n";
					echo "<thead>\n<tr>";
					echo "<th>PLACE</th>";
					echo "<th>ADDRESS</th>";
					echo "<th>PHONE</th>";
					echo "<th>PRICE</th>";
					echo "<th>RATING</th>";
					echo "</tr>\n</thead>\n";
					while($row = mysql_fetch_array($rs)) {
						echo "<tr><td>$row[RestName]</td><td>$row[Address]</td><td>$row[Phone]</td><td>$row[Price]</td><td>$row[Rating]</td></tr>\n";
					}
					echo "</table><br />\n";
				}
        ?>
    </body>
</html>

Open in new window

The Non-Sorting Table, The one I need.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <title>TableTalk</title>
        <link href="../demo.css" rel="stylesheet" type="text/css" />
        <script type="text/javascript" src="jquery-1.3.1.min.js"></script>
        <script type="text/javascript" src="jquery.tablesorter.min.js"></script>
        <script type="text/javascript">
			$(document).ready(function() {
				$("#sortedtable").tablesorter({ sortlist: [0,0] });
			});
		</script>
        
    </head>
    <body>
    	<table width="720" border="0" align="left" cellpadding="0" cellspacing="2" id="sortedtable">
    	<tr>
      	<td width="15" bgcolor="#FFDAA6">&nbsp;</td>
      	<td width="12">&nbsp;</td>
      	<td width="150"><font face="Arial, Helvetica, sans-serif" style="font-weight:bold">
     		 	<a href="js.php?sort=RestName">PLACE</a></font></td>
      	<td width="150"><font face="Arial, Helvetica, sans-serif" style="font-weight:bold">
      			<a href="js.php?sort=Address">ADDRESS</a></font></td>
      
      	<td width="150"><font face="Arial, Helvetica, sans-serif" style="font-weight:bold">PHONE</font></td>
      
     	<td width="100"><font face="Arial, Helvetica, sans-serif" style="font-weight:bold">
      			<a href="js.php?sort=Price">PRICE</a></font></td>
      
       	<td width="100"><font face="Arial, Helvetica, sans-serif" style="font-weight:bold">
      			<a href="js.php?sort=Rating">RATING</a></font></td>
      
    	</tr>
        <?php
			include("config.php");
				$query="SELECT tblRestaurants.RestName, tblLocations.CityID,
				tblLocations.AreaID, tblLocations.CuisineID, 		
				CONCAT(tblLocations.StreetNumber,' ', tblLocations.Street) Address,
				tblLocations.Phone, tblDetails.Price, tblDetails.Rating
				FROM tblRestaurants INNER JOIN (tblLocations LEFT JOIN tblDetails
				ON tblLocations.LocationID = tblDetails.LocationID) 				
				ON tblRestaurants.RestID = tblLocations.RestID
				WHERE tblLocations.CityID='16'
				AND tblLocations.AreaID='131'
				AND tblLocations.CuisineID='3'
				ORDER BY tblRestaurants.RestName ASC";
				$result=mysql_query($query);
				$num=mysql_numrows($result);
				mysql_close();
				?>
				<?php
				$i=0;
				while ($i < $num) {
				$f1=mysql_result($result,$i,"RestName");
				$f2=mysql_result($result,$i,"Address");
				$f3=mysql_result($result,$i,"Phone");
				$f4=mysql_result($result,$i,"Price");
				$f5=mysql_result($result,$i,"Rating");
				?>
					
                <tr>
      			<td bgcolor="#FFDAA6">&nbsp;</td>
      			<td width="12" style="font-family:Arial, Helvetica, sans-serif;">&nbsp;</td>
     			<td width="150" style="margin-left:15px; font-family:Arial, Helvetica, sans-serif;"><?php echo $f1; ?></td>
      			<td width="150" style="margin-left:15px; font-family:Arial, Helvetica, sans-serif;"><?php echo $f2; ?></td>
      			<td width="150" style="margin-left:15px; font-family:Arial, Helvetica, sans-serif;"><?php echo $f3; ?></td>
      			<td width="100" style="margin-left:15px; font-family:Arial, Helvetica, sans-serif;"><?php echo $f4; ?></td>
     			<td width="100" style="margin-left:15px; font-family:Arial, Helvetica, sans-serif;"><?php echo $f5; ?></td>
    			</tr>

 				<?php
				$i++;
				}
				?>
				</table>   
    </body>
</html>

Open in new window

Tomas Helgi Johannsson

Hi!

Put the mysql_close() in line 50 after line 74 (after closing the while loop)
otherwise you risk loosing your resultset before working with it in the while loop.

Then use mysql_fetch_array to get the result into an array to work with.
http://php.net/manual/en/function.mysql-query.php
http://www.php.net/manual/en/function.mysql-fetch-array.php

Regards,
Tomas Helgi

DS928

ASKER

Thank you Tomas. I moved the mysql_close() and even removed it, still not sorting. I read through the links and being new to this I am even more confused!

ASKER CERTIFIED SOLUTION

Tomas Helgi Johannsson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DS928

ASKER

Hello Tomas. Looking at it now. Thank you for the link.

DS928

ASKER

OK I loaded the code and it almost works. It only sorts ASC. I have to hold the shift key down to sort DSC. Somehow that doesn't seem right? Also the gif arrows are not appearing. I really like this version. Ver slick looking. Just have to hammer it out.
Not sure what I am missing.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link href="../demo.css" rel="stylesheet" type="text/css" />
<title>Untitled Document</title>
<script type="text/javascript" src="Steelers/jquery-latest.js"></script>
<script type="text/javascript" src="Steelers/jquery.tablesorter.js"></script> 
<script>
	$(document).ready(function()
     {
     $("#myTable").tablesorter();
     } );
     
     $(document).ready(function()
          {
     $("#myTable").tablesorter( {sortList: [[0,0], [1,0]]} );
     } );
	 </script>  
</head>

<body>
<table id="myTable" class="tablesorter"> <thead>
 <tr>
      <th>Last Name</th>
      <th>First Name</th>
      <th>Email</th>
      <th>Due</th>
      <th>Web Site</th>
 </tr>
 </thead>
 <tbody>
 <tr>
      <td>Smith</td>
      <td>John</td>
      <td>jsmith@gmail.com</td> 
      <td>$50.00</td>
      <td>http://www.jsmith.com</td>
 </tr>
      
 <tr>
      <td>Bach</td>
      <td>Frank</td>
      <td>fbach@yahoo.com</td>
      <td>$50.00</td>
      <td>http://www.frank.com</td>
 </tr>
      
<tr>
      <td>Doe</td>
      <td>Jason</td>
      <td>jdoe@hotmail.com</td>
      <td>$100.00</td>
      <td>http://www.jdoe.com</td>
 </tr>
 <tr>
      <td>Conway</td>
      <td>Tim</td>
      <td>tconway@earthlink.net</td>
      <td>$50.00</td>
      <td>http://www.timconway.com</td>
</tr>
</tbody>
</table> 
</body>
</html>

Open in new window

DS928

ASKER

Got the Up/Down arrows to show. But it's still not letting two sorts without the shift key.
And I took this out and it is working!

<script>
	$(document).ready(function()
     {
     $("#myTable").tablesorter();
     } );
 </script>

Open in new window

Next, I will try to incorporate this into my code.

Tomas Helgi Johannsson

Hi!

The sortMultiSortKey is default Shift key but can be Alt or Ctrl, see this section
http://tablesorter.com/docs/#Configuration

And also you can sort the data on one or more columns when you initialize the table
see third example here
http://tablesorter.com/docs/#Getting-Started

Regards,
Tomas Helgi

DS928

ASKER

Thank you. I have a question. I already have two mysql pfp on this page. Is there a way to call this query from another page?

Tomas Helgi Johannsson

Hi!

Yes. If you put the mysql connection and query in a php function that returns an array in a php file you can then include that other php files for reuse. :)

http://php.net/manual/en/language.functions.php
http://php.net/manual/en/function.include.php

Regards,
Tomas Helgi

DS928

ASKER

OK. This is the working code with my database. Would all of this go into a seperate file along with the formatting? Like I said, I'm new. By the way. I love this table with the arrows and such. This great! Thank you so much!

<title>Steelers JayZ</title>
<script type="text/javascript" src="jquery-latest.js"></script>
<script type="text/javascript" src="jquery.tablesorter.js"></script> 
<script>
     $(document).ready(function()
          {
     $("#myTable").tablesorter( {sortList: [0,0]} );
     } );
	 </script>  
</head>

<body>
<!--///////////////////////////////////////////////////////////////////////////////////////////////////-->
<?php
			include("config.php");
				if(!$rs = mysql_query("SELECT tblRestaurants.RestName, tblLocations.CityID,
				tblLocations.AreaID, tblLocations.CuisineID, 		
				CONCAT(tblLocations.StreetNumber,' ', tblLocations.Street) Address,
				tblLocations.Phone, tblDetails.Price, tblDetails.Rating
				FROM tblRestaurants INNER JOIN (tblLocations LEFT JOIN tblDetails
				ON tblLocations.LocationID = tblDetails.LocationID) 				
				ON tblRestaurants.RestID = tblLocations.RestID
				WHERE tblLocations.CityID='16'
				AND tblLocations.AreaID='131'
				AND tblLocations.CuisineID='3'
				ORDER BY tblRestaurants.RestName ASC")) {
					echo "Cannot parse query";
				}
				elseif(mysql_num_rows($rs) == 0) {
					echo "No records found";
				}
				else {
					echo "<table id=\"myTable\" table width=\"720\" class=\"tablesorter\" align=\"Left\" cellspacing=\"0\">\n";
					echo "<thead>\n<tr>";
					echo "<th>PLACE</th>";
					echo "<th>ADDRESS</th>";
					echo "<th>PHONE</th>";
					echo "<th>PRICE</th>";
					echo "<th>RATING</th>";
					echo "</tr>\n</thead>\n";
					while($row = mysql_fetch_array($rs)) {
					echo"<tr><td>$row[RestName]</td><td>$row[Address]</td><td>$row[Phone]</td><td>$row[Price]</td><td>$row[Rating]</td></tr>\n";
					}
					echo "</table><br />\n";
				}
        ?>
</body>
</html>

Open in new window

I've been looking at the links.......Still not quite sure what to do.

DS928

ASKER

OK all is well. It works perfectly and I am very happy! Thank you.

DS928

ASKER

Helped me with a very difficult problem that I was having, getting a table to sort.

Tomas Helgi Johannsson

Hi!

I would separate the html and the queries and only put the database related stuff into a separte file which you could then reuse in other php files where you do the actual html code for the webpage. The functions shuould then only receive and return data either single or array.
Glad to help.

Regards,
Tomas Helgi