Sort query results by HTML table column

Question

I'm looking for a more efficient, elegant and secure way to sort data displayed inside an HTML table. This is what I've come up with so far and it looks somewhat bloated / noobish.

I also thought of something like storing the sorting in a $_SESSION so I don't have to carry the currently selected order over to the next page "manually" within the URL. Maybe a "safe&simple" piece of JavaScript+PHP (but no frameworks please) would make sense? My thoughts go in circles for hours now.

<?php
$ro_arr = array(
    "kid" => "kid",
    "kwd" => "Keyword",
    "cpc" => "ApproximateCPC",
    "cmp" => "Competition",
    "mov" => "MonthlyValue",
    "gms" => "GlobalMonthlySearches",
    "lms" => "LocalMonthlySearches",
    "dfc" => "KeywordDifficulty",
    "com" => "com",
    "net" => "net",
    "org" => "org",
    );

if (!empty($_GET['ro']) && strlen($_GET['ro']) == 7 && array_key_exists($col = substr($_GET['ro'], 0, 3), $ro_arr)) {
    $dir = (substr($_GET['ro'], 4, 3) == 'asc' ? "ASC" : "DESC");
    $res_order = $res_arr[''. $col .''] ." ". $dir;

} else {
    $_GET['ro'] = "kid_asc";
    $res_order = "kid ASC";
}
?>

<!-- just for testing -->
<pre><?php echo $res_order; ?></pre>
<!-- done testing -->

<table>
  <tr>
    <td><a href="sort.v1.php?ro=<?php echo ($_GET['ro']=='kwd_asc')?"kwd_dsc":"kwd_asc"; ?>">Keyword</a></td>
    <td><a href="sort.v1.php?ro=<?php echo ($_GET['ro']=='cmp_dsc')?"cmp_asc":"cmp_dsc"; ?>">Comp.</a></td>
    <td><a href="sort.v1.php?ro=<?php echo ($_GET['ro']=='cpc_dsc')?"cpc_asc":"cpc_dsc"; ?>">Ad CPC</a></td>
    <td><a href="sort.v1.php?ro=<?php echo ($_GET['ro']=='mov_dsc')?"mov_asc":"mov_dsc"; ?>">Value</a></td>
    <td><a href="sort.v1.php?ro=<?php echo ($_GET['ro']=='gms_dsc')?"gms_asc":"gms_dsc"; ?>">Global Searches</a></td>
    <td><a href="sort.v1.php?ro=<?php echo ($_GET['ro']=='lms_dsc')?"lms_asc":"lms_dsc"; ?>">Local Searches</a></td>
    <td><a href="sort.v1.php?ro=<?php echo ($_GET['ro']=='dfc_asc')?"dfc_dsc":"dfc_asc"; ?>">Difficulty</a></td>
    <td width="22"><a href="sort.v1.php?ro=<?php echo ($_GET['ro']=='com_dsc')?"com_asc":"com_dsc"; ?>">com</a></td>
    <td width="22"><a href="sort.v1.php?ro=<?php echo ($_GET['ro']=='net_dsc')?"net_asc":"net_dsc"; ?>">net</a></td>
    <td width="22"><a href="sort.v1.php?ro=<?php echo ($_GET['ro']=='org_dsc')?"org_asc":"org_dsc"; ?>">org</a></td>
  </tr>
</table>

Use the "datatables.net" for sorting in Client Side which is very fast rather than using in Server Side — Venkat.R
– Venkat.R, Commented Jun 17, 2013 at 3:24

Nir Alfasi · Accepted Answer · 2013-06-17 03:00:30Z

1

You can reduce the amount of code to almost a half if you'll use two parameters instead of one:

The parameter by which you're sorting: kid, com, net, org...
Order: asc, dsc

answered Jun 17, 2013 at 3:00

Nir Alfasi

53.6k11 gold badges94 silver badges138 bronze badges

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

5 Comments

nimmneun Over a year ago

TY. I thought about that too, but I'd have to use an exception for kwd_* and dfc_* I guess. These two are supposed be ordered ASC by default, whereas it's the opposite with the others.

Nir Alfasi Over a year ago

@nimmneun I don't understand how the default behavior should be affected when you split the variables

nimmneun Over a year ago

what I meant is that - if the last selected sorting direction is ASC, all table headers will show DESC unless I add an exception for kwd_* and dfc_*. I previously did split up COLUMN and ORDER with explode("_", $_GET['ro'])to keep the URL more clean, but moved on to what I currently have.

Nir Alfasi Over a year ago

@nimmneun it sounds like something that can be easily fixed by replacing the order for the two exceptions, it doesn't require duplicating the whole thing.

nimmneun Over a year ago

ok, after working on something different for a bit ... your suggestion was applied within a minute =) I was probably suffering from severe temporary blindness earlier. The updated code is ~400 Bytes lighter now. TY =)

Jon P · Accepted Answer · 2013-06-17 02:58:15Z

0

If your table isn't too large and you're not adverse to using jQuery, use tablsorter. Once you get into the thousands of rows, client side sorting can get a little unwiledy.

answered Jun 17, 2013 at 2:58

Jon P

19.9k8 gold badges52 silver badges76 bronze badges

3 Comments

nimmneun Over a year ago

TY. ATM I only use 10k rows for testing purposes and am still in the 0.002-0.005s execution time range. But "kid" is set to unsigned mediumint already, which will hopefully be enough. Do you have any experiences with jQuery+tablesorter how if/how it effects scaling?

Jon P Over a year ago

@nimmneun Client side sorting realy does depend somewhat on the amound of data returned. I've notice slowdown on one of my projects at aroun 2k rows. It may change for you. Best way to find out if it is suitable is to download it and try it. Table sorter can be very quick to apply so you have nothing to lose by giving it a try. Also once you get to table of that size, ask yourself, how useful or user friendly a dataset of that size is.

nimmneun Over a year ago

@ JonP + Venkat, you both convinced me to have a more indepht look at client side result processing. Depending on the set filters, the number of results actually returned will usually be in the low 100s or below. Since I want to preserve the ability not only to filter, but also load additional results dynamically onKeyUp() I will probably have to dig deeper into client side processing first I guess.

Collectives™ on Stack Overflow

Sort query results by HTML table column

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related