Getting Ghost Pages with Page Count Code

[sailorsmokey]

Hi Everyone,

 

I have an ecommerce store that I have done some coding for, and each category has some coding to create pages to display items in that category.  For some reason, I am getting a couple of "ghost" pages on the end of a couple categories.

 

For example, look at this page:  http://www.autismcommunitystore.com/view.php?category=4

 

Pages 5 & 6 are blank - page 4 has the last of the items in the category.  I have a table called prods_to_cats which connects the products and the categories (a many to many relationship).

 

I'm posting the code here - could someone just let me know if they spot something that looks like trouble?

 

Thank you in advance for your time!

 

<?
$category=$_REQUEST['category'];
$showcategory=$category;

include 'connect.php';

$thiscategory = mysql_query("SELECT * FROM category WHERE category_id=$showcategory", $dbh);
$thisrow = mysql_fetch_array($thiscategory);
$this_des=$thisrow["category_description"];
$this_text=$thisrow["category_text"];

$titlekey="Products listed in $this_des at Autism Community Store 877-422-5932";


// If current page number, use it
// if not, set one!

if(!isset($_GET['page'])){
    $page = 1;
} else {
    $page = $_GET['page'];
}

// Define the number of results per page
$max_results = 40;

// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results);

echo  '<div id="product">';
$result = mysql_query("SELECT DISTINCT product.id, product.* FROM prods_to_cats INNER JOIN product ON prods_to_cats.id=product.id WHERE prods_to_cats.category_id=$showcategory ORDER BY product.title ASC LIMIT $from, $max_results", $dbh);
$numresult = mysql_num_rows($result);
if($numresult==0)
{
echo "<p style='padding-left: 50px'>There are no products listed in this category.</p>\n";
}
else
{

$thiscategory = mysql_query("SELECT * FROM category WHERE category_id=$showcategory", $dbh);
$thisrow = mysql_fetch_array($thiscategory);
$this_des=$thisrow["category_description"];
echo "<table width='100%'><tr>";

echo "<div style='position:relative; left:150px;'><font size='2'>Return To: <strong><a href='http://www.autismcommunitystore.com'>Home </a></strong></font></div><br/>";

echo "<div style='position:relative ; width:100%' align='center'>";
echo "<br />";
echo "<img src='http://www.watchesandthings.com/autism/images/suppliesHeader.gif'>";
echo "</div>";
echo "<h2>";
echo "<div style='padding-right:20px ; position:relative ; width;100%; text-align:right;'>";
echo "<div style='position:relative ; width;80%; text-align:center; left:20%;'>";
echo $this_des;
echo "</div>";
echo "</div>";
echo "</h2>";
echo "<div style='position:relative ; padding-left:30px ; padding-right:30px ; '>";
echo "<p>";
echo "$this_text </p>";
echo "</div>";
echo "</tr>";

$count = 1;
$column = 1; //initialize column 1 or 2


//-----------------------code for drop down menu----------------------------------------
include 'browserChecker.php';

/*
echo $browser;
echo "<br/>";
echo $browser_version;
echo "<br/>";
$main_version=$browser_version[0];
echo $main_version;
*/

if ($browser=='IE' && $browser_version<7){
echo '<br />';
	//make version 6 compatible select box
	if ($category==1 || $category==24){
}else{
	?>

<center>
<div id="hiddendiv" style="display:;">
  <form method="get" enctype="multipart/form-data" action="http://www.autismcommunitystore.com/viewsubcategory.php">
  <select id="myselectbox" name="subcategory" >
  <?
$sql="SELECT * FROM subcategory ORDER BY sub_description ASC";
$result=mysql_query($sql, $dbh);

while($myrow=mysql_fetch_array($result))
{
if ($myrow['category_id']==$category){

$myrow['sub_description']

?>
    <option name="subcategory" value="<? echo $myrow['sub_id'] ?>"  ><? echo $myrow['sub_description'] ?> </option>
     <?
}//close if for category check
}//close drop down while loop

?>
</select>
  </center>
  <div id="go_subcat">
  <input type="submit"  value="  GO  " style="background-color: #E4DFF3; color: #6F5079; border: 2px solid #6F5079">
  </div>
  </form>
</div>
<?
}//end check category

	}else{
	//make normal select box


if ($category==1 || $category==24){
}else{
?><div style="z-index:2"><br /><form method="get" enctype="multipart/form-data" action="http://www.autismcommunitystore.com/viewsubcategory.php">					
  <div align="center" style='z-index:5'>
  <select name="subcategory" size="1" style="background-color: #ffffff; color: #6F5079; border: 2px solid #6F5079">
    <?
$sql="SELECT * FROM subcategory ORDER BY sub_description ASC";
$result=mysql_query($sql, $dbh);

while($myrow=mysql_fetch_array($result))
{
if ($myrow['category_id']==$category){

?>
    <option name="subcategory" value="<? echo $myrow['sub_id'] ?>"  ><? echo $myrow['sub_description'] ?>  </option>
     <?
}//close if for category check
}//close drop down while loop

?>
  </select>
  <input type="submit"  value="  GO  " style="background-color: #E4DFF3; color: #6F5079; border: 2px solid #6F5079">
  </div>
</form>
</div>

<?
}

}//end browser checker if


//-----------------------end code for drop down menu----------------------------------------


//reste variables
$result = mysql_query("SELECT DISTINCT product.id, product.* FROM prods_to_cats INNER JOIN product ON prods_to_cats.id=product.id WHERE prods_to_cats.category_id=$showcategory ORDER BY product.title ASC LIMIT $from, $max_results", $dbh);





echo "<TABLE cellspacing='20'>";
while($myrow = mysql_fetch_array($result))
{

extract($myrow);
$location=$image;
$price=$sale_price;
$item=$myrow["id"];
$stockstatus=$stat;
if($stat=='IN STOCK')
{
$stockstatus="";
}
else
{
$stockstatus="--Item Coming Soon--";
}
if($price>0)
{
$printout="<table border=0 height=70 width=250><tr><td width='100px' align='center' ><a href=http://www.autismcommunitystore.com/item/$item/><img src=http://www.watchesandthings.com/autism/files/thumbnails/$location border=0 alt=$product_id></a></td><td><a href=http://www.autismcommunitystore.com/item/$item/>$title</a><br><span class=prod-dollar>$$price</span><br>$stockstatus</td></tr></table>";
}
else
{
$printout="<table border=0 height=70 width=250><tr><td width='100px' align='center' ><a href=http://www.autismcommunitystore.com/item/$item/><img src=http://www.watchesandthings.com/autism/files/thumbnails/$location border=0 alt=$product_id></a></td><td><a href=http://www.autismcommunitystore.com/item/$item/>$title<br /><span class=prod-dollar>Click for Pricing</span></a><br>$stockstatus</td></tr></table>";
}
if ($column=="1")
{
echo "<tr><td>$printout</td>"; // first column
}
else {
echo "<td>$printout</td></tr>"; // 2nd column
}
// end of columns
$count += 1;
// this is a modulus operator it gets the remainder of the equation
$column = $count % 2;
} // end of while loop
echo "</table>";
;

// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM prods_to_cats WHERE category_id=$showcategory"),0);
//$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM product"),0);

// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);

echo "<p><center>Pages<br />";

// Build Previous Link
if($page > 1){
    $prev = ($page - 1);
    echo "<a href='http://www.watchesandthings.com/autism/view.php?category=$showcategory&page=$prev'><<Previous</a> ";
}

for($i = 1; $i <= $total_pages; $i++){
    if(($page) == $i){
        echo "$i ";
        } else {
             echo "<a href='http://www.watchesandthings.com/autism/view.php?category=$showcategory&page=$i'>$i</a> ";
        }
}

// Build Next Link
if($page < $total_pages){
    $next = ($page + 1);
    echo "<a href='http://www.watchesandthings.com/autism/view.php?category=$showcategory&page=$next'>Next>></a>";
}

echo "</center>";

}//end of else>

