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();
?>

What do you mean by "sort alphanumeric values that are retrieved by the query"? What values? Are you trying to sort results returned by the query? Are you trying to sort within the query itself (you're already sorting by Autore)? — user428517
– user428517, Commented Oct 19, 2012 at 13:49
Are you trying to sort within the query itself yes, like this stackoverflow.com/questions/3489783/… but how must adapt code for my situation? — user143822
– user143822, Commented Oct 19, 2012 at 13:53

Alexey Sidorov · Accepted Answer · 2012-10-19 13:55:18Z

1

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());
}

answered Oct 19, 2012 at 13:55

Alexey Sidorov

8661 gold badge10 silver badges17 bronze badges

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

6 Comments

user143822 Over a year ago

I try to change but nothing change. Look here the entire code pastebin.com/cMLnqELL

Alexey Sidorov Over a year ago

you have to add <a href="/?name_sort=desc"> or <a href="/"> depends on the name_sort param.

user143822 Over a year ago

where i have to add your code? Can u post my modified code on Pastebin, please?

Alexey Sidorov Over a year ago

$sortName = ''; $sortNameSql = 'DESC'; if ($_GET['sort_name'] == 'desc') { $sortName = 'sort_name=asc'; $sortNameSql = 'ASC'; } $sql = mysql_query("SELECT * FROM demo WHERE Autore LIKE '%$Nome%' AND Titolo LIKE '%$Numero%' AND cit LIKE '%$city%' ORDER BY Autore " . $sortNameSql . "") or die(mysql_error()); echo '<td width="auto"> <a href="/' . $sortName . '"><i>Nome</i></a></td>';

Alexey Sidorov Over a year ago

oh, god :) It`s smth like this))

|

Touki · Accepted Answer · 2012-10-19 14:16:05Z

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

edited Oct 19, 2012 at 14:16

answered Oct 19, 2012 at 13:48

Touki

7,5353 gold badges44 silver badges66 bronze badges

6 Comments

user143822 Over a year ago

I try but i don't undestand tablesorter, i don't know how must it include inside my code. Can u help me, please?

Touki Over a year ago

@user143822 Check edition. This would do the job quickly. You may have a look at jQuery to understand better what i've done.

user428517 Over a year ago

This looks like a good JavaScript solution. @user143822 if you want to do this in PHP it's going to involve a lot more work. You'd want to make your table headers clickable links that set a $_GET parameter which you'll use to sort by as per Alexey Sidorov's answer.

Touki Over a year ago

@user143822 I guess that's because you forgot </table> Try this full code : pastebin.com/dLJqJQuX

Touki Over a year ago

Edition: You need a <th> in your table headers instead of <td>'s

|

GarethL · Accepted Answer · 2012-10-19 15:04:44Z

0

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>

edited Oct 19, 2012 at 15:04

answered Oct 19, 2012 at 13:49

GarethL

1,4831 gold badge15 silver badges16 bronze badges

9 Comments

GarethL Over a year ago

read the link in my answer. It explains step-by-step how to implement the solution. If language is a problem, I speak Italian.

user143822 Over a year ago

si parlo anche io in italiano, mi daresti un a mano? Ho provato la soluzione sopra la tua, quella di Alexey Sidorov, ma mi cambia nulla

GarethL Over a year ago

Allora, devi scaricare prima lo script Javascript, come scritto nel link, poi lo devi includere nella sezione "HEAD" del tuo HTML, in questo modo: <script type="text/javascript" src="percorso/allo/script/sorttable.js"></script>. Aggiungi un class CSS "sortable" alla tabella, e a quel punto, fa tutto lo script. ENGLISH: You need to download the javascript, as in the link I linked to, then you need to add it to you HTML HEAD section, so: <script type="javascript/html" src="path/to/script/sorttable.js"></script>. You need to add a "sortable" class to table, and the script does the rest.

GarethL Over a year ago

Se vuoi un esempio di come funziona questo script, vai al sito di Fabriano Rugby (google), e guarda la sezione "statistiche". La tabella che vedi usa esattamente questo script "sorttable".

user143822 Over a year ago

ho provato ma niente. Ho scritto così: pastebin.com/E7Bf4P6K guarda anche l'immagine imageshack.us/a/img839/5231/error2we.png

|

Collectives™ on Stack Overflow

sort rows of html table - mysql

3 Answers 3

6 Comments

6 Comments

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

6 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related