Filtering Items by category

[stef_charle]

Hi guys

 

I am new to the forums and the php world is also pretty new to me so I need a bit of help.......

 

I have a script that is going to work similar to a classified ad website (craigslist, kijiji) but pretty basic overall. I am able to display the results with pagination and even have links that will filter the results by category (sports, entertainment etc....). Currently the script is checking the $_GET['category'] and an if statement to figure out the query it should use to get the desired results. Please see the code below

 

if (isset($_GET['category'])){
   $getListing = "SELECT * FROM coupon WHERE Category = '$category' LIMIT $offset, $rowsperpage";
} else {
   $getListing = "SELECT * FROM coupon LIMIT $offset, $rowsperpage";
}

 

So basically if the category is set return anything that is in that category from the database, if not return all results from the database. This works fine but the issue comes in when a person clicks to go to the next page. When the next button is clicked its returning no results because the category is showing up as blank. The code for the next page is below :

 

echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage&category=$category'>Next</a> ";

 

Now I know the first thing you probably would think is $category must have no value but it is actually set at the top of the script using

$category = $_GET['category'];

 

I apologize as I am having trouble making this completely clear. If this doesn't make sense or seems like a bad way of accomplishing this let me know.

 

Thanks

[phpSensei]

Thats because sometimes the total number of results returned by the query is much less than the offset

 

if $offset exceeds $rowsperpage, than you will get a blank or mysql error.

[stef_charle]

I definitely understand what your saying but in this case I don't think its the issue because there are more then 1 page of results for each category. This is definitely something that could come up later which is good to keep in mind.

 

It seems like on the Next / Prev links the category isnt site in the address bar ......its showing as

 

http://www.somesite.com/dbtest/viewCoupon.php?currentpage=2&category=

 

So for whatever reason the category isn't being passed .....Anyway I figured maybe I should just post the entire script.....here it is

 

<?php
$username = "administrator";
/* $username = mysql_real_escape_string($username); */
$password = "password";
/* $password = mysql_real_escape_string($password); */
$hostname = "localhost"; 
/* $hostname = mysql_real_escape_string($hostname); */

//connection to the database
$dbhandle = mysql_connect(localhost, $username, $password) 
or die("Unable to connect to MySQL");


//select a database to work with
$selected = mysql_select_db("dbtest",$dbhandle) or die("Could not select database");
  
// find out how many rows are in the table 
if (isset($_GET['category'])){
   $category = $_GET['category'];
   $calcRows = "SELECT COUNT(*) FROM coupon WHERE Category = '$category'";
} else {
   $calcRows = "SELECT COUNT(*) FROM coupon";
} 


$result = mysql_query($calcRows, $dbhandle) or die("error with mysql query for calcRows");
$r = mysql_fetch_row($result);
$numrows = $r[0];


// number of rows to show per page
$rowsperpage = 5;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);


// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   // cast var as int
   $currentpage = (int) $_GET['currentpage'];
} else {
   // default page num
   $currentpage = 1;
} // end if



// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
} // end if

// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;



// Check to see what query to use ....are we returning all results or are they filtered
if (isset($_GET['category'])){
   $getCoupons = "SELECT * FROM coupon WHERE Category = '$category' LIMIT $offset, $rowsperpage";
} else {
   $getCoupons = "SELECT * FROM coupon LIMIT $offset, $rowsperpage";
} 


$result = mysql_query($getCoupons, $dbhandle) or die("Error with getCoupons query");

echo " <div id='container'>";

//fetch tha data from the database 
while ($row = mysql_fetch_array($result)) {

$ID = $row{'Coupon_ID'};
$Company = $row{'Company'};
$Category = $row{'Category'};
$Description = $row{'Description'};
$Email = $row{'OwnerEmail'};
$Expiry = $row{'ExpiryDate'};
$ImageLink = $row{'ImageLink'};
    
   		 
  	echo "<div id='couponCont'>
    		<div id='couponImage' >
		<img src='$ImageLink'></img>
    		</div>	
	 <div id='couponInfo'>
	  <p><b>$Company</b><br>
	    $Category<br>
	 	$Description<br>
		$Email<br>
		$Expiry<br></p>
		<a href = 'contactSeller.php?id=$ID' rel='gb_page_center[560,550]'>Contact Seller</a>
		</div>
	 </div>

	 ";

}

$range = 3;

echo "<div id = 'category'>
  <a href='{$_SERVER['PHP_SELF']}?category=Food'>Food</a>
  <a href='{$_SERVER['PHP_SELF']}?category=Sports'>Sports</a>
          <a href='{$_SERVER['PHP_SELF']}?category=Entertainment'>Entertainment</a>
  </div>";

echo "<div id= 'pageNav'>";

// if not on page 1, don't show back links
if ($currentpage > 1) {
   // show << link to go back to page 1
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1&category=$category'><<</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage&category=$category'>Prev</a> ";
} // end if 

if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page 
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage&category=$category'>Next</a> ";
   // echo forward link for lastpage
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages&category=$category'>>></a> ";
} // end if

echo "</div>
  
	  </div>";


//close the connection
mysql_close($dbhandle);
?>

 

 

I'm sure theres alot of suggestions people can give.....This is one of the first scripts I've ever written....Hopefully this makes the problem more obvious

[phpSensei]

Do a mysql_error at the end of your script

 

die(mysql_error());

 

 

and turn on you error_reporting.

[Drummin]

I don't see the problem in your script, but this works just fine.

<?PHP
if (isset($_GET['category'])){
   $category = $_GET['category'];
} 
echo "<div id = 'category'>
  <a href='{$_SERVER['PHP_SELF']}?category=Food'>Food</a>
  <a href='{$_SERVER['PHP_SELF']}?category=Sports'>Sports</a>
      <a href='{$_SERVER['PHP_SELF']}?category=Entertainment'>Entertainment</a>
  </div>";
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1&category=$category'>Test</a> ";
?>