[RESOLVED] ASC / DESC Table

[shqip]

Could anyone help me with sorting my table so if I click on the title of a column the table will be sorted by that column ascending and if i click it again it with sort by descending e.g. FirstName ascending. This is my source:

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM Persons ORDER BY age");

while($row = mysql_fetch_array($result))
  {
  echo $row['FirstName'];
  echo $row['LastName'];
  echo $row['Age'];
  echo <br />";
  }

mysql_close($con);
?>

Thanks, Shqip

[dagon]

make the title a link foo.php?sort=asc
check the current state, if its asc then add sort by asc and make the link sort dec
and visa versa.

[shqip]

I got everything working but when I click the header name (e.g. FirstName) it only sorts asc not desc any help?

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

if ($_GET['sort'] == "asc") 
     $sort = "asc"; 
else 
     $sort = "desc";

switch ($_GET['order']) 
{ 
     case "Age": 
          $order = $_GET['order']; 
          break; 
     case "LastName": 
          $order = $_GET['order']; 
          break; 
     case "FirstName": 
     default: 
          $order = "FirstName"; 
          break; 
} 

$sql  = "SELECT * FROM persons ORDER BY $order {$sort}";
$result = mysql_query($sql) or die("MySQL error: $sql<br />" . mysql_error());

echo '<table border="1">
<tr>
<th><a href="index.php?order=FirstName&sort=asc">Firstname</th>
<th><a href="index.php?order=LastName&sort=asc">Lastname</th>
<th><a href="index.php?order=Age&sort=asc">Age</th>
</tr>';

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['FirstName'] . "</td>";
  echo "<td>" . $row['LastName'] . "</td>";
  echo "<td>" . $row['Age'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>

[shqip]

I found two errors which I just can't fix any help?

Notice: Undefined index: sort in C:\wamp\www\TestWebsite\Index.php on line 16
Notice: Undefined index: order in C:\wamp\www\TestWebsite\Index.php on line 21

[Servyces]

shqip;10931181 wrote:

I got everything working but when I click the header name (e.g. FirstName) it only sorts asc not desc any help?

That's because nowhere in your code you define the "?sort=desc" part of the link. Once asc has been clicked, desc link should be generated. Should be something like:

if ( $sort == "asc" ) {
   echo '<a href="yourlink.php?sort=desc">';
}

[shqip]

Servyces;10931188 wrote:

That's because nowhere in your code you define the "?sort=desc" part of the link. Once asc has been clicked, desc link should be generated. Should be something like:

if ( $sort == "asc" ) {
   echo '<a href="yourlink.php?sort=desc">';
}

I have been away, so sorry for the late reply.

Isn't what your saying meant to happen here?

if ($_GET['sort'] == "asc") 
     $sort = "asc"; 
else 
     $sort = "desc";

Also again any help with these two errors?

Notice: Undefined index: sort in C:\wamp\www\TestWebsite\Index.php on line 16
Notice: Undefined index: order in C:\wamp\www\TestWebsite\Index.php on line 21

[shqip]

I did it, got it work here's the final working code enjoy. 😃

<?php 
$con = mysql_connect("localhost","peter","abc123"); 
if (!$con) 
  { 
  die('Could not connect: ' . mysql_error()); 
  } 

mysql_select_db("my_db", $con); 

$sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'FirstName'; 

$sort_order = 'asc'; 
if(isset($_GET['sort_by'])) 
{ 
    if($_GET['sort_by'] == 'asc') 
    { 
        $sort_order = 'desc'; 
    } else 
    { 
        $sort_order = 'asc'; 
    } 
} 

switch($sort) 
{ 
    case 'FirstName': 
        $order_by = 'FirstName'; 
        break; 
    case 'LastName': 
        $order_by = 'LastName'; 
        break; 
    case 'Age': 
        $order_by = 'Age'; 
        break; 
 } 

$sql  = "SELECT * FROM persons ORDER BY $sort $sort_order"; 
$result = mysql_query($sql) or die("MySQL error: $sql<br />" . mysql_error()); 

echo '<table border="1"> 
<tr> 
<th><a href="?sort=FirstName&sort_by='.$sort_order.'">Firstname</th> 
<th><a href="?sort=LastName&sort_by='.$sort_order.'">Lastname</th> 
<th><a href="?sort=Age&sort_by='.$sort_order.'">Age</th> 
</tr>'; 

while($row = mysql_fetch_array($result)) 
  { 
  echo "<tr>"; 
  echo "<td>" . $row['FirstName'] . "</td>"; 
  echo "<td>" . $row['LastName'] . "</td>"; 
  echo "<td>" . $row['Age'] . "</td>"; 
  echo "</tr>"; 
  } 
echo "</table>"; 

mysql_close($con); 
?>

[chrisdee]

Great script! Just what I was looking for.
Thank you, thank you, thank you🙂

I have taken the liberty of rewriting it a bit so it will loop through the table titles and content. This way one does not have to manually write all the table title names. Its easier when you have big tables.

<?php echo "<a href=./>Home</a><br>";
// connect to db
$connect = mysqli_connect('localhost','user','pass','database');

// sets id as default sort (you can set something else than id)
$sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'id';

// define sort order
$sort_order = 'asc';
if(isset($_GET['sort_by'])) {
 if($_GET['sort_by'] == 'asc') {
  $sort_order = 'desc';
 } else {
  $sort_order = 'asc';
 }
}

// query table
$query  = mysqli_query($connect,("SELECT * FROM table ORDER BY " . $sort . " " . $sort_order));
$keys   = mysqli_fetch_array($query, MYSQLI_ASSOC);

// html table
echo "<table border='1'>";

// print out table columns
echo "<tr>";
foreach(array_keys($keys) as $key) {
 switch($sort) {
  case $key :
   $order_by = $key;
   break;
 }
 if($sort==$key) {
  echo "<td bgcolor=lightgreen><a href='?sort=$key&sort_by=" . $sort_order . "'>$key</a></td>";
 } else {
  echo "<td bgcolor=lightblue><a href='?sort=$key&sort_by=" . $sort_order . "'>$key</a></td>";
 }
}
echo "</tr>";

// print out table contents
$query  = mysqli_query($connect,("SELECT * FROM table ORDER BY " . $sort . " " . $sort_order));
while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
 echo "<tr>";
 foreach($row as $rows) {
  echo "<td>" . $rows . "</td>";
 }
 echo "</tr>";
}

echo "</table>";

// close connection
mysqli_free_result($query);
mysqli_close($connect);
?>