1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 2:14 pm May 1, 2026

Forum Moderators: coopster

Message Too Old, No Replies

Sort Columns PHP Data

         

charger9

2:33 pm on Aug 11, 2008 (gmt 0)

10+ Year Member



I have a table that displays info from a database via php/mysql. I would like to be able to click on the column title and sort the data in the column. I'm not super familiar with php and all the terms that go with it, can someone look at this and give me an example of what to do to get started?

Here is my code (personal data removed):

<?php
$hostname_LL = "#*$!#*$!x";
$database_LL = "#*$!#*$!x";
$username_LL = "#*$!#*$!x";
$password_LL = "#*$!#*$!x";
$LL = mysql_pconnect($hostname_LL, $username_LL, $password_LL) or trigger_error(mysql_error(),E_USER_ERROR);

mysql_select_db($database_LL, $LL);
$query_sv = "SELECT * FROM saves ORDER BY date ASC";
$sv = mysql_query($query_sv, $LL) or die(mysql_error());
$row_sv = mysql_fetch_assoc($sv);
$totalRows_sv = mysql_num_rows($sv);
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Customer Saves Records</title>
<style type="text/css">
<!--
.style3 {font-size: 12px; font-family: Arial, Helvetica, sans-serif; }
.style5 {font-size: 12px; font-family: Arial, Helvetica, sans-serif; font-weight: bold; }
.style6 {
font-size: 14px;
font-family: Arial, Helvetica, sans-serif;
font-weight: bold;
}
-->
</style>
</head>

<body>
<p class="style6">Customer Saves Records</p>

