Sorting by specific colum alphabeticly , with the click of a link

netpants · December 18, 2006

Ok I have my display.php http://www.freegamespot.net/dancefactory/display.php and it displays all of the entries in a nice format from my database, now I need to be able to click on the Name link at the top and sort that display in alphabeticle order by name, or date, or prize, or address if I want. How do I turn those table header names into links that would sort that display into whatever one I want it sorted by. Here is the code I used to display my database infromation. As you will see I have already attempted this but it is not working. Please help.

[code]<html>

<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Add Winner</title>
</head>

<body bgcolor="#000000">


<img border="0" src="1392094530_m.jpg" width="170" height="119">
<table border="0" width="986">
<tr>
<td width="113"><a href="insert.php">Add Winner</a>
<a href="display.php">Display Winner</a></td>
<td width="863" rowspan="3" valign="top">
<table border="0" width="100%">
<tr>
<td> <?
$username="";
$password="";
$database="";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM Winners";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

?>

<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th><a href="<? $orderby = 'id' ?>">ID</a></th>
<th>Date</th>
<th><a href="<? $orderby = 'name' ?>">Name</a></th>
<th>Birthday</th>
<th>Phone</th>
<th>Street Number</th>
<th>Street Name</th>
<th>City</th>
<th>State</th>
<th>Zip</th>
<th>Prize</th>
</tr>

<?
$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$date=mysql_result($result,$i,"loggedDate");
$first=mysql_result($result,$i,"firstName");
$last=mysql_result($result,$i,"lastName");
$birth=mysql_result($result,$i,"birth");
$phone=mysql_result($result,$i,"phone");
$streetnumber=mysql_result($result,$i,"streetNumber");
$streetname=mysql_result($result,$i,"streetName");
$city=mysql_result($result,$i,"city");
$state=mysql_result($result,$i,"state");
$zip=mysql_result($result,$i,"zip");
$prize=mysql_result($result,$i,"prize");
?>

<tr>
<td bgcolor="#333333"><? echo "$id"; ?></td>
<td bgcolor="#202020"><? echo "$date"; ?></td>
<td bgcolor="#333333"><? echo "$first $last"; ?></td>
<td bgcolor="#202020"><? echo "$birth"; ?></td>
<td bgcolor="#333333"><? echo "$phone"; ?></td>
<td bgcolor="#202020"><? echo "$streetnumber"; ?></td>
<td bgcolor="#333333"><? echo "$streetname"; ?></td>
<td bgcolor="#202020"><? echo "$city"; ?></td>
<td bgcolor="#333333"><? echo "$state"; ?></td>
<td bgcolor="#202020"><? echo "$zip"; ?></td>
<td bgcolor="#333333"><? echo "$prize"; ?></td>
</tr>
<?
++$i;
}
echo "</table>";

?>
</td>
</tr>
</table>


</body>

</html>

</html>[/code]
Thank you

chiprivers · December 18, 2006

You need to add an ordery_by variable: $order_by

At the beginning of the script we will check to see if a column has been selected to order by, if not default to last name in alphabetical order:
[code]
<?php
if (isset($_GET['order_by'])) {
$order_by = $_GET['order_by'];
} else {
$order_by = "lastName";
}
?>
[/code]

Amend your select statement to use the order by condition:
[code]
<?php
$query="SELECT * FROM Winners ORDER BY ".$order_by." ASC";
?>
[/code]

You then need to make your headers in the table into links:
[code]
<th><a href="<?php echo $_SERVER['PHP_SELF']."?order_by=\"column_name\""; ?>">ID</a></th>
[/code]

In the above code remember to change column_name to the relevent table column names from your database.

netpants · December 18, 2006

THanks for your help, but when I added that bit of code to the beggining of the script, it didnt display anything the page went blank.

craygo · December 18, 2006

You would have to change the links and the query.
put this before your query
[code]if(isset($_GET['sort'])){
$sort = $_GET['sort'];
$order = $_GET['order'];
} else {
$sort = "lastName";
$order = "ASC";
}[/code]

Now your query would be
[code]$query="SELECT * FROM Winners ORDER BY $sort $order";[/code]

now the links are a little tougher. try this before your table output
[code]// array of values
$sort_array = array("lastName" => "ASC", "loggedDate" => "ASC", "birth" => "ASC", "phone" => "ASC", "streetNumber" => "ASC", "streetName" => "ASC", "city" => "ASC", "state" => "ASC", "zip" => "ASC", "prize" => "ASC");
$bgcolor = "#333333";
foreach($sort_array as $linksort => $linkorder){
// alternate color
if($bgcolor == "#202020"){
$bgcolor = "#333333";
} else {
$bgcolor = "#202020";
}
if($linksort == $sort && $linkorder == $order){
echo "<td bgcolor=$bgcolor><a href=\"".$_SERVER['PHP_SELF']."?sort=".$linksort."&order=DESC\">".$linksort."</a></td>";
} else {
echo "<td bgcolor=$bgcolor><a href=\"".$_SERVER['PHP_SELF']."?sort=".$linksort."&order=".$linkorder."\">".$linksort."</a></td>";
}
}[/code]

Hopefully that is all set I didn't test it

Ray

netpants · December 18, 2006

