Hi all,

Here's something fairly common on sites and I'm trying to work out what part PHP should play...

My page has a list of data, 5 columns, and about 20 rows displayed to the user, and the data comes from a mySQL View. I want the user to be able to click at the top of the column and have all the table data re-order by that column, alternating between asc or desc (odd click asc, even click desc).

The aim is to have this work with AJAX so the page doesn't completely re-load each time. But where do I start? I've already written a php script to deal with the output, but then realised I don't know how to intercept the user's click!?

It seems like there's three parts involved - 1. User clicks on column. 2. request goes to server (or to AJAX). 3. Something (PHP?) generates the output page. As a java/C++ programmer I like PHP, but can do javascript and servlets but would like simplest method. What should I use for these three parts?

Expert guidance, educated guesses or even working examples all welcome!!

Thanks!
matt

Ok, a step by step explanation takes a little to much time now, but I can tell you how to intercept clicks.

First of all make sure your table has an id, or another way to access it easily from javascript. We'll assume you have a table looking like:

<table id="data-table">
<tr>
<th>Col1</th>
<th>Col2</th>
</tr>
<tbody id="data">
... data ...
</tbody>
</table>

To handle user clicks, the easiest way is to add an "onclick" atribute to the table header cells:

<table id="data-table">
<tr>
<th onclick="orderBy('data-table', 'col1')">Col1</th>
<th onclick="orderBy('data-table', 'col2')">Col2</th>
</tr>
<tbody id="data-table-data">
... data ...
</tbody>
</table>

Then we need to define this function, and a function to create an object to send requests to the server that works in most browsers.

<script type="text/javascript">
// the function to create the request handler
function createHTTPHandler(){
  httphandler = false;
  /*@cc_on @*/
  /*@if (@_jscript_version >= 5)
  // JScript gives us Conditional compilation, we can cope with old IE versions.
  // and security blocked creation of the objects.
    var httphandler;

    var msxmlhttp = new Array(
      'Msxml2.XMLHTTP.5.0',
      'Msxml2.XMLHTTP.4.0',
      'Msxml2.XMLHTTP.3.0',
      'Msxml2.XMLHTTP',
      'Microsoft.XMLHTTP');
    for (var i = 0; i < msxmlhttp.length; i++) {
      try {
        httphandler = new ActiveXObject(msxmlhttp[i]);
      } catch (e) {
        httphandler = false;
      }
    }
  @end @*/
  if(!httphandler && typeof XMLHttpRequest != "undefined")
    httphandler = new XMLHttpRequest();
  return httphandler;
}

//some needed gobal variables
var currentOrder = null;
var clicks = 0;

//our sort function
function orderBy(tableId, order){
  table = document.getElementById(tableId);

  var orderWay = "ASC";
  if(currentOrder == order){
    if(clicks == 1){
      orderWay = "DESC";
      clicks = 0;
    }
  }
  else{
    currentOrder == order;
    clicks = 1;
  }

  http_request=createHTTPHandler();

  http_request.onreadystatechange=function(){
    if(http_request.readyState==4){
      if(http_request.status == 200){
        //assuming the server sends the data back as JSON code
        serverData = eval('('+http_request2.responseText+')');
        /*
        Let's say the returned data is something like

        array(
            array(
                'row1-col1',
                'row1-col2'
            ),
            array(
                'row2-col1',
                'row2-col2'
            ),
            ...
        );
        */

        var dataTbody = document.getElementById(tableId+"-data");
        dataTbody.innerHTML = "";

        for(row = 0; row < serverData.length; row ++){
          dataTbody.innerHTML += "<tr>";
          for(col = 0; col < serverData[row].length; col ++){
            dataTbody.innerHTML += "<td>"+serverData[row][col]+"</td>";
          }
          dataTbody.innerHTML += "</tr>";
        }
      }
    }
  }

  http_request.open('GET', "theurltoyourscript.php?with=arguments", true);
  http_request.setRequestHeader("X_USERAGENT", "AJAX"); //not required, but an easy way to recognise ajax requests on the server
  http_request.send(null);
}
</script>

<table id="data-table">
<tr>
<th onclick="orderBy('data-table', 'col1')">Col1</th>
<th onclick="orderBy('data-table', 'col2')">Col2</th>
</tr>
... data ...
</table>

I haven't tested this myself, but it should work. I hope this helps.

Thanks Orodreth,

Got a working AJAX / PHP page which orders and sorts beautifully, using a combination of your code and this tutorial on IBM site.

I won't post the whole code but here's the description of the main table

<table id="data_table" width="100%" cellpadding=3 cellspacing=3>
        <tr>
                <th onclick="orderBy('populate_table.php', 'col_name=name', 'data_body')">name</th>
                <th onclick="orderBy('populate_table.php', 'col_name=score', 'data_body')">score</th>
                <th onclick="orderBy('populate_table.php', 'col_name=rank', 'data_body')">rank</th>
                <th onclick="orderBy('populate_table.php', 'col_name=company', 'data_body')">company</th>
         </tr>
       
        <tbody id="data_body">
</table>

I'll describe the process because it might be useful for newbies like me:

This HTML page contains the above table def, and a javascript function orderBy(URL, arg, destination) which determines whether to sort
ASC or DESC, then does a GET request which is picked up by populate_table.php. That runs an SQL statement on a MySQL view, and populates the table and the resulting html comes back to this HTML file and becomes the table body, data_body.

The only problem remaining problem is I need to display data, (ordered by name ascending) when the page first loads. How do I do this without the user having to click?

I also have to consider browsers with javascript disabled, who will not be able to sort. These should display data_table ordered by name ascending so can I kill two birds with one stone here?

Thanks for all the advice - pretty easy when someone points you in the right direction!
matt

Sure, just populate your table when you generate the page with php.

In my example,

<tbody id="data-table-data">
... data ...
</tbody>

with "...data..." I didn't only mean "here comes the ordere data", but also "here comes the initial data".

Just make sure the orderBy function erases the initial data before displaying the newly loaded data.