How to sort rows of HTML table that are called from MySQL

Question

I know it's such a basic thing, but a Google search hasn't shown me how to re-sort the rows after clicking the th links.

I've got this:

<table border="1">
  <tr>
    <th>Type:</th>
    <th>Description:</th>
    <th>Recorded Date:</th>
    <th>Added Date:</th>
  </tr>

<?php 
while($row = mysql_fetch_array($result)){
    ?>
    <tr>
        <td><?php echo $row['type'] ?></td>
        <td><?php echo $row['description'] ?></td>
        <td><?php echo $row['recorded_date'] ?></td>
        <td><?php echo $row['added_date'] ?></td>
    </tr>
    <br /> 


  <?php 
}
mysql_close();
?>
</table>

I need to be able to click type and sort alphabetically, and click on either Recorded Date or Added Date and sort by date. I see that I need to have the MySQL queries do this, but do I set them up as conditionals with a href tags?

Kibbee · Accepted Answer · 2010-08-16T00:45:41.710

52

The easiest way to do this would be to put a link on your column headers, pointing to the same page. In the query string, put a variable so that you know what they clicked on, and then use ORDER BY in your SQL query to perform the ordering.

The HTML would look like this:

<th><a href="mypage.php?sort=type">Type:</a></th>
<th><a href="mypage.php?sort=desc">Description:</a></th>
<th><a href="mypage.php?sort=recorded">Recorded Date:</a></th>
<th><a href="mypage.php?sort=added">Added Date:</a></th>

And in the php code, do something like this:

<?php

$sql = "SELECT * FROM MyTable";

if ($_GET['sort'] == 'type')
{
    $sql .= " ORDER BY type";
}
elseif ($_GET['sort'] == 'desc')
{
    $sql .= " ORDER BY Description";
}
elseif ($_GET['sort'] == 'recorded')
{
    $sql .= " ORDER BY DateRecorded";
}
elseif($_GET['sort'] == 'added')
{
    $sql .= " ORDER BY DateAdded";
}

$>

Notice that you shouldn't take the $_GET value directly and append it to your query. As some user could got to MyPage.php?sort=; DELETE FROM MyTable;

edited Aug 16 '10 at 00:45

answered Aug 16 '10 at 00:02

Kibbee

65,369
27
142
182

Hmm. I'm getting a Parse error: syntax error, unexpected T_IS_EQUAL for that first 'if' statement – Joel Aug 16 '10 at 00:28
1

Yeah, my syntax probably isn't perfect. I think it's fixed now. – Kibbee Aug 16 '10 at 00:33
I can't edit it myself, but I found the errors. Main one is you need $sql not sql. Also, my table names are different on a couple columns. But, THANK you for doing this! It got me with the right answer! – Joel Aug 16 '10 at 00:35
1

editted to fix the syntax problems. PHP isn't a language I use everyday. Haven't used it in a while. Those $ always get me. I always found it really annoying to have to start variables with a special character. Can't think of why it would be required. No other language I can think of requires anything like that. – Kibbee Aug 16 '10 at 00:47
Nice. You can dry it out a bit with this map: $order = array( "type" => array("Type","type"), "desc" => array("Description", "Description"), ... ); Looping through it you can both generate in loop the table headers and directly map the order parameter to the column. – nad2000 Apr 23 '13 at 09:33

score 42 · Answer 2 · answered Aug 16 '10 at 00:08

42

That's actually pretty easy, here's a possible approach:

<table>
    <tr>
        <th>
            <a href="?orderBy=type">Type:</a>
        </th>
        <th>
            <a href="?orderBy=description">Description:</a>
        </th>
        <th>
            <a href="?orderBy=recorded_date">Recorded Date:</a>
        </th>
        <th>
            <a href="?orderBy=added_date">Added Date:</a>
        </th>
    </tr>
</table>
<?php
$orderBy = array('type', 'description', 'recorded_date', 'added_date');

$order = 'type';
if (isset($_GET['orderBy']) && in_array($_GET['orderBy'], $orderBy)) {
    $order = $_GET['orderBy'];
}

