0

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

Attachments Result.jpg 143.65 KB
5
Contributors
10
Replies
11
Views
7 Years
Discussion Span
Last Post by SKANK!!!!!
Featured Replies
  • 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, [url]http://javascript.internet.com/forms/sort-data-table.html[/url] is one Read More

0

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

0

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

0

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

0
<?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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.