I am to noob for this I think, I followed both of you guys directions, and both ways the display output was nothing.

craygo · December 18, 2006

This will do it. Only thing I would suggest it making your fields names in your db a little more pleasing because this needs to use the exact filed name for the sort so it shows the field name not a friendly name.
Example: LastName, FirstName, BirthDate, StreetName ect. ect. ect...

[code]<html>

<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Add Winner</title>
</head>

<body bgcolor="#000000">


<img border="0" src="1392094530_m.jpg" width="170" height="119">
<table border="0" width="986">
<tr>
<td width="113"><a href="insert.php">Add Winner</a>
<a href="display.php">Display Winner</a></td>
<td width="863" rowspan="3" valign="top">
<table border="0" width="100%">
<tr>
<td> 
<?
$username="";
$password="";
$database="";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
// set sort
if(isset($_GET['sort'])){
$sort = $_GET['sort'];
$order = $_GET['order'];
} else {
$sort = "lastName";
$order = "ASC";
}

$query="SELECT * FROM Winners ORDER BY $sort $order";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

?>

<table border="0" cellspacing="2" cellpadding="2">
<tr>
<?
foreach($sort_array as $linksort => $linkorder){
// alternate color
if($bgcolor == "#202020"){
$bgcolor = "#333333";
} else {
$bgcolor = "#202020";
}
if($linksort == $sort && $linkorder == $order){
echo "<td bgcolor=$bgcolor><a href=\"".$_SERVER['PHP_SELF']."?sort=".$linksort."&order=DESC\">".$linksort."</a></td>";
} else {
echo "<td bgcolor=$bgcolor><a href=\"".$_SERVER['PHP_SELF']."?sort=".$linksort."&order=".$linkorder."\">".$linksort."</a></td>";
}
}
?>
</tr>
<?
$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$date=mysql_result($result,$i,"loggedDate");
$first=mysql_result($result,$i,"firstName");
$last=mysql_result($result,$i,"lastName");
$birth=mysql_result($result,$i,"birth");
$phone=mysql_result($result,$i,"phone");
$streetnumber=mysql_result($result,$i,"streetNumber");
$streetname=mysql_result($result,$i,"streetName");
$city=mysql_result($result,$i,"city");
$state=mysql_result($result,$i,"state");
$zip=mysql_result($result,$i,"zip");
$prize=mysql_result($result,$i,"prize");
?>

<tr>
<td bgcolor="#333333"><? echo "$id"; ?></td>
<td bgcolor="#202020"><? echo "$date"; ?></td>
<td bgcolor="#333333"><? echo "$first $last"; ?></td>
<td bgcolor="#202020"><? echo "$birth"; ?></td>
<td bgcolor="#333333"><? echo "$phone"; ?></td>
<td bgcolor="#202020"><? echo "$streetnumber"; ?></td>
<td bgcolor="#333333"><? echo "$streetname"; ?></td>
<td bgcolor="#202020"><? echo "$city"; ?></td>
<td bgcolor="#333333"><? echo "$state"; ?></td>
<td bgcolor="#202020"><? echo "$zip"; ?></td>
<td bgcolor="#333333"><? echo "$prize"; ?></td>
</tr>
<?
++$i;
}
echo "</table>";

?>
</td>
</tr>
</table>


</body>

</html>

</html>

<?php
if(isset($_GET['sort'])){
$sort = $_GET['sort'];
$order = $_GET['order'];
} else {
$sort = "lastName";
$order = "ASC";
}

$query="SELECT * FROM Winners ORDER BY $sort $order";
echo "<table><tr>";
// array of values
$sort_array = array("lastName" => "ASC", "loggedDate" => "ASC", "birth" => "ASC", "phone" => "ASC", "streetNumber" => "ASC", "streetName" => "ASC", "city" => "ASC", "state" => "ASC", "zip" => "ASC", "prize" => "ASC");
$bgcolor = "#333333";
foreach($sort_array as $linksort => $linkorder){
// alternate color
if($bgcolor == "#202020"){
$bgcolor = "#333333";
} else {
$bgcolor = "#202020";
}
if($linksort == $sort && $linkorder == $order){
echo "<td bgcolor=$bgcolor><a href=\"".$_SERVER['PHP_SELF']."?sort=".$linksort."&order=DESC\">".$linksort."</a></td>";
} else {
echo "<td bgcolor=$bgcolor><a href=\"".$_SERVER['PHP_SELF']."?sort=".$linksort."&order=".$linkorder."\">".$linksort."</a></td>";
}
}
echo "</tr></table>";[/code]

Ray

netpants · December 18, 2006

Man so this is not and easy task. Lots of code to just make the column name a link to sort them by columns instead of the order they were listed.

craygo · December 18, 2006

Well it will work just the way it is. Just the column names won't be so nice.

ray

Sign In

Sorting by specific colum alphabeticly , with the click of a link

Recommended Posts

netpants

Link to comment

Share on other sites

chiprivers

Link to comment

Share on other sites

netpants

Link to comment

Share on other sites

craygo

Link to comment

Share on other sites

netpants

Link to comment

Share on other sites

craygo

Link to comment

Share on other sites

netpants

Link to comment

Share on other sites

craygo

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information