$query = 'SELECT * FROM aTable ORDER BY '.$order;

// retrieve and show the data :)
?>

That'll do the trick! :)

answered Aug 16 '10 at 00:08

Guido Hendriks

5,706
3
27
37

4

There's definitely **NO** possible SQL injection, look closer! Whitelisted the possible fields to order by. – Guido Hendriks Aug 16 '10 at 00:21
1

I didn't downvote, but for some reason, I don't remember the checks being there when I first saw your answer. However I may just have missed it, and some others may have missed it as well. I like your style better than mine, so I'm giving you an upvote too. – Kibbee Aug 16 '10 at 00:25
@Kibbee you didn't mistake it for Sarfraz's attempt did you? Which he's now deleted but had sql injection issues? – Aug 16 '10 at 00:38
No, I still see Sarfraz's answer. I might have had the 2 confused. For some reason I remember a straight concatenation of the query and the $_GET. Guess I just missed something, others may have also. – Kibbee Aug 16 '10 at 00:45
I like this answer best, however I just appended DELETE added_date='some date' and the SQL INJECTION deleted the record... Can someone help with preventing this??? – BarclayVision Aug 17 '16 at 11:45
@BarclayVision Because of the use of whitelisting, there's no danger of SQL injection in this code. – Guido Hendriks Aug 17 '16 at 11:46

score 1 · Answer 3 · edited May 23 '17 at 10:31

A SIMPLE TABLE SORT PHP CODE:

(the simple table for several values processing and sorting, using this sortable.js script )

<html><head>
<script src="sorttable.js"></script>

<style>
tbody tr td {color:green;border-right:1px solid;width:200px;}
</style>
</head><body>

<?php
$First = array('a', 'b', 'c', 'd');
$Second = array('1', '2', '3', '4');

if (!empty($_POST['myFirstvalues'])) 
{ $First = explode("\r\n",$_POST['myFirstvalues']); $Second = explode("\r\n",$_POST['mySecondvalues']);}

?>

</br>Hi User. PUT your values</br></br>
<form action="" method="POST">
projectX</br>
<textarea cols="20" rows="20" name="myFirstvalues" style="width:200px;background:url(untitled.PNG);position:relative;top:19px;Float:left;">
<?php foreach($First as $vv) {echo $vv."\r\n";}?>
</textarea>

The due amount</br>
<textarea cols="20" rows="20" name="mySecondvalues" style="width:200px;background:url(untitled.PNG);Float:left;">
<?php foreach($Second as $vv) {echo $vv."\r\n";}?>
</textarea>
<input type="submit">
</form>

<table class="sortable" style="padding:100px 0 0 300px;">
<thead style="background-color:#999999; color:red; font-weight: bold; cursor: default;  position:relative;">
  <tr><th>ProjectX</th><th>Due amount</th></tr>
</thead>
<tbody>

<?php
foreach($First as $indx => $value) {
    echo '<tr><td>'.$First[$indx].'</td><td>'.$Second[$indx].'</td></tr>';
}
?>
</tbody>
<tfoot><tr><td>TOTAL  = &nbsp;<b>111111111</b></td><td>Still to spend  = &nbsp;<b>5555555</b></td></tr></tfoot></br></br>
</table>
</body>
</html>

source: php sortable table

score 0 · Answer 4 · answered Apr 24 '13 at 11:54

//this is a php file

