Sort sql query & ID reset and start in numerical order every time I make a query or sort from tag

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, such as
enter image description here
How can i modify my code? 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<img src="images/smilies/biggrin.gif" border="0" alt="">EEPSKYBLUE;}
</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&#8243;>&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());

while($row=mysql_fetch_array($sql)){

    echo "<tr>";
    echo "<td width='auto' bgcolor=”#FF0000 &#8243;>" . "&nbsp;". "<b>"  . $row[0] . "&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();
?>

I would click on Nome and other th (except ID) to reorder the values alphanumeric AZ or ZA viceversa when i click. At the moment this is my result page (look code above) when i call a query: enter image description here

I would, too - but I do not know how to do - that the ID value always start from 1 and continue in numerical order according to the number of x values retrieved every time I make a query or sort from th tag.
In my case, instead, is always dependent on the value it represents. You see it from the picture that the numbers appear in random order. Here, I do not want this.

for sorting table columns you may want to have a look at http://tablesorter.com/docs/ — David Michael Harrison, Oct 19 '12 at 12:25
@DavidMichaelHarrison i don't want table sorter i want just click on Nome or other **th** to order A-Z Z-A or numerical/symbols order — user143822, Oct 19 '12 at 12:29
to what end? you said you wanted to click on the th elements in order to "reorder the values alphanumeric" — David Michael Harrison, Oct 19 '12 at 12:32
sorry for the misunderstanding. Not so..For sort I mean this: http://stackoverflow.com/questions/3489783/how-to-sort-rows-of-html-table-that-are-called-from-mysql I do not know how to adapt the code — user143822, Oct 19 '12 at 12:55

score 1 · Accepted Answer · answered Oct 19 '12 at 12:22

1

Try this code-part:

    $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>";

}

answered Oct 19 '12 at 12:22

RDK

4,540
2
20
29

yeah! ID order solve! thank you..but u can help me to order the fields (th) ? – user143822 Oct 19 '12 at 12:33
if u look picture above you can see that ** Nome** has not the possibility to sort A-Z or Z-A/numerical-symbols and viceversa. How code can i add to solve this problem? i want able this possibility for all except ID. – user143822 Oct 19 '12 at 12:40
mysql_query("SELECT * FROM demo WHERE Autore LIKE '%$Nome%' AND Titolo LIKE '%$Numero%' AND cit LIKE '%$city%' ORDER BY Autore, Nome ASC") – RDK Oct 19 '12 at 12:42
sorry for the misunderstanding. Not so..For sort I mean this: http://stackoverflow.com/questions/3489783/how-to-sort-rows-of-html-table-that-are-called-from-mysql I do not know how to adapt the code – user143822 Oct 19 '12 at 12:47

Sort sql query & ID reset and start in numerical order every time I make a query or sort from tag

1 Answers1

Linked