Solved

Sorting a Table By Clicking on the Header

Posted on 2013-01-29
13
518 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
 

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Mysql sync between 3-4 mysql db 4 25
mysqli 3 23
How to send multiple emails at the same time in PHP 12 29
str_replace not working in php script 4 14
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now