sort rows of html table - mysql

Question

I have a database called forms1 and a table named demo
The fields in the table are ID, Autore, Titolo, cit

I wish I had a first line that gives me the ability to sort alphanumeric values ​​that are retrieved by the query.
For sort i mean this:
How to sort rows of HTML table that are called from MySQL

My problem is adapt code for my situation. This is cerca2.php:

<style>
br {margin-bottom:-10px;}
</style>

<form action="cerca2.php" method="post">
<b>Nome</b>&nbsp;<input type="text" name="Nome">&nbsp;&nbsp;
<b>Numero&nbsp;</b><input type="text" name="Numero">&nbsp;&nbsp;
<b>city&nbsp;</b><input type="text" name="city">&nbsp;&nbsp;
<input type="Submit">
</form>

<style>
tr:nth-of-type(odd) { background-color: AZURE; }
tr:nth-of-type(even) { background-color: CYAN; }
</style>
<style>
tr:hover{background-color:DEEPSKYBLUE;}
</style>

<?php

echo "<table border='1' style='border-collapse: collapse;border-color: silver;'>";  
echo "<tr style='font-weight: bold;'>";  
echo "<td width='auto' bgcolor=”#7FFFD4″>&nbsp;<i>ID<i/></td>";  
echo "<td width='auto' >&nbsp;<i>Nome<i/></td>";
echo "<td width='auto' ></td>";
echo "<td ></td>";
echo "</tr>";

define('DB_NAME', 'forms1');
define('DB_USER', 'root');
define('DB_PASSWORD', '');
define('DB_HOST', 'localhost');

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if (!$link) {
    die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db(DB_NAME, $link);

if (!$db_selected) {
    die('Can\'t use ' . DB_NAME . ': ' . mysql_error());
}

$Nome = str_replace(' ', '%', $_POST['Nome']);
$Numero = str_replace(' ', '%', $_POST['Numero']); 
$city = str_replace(' ', '%', $_POST['city']); 

$arNome = str_split($Nome);
$arNumero = str_split($Numero);
$arcity = str_split($city);

$Nome='';
foreach ($arNome as $value) 
{
   if ($value=='%') continue;
   $Nome.=$value.'%';

}

$Numero='';
foreach ($arNumero as $value) 
{
   if ($value=='%') continue;
   $Numero.=$value.'%';

}

$city='';
foreach ($arcity as $value) 
{
   if ($value=='%') continue;
   $city.=$value.'%';

}

$sql = mysql_query("SELECT * FROM demo WHERE Autore LIKE '%$Nome%' AND Titolo LIKE '%$Numero%' AND cit LIKE '%$city%' ORDER BY Autore") or die(mysql_error());


$i = 0; while($row=mysql_fetch_array($sql)){
        $i++;
        echo "<tr>";
        echo "<td width='auto' bgcolor=”#FF0000 &#8243;>" . "&nbsp;". "<b>"  . $i . "&nbsp;". "<b/>". "</td>";
        echo "<td width='auto'>" . "&nbsp;" . $row[1] . "&nbsp;" . "</td>";
        echo "<td width='auto'>". "</td>";
        echo "<td width='auto'>" . "&nbsp;". "<i>" . $row[2] . "<i/>". "&nbsp;" . "</td>";     
        echo "<td width='auto'>" . "&nbsp;". "<i>" . $row[3] . "<i/>". "&nbsp;" . "</td>";
        echo "</tr>";

}

mysql_close();
?>

Answer 1

If you're looking for a Javascript solution tablesorter would do the job. Handy and easy.

EDIT
At the very top add :

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
<script type="text/javascript" src="http://tablesorter.com/__jquery.tablesorter.min.js"></script>
<script type="text/javascript">
    $(document).ready(function(){ $("table").tablesorter(); });
</script>

http://pastebin.com/dLJqJQuX

Answer 2

You should include LIMIT to your sql query, so you have to sort not only data you have already printed, and the hidden.

In your case try to reload page and use $_GET['name_sort'] to change your sql

if ($_GET['name_sort'] == 'desc') {
    $sql = mysql_query("SELECT * FROM demo WHERE Autore LIKE '%$Nome%' AND Titolo LIKE '%$Numero%' AND cit LIKE '%$city%' ORDER BY Autore DESC") or die(mysql_error());

} else {

    $sql = mysql_query("SELECT * FROM demo WHERE Autore LIKE '%$Nome%' AND Titolo LIKE '%$Numero%' AND cit LIKE '%$city%' ORDER BY Autore ASC") or die(mysql_error());
}

Answer 3

If you want a sortable table, I use the javascript library called "sortable" - it's easy to configure and use.

EDIT showing example HTML: This HTML expects the "sortable.js" file to be in the same directory as the HTML file:

<html>
  <head>
    <script type="text/javascript" src="sortable.js"></script>
  </head>
  <body>
    <h1>Test Table</h1>
    <table id="myTable" class="sortable">
      <tr>
        <th>First Column</th>
        <th>Second Column</th>
        <th>Third Column</th>
      </tr>

      <tr>
        <td>1</td>
        <td>Hello</td>
        <td>10/01/2012</td>
      </tr>

      <tr>
        <td>2</td>
        <td>Goodbye!</td>
        <td>10/04/2011</td>
      </tr>

      <tr>
        <td>3</td>
        <td>Welcome</td>
        <td>22/09/1985</td>
      </tr>
    </table>
  </body>
</html>