Results sorting

[s0bigg]

I am using a php code to perform search queries through MySQL database. The database consists of a product title, url, posted date and store name. The query is looking at the title and returns relevant results. I want to add a sorting feature on my page that will allow users to sort those results by date and/or store. Its a pretty basic thing, but I am just having hard time to get it to work. Basically if a user searches for iPhone, here's what they would see in sorting options next to the search results

Posted Date
Today (8)
Yesterday (2)
Last week (15)

Store
Apple (3)
Amazon (2)
Microsoft (0) - just kidding, if its zero it shouldn't be here.

So when they click on those individual links, it will kind of sort out and just show those narrowed results to the users. Help me out guys! you are the best. Currently all the results are sorted by date in descending order by default. Here's the code that I am using for search.. Thanks!!

mysql_select_db("fake_db",$con);

$sql = "SELECT DISTINCT * FROM data WHERE (";
while(list($key,$val)=each($split_stemmed ))
	{
              if($val<>" " and strlen($val) > 0)
              {
            
              $sql .= "(title LIKE '%$val%') AND";
              }
	}
              $sql=substr($sql,0,(strLen($sql)-3));
             $sql .= ") ORDER BY pdate DESC";
	$query = mysql_query($sql) or die(mysql_error());
	

	$row_sql = mysql_fetch_assoc($query);
	$total = mysql_num_rows($query);

if (!isset($_GET['startrow']) or !is_numeric($_GET['startrow']))
{
  $startrow = 0;
}
else
{
  $startrow = (int)$_GET['startrow'];
}
$sql .= " limit $startrow,$limit";

$result = mysql_query($sql) or die("Couldn't execute query");
$count = 1 + $startrow;
while ($row= mysql_fetch_array($result))
  {
  $title = $row["title"];
  $link=$row["link"];
  $pdate=$row["pdate"];
	echo '
				
					<h2 class="title"><a href="'.$link.'">'.$title.'</a></h2>
					<div class="meta">
						'.$pdate.'
					</div>
				';
				  $count++ ;
					}
					?>
				</div>
				
				<?php

[s0bigg]

Please help guys!!

[EvolutionFallen]

Not a lot of time to write a full response now, but:

These links should have variables at the end. Then based on the variables, you alter your WHERE and ORDER BY statements. So if the link is say http://www.yourdomain.com/page.php?filter=store&value=apple

<?php
if( isset($_GET['filter']) ) {
   $possible_filters = array('list','of','possible','filters'); // list of allowable filters
   $url_filter = trim( $_GET['filter'] ); // name of filter taken from URL
   if( in_array( $url_filter, $possible_filters ) { // if filter from URL is an expected filter type...
      $value = stripslashes( htmlentities( trim( $_GET['value'] ) ) ); // clean
      // Write your WHERE clause options, possibly in a switch statement. Namely because you have to check for $value's type, as you need to surround it in quotes if it's not a numerical value.
?>

Sorry hope that makes sense, kind of in a rush right now but will take another look later.

[s0bigg]

Thats great!! I had a same thing in mind, the only thing that I am struggling with is how do I get the list in hierarchy. That filter list needs to be dynamic too :)

[diafol]

Easier to get a where clause like this?

$search = $_GET['search'];
if(isset($_GET['operator'])){
   if($_GET['operator'] == 'AND'){
      $operator = 'AND';
   }else{
      $operator = 'OR';
   }
}

$words = preg_split("/[\s,]+/", $search);
$cleanwords = array_map('mysql_real_escape_string',$words);
$where = " `title` LIKE '%" . implode ("%' $operator `title` LIKE '%", $cleanwords) . "%'";

$result = mysql_query("... WHERE$where");

**NOT TESTED - off top of my head**
That should make the search dynamic. Haven't thought of the hierarchy yet. WIll come back if nobody replies.

BTW unless it's obvious, the $_GET is a set of radiobuttons in your form to set OR or AND for multiple search terms.

//EDIT

SELECT provider, COUNT(provider) AS cnt FROM data WHERE$where GROUP BY provider

I think that will give you a list of providers.