?
Solved

Sorting a Table By Clicking on the Header

Posted on 2013-01-29
13
Medium Priority
?
525 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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 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…
Suggested Courses

752 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