Hi to all
Result of my SQL Query is attached

My Query is

SELECT * FROM $tbl_name WHERE TO_DAYS( NOW( ) ) - TO_DAYS(  `pDate` ) >20
 AND mid(`SiteID`,1,1) ='C' AND  `cDate` LIKE  '0000-00-00' AND  `crDate` LIKE  '0000-00-00'

For Showing I use this Code

echo "<table cellpadding=2 align=center cellspacing=2 width=90% style='font-family:Calibri,Arial' >";

  echo "<tr>";
  echo "<th bgcolor=#666666 width=250px>Site ID</th>";
  echo "<th bgcolor=#666666 width=250px>Code</th>";
  echo "<th bgcolor=#666666>Owner Name</th>";
  echo "<th bgcolor=#666666>Due Month</th>";
  echo "<th bgcolor=#666666>Process Date</th>";

  echo "</tr>";

		while($row = mysql_fetch_array($result))
		{
	if($bgcolor=='#cccccc'){$bgcolor='#ffffff';}
else{$bgcolor='#cccccc';}

  echo "<tr>";
  echo "<td bgcolor=$bgcolor>" . $row['SiteID'] . "</td>";
  echo "<td bgcolor=$bgcolor>" . $row['Code'] . "</td>";
  echo "<td bgcolor=$bgcolor>" . $row['OwnerName'] . "</td>";
  echo "<td bgcolor=$bgcolor>" .date("F",strtotime($row['DurationS'])). "</td>";
  echo "<td bgcolor=$bgcolor>" .date("j-F-Y",strtotime($row['pDate'])). "</td>";

  echo "</tr>";	
  }
  echo "</table>";

I want To Filter Code in ascending and descending order.
Is it possible

Recommended Answers

All 10 Replies

You question is confusing. filtering the result and ordering the result is different thing. we use "where" clause to filter and "order by " clause to order.

Your queries for ordering are as follows for ordering.

ascending
SELECT * FROM $tbl_name WHERE TO_DAYS( NOW( ) ) - TO_DAYS( `pDate` ) >20
AND mid(`SiteID`,1,1) ='C' AND `cDate` LIKE '0000-00-00' AND `crDate` LIKE '0000-00-00' order by code

descending
SELECT * FROM $tbl_name WHERE TO_DAYS( NOW( ) ) - TO_DAYS( `pDate` ) >20
AND mid(`SiteID`,1,1) ='C' AND `cDate` LIKE '0000-00-00' AND `crDate` LIKE '0000-00-00' order by code desc

adding to urtrivedi's answer

if you want the sort to be alterable on the page, output the sorted query data,
and
there are many javascripts that perform live table sorts onclick of the header field, http://javascript.internet.com/forms/sort-data-table.html is one

I want to filter where I show result not via query. Like a filter in Excel which appears in header.

I want this right of Ascending and descending for end users.

Use array_multisort. Look at the examples to see the usage.

I understand array_multisort but I have no idea how to implement in my code.

<?php 
//get which column clicked, if empty, assign first column as default.
$col = $_REQUEST['col'];
if(empty($col)) {
	$col = "siteid";
}
//sort type asc or desc. Defaults to asc
$sort = ($_REQUEST['sort'] == "asc") ? "desc" : "asc";
//display table. I have used href tag to show how it works. href has 2 variables col [column] and sort [sorting order]
echo "<table cellpadding=2 align=center cellspacing=2 width=90% style='font-family:Calibri,Arial' >";
echo "<tr>";
echo "<td bgcolor=#666666 width='250px'><a href='testing.php?col=siteid&sort=$sort'>Site ID</a></td>";
echo "<td bgcolor=#666666 width='250px'><a href='testing.php?col=code&sort=$sort'>Code</a></td>";
echo "<td bgcolor=#666666><a href='testing.php?col=ownername&sort=$sort'>Owner Name</a></td>";
echo "</tr>";
$i =0;
//while loop to assign/fetch values. In your case, mysql_fetch_assoc
while($i < 10) {
	$info['SiteID'] = 134+$i;
	$info['Code'] = 34+$i."a";
	$info['OwnerName'] = "testuser".$i;
	$dataholder[] = $info;
	$i++ ;
}
//in this foreach loop, I am separating every individual clickable column to a separate array. You can do it in the while loop itself. 
foreach ($dataholder as $dataholder_key => $row) {
    $siteid[$dataholder_key]  = $row['SiteID'];
    $code[$dataholder_key] = $row['Code'];
    $ownername[$dataholder_key] = $row['OwnerName'];
}
if($sort == "asc") {
//here, I have used variable variables. ${$col} corresponds to ${siteid} [the default value] or whatever the user has clicked. 
array_multisort(${$col},SORT_DESC,SORT_STRING,$dataholder);
} else {
array_multisort(${$col},SORT_ASC,SORT_STRING,$dataholder);
}

//then displaying the result
foreach($dataholder as $key => $value) {
	$bgcolor = ($bgcolor == '#cccccc') ? '#ffffff' : '#cccccc';
	echo "<tr>";
	echo "<td bgcolor=$bgcolor>" . $value['SiteID'] . "</td>";
	echo "<td bgcolor=$bgcolor>" . $value['Code'] . "</td>";
	echo "<td bgcolor=$bgcolor>" . $value['OwnerName'] . "</td>";
	echo "</tr>";
}
echo "</table>";
 ?>

This is just a quick example on how to use array_multisort. I hope you can make out how it works from this example.

it doesnt make sense to do it any other way but in the query

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.