I am having an issue sorting my datatable by the column header.
I have a build-query mechanism on my site that can take in 1 or more values the user selects. The values get turned into PHP variables that then get passed into the query and prints out the datatable to the screen. The address bar URL is updated as well.
I want to be able to sort by the column headers of the table.
I will show you the PHP code bypassing the whole build-query function. I'll start right at the SORT portion and the query:
<?php
if ($_GET['sort'] == "") {
$sort_by = "BOL_NUMBER";
} else {
$sort_by = $_GET['sort'];
}
$select = "";
if ($_SESSION['where'] != "") {
$select = "SELECT * FROM `mainTable` WHERE (". $_SESSION['where'] . ") ORDER BY " . $sort_by . "";
}
// $_SESSION['where'] comes from the build query and can contain 1 or more values
$QueryResult = mysql_query($select) or die ();
$resnum = mysql_num_rows($QueryResult);
This next part is where the table gets populated:
if ($resnum == 0) {
echo "no results";
} else {
echo "<table>\n";
echo "<thead><tr>" .
"<th>BOL</th>" .
"<th>CONTAINER</th>" .
"<th>LOCATION</th>" .
"<th>STATUS</th>" .
"</tr></thead><tbody>\n";
while(($Row = mysql_fetch_assoc($QueryResult)) !== FALSE) {
echo "<tr>";
echo "<td>{$Row[BOL_NUMBER]}</td>";
echo "<td>{$Row[CONTAINER_NUMBER]}</td>";
echo "<td>{$Row[LOCATION_CITY]}</td>";
echo "<td>{$Row[STATUS_TYPE]}</td>";
echo "</tr>";
}
echo "</tbody></table>\n";
?>
There are many more columns. I just picked a couple.
I found this page for this next part:
Sorting html table with a href and php from sql database
So I tried to apply what I read in the link to the headers, like this:
"<th><a href='myPage.php?sort=BOL_NUMBER'>BOL</a></th>" .
"<th><a href='myPage.php?sort=CONTAINER_NUMBER'>CONTAINER</a></th>" .
"<th><a href='myPage.php?sort=LOCATION_CITY'>LOCATION</a></th>" .
"<th><a href='myPage.php?sort=STATUS_TYPE'>STATUS</a></th>" .
Now, I can click on the column headers, but when I do, it does not keep the user's selection and I can tell because the URL changes like this example below:
(this is just an example. it does not include the parameters in the table above) (just keep note of the &sort in this url)
http://home.someCompany.com/myAPP/mypage.php?direction=I&type=&submit=Go&city=&pod=&terminal=&ramp=&container=&bol=&voyage=&conStatus=&con_location=&sort=&status=
Will change to this (if I select the header for CONTAINER):
http://home.someCompany.com/myAPP/mypage.php?&sort=CONTAINER_NUMBER
When this happens, the datatable is no longer on the screen. It's like it removes everything from the query and just adds the sort. But there is now nothing to sort.
sortoptions in with all the$_GETquery parameters it received. Thehttp_build_queryfunction is useful for this.