sorting output via link

[Gilead]

Hey guys. Could use more help on this project.
I got the database entries to display onscreen in a table. What I want to do now is to make the output sort by various headings.

I can do it manually by changing the query
$result = mysql_query("SELECT * FROM $table_name ORDER BY headername");

What is the best way to go about this?
Everything I've tried ends up with an error at the link.
$result = mysql_query("SELECT * FROM $table_name ORDER BY $sort");
$orders=array("contactid","network","network_representative","firstname","lastname","organization_title"); //what I want to be able to sort by
$key=array_search($_GET['sort'],$orders);
$sort=$orders[$key];

Then in the headers area:
...<th><a href="?sort=network">Network</a></th>...
Thanks guys!

[jatar_k]

with an error at the link

not sure what you mean, just the html output that ends up wrong? or when you click the link you created you get errors?

what you have there is ok, though I would make sure you clean that GET var before pushing it into the query, which maybe you do with the array search but you also seem to use the $sort var in the first row, before that check.

[Gilead]

I wanted to automate the process, so when you click on the header, it would sort the table by that selection, but it doesn't work and I'm not sure why it doesn't work.
Does that make more sense?

[jatar_k]

well, if you use that cde exactly you are trying to use a var before it's set, it looks better like this

$orders = array("contactid","network","network_representative","firstname","lastname","organization_title"); //what I want to be able to sort by
$key = array_search($_GET['sort'],$orders);
$sort = $orders[$key];
$result = mysql_query("SELECT * FROM $table_name ORDER BY $sort");

[rocknbil]

I would do it like this. Then you can apply it to any table, and if you need to change the table, you can.

$fieldNames = get_field_names($tablename);
$count = count($fieldNames)-1; // so you only do it once
$sort = (isset($_GET['sort']) and in_array($_GET['sort'],$fieldNames))?$_GET['sort']:'id';
// You should address this too somehow
$direction = (isset($_GET['dir']) and (($_GET['dir']=='asc') or ($_GET['dir']=='desc')))?$_GET['dir']:'asc';

// Do stuff to get your actual data, store in scalar, note the "if" below 
$tablecontent=null; 
$query = "select * from table order by $sort $direction"; 
$result = mysql_query($query) or die("Cannot execute query: " . mysql_error()); 
while ($row=mysql_fetch_array($result)) { 
 $tablecontent .= '<tr>'; 
 // Skip the ID field - or maybe not, if you need it 
 for ($i=1;$i<=$count;$i++) { 
 // Remembering mysql_fetch_array returns both an indexed and 
 // an associative array 
 $tablecontent .= '<td>' . $row[$i] . '</td>'; 
 } 
 $tablecontent .= '</tr>'; 
} 

if ($tablecontent) { 
 echo '<table><tr>'; 
 for ($i=1;$i<=$count;$i++) { 
 $tablecontent .= '<th><a href="script.php?sort=' . $fieldNames[$i] . '">' . $fieldNames[$i] . '</th>'; 
 } 
 echo "</tr>$tablecontent </table>"; 
} 
else { echo "<p>There are no results to display.</p>"; } 

function get_field_names($table) { 
 if (! isset($table)) { die("No table in field names"); } 
 $fields = Array (); 
 $counter=0; 
 $result = mysql_query("show columns from $table"); 
 if (!$result) { error("Could not get table names: " . mysql_error()); } 
 while ($row = mysql_fetch_array($result,MYSQL_NUM)) { 
 $fields[$counter]=$row[0]; 
 $counter++; 
 } 
 return $fields; 
} 

Of course, if you want it to work really well you'd have a scheme to associate plain names with the field names so "fname" becomes "First Name" in the header, and also do it in such a way that you don't reveal your table field names publicly, but that's a start. You'd do that with a "plain names" table,

id|anonymous_name|fieldname|display_value

giving you

$plain_names = Array(
'fname' => array('fn','First Name'),
'lname' => array('ln','Last Name'),
// etc.
);

... Then store those in another array, so you'd output

$tablecontent .= '<th><a href="script.php?sort=' . $plain_names[$fieldNames[$i]][0] . '">' . $plain_names[$fieldNames[$i]][1] . '</th>';

[Gilead]

Thanks so much!