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?
This question is related to
php
mysql
html-table
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! :)
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.
//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
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 = <b>111111111</b></td><td>Still to spend = <b>5555555</b></td></tr></tfoot></br></br>
</table>
</body>
</html>
source: php sortable table
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>';
Source: Stackoverflow.com