problem with url link sorting

[scmeeker]

I'm trying to figure out a way to incorporate multiple sorts on my php page. On the page of products, I want users to have three choices of links: one by date, one by price - low to high and one by price - high to low. I know how to perform ASC & DESC sorts in MySql but I'm not sure how to go about this sort with the three choices without creating more pages. I would really like it to stay with the same page. Is this even possible?? How would I go about it?

 

Here is the section of code :

 

 $get_items_sql = mysql_query("SELECT id, thumb, username, sub_id, title, ROUND(price,2) AS price, date FROM product WHERE  CURDATE() <= relist_date AND inactive IS NULL AND sold IS NULL AND cat_id = '1' ORDER BY date ")
		or die(mysql_error());;

            if (mysql_num_rows($get_items_sql) < 1)
            {
                $content = "<p><em>Sorry, no items in this category.</em></p>\n";
            }
            else
            {
			$col = 0;
                $content .= "<ul>\n";
                while ($items = mysql_fetch_array($get_items_sql))
                {
                    $item_url = "items3.php?id={$items['id']}&username={$items['username']}";
                    $item_title = stripslashes($items['title']);
                    $item_price = $items['price'];
                    $item_photo = $items['thumb'];
                $item_username = $items['username'];
                    $item_date = $items['date'];
                    $content .= "";

              list($width) = getimagesize("image_files/{$item_photo}");
  // set the maximum width of the image here
  $maxWidth = 100;
  if ($width > $maxWidth);
             
             
                   $content .= "<table width=\"693\" border=\"0\" class=anotherfont><tr><td width=\"101\"> <a href=\"{$item_url}\">
                    <img alt=\"{$item_title}\" border=0 width=\"{$maxWidth}\" src=\"image_files/{$item_photo}\" /></a><td width=\"200\">      <a href=\"{$item_url}\">{$item_title}</a></td>
                    <td width=\"109\">{$item_username}</td><td width=\"101\"> {$item_date}</td><td width=\"99\">\${$item_price} USD </td></tr></table>";
                    $content .= "\n";

[Andy-H]

Somewhere in the page add the html anchor tags:

 

 

<a href="?order=1">Price (low to high)</a>
<a href="?order=2">Price (high to low)</a>
<a href="?order=3">Age (new to old)</a>

 

 $get_items_query = "SELECT id, thumb, username, sub_id, title, ROUND(price,2) AS price, date FROM product WHERE  CURDATE() <= relist_date AND inactive IS NULL AND sold IS NULL AND cat_id = '1' ORDER BY ";


   switch($_GET['order'])
   {
      case 1:
         $order = "price ASC";
      break;
      case 2:
         $order = "price DESC";
      break;
      default:
         $order = "relist_date DESC";
   }
$get_items_sql = mysql_query($get_items_query . $order);


            if (mysql_num_rows($get_items_sql) < 1)
            {
                $content = "<p><em>Sorry, no items in this category.</em></p>\n";
            }
            else
            {
            $col = 0;
                $content .= "<ul>\n";
                while ($items = mysql_fetch_array($get_items_sql))
                {
                    $item_url = "items3.php?id={$items['id']}&username={$items['username']}";
                    $item_title = stripslashes($items['title']);
                    $item_price = $items['price'];
                    $item_photo = $items['thumb'];
                   $item_username = $items['username'];
                    $item_date = $items['date'];
                    $content .= "";
               
              list($width) = getimagesize("image_files/{$item_photo}");
  // set the maximum width of the image here
  $maxWidth = 100;
  if ($width > $maxWidth);
             
             
                   $content .= "<table width=\"693\" border=\"0\" class=anotherfont><tr><td width=\"101\"> <a href=\"{$item_url}\">
                    <img alt=\"{$item_title}\" border=0 width=\"{$maxWidth}\" src=\"image_files/{$item_photo}\" /></a><td width=\"200\">      <a href=\"{$item_url}\">{$item_title}</a></td>
                    <td width=\"109\">{$item_username}</td><td width=\"101\"> {$item_date}</td><td width=\"99\">\${$item_price} USD </td></tr></table>";
                    $content .= "\n";

 

[scmeeker]

I tried this but now nothing from my database is showing up. It says "Sorry, no items in this category." From this line of code:

            if (mysql_num_rows($get_items_sql) < 1)
            {
                $content = "<p><em>Sorry, no items in this category.</em></p>\n";
            }

 

 

[Andy-H]

Somewhere in the page add the html anchor tags:

 

 

<a href="?order=1">Price (low to high)</a>
<a href="?order=2">Price (high to low)</a>
<a href="?order=3">Age (new to old)</a>

 

 $get_items_query = "SELECT id, thumb, username, sub_id, title, ROUND(price,2) AS price, date FROM product WHERE  CURDATE() <= relist_date AND inactive IS NULL AND sold IS NULL AND cat_id = '1' ORDER BY ";


   switch($_GET['order'])
   {
      case 1:
         $order = "price ASC";
      break;
      case 2:
         $order = "price DESC";
      break;
      default:
         $order = "relist_date DESC";
   }
$get_items_sql = mysql_query($get_items_query . $order)or trigger_error(mysql_error(), E_USER_ERROR);


            if (mysql_num_rows($get_items_sql) < 1)
            {
                $content = "<p><em>Sorry, no items in this category.</em></p>\n";
            }
            else
            {
            $col = 0;
                $content .= "<ul>\n";
                while ($items = mysql_fetch_array($get_items_sql))
                {
                    $item_url = "items3.php?id={$items['id']}&username={$items['username']}";
                    $item_title = stripslashes($items['title']);
                    $item_price = $items['price'];
                    $item_photo = $items['thumb'];
                   $item_username = $items['username'];
                    $item_date = $items['date'];
                    $content .= "";
               
              list($width) = getimagesize("image_files/{$item_photo}");
  // set the maximum width of the image here
  $maxWidth = 100;
  if ($width > $maxWidth);
             
             
                   $content .= "<table width=\"693\" border=\"0\" class=anotherfont><tr><td width=\"101\"> <a href=\"{$item_url}\">
                    <img alt=\"{$item_title}\" border=0 width=\"{$maxWidth}\" src=\"image_files/{$item_photo}\" /></a><td width=\"200\">      <a href=\"{$item_url}\">{$item_title}</a></td>
                    <td width=\"109\">{$item_username}</td><td width=\"101\"> {$item_date}</td><td width=\"99\">\${$item_price} USD </td></tr></table>";
                    $content .= "\n";

[scmeeker]

I did put the HTML links in the page but it still not working and showing the same output of "Sorry, no items in this category."

 

I noticed that your ORDER BY was blank after it. Was that intentional?

 

Thanks for your help.

[scmeeker]

Got it!  Thanks!!