[RESOLVED] How to Sort Ascending My Query Results

transfield

Hello,
I have queried my MySQL database & displayed the results on a page. Now, I want to sort ascending a particular field by clicking on the column header which is supposed to be a hyperlink. How do I do this?

I've attached a screen shot to give you a clearer picture of what I am trying to achieve.

Thanks a lot for your advice.

vishwavivek

in your sql query add another parameter ( $sort_by_column ) whose value will get change as per the user's click.

e.g.
if your query is ....

$sql = " SELECT field_name1, field_name2 FROM table_name"

then modify it as ........

$sql = " SELECT field_name1, field_name2 FROM table_name ORDER BY $sort_by_column"

whatever column you want to sort, you just need to pass the querystring as the column name

hope this helps

vishwa vivek garg
( http://vishwavivek.wordpress.com/)

transfield

Thank you for your reply, vishwavivek. I understand what you are saying. However, I'd like to know the following:-

1.How should the hyperlink be written / coded?

This hyperlink will be pointing to another page(lets call it page2.php) where the connection to the database & SELECT statements reside. How does page2.php understand where this link is coming from & what to do?

Warm Regards.

esukf

You pass the field you want to sort by a GET querystring. You then build the $sql query based on the passed field.

<a href="test.php?sortby=name">name</a>
<a href="test.php?sortby=password">password</a>

<?php
//$_GET['sortby'] = "name";

$field = $_GET['sortby'];

$sql = "SELECT * from table"; //unsorted query

if(isset($field)){
	$sql = $sql.' ORDER BY '.$field.' ASC'; //sort based on passed GET field
}	 

echo $sql
?>

transfield

Thank you for your reply, esukf. Your explantation seems logical but for some strange reason, I'm getting only a blank screen. The screen shot is attached. What am I overlooking here?

Thanks.

esukf

Have you actually connect to your database and output the SELECT results to a table??
Example:-

<?php
//$_GET['sortby'] = "name";

$field = $_GET['sortby'];

$sql = "SELECT * from table"; //unsorted query

if(isset($field)){
    $sql = $sql.' ORDER BY '.$field.' ASC'; //sort based on passed GET field
}    

$conn = mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("your_db", $conn) or die(mysql_error());

$result = mysql_query($sql) or die(mysql_error());
?>
<table>
	<tr>
		<th><a href="test.php?sortby=id">id</a></th>
		<th><a href="test.php?sortby=username">username</a></th>
		<th><a href="test.php?sortby=password">password</a></th>
		<th><a href="test.php?sortby=email">email</a></th>
		<th><a href="test.php?sortby=name">name</a></th>
		<th><a href="test.php?sortby=hand_phone">hand_phone</a></th>
		<th><a href="test.php?sortby=active">active</a></th>
		<th><a href="test.php?sortby=expiry">expiry</a></th>
	<tr>
<?php 
  while($row = mysql_fetch_assoc($result){
?>
	<tr>
		<td><?php echo $row['id'] ?></td>
		<td><?php echo $row['username'] ?></td>
		<td><?php echo $row['password'] ?></td>
		<td><?php echo $row['email'] ?></td>
		<td><?php echo $row['name'] ?></td>
		<td><?php echo $row['hand_phone'] ?></td>
		<td><?php echo $row['active'] ?></td>
		<td><?php echo $row['expiry'] ?></td>
	<tr>
<?php
	}
?>
</table>

transfield

Thank you for your reply esukf. I appreciate your patience & initiative. I'm getting a

Parse error: syntax error, unexpected '{'

on this line

while($row = mysql_fetch_assoc($result){

. How do I debug this?

esukf

A bracket was missing from my original code

while($row = mysql_fetch_assoc($result)){

transfield

Thank you very much for your guidance, esukf. The code works beautifully well :-) I appreciate your help.