Sorting mysql table by columns with php ?

[chrisdee]

I'm going half crazy. I'm having problems getting the script I'v made below to let me sort by column names. Meaning when I click on the colum titles of the table Im trying to make them sortable. Any ideas ?

<?php
$connect = mysql_connect("localhost", "user", "pass"); # Connects to database
mysql_select_db("database");
$table = "table";


$query_title = mysql_query("SELECT * FROM $table ORDER BY $sort");

print "<table border=1 cellpadding=2>";
print "<tr><td bgcolor=lightblue>Nr</td>";
$title = mysql_fetch_array($query_title, MYSQL_ASSOC);
foreach(array_keys($title) as $t) {
 if($t=="id") {} else {
 print "<td bgcolor=lightblue><a href=index.php?sort=$t>" . $t . "</a></td>";
}}
 print "<td bgcolor=lightblue>Edit</td><td bgcolor=lightblue>Delete</td>";
print "</tr>";


$query_content = mysql_query("SELECT * FROM $table");

while ($content = mysql_fetch_array($query_content, MYSQL_NUM)) {
 $nr++;
 print "<tr><td>$nr</td>";
 foreach($content as $c) {
  if($c==$content[0]) {}
  elseif($c==$content[1]) {
    print "<td><a href=audio/".rawurlencode($c)." target=new>$c</a></td>";
  }
  else {
  print "<td>" . $c . "</td>";
 }}
 print "<td><a href=edit.php?id=$content[0]>Edit</a></td><td><a href=shure.php?id=$content[0]>Delete</a></td>";
 print "</tr>";
}
print "</table>";
?>

[chrisdee]

Nevermind. I finally got it to work🙂))

<?php
print "<a href=add.php>Add</a><br><br>";

$connect = mysql_connect("localhost", "user", "pass"); # Connects to database
mysql_select_db("database");
$table = "table";

$query_title = mysql_query("SELECT * FROM $table");

print "<table border=1 cellpadding=2>";
print "<tr><td bgcolor=lightblue>Nr</td>";
$title = mysql_fetch_array($query_title, MYSQL_ASSOC);
foreach(array_keys($title) as $t) {
 if($t=="id") {} else {
 print "<td bgcolor=lightblue><a href=index.php?sort=$t>" . $t . "</a></td>";
}}
 print "<td bgcolor=lightblue>Edit</td><td bgcolor=lightblue>Delete</td>";
print "</tr>";

$sort = $_GET['sort'];
if(empty($sort)) {
 $query_content = mysql_query("SELECT * FROM $table ORDER BY file ASC");
}
else {
 $query_content = mysql_query("SELECT * FROM $table ORDER BY $sort ASC");
}

while ($content = mysql_fetch_array($query_content, MYSQL_NUM)) {
 $nr++;
 print "<tr><td>$nr</td>";
 foreach($content as $c) {
  if($c==$content[0]) {}
  elseif($c==$content[1]) {
    print "<td><a href=audio/".rawurlencode($c)." target=new>$c</a></td>";
  }
  else {
  print "<td>" . $c . "</td>";
 }}
 print "<td><a href=edit.php?id=$content[0]>Edit</a></td><td><a href=shure.php?id=$content[0]>Delete</a></td>";
 print "</tr>";
}
print "</table>";
?>

[chrisdee]

Next step is to make the script let me sort both asc and desc. Meaning, if I click on the title once it should sort asc. If I click once more it should order desc.

Any ideas ?

[cretaceous]

this is my solution, there are others:
ok you have this as the a href link:

<a href=index.php?sort=$t>" . $t . "</a>

so when that actual column is the one being sorted on:

if ($sort==$t) echo "<a href=index.php?sort=$t&amp;dsc=1>" . $t . "</a>";

then do a get on 'dsc' and if set change "ORDER BY $sort ASC" to "ORDER BY $sort DESC"

and make sure a href reverts to plain "<a href=index.php?sort=$t>" . $t . "</a>";

I tend to leave out the ASC in SQL statements as ASC is the default behaviour

you can add things to alter text colour of the column being sorted or add a little arrow image that changes colour and direction depending if ASC or DESC sorting