echo "</table>";
echo "</div>";
?>

[gizmola]

In a nutshell what you're doing is a bad idea.  You have one query you are paginating and an entirely different query you use to create the page numbers.

[sailorsmokey]

gizmola,

 

Thanks for the input.  I guess I don't really understand what's wrong with the way it is, with the two queries.  Any expansion on that comment would be appreciated.

 

But your comment did lead me to find the issue.  So the pagination query is

 

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM prods_to_cats WHERE category_id=$showcategory"),0);

 

Now, the table prods_to_cats has the following columns:

 

id int(11)

category_id int(11)

sub_id int(11)

sub_id_2nd int(11)

 

I realized that I have it set up to list each product in there for any different combination of subcategories.  So I have some products that are in two or more different subcategories of the same main category.

 

One entry I found listed category_id as 4, sub_id 112  and then the same product in category_id 4, sub_id 103

 

The query would count this twice because it's only counting the category_id field......

 

I think I need some sort of DISTINCT command or something......not distinct category_id.....

 

is there a way to select a distinct combination of id and category_id?  If someone knows off the top of their head, please do share - meanwhile, I am googling......

 

*update*  I think maybe I need an Inner Join?  Could someone help me understand, as I only kind of understand an inner join......  Also, I think a mod may need to move this topic to the SQL forum, as the topic is shifting to SQL rather than PHP

[gizmola]

The first answer is, that your query that determines the overall result set you're going to paginate should just be a count(*) of the query you are then going to paginate using LIMIT.  Any difference between the queries opens the door for the pagination to be off, because the number of rows in each of the different queries could vary, and in your case that is my best guess at your problem.

 

If you have a query where you do SELECT DISTINCT id, category_id FROM ... you will already only get a row for each DISTINCT combination of id + category_id.  If you needed to get a COUNT() of that same query it is important that you do a  SELECT COUNT(DISTINCT id, category_id) FROM.... so that you get the same count of rows as the actual query will produce.

 

An inner join creates a row anytime there is a match for values in the join column.  For example:

 

table_a

-------

id: 2

name: red

-----------

id: 3

name: blue

------------

id: 4

name: green

------------

 

 

And you have :

 

table_b

-------------

id: 1

name: apple

color_id: 2

------------

id: 2

name: apple

color_id: 4

------------

id: 3

name: berry

color_id: 3

 

And you join these tables together on table_b.color_id = table_a.id, then you should expect to get 3 rows in your result set:

 

apple - red

apple - green

crayon - blue

 

Let's say you also have one more row in table_b

 

id: 4

name: car

color_id: 5

 

You run the same inner join again, you will get the same results set as before, even though there are 4 rows in table_b.  Because there is no match for color_id in the table_a, a row will not be produced.

[sailorsmokey]

Gizmola,

 

Thank you for all the help.  This clarified some things for me, although I don't yet have that "aha moment" of fully grasping the joins.  (Meaning I have to reread your comments in order to sort through things, and I couldn't yet explain this to someone else.....).  Your comment on the two queries does make logical sense - I'm just a little behind the curve ball, in that the 2 queries are more clear to me.  That being said, I think I need to do some more learning on queries again, as it would also be more efficient to have one query rather than two.

 

I will work on studying up on queries some more. 

 

Meanwhile, we can mark this particular problem as solved!

 

Thank you again!