Sort using php and jquery

Question

I'm trying to re-order a HTML table when the user clicks on the table header. Here is all I can do, but still doesn't work.

in the HTML:

// onClick on table header 
var par='ASC';
sort(par);

from: ajax.js

function sort(orderByType)
{
  $.ajax({
    url: "sort.php",
    type: "get",
    data: "orderby="+orderByType,
    success: function(data){
      alert(data);
      $("t1").text(data);
    }
  });
}

sort.php

$con = mysql_connect("localhost","root","root");
if (!$con) {
  die('Could not connect: ' . mysql_error());
}

$orderBy = $_GET['orderby'];

mysql_select_db("icrisis", $con);

$result = mysql_query("SELECT * FROM messages,user  
                                        where user_id = from_user
                                        ORDER BY user_name".$orderBy);

while($row = mysql_fetch_array($result))
{      
  echo "<tbody><tr><td>"."&#8226;"."</td><td>".
       $row["Time_Date"]."</td><td>".
       $row["player_name"]."</td><td></td><td></td><tr><td></td><td colspan='4'>".
       $row["msg_desc"]."</td></tr></tbody>";
}

mysql_close($con);

It doesn't see the $orderBy. And then I want to add the new order to my page - how could that be done?

Could the variable sent to function sort be dynamic, i.e. to be ASC or DESC on click?

Answer 1

You should try: tablesorter its for sorting tables. And you don't even need to use php with this solution just jquery. Hope its usefull.

To reply to your comment on Daan's anwser you could update tablesorter with ajax as described here.

Answer 2

I'm not sure if that is the cause of your problem, but I believe you miss a space.

The last line of your query is now:

ORDER BY user_name".$orderBy);

But should be:

ORDER BY user_name ".$orderBy);

Answer 3

As Daan said you are missing the space and that is probably the cause, however I will add:

You should probably check the result of the mysql_query() call because currently you don't know whether that is failing or not, which could be the cause of your problem. For example:

$result = mysql_query('SELECT ...');
if (!$result) {
    die(mysql_error());
}

while($row = mysql_fetch_array($result)) {
//etc.

Also you shouldn't really be building the SQL statement based on strings that have come from the browser, as these cannot be trusted, and someone could currently add malicious SQL into what you are executing. See SQL Injection.

You could validate the string first which would be far safer, and is easy because you only have two possible values, e.g.

if (isset($_GET['orderby']) && $_GET['orderby'] == 'DESC') {
    $orderBy = 'DESC';
} else {
    $orderBy = 'ASC';
}

Answer 4

Ok i had enough of this sort and i don't know what to do any more I give you the code and you gurus tell me what to do

javascript file

function sort(orderByType)
 {
  $.ajax({
    url: "sort.php",
    type:"get",
    data: "orderby= "+orderByType,
    success:function(data){  
    $("#t1").html(data);

          }
  });}

sort.php

if (isset($_GET['orderby']) && $_GET['orderby'] == 'ASC') 
{
    $orderBy = 'ASC';
} 
else 
{
    $orderBy = 'DESC';
}

$result = mysql_query("SELECT * FROM messages,Player where player_id = from_user
            ORDER BY player_name".$orderby);

echo "<thead><tr>
<th  style='color:royalblue;'>&#8226;</th>
<th  align='center'>Temps</th>
<th align='left' onClick='orderBy(); sort($orderBy);'>De:</th>
<th align='left'>A:</th>
<th align='left'>Gr</th>
</tr>
</thead> ";

while($row = mysql_fetch_array($result))

{

echo "<tbody>
   <tr class='highlight'>
   <td width='30' align='center' style='color:royalblue'>"."&#8226;"."</td>
      <td width='70' align='left'>".$row["Time_Date"]."</td>
   <td width='600' align='left'>".$row["player_name"]."</td>
   <td width='600' align='left'></td>
   <td width='100' align='left'></td>
   <tr class='highlight'>
   <td></td>
   <td colspan='4'>".$row["msg_desc"]."</td></tr>
   </tbody>";
     }

here as you can see i add the table header and body again an error appears when i try to click table header again any ideas