Solved

Sorting a Table By Clicking on the Header

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

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 24

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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 24

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 24

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 24

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Creating and Managing Databases with phpMyAdmin in cPanel.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

758 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

21 Experts available now in Live!

Get 1:1 Help Now