Page 1 of 1

Sort Query Results

Posted: Tue Feb 20, 2007 2:42 am
by transfield
Hello,
I'm querying a Mysql database & displaying the results on a html page. This part is working fine.

I now want to sort the query results ascending by clicking on the column headers. I'm using a code which I got from another forum but I don't know how to edit it. This code is working fine but it re-queries & sorts all the records in the whole database. I don't want it to re-query & sort all the records in the whole database. I want it to sort only the query results from the 1st query.

Please help me to edit this code. Thank you.

Code: Select all

<?php 
//the 1st query that comes from a html form 
$Text_Box_17 = $_POST['Text_Box_17']; 
$query="SELECT * FROM land WHERE full_add LIKE '%$Text_Box_17%'"; 

//the sorting part 
$field = $_GET['sortby']; 
if(isset($field)) 
{ 
   $query = $query.' ORDER BY '.$field.' ASC'; 
} 

//the connection to the mysql database 
mysql_connect ("$host","$username","$password"); 
mysql_select_db($database) or die( "Where's the database man?"); 

$result=mysql_query($query); 
$num=mysql_numrows($result); 
//the table to display the results
?> 

<table width=700 border=1 height="0" style="border-collapse: collapse" bordercolor="#111111" cellpadding="0" cellspacing="0"> 
<tr bgcolor ="cyan"> 
<td width="80" align="center" bgcolor="#CCFFFF"><b><font face="arial" size="2.5"><a href="test_metro_agents.php?sortby=date">date</a></font></strong></td> 
<td width="570" align="center" bgcolor="#CCFFFF"><b><font face="arial" size="2.5"><a href="test_metro_agents.php?sortby=full_add">full_add</a></font></strong></td> 
<td width="50" align="center" bgcolor="#CCFFFF"><b><font face="arial" size="2.5"><a href="test_metro_agents.php?sortby=status">status</a></font></strong></div></td> 
</tr> 

<tr> 
<?php 
while($row = mysql_fetch_assoc($result)){ 
?> 
<tr> 
<td align="center" bgcolor="#CCFFFF"><div align="center"><font face="arial" size="2"><?php echo $row['date'] ?></font>&nbsp;</div></td> 
<td align="center" bgcolor="#CCFFFF"><div align="left"><font face="arial" size="2"><?php echo $row['full_add'] ?></font>&nbsp;</div></td> 
<td align="center" bgcolor="#CCFFFF"><div align="center"><font face="arial" size="2"><?php echo $row['status'] ?></font>&nbsp;</div></td> 
<tr> 
<?php 
} 
?> 
</table>

Posted: Tue Feb 20, 2007 3:22 am
by Kieran Huggins
I think you were making it just a little too complicated:

Code: Select all

//the sorting part
if(isset($_GET['sortby'])) $query .= ' ORDER BY '.$_GET['sortby'].' ASC';
//... and for good measure:
$result=mysql_query($query) or die('query error: '.MySQL_error());
The old way, you were assigning something to the field variable, which then existed, no matter what!

Posted: Tue Feb 20, 2007 3:39 am
by transfield
Sorry I don't understand what you are saying. What am I supposed to do now? How do I edit this code to suit my needs?

Posted: Tue Feb 20, 2007 4:42 am
by Rovas
It' s pretty simple: you add the code given by Kieran Huggins to your existing code (the one that displays the unsorted data) and you put in you table headers a link to current page in which you add sort=NameAsc something like this

Code: Select all

<th><a href="www.example.com/page.php?cat=1&sort=Name">Name</a></th>
If you want you can use AJAX on the table (simply create a JS scripts that loads a HTML page (or a XML page) which has your sorted data in the table).

Posted: Tue Feb 20, 2007 5:05 am
by transfield
You mean like this? No it's still sorting the whole database instead of sorting only the query results from the 1st query. I don't want it to re-query & sort all the records in the whole database. I want it to sort only the query results from the 1st query.

Code: Select all

<?php
$Text_Box_17 = $_POST['Text_Box_17']; 
$query="SELECT * FROM land WHERE full_add LIKE '%$Text_Box_17%'"; 

//the sorting part 
if(isset($_GET['sortby'])) $query .= ' ORDER BY '.$_GET['sortby'].' ASC';

//the connection to the mysql database 
mysql_connect ("$host","$username","$password"); 
mysql_select_db($database) or die( "Where's the database man?"); 

$result=mysql_query($query) or die('query error: '.MySQL_error()); 
$num=mysql_numrows($result); 
?> 

<table width=700 border=1 height="0" style="border-collapse: collapse" bordercolor="#111111" cellpadding="0" cellspacing="0"> 
<tr bgcolor ="cyan"> 
<td width="80" align="center" bgcolor="#CCFFFF"><b><font face="arial" size="2.5"><a href="test_metro_agents.php?cat=1&sort=date">date</a></font></strong></td> 
<td width="570" align="center" bgcolor="#CCFFFF"><b><font face="arial" size="2.5"><a href="test_metro_agents.php?sortby=full_add">full_add</a></font></strong></td> 
<td width="50" align="center" bgcolor="#CCFFFF"><b><font face="arial" size="2.5"><a href="test_metro_agents.php?sortby=status">status</a></font></strong></div></td> 
</tr> 

<tr> 
<?php 
while($row = mysql_fetch_assoc($result)){ 
?> 
<tr> 
<td align="center" bgcolor="#CCFFFF"><div align="center"><font face="arial" size="2"><?php echo $row['date'] ?></font>&nbsp;</div></td> 
<td align="center" bgcolor="#CCFFFF"><div align="left"><font face="arial" size="2"><?php echo $row['full_add'] ?></font>&nbsp;</div></td> 
<td align="center" bgcolor="#CCFFFF"><div align="center"><font face="arial" size="2"><?php echo $row['status'] ?></font>&nbsp;</div></td> 
<tr> 
<?php 
} 
?> 
</table>

Posted: Tue Feb 20, 2007 5:35 am
by Kieran Huggins
if you want to cache the results of the $_POST for future page loads, I suggest using the $_SESSION array:

at the top of your script:

Code: Select all

session_start(); // this starts/resumes the session 
//google "PHP session tutorial" if you don't know what this is

//the 1st query that comes from a html form
if(isset($_POST['Text_Box_17'])){
   $Text_Box_17 = $_SESSION['Text_Box_17'] = $_POST['Text_Box_17']; // double assignment, they're all equal
}else{
   $Text_Box_17 = $_SESSION['Text_Box_17']; // re-assign from the session variable
}
also your first column link is "sort=date" and should be "sortby=date"

Posted: Tue Feb 20, 2007 6:09 am
by transfield
Thank you very much, Kieran Huggins. Yes, it works fine now. I appreciate your help.

Posted: Tue Feb 20, 2007 6:30 am
by onion2k
Kieran Huggins wrote:I think you were making it just a little too complicated:

Code: Select all

//the sorting part
if(isset($_GET['sortby'])) $query .= ' ORDER BY '.$_GET['sortby'].' ASC';
//... and for good measure:
$result=mysql_query($query) or die('query error: '.MySQL_error());
The old way, you were assigning something to the field variable, which then existed, no matter what!
Keiran Huggins in "putting unescaped $_GET data straight into SQL" shocker!

Tsk.

Posted: Tue Feb 20, 2007 11:07 am
by Kieran Huggins
8O :oops:

Code: Select all

$query .= ' ORDER BY '.MySQL_real_escape_string($_GET['sortby']).' ASC';