Solved

Sorting a Table By Clicking on the Header

Posted on 2013-01-29
13
520 Views
Last Modified: 2013-01-30
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

0
Comment
Question by:DS928
  • 8
  • 5
13 Comments
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 38834810
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
0
 

Author Comment

by:DS928
ID: 38836248
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!
0
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 500 total points
ID: 38836302
Hi

Try changing the class to class="tablesorter" in this line
echo "<table id=\"sortedtable\" class=\"bordered\" cellspacing=\"0\">\n";

also take a look at this link
http://tablesorter.com/docs/#Demo

Regards,
    Tomas Helgi
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:DS928
ID: 38836531
Hello Tomas.  Looking at it now.  Thank you for the link.
0
 

Author Comment

by:DS928
ID: 38836680
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

0
 

Author Comment

by:DS928
ID: 38836831
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.
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 38836979
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
0
 

Author Comment

by:DS928
ID: 38837116
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?
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 38837202
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
0
 

Author Comment

by:DS928
ID: 38837724
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.
0
 

Author Comment

by:DS928
ID: 38838164
OK all is well.  It works perfectly and I am very happy!  Thank you.
0
 

Author Closing Comment

by:DS928
ID: 38838167
Helped me with a very difficult problem that I was having, getting a table to sort.
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 38838504
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
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question