<html>
<head>
<style>
a:link {color:green;}
a:visited {color:purple;}
A:active {color: red;}
A:hover {color: red;}
table
{
    width:50%;
    height:50%;
}
table,th,td
{
    border:1px solid black;
}
th,td
{
    text-align:center;  
    background-color:yellow;
}
th
{
    background-color:green;
    color:white;    
}
</style>
<script type="text/javascript">
function working(str)
{
if (str=="")
  {
  document.getElementById("tump").innerHTML="";
  return;
  } 
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("tump").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","getsort.php?q="+str,true);
xmlhttp.send();
}
</script>
</head>
<body bgcolor="pink">
<form method="post">
<select name="sortitems" onchange="working(this.value)">
<option value="">Select</option>
<option value="Id">Id</option>
<option value="Name">Name</option>
<option value="Email">Email</option>
<option value="Password">Password</option>
</select>
<?php 
$connect=mysql_connect("localhost","root","");
$db=mysql_select_db("test1",$connect);
$sql=mysql_query("select * from mine");
echo "<center><br><br><br><br><table id='tump' border='1'>
<tr>
<th>Id</th>
<th>Name</th>
<th>Email</th>
<th>Password</th>
</tr>";
echo "<tr>";
while ($row=mysql_fetch_array($sql))
{?>
<td><?php echo "$row[Id]";?></td>
<td><?php echo "$row[Name]";?></td>
<td><?php echo "$row[Email]";?></td>
<td><?php echo "$row[Password]";?></td>
<?php echo "</tr>";
}
echo "</table></center>";?>
</form>
<br>
<div id="tump"></div>
</body>
</html>
------------------------------------------------------------------------
that is another php file

<html>
<body bgcolor="pink">
<head>
<style>
a:link {color:green;}
a:visited {color:purple;}
A:active {color: red;}
A:hover {color: red;}
table
{
    width:50%;
    height:50%;
}
table,th,td
{
    border:1px solid black;
}
th,td
{
    text-align:center;  
    background-color:yellow;
}
th
{
    background-color:green;
    color:white;    
}
</style>
</head>
<?php
$q=$_GET['q'];
$connect=mysql_connect("localhost","root","");
$db=mysql_select_db("test1",$connect);
$sql=mysql_query("select * from mine order by $q");
echo "<table id='tump' border='1'>
<tr>
<th>Id</th>
<th>Name</th>
<th>Email</th>
<th>Password</th>
</tr>";
echo "<tr>";
while ($row=mysql_fetch_array($sql))
{?>
<td><?php echo "$row[Id]";?></td>
<td><?php echo "$row[Name]";?></td>
<td><?php echo "$row[Email]";?></td>
<td><?php echo "$row[Password]";?></td>
<?php echo "</tr>";
}
echo "</table>";?>
</body>
</html>



that will sort the table using ajax

What if i have dynamic tables for example. Each records or rows is a different table. Will this solution work for the same? — Sanju Menon, Nov 30 '15 at 08:22

score 0 · Answer 5 · answered Jan 09 '16 at 17:31

This is the most simple solution that use:

// Use this as first line upon load of page

$sort = $_GET['s'];

// Then simply sort according to that variable

$sql="SELECT * FROM tracks ORDER BY $sort";

echo '<tr>';
echo '<td><a href="report_tracks.php?s=title">Title</a><td>';
echo '<td><a href="report_tracks.php?s=album">Album</a><td>';
echo '<td><a href="report_tracks.php?s=artist">Artist</a><td>';
echo '<td><a href="report_tracks.php?s=count">Count</a><td>';
echo '</tr>';

score 0 · Answer 6 · answered Oct 14 '16 at 07:09

It depends on nature of your data. The answer varies based on its size and data type. I saw a lot of SQL solutions based on ORDER BY. I would like to suggest javascript alternatives.

In all answers, I don't see anyone mentioning pagination problem for your future table. Let's make it easier for you. If your table doesn't have pagination, it's more likely that a javascript solution makes everything neat and clean for you on the client side. If you think this table will explode after you put data in it, you have to think about pagination as well. (you have to go to first page every time when you change the sorting column)

Another aspect is the data type. If you use SQL you have to be careful about the type of your data and what kind of sorting suites for it. For example, if in one of your VARCHAR columns you store integer numbers, the sorting will not take their integer value into account: instead of 1, 2, 11, 22 you will get 1, 11, 2, 22.

You can find jquery plugins or standalone javascript sortable tables on google. It worth mentioning that the <table> in HTML5 has sortable attribute, but apparently it's not implemented yet.

How to sort rows of HTML table that are called from MySQL

6 Answers6

Linked

Related