<table width="800" border="0">
<tr>
<td><span class="style5">Date</span></td>
<td><span class="style5">Employee</span></td>
<td><span class="style5">Service</span></td>
<td><span class="style5">Account #</span></td>
<td><span class="style5">Reason for cancel</span></td>
<td><span class="style5">Reason Code</span></td>
<td><span class="style5">Saved</span></td>
<td><span class="style5">Promo used</span></td>
<td><span class="style5">Value</span></td>
</tr>
<?php do { ?>
<tr>
<td height="25"><span class="style3"><?php echo $row_sv['date']; ?></span></td>
<td><span class="style3"><?php echo $row_sv['employee']; ?></span></td>
<td><span class="style3"><?php echo $row_sv['service']; ?></span></td>
<td><span class="style3"><?php echo $row_sv['account']; ?></span></td>
<td><span class="style3"><?php echo $row_sv['reason']; ?></span></td>
<td><span class="style3"><?php echo $row_sv['rcode']; ?></span></td>
<td><span class="style3"><?php echo $row_sv['saved']; ?></span></td>
<td><span class="style3"><?php echo $row_sv['promo']; ?></span></td>
<td><span class="style3"><?php echo $row_sv['value']; ?></span></td>
</tr>

<?php } while ($row_sv = mysql_fetch_assoc($sv)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($sv);
?>

d40sithui

3:23 pm on Aug 11, 2008 (gmt 0)

10+ Year Member



This is relatively simple. All it involves is manipulating your query whenever the user clicks on the column name and then redisplaying the results. To change the query, you need to take a parameter variable and insert into the query, usually from the URL. To redisplay the results, you will either refresh the whole page OR use AJAX. If you are the original author of the script above, then I think you should be able to accomplish this.
Here are some tips to get you started.

Instead of this::


<td><span class="style5">Date</span></td>

You make it a link to the same page. So something like this::


<td><span class="style5"><a href="<? echo $_SERVER['PHP_SELF']."?orderby=Date";?>Date</a></span></td>

You would do this for each of the column names. If you have pagination involved, you will have other variables in the link to preserve page number and data order.

Once the user clicks on the link, he/she will be directed to the same page. You will then have something to retrieve the $_GET var to change your query.
Instead of this::


$query_sv = "SELECT * FROM saves ORDER BY date ASC";

You should have something like this.


if(isset($_GET['orderby'])){
$orderby = $_GET['orderby'];
$query_sv = "SELECT * FROM saves ORDER BY ".mysql_real_escape_string($orderby)." ASC";
}
//default query
else{
$query_sv = "SELECT * FROM saves ORDER BY date ASC";
}

Remember to check the $orderby variable for valid characters before running against the db.

charger9

4:00 pm on Aug 11, 2008 (gmt 0)

10+ Year Member



This works perfectly--thank you! Would it be easy to modify this so that on the 1st click it sorts the columns ASC and on the second click DESC?

d40sithui

2:58 pm on Aug 12, 2008 (gmt 0)

10+ Year Member



Of course. It's all about preserving variables. To implement the sorting by ASC/DESC you will most likely need to introduce another vairable and having the page "remember" it. It's a bit more involved, but still simple.
First you'll need to change this part::

if(isset($_GET['orderby'])){
$orderby = $_GET['orderby'];
$query_sv = "SELECT * FROM saves ORDER BY ".mysql_real_escape_string($orderby)." ASC";
}
//default query
else{
$query_sv = "SELECT * FROM saves ORDER BY date ASC";
}

to something like this ::


if(isset($_GET['sortby'])){
$sortby = $_GET['sortby'];
if($strcasecmp($sortby, "desc")==0){
$newsort = "ASC";
}
else{
$newsort = "DESC";
}
}
else{
$sortby = "ASC";
$newsort = "DESC";
}
if(isset($_GET['orderby'])){
$orderby = $_GET['orderby'];
$query_sv = "SELECT * FROM saves ORDER BY ".mysql_real_escape_string($orderby);

if(strcasecmp($sortby, "desc")==0){
$query_sv.= " DESC";
}
else{
$query_sv.= " ASC";
}
}
//default query
else{
$query_sv = "SELECT * FROM saves ORDER BY date ASC";
}

Now change your links and give it another link parameter. So instead of this::


<td><span class="style5"><a href="<? echo $_SERVER['PHP_SELF']."?orderby=Date";?>Date</a></span></td>

You'll have this::

<td><span class="style5"><a href="<? echo $_SERVER['PHP_SELF']."?orderby=Date&sortby=$newsort";?>Date</a></span></td>

randylee73

4:22 am on Aug 30, 2008 (gmt 0)

10+ Year Member



I'm trying to solve the same problem in my php app. This last solution works well, except that I have multiple columns I want to use to sort, and I want each one to start with ASC order the first time I click on it. The above solution flip flops ASC and DESC no matter which column you select. Any thoughts?

Servo

6:56 pm on Aug 31, 2008 (gmt 0)

10+ Year Member



Hi I have used this code and the same format as this guy used for sortable columns but they code does not work for me. I have been staring it down now for over a day and i just cant figure out what it wrong..it pulls all the data from my data base but it does not show me the headers at all.

and this is the code for everything 


<?php
$hostname_LL = "localhost";
$database_LL = "database";
$username_LL = "user";
$password_LL = "pass";
$LL = mysql_pconnect($hostname_LL, $username_LL, $password_LL) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($database_LL, $LL);
if(isset($_GET['sortby'])){
$sortby = $_GET['sortby'];
if(strcasecmp($sortby, "desc")==0){
$newsort = "ASC";
}
else{
$newsort = "DESC";
}
}
else{
$sortby = "ASC";
$newsort = "DESC";
}
if(isset($_GET['orderby'])){
$orderby = $_GET['orderby'];
$query_sv = "SELECT `Team`, `Record`, `At Home`, `Away`, `Division Record`, `Streak` FROM `statistics` ORDER BY ".mysql_real_escape_string($orderby);
if(strcasecmp($sortby, "desc")==0){
$query_sv.= " DESC";
}
else{
$query_sv.= " ASC";
}
}
//default query
else{ 
$query_sv = "SELECT `Team`, `Record`, `At Home`, `Away`, `Division Record`, `Streak` FROM `statistics` ORDER BY Team ASC"; }
$sv = mysql_query($query_sv, $LL) or die(mysql_error());
$row_sv = mysql_fetch_assoc($sv);
$totalRows_sv = mysql_num_rows($sv);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Stats</title>
</head>
<body>
<table width="800" border="0">
<tr>
<td><a href="<? echo $_SERVER['PHP_SELF']."?orderby=Team&sortby=$newsort";?>Team</a></td>
<td><a href="<? echo $_SERVER['PHP_SELF']."?orderby=Record&sortby=$newsort";?>Record</a></td>
<td><a href="<? echo $_SERVER['PHP_SELF']."?orderby=At Home&sortby=$newsort";?>At Home</a></td>
<td><a href="<? echo $_SERVER['PHP_SELF']."?orderby=Away&sortby=$newsort";?>Away</a></td>
<td><a href="<? echo $_SERVER['PHP_SELF']."?orderby=Division Record&sortby=$newsort";?>Division Record</a></td>
<td><a href="<? echo $_SERVER['PHP_SELF']."?orderby=Streak&sortby=$newsort";?>Streak</a></td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_sv['Team']; ?></td>
<td><?php echo $row_sv['Record']; ?></td>
<td><?php echo $row_sv['At Home']; ?></td>
<td><?php echo $row_sv['Away']; ?></td>
<td><?php echo $row_sv['Division Record']; ?></td>
<td><?php echo $row_sv['Streak']; ?></td>
</tr>
<?php } while ($row_sv = mysql_fetch_assoc($sv)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($sv);
?>

for some reason the code will not work showing the links..please help

[edited by: coopster at 3:08 pm (utc) on Sep. 2, 2008]
[edit reason] removed url TOS [webmasterworld.com] [/edit]

Servo

7:29 pm on Aug 31, 2008 (gmt 0)

10+ Year Member



Nevermind guys i figure it out thanks....

instead of this 

<td><a href="<? echo $_SERVER['PHP_SELF']."?orderby=Streak&sortby=$newsort";?>Streak</a></td>

i needed this 

<td><a href="<? echo $_SERVER['PHP_SELF']."?orderby=Streak&sortby=$newsort";?>">Streak</a></td>

Once agian thanks for everything this code rocks!

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 2:14 pm May 1, 2026