Sorting results by

I am working on a script which shows results a lot like phpmyadmin. It has multiple columns and need a way to sort by each column when the column title is clicked on. How would I go about doing this?

Here is what I have so far...


<?php
include("inc/config.php");
include("inc/database.php");
include("inc/header.php");

$default_sort = 'id';
$allowed_order = array ('id', 'CompanyName', 'FirstName','LastName');
$from = (($page * $max_results) - $max_results);

if ($_GET["cmd"] == "order" ||
!in_array ($_GET["cmd"] == "order", $allowed_order)) {
$order = $default_sort;
} else {
$order = $_GET["cmd"] == "order";
}

$query = "SELECT * FROM contacts ORDER BY $order LIMIT $from, $max_results";
$result = mysql_query($query);

$numrows = mysql_num_rows($result);
if ($numrows == 0) {
echo "No data to display!";
exit;
}

$row = mysql_fetch_assoc ($result);
echo "<tr>\n";
foreach ($row as $heading=>$column) {
echo "<td class=\"header\">";
if (in_array ($heading, $allowed_order)) {
echo "<a href=\"?cmd=order&by=$heading\">$heading</a>";
} else {
echo $heading;
}
echo "</td>\n";
}
echo "</tr>\n";

mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
echo "<tr>\n";
foreach ($row as $column) {
echo "<td>$column</td>\n";
}
echo "</tr>\n";
}
?>


Can't get it to actually sort though. When I try clicking on one of the headings it doesn't do anything but reload the same info.

Here is the entire script if anyone wants to look at it. The last post was just the part having problems.

<?php
include("inc/config.php");
include("inc/database.php");
include("inc/header.php");

session_start();

$_Username = "Testuser";
$_Password = "Testpass";

if ($_POST['submitted'] == "True") {
if ($_POST['username'] == $_Username && $_POST['password'] == $_Password) {
$_SESSION['Logged_In'] = "True";
$_SESSION['Username'] = $_Username;
}
}

