SQL ORDER BY / Sort Php

I am struggling with my php and would love some assistance in the right direction. Here is my code so far:

    <?php
    $connection = mysql_connect("localhost", "root") or die(mysql_error());
    mysql_select_db("rsi", $connection) or die(mysql_error());

    $query = "SELECT * FROM events"; 
    $result = mysql_query ($query) or die ("error in query"); 

if (mysql_num_rows($result)>0) {
    echo "<table border=1></tr>" .
        "<th>ID</th>" .
        "<th>Name</th>" .
        "<th>Date</th>" .
        "<th>Location</th>" ;

while ($row = @ mysql_fetch_array($result)){
             print "<tr>";
             print "<td>".$row['id']."</td>"; 
             print "<td>".$row['name']."</td>"; 
             print "<td>".$row['date']."</td>"; 
             print "<td>".$row['location']."</td>"; 
             print "</tr>"; 
}
print "</table>";
}


?>

I am now hoping to order the results from the database by clicking on each/any of the headers (for example date). Thanks for your time.

To order the result you use ORDER BY.

Here's an example to order by id:

$query = "SELECT * FROM events ORDER BY id";

To get it to order when you click on a header you need to make it execute the new query in some way. How to do that I leave out for you to find. Typically you reload the whole page with a parameter on what to sort on or use ajax, which would let you update contents on the page without reloading the page.

You have to pass the order by column name and order ascending or decending by either GET or POST. Here is an example of GET.

$query = "SELECT * FROM events"; 
if(isset($_GET['sort'])
{
    $query.= ' order by '.mysql_real_escape_string($_GET['sort']).' '.mysql_real_escape_string($_GET['order']);
}
$result = mysql_query ($query) or die ("error in query"); 


while ($row = @ mysql_fetch_array($result)){
       print "<tr>";
       print "<td>".$row['id']."</td>"; 
       print "<td>".$row['name']."</td>"; 
       print "<td><a href='http://www.domain.com/currentpageurl?sort=date&order=asc'>".$row['date']."</td>"; 
       print "<td>".$row['location']."</td>"; 
       print "</tr>"; 
    }

If you use Shakti's answer, I would recommend not using $_GET directly in the query. I would run your $_GET through some sort of sanity check and only allow specific values, otherwise you are leaving your site open to SQL injection attacks.

I think you are looking to sort the table in the browser. You probably don't want to keep querying your database for the same information just in a different sort order. Something like this perhaps: http://tablesorter.com/docs/#Demo

To use tablesorter:

  1. Change one line of your code to:
echo "<table border=1 id="myTable" class="tablesorter"><tr>"
."<th>ID</th>" ."<th>Name</th>" ."<th>Date</th>"
."<th>Location</th></tr>" ;
  1. Add this line at the end of the body:
<script>
    $(document).ready(function() 
        { 
            $("#myTable").tablesorter(); 
        } 
    ); 
</script>
  1. If you don't already have jquery, include it in the <Head> section:
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>

  1. Download jquery.tablesorter.js from http://tablesorter.com/__jquery.tablesorter.zip

  2. Include the file jquery.tablesorter.js like this:

<script type="text/javascript" src="/path/to/jquery.tablesorter.js"></script>