2

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.

6
  • Why do this with a database sort? Try using the plethora of JS based table sorting plugins (for example tablesorter.com) Commented Aug 19, 2014 at 16:10
  • One solution is to keep all the other parameters in session variables. Another way is to have the PHP construct a full URL for the column headings, by merging the sort options in with all the $_GET query parameters it received. The http_build_query function is useful for this. Commented Aug 19, 2014 at 16:10
  • It should also be noted that you are passing raw data into mysql_query (which extremely dangerous) opening you up to injection attacks and more. You should in the very least escape the injected parameters ($_GET) or switch to a non-deprecated database library like PDO or mysqli_* Commented Aug 19, 2014 at 16:11
  • @drmarvelous, I am indeed using mysql_real_escape_string up in the build-query mechanism. I just didn't display it. Commented Aug 19, 2014 at 16:26
  • @drmarvelous, I am looking at tablesorter.com. Will I have to change much of my existing code to be able to use tablesorter.com? Commented Aug 19, 2014 at 16:28

1 Answer 1

0

The $_SERVER['QUERY_STRING'] superglobal variable give you access to the GET parameters. By using it you can keep the current parameters of the request.

So by changing your link by this :

$urlQuery = str_replace(array('&sort=BOL_NUMBER', '&sort=CONTAINER_NUMBER', '&sort=LOCATION_CITY', '&sort=STATUS_TYPE'), '', $_SERVER['QUERY_STRING']); // To clean previous sorting, maybe replace by a regex

"<th><a href='myPage.php?' . $urlQuery  . '&sort=BOL_NUMBER'>BOL</a></th>" .
"<th><a href='myPage.php?' . $urlQuery  . '&sort=CONTAINER_NUMBER'>CONTAINER</a></th>" .
"<th><a href='myPage.php?' . $urlQuery  . '&sort=LOCATION_CITY'>LOCATION</a></th>" .
"<th><a href='myPage.php?' . $urlQuery  . '&sort=STATUS_TYPE'>STATUS</a></th>" .

You should reach your goal.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.