if ($_SESSION['Logged_In'] != "True") {
echo "<form method=\"post\" action=\"index.php\">
Username: <input type=\"text\" name=\"username\"><br />
Password: <input type=\"password\" name=\"password\"><br />
<input type=\"hidden\" name=\"submitted\" value=\"True\">
<input type=\"submit\" name=\"loginsubmit\" value=\"Login\">
</form>";
} else {

if (!isset($cmd)) {
$result = mysql_query("SELECT * FROM contacts");

while($row=mysql_fetch_array($result))
{
$id=$row["id"];
}
}

if ($_GET['cmd'] == "logout") {
session_start();
$_SESSION = array();
session_destroy();
echo "<meta http-equiv=\"refresh\" content=\"0\"; url=\"index.php\">";
}

if ($_GET["cmd"] == "export") {
$sql = "SELECT * FROM contacts";
$result = mysql_query($sql) or die(mysql_error());
$fields = mysql_num_fields($result);

for ($i = 0; $i < $fields; $i++) {
$header .= mysql_field_name($result, $i) . "\t";
}

while($row = mysql_fetch_row($result)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r","",$data);

if ($data == "") {
$data = "\n(0) Records Found!\n";
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=contacts.xls");
header("Pragma: no-cache");
header("Expires: 0");
echo "$header\n$data";
}

if ($_GET["cmd"] == "delete") {
$sql = "DELETE FROM contacts WHERE id='$id'";
$result = mysql_query($sql) or die(mysql_error());
echo "Record Deleted";
}

if (!isset($_GET['page'])) {
$page = 1;
} else {
$page = $_GET['page'];
}

$default_sort = 'id';
$allowed_order = array('id', 'CompanyName', 'FirstName','LastName');
$from = (($page * $max_results) - $max_results);

if ($_GET["cmd"] == "order" &&
in_array ($_GET["by"], $allowed_order)) {
$order = $_GET['by'];
} else {
$order = $default_order;
}

$query = "SELECT * FROM contacts ORDER BY $order LIMIT $from, $max_results";
$result = mysql_query($query) or die($query.' caused '.mysql_error());

$numrows = mysql_num_rows($result);
if ($numrows == 0) {
echo "No data to display!";
exit;
}

if ($_POST['addsubmit']) {
$companyname = $_POST["companyname"];
$firstname = $_POST["firstname"];
$lastname = $_POST["lastname"];
$address = $_POST["address"];
$aptno = $_POST["aptno"];
$city = $_POST["city"];
$state = $_POST["state"];
$zip = $_POST["zip"];
$workphone = $_POST["workphone"];
$x = $_POST["x"];
$request = $_POST["request"];
$called = $_POST["called"];
$comments = $_POST["comments"];
$catalog = $_POST["catalog"];
$pricebook = $_POST["pricebook"];
$conpamp = $_POST["conpamp"];
$binder = $_POST["binder"];
$sentrequest = $_POST["sentrequest"];
$motiv = $_POST["motiv"];

$sql = "INSERT INTO contacts (CompanyName, FirstName, LastName, Address, AptNo, City, State, Zip, WorkPhone, X, Request, Called, Comments, Catalog, Pricebook, Conpamp, Binder, SentRequest, Motiv) VALUES ('$companyname', '$firstname', '$lastname', '$address', '$aptno', '$city', '$state', '$zip', '$workphone', '$x', '$request', '$called', '$comments', '$catalog', '$pricebook', '$conpamp', '$binder', '$sentrequest', '$motiv')";
$result = mysql_query($sql) or die(mysql_error());
echo "Record Added";
}

if ($_POST["cmd"] == "edit") {
if (!isset($_POST["editsubmit"])) {
$sql = "SELECT * FROM contacts WHERE id=$id";
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_array($result);
}

if ($_POST["editsubmit"]) {
$companyname = $_POST["companyname"];
$firstname = $_POST["firstname"];
$lastname = $_POST["lastname"];
$address = $_POST["address"];
$aptno = $_POST["aptno"];
$city = $_POST["city"];
$state = $_POST["state"];
$zip = $_POST["zip"];
$workphone = $_POST["workphone"];
$x = $_POST["x"];
$request = $_POST["request"];
$called = $_POST["called"];
$comments = $_POST["comments"];
$catalog = $_POST["catalog"];
$pricebook = $_POST["pricebook"];
$conpamp = $_POST["conpamp"];
$binder = $_POST["binder"];
$sentrequest = $_POST["sentrequest"];
$motiv = $_POST["motiv"];

$sql = "UPDATE contacts SET CompanyName='$companyname',FirstName='$firstname',LastName='$lastname',Address='$address',AptNo='$ap tno',City='$city',State='$state',Zip='$zip',WorkPhone='$workphone',X='$x',Request='$request',Called= '$called',Comments='$comments',Catalog='$catalog',Pricebook='$pricebook',Conpamp='$conpamp',Binder=' $binder',SentRequest='$sentrequest',Motiv='$motiv' WHERE id='$id'";
$result = mysql_query($sql) or die(mysql_error());
echo "Record Edited";
}
}

$sql = mysql_query("SELECT * FROM contacts LIMIT $from, $max_results");
?>
<table border="0" cellspacing="0" cellpadding="3" width="100%" class="tabs">
<tr>
<td width="8">&nbsp;</td>
<td bgcolor="silver" align="center" width="64" nowrap="nowrap" class="tab">
<nobr><a href="index.php"><b>Browse</b></a></nobr>
</td>
<td width="8">&nbsp;</td>
<td bgcolor="#DDDDDD" align="center" width="64" nowrap="nowrap" class="tab">
<nobr><a href="search.php" onClick="return dropdownmenu(this, event, menu1, '250px')" onMouseout="delayhidemenu()"><b>Search</b></a></nobr>
</td>
<td width="8">&nbsp;</td>
<td bgcolor="#DDDDDD" align="center" width="64" nowrap="nowrap" class="tab">
<nobr><a href="index.php" onClick="expandcontent('add')" style="cursor:hand; cursor:pointer"><b>Add</b></a></nobr>
</td>
<td width="8">&nbsp;</td>
<td bgcolor="#DDDDDD" align="center" width="64" nowrap="nowrap" class="tab">
<nobr><a href="export.php"><b>Export</b></a></nobr>
</td>
<td width="8">&nbsp;</td>
<td bgcolor="#DDDDDD" align="center" width="64" nowrap="nowrap" class="tab">
<nobr><a href="index.php?cmd=logout"><b>Logout</b></a></nobr>
</td>
<td width="8">&nbsp;</td>
</tr>
</table><br />
<table cellpadding="0" spacepadding="0" width="100%">
<?php
$row = mysql_fetch_assoc ($result);
echo "<tr>\n";
foreach ($row as $heading=>$column) {
echo "<td class=\"header\">";
if (in_array ($heading, $allowed_order)) {
echo "<a href=\"?cmd=order&by=$heading\">$heading</a>";
} else {
echo $heading;
}
echo "</td>\n";
}
echo "</tr>\n";

mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
echo "<tr>\n";
foreach ($row as $column) {
echo "<td onMouseover=\"changeto(event, '#F2F2F2')\" onMouseout=\"changeback(event, '#DDDDDD')\" class=\"item\">$column</td>\n";
}
echo "</tr>\n";
}
?>
<tr id="add" class="switchcontent">
<td><form method="post" action="index.php"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="companyname" value="" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="firstname" value="" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="lastname" value="" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="address" value="" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="aptno" value="" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="city" value="" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="state" value="" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="zip" value="" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="workphone" value="" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="x" value="" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="request" value="" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="called" value="called"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="comments" value="" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="catalog" value="catalog"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="pricebook" value="pricebook"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="conpamp" value="conpamp"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="binder" value="binder"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="sentrequest" value="sentrequest"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="motiv" value="motiv"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="hidden" name="cmd" value="add"><input type="submit" name="addsubmit" value="Add"></form></td>
<td></td>
<td></td>
</tr>
<?php
while($row = mysql_fetch_array($sql)){
?>
<tr>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><?=$row["id"]?></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><?=ucwords(strtolower($row["CompanyName"]));?></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><?=ucwords(strtolower($row["FirstName"]));?></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><?=ucwords(strtolower($row["LastName"]));?></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><?=ucwords(strtolower($row["Address"]));?></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><?=ucwords(strtolower($row["AptNo"]));?></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><?=ucwords(strtolower($row["City"]));?></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><?=strtoupper($row["State"]);?></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><?=$row["Zip"]?></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><?=$row["WorkPhone"]?></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><?=$row["X"]?></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><?=ucwords(strtolower($row["Request"]));?></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="called" value="called"<?=$row["Called"]?>></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><?=ucwords(strtolower($row["Comments"]));?></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="catalog" value="catalog"<?=$row["Catalog"]?>></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="pricebook" value="pricebook"<?=$row["Pricebook"]?>></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="conpamp" value="conpamp"<?=$row["Conpamp"]?>></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="binder" value="binder"<?=$row["Binder"]?>></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="sentrequest" value="sentrequest"<?=$row["SentRequest"]?>></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="motiv" value="motiv"<?=$row["Motiv"]?>></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><img src="images/button_edit.png" border="0" onClick="expandcontent('<?=$row["id"]?>')" style="cursor:hand; cursor:pointer" alt="Edit"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><a href="index.php?cmd=delete&id=<?=$row["id"]?>"><img src="images/button_drop.png" border="0" alt="Delete"></a></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="select" value="select"></td>
</tr>
<tr id="<?=$row["id"]?>" class="switchcontent">
<td><form method="post" action="index.php"><input type="hidden" name="id" value="<?=$row["id"]?>"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="companyname" value="<?=$row["CompanyName"]?>" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="firstname" value="<?=$row["FirstName"]?>" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="lastname" value="<?=$row["LastName"]?>" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="address" value="<?=$row["Address"]?>" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="aptno" value="<?=$row["AptNo"]?>" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="city" value="<?=$row["City"]?>" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="state" value="<?=$row["State"]?>" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="zip" value="<?=$row["Zip"]?>" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="workphone" value="<?=$row["WorkPhone"]?>" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="x" value="<?=$row["X"]?>" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="request" value="<?=$row["Request"]?>" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="called" value="called"<?=$row["Called"]?>></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="text" name="comments" value="<?=$row["Comments"]?>" size="20"></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="catalog" value="catalog"<?=$row["Catalog"] ?>></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="pricebook" value="pricebook"<?=$row["Pricebook"]?>></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="conpamp" value="conpamp"<?=$row["Conpamp"]?>></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="binder" value="binder"<?=$row["Binder"]?>></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="sentrequest" value="sentrequest"<?=$row["SentRequest"]?>></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="checkbox" name="motiv" value="motiv"<?=$row["Motiv"]?>></td>
<td onMouseover="changeto(event, '#F2F2F2')" onMouseout="changeback(event, '#DDDDDD')" class="item"><input type="hidden" name="cmd" value="edit"><input type="submit" name="editsubmit" value="Save"></form></td>
<td></td>
<td></td>
</tr>
<?php
}
echo "</table><center>";

$total_results = mysql_result(mysql_query("SELECT COUNT(*) AS Num FROM contacts"),0);

$total_pages = ceil($total_results / $max_results);

if($page > 1){
$prev = ($page - 1);
echo "<a href=\"?page=$prev\"><< Previous</a>&nbsp;";
}

for($i = 1; $i <= $total_pages; $i++){
if(($page) == $i){
echo "$i&nbsp;";
} else {
echo "<a href=\"?page=$i\">$i</a>&nbsp;";
}
}

if($page < $total_pages){
$next = ($page + 1);
echo "<a href=\"?page=$next\">Next >></a>";
}
echo "</center>";
}

include("inc/footer.php");
?>

Really need some help with this :(

<?php
include('inc/config.php');
include('inc/database.php');
include('inc/header.php');

$default_sort = 'id';
$allowed_order = array ('id', 'CompanyName', 'FirstName','LastName');
$from = ($page - 1) * $max_results;

$order = ($_GET['cmd'] == 'order' && in_array($_GET['by'], $allowed_order)) ? $_GET['by'] : $_GET['cmd'];

$result = mysql_query('SELECT * FROM contacts ORDER BY '.$order.' LIMIT '.$from.', '.$max_results) or die('Query failed. Reason: '.mysql_error());

if(!mysql_num_rows($result)) {
echo "No data to display!";
exit();
}
$row = mysql_fetch_assoc ($result);
echo '<tr>'."\n";
foreach ($row as $heading=>$column) {
echo '<td class="header">';
echo (in_array ($heading, $allowed_order) ? '<a href="?cmd=order&by='.$heading.'">'.$heading.'</a>' : $heading;
echo '</td>'."\n";
}
echo '</tr>'."\n";

mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
echo '<tr>'."\n";
foreach ($row as $column) {
echo '<td>'.$column.'</td>'."\n";
}
echo '</tr>'."\n";
}
?>
dumpfi

Parse error: parse error, unexpected ';' in c:\inetpub\wwwroot\literature\index.php on line 106

:S

I got it working. I replaced part of mine with this:


$order = ($_GET['cmd'] == 'order' && in_array($_GET['by'], $allowed_order)) ? $_GET['by'] : $default_sort;


Thanks!