pagination problem

[samoht]

Hello all,

 

I have run into a small problem - but I am not sure the approach to the solution.

I created a catalog.php page that displayed the products that match my query. At the top of the page I set 2 variables

1 for the number of products to display per page and 1 for the number of products per row

<?php
$productsPerRow = 3;
$productsPerPage = 27;

then I made a limit query and two functions to handel my pagination.

 

The problem is that the display has now changed from products to Items - meaning I am grouping the products together that share the same item and displaying the item. This is a problem for my pagination because I my limit query is simply based on number of rows returned in my query.

 

Any Ideas how I can paginate based on the # of groups of products or unique Items??

 

Hopefully this makes sense.

[MasterACE14]

doesn't make much sense lol, but if you showed us your complete pagination script we may get what you mean 

 

Regards ACE

[samoht]

ok here ya go,

 

here are my paging functions:

<?php
/**************************
    Paging Functions
***************************/

function getPagingQuery($sql, $itemPerPage = 10)
{
    if (isset($_GET['page']) && (int)$_GET['page'] > 0) {
        $page = (int)$_GET['page'];
    } else {
        $page = 1;
    }
   
    // start fetching from this row number
    $offset = ($page - 1) * $itemPerPage;
   
    return $sql . " LIMIT $offset, $itemPerPage";
}

/*
   
*/
function getPagingLink($sql, $itemPerPage, $strGet = '')
{
    $result        = dbQuery($sql);
    $pagingLink    = '';
    $totalResults  = dbNumRows($result);
    $totalPages    = ceil($totalResults / $itemPerPage);
   
    // how many link pages to show
    $numLinks      = 10;

       
    // create the paging links only if we have more than one page of results
    if ($totalPages > 1) {

        $self = 'http://' . $_SERVER['HTTP_HOST'] . $_SERVER['PHP_SELF'] ;

        if (isset($_GET['page']) && (int)$_GET['page'] > 0) {
            $pageNumber = (int)$_GET['page'];
        } else {
            $pageNumber = 1;
        }
       
        // print 'previous' link only if we're not
        // on page one
        if ($pageNumber > 1) {
            $page = $pageNumber - 1;
            if ($page > 1) {
                $prev = " <input type=\"button\" value=\"<\" onClick=\"window.location.href='$self?page=$page&$strGet';\">";// <a href=\"$self?page=$page&$strGet/\" class=\"prev\">Prev</a> ";
            } else {
                $prev = " <input type=\"button\" value=\"<\" onClick=\"window.location.href='$self?$strGet';\">";// <a href=\"$self?$strGet\" class=\"prev\">Prev</a> ";
            }   
               
            $first = " <input type=\"button\" value=\"<<\" onClick=\"window.location.href='$self?$strGet';\">";// <a href=\"$self?$strGet\"class=\"first\">First</a> ";
        } else {
            $prev  = ''; // we're on page one, don't show 'previous' link
            $first = ''; // nor 'first page' link
        }
   
        // print 'next' link only if we're not
        // on the last page
        if ($pageNumber < $totalPages) {
            $page = $pageNumber + 1;
            $next = " <input type=\"button\" value=\">\" onClick=\"window.location.href='$self?page=$page&$strGet';\">";
            $last = " <input type=\"button\" value=\">>\" onClick=\"window.location.href='$self?page=$totalPages&$strGet';\">";
        } else {
            $next = ''; // we're on the last page, don't show 'next' link
            $last = ''; // nor 'last page' link
        }

        $start = $pageNumber - ($pageNumber % $numLinks) + 1;
        $end   = $start + $numLinks - 1;       
       
        $end   = min($totalPages, $end);
       
        $pagingLink = array();
        for($page = $start; $page <= $end; $page++)    {
            if ($page == $pageNumber) {
                $pagingLink[] = " $page ";   // no need to create a link to current page
            } else {
                if ($page == 1) {
                    $pagingLink[] = " <a href=\"$self?$strGet\" class=\"page\">$page</a> ";
                } else {   
                    $pagingLink[] = " <a href=\"$self?page=$page&$strGet\" class=\"page\">$page</a> ";// impliment later with some work on $self<a href=\"$self/$page/$strGet\" class=\"page\">$page</a>";
                }   
            }
   
        }
       
        $pagingLink = implode('', $pagingLink);
       
        // return the page navigation link
        $pagingLink = $first . $prev . $pagingLink . $next . $last;
    }
   
    return $pagingLink;
}

 

and here is the head section of one of my catalog pages (i.e. the query to my db)

<?php
$productsPerRow = 3;
$productsPerPage = 27;

$QFrom         = "FROM product pd, item i, productprice pp, packaging pack, size s, brands b";
$QWhere        = "
WHERE i.ItemId IN
(
    SELECT ItemId
    FROM features f
    INNER JOIN itemfeatures itf ON f.FeatureId = itf.FeatureId
    WHERE f.Name = '$pageTitle'
)
AND i.ItemId IN
(
    SELECT ItemId
    FROM features f
    INNER JOIN itemfeatures itf ON f.FeatureId = itf.FeatureId
    WHERE f.Name = '$spageTitle'
)
AND pd.PackageId = pack.PackageId
AND i.BrandId = b.BrandId
AND pd.SizeId = s.SizeId
AND pd.ItemId = i.ItemId
AND pd.ProductId = pp.ProductId
AND pd.FlagStatus = 'A'
AND ClientPriceCode = '$CPC'";
$QOrderBy    = "ORDER BY i.Name";

# here I add stuff to the query based on user input or limitations etc. I left this out because not needed for you.

$sql = "SELECT i.Name AS ItemName, pd.ItemId as Id, pp.ProductId, pd.Name, pd.PriceRetail, pp.PriceSell, i.ImagetnURL, QtyInStock, pack.PackageType, s.Name as Size
$QFrom $QWhere $QOrderBy";
#gets a little messy here because I am trying to set up the displaying counter
$tresult = dbQuery($sql);
//if($pagging == 1){
$result     = dbQuery(getPagingQuery($sql, $productsPerPage));
$pagingLink = getPagingLink($sql, $productsPerPage, "g=$cgId&c=$catId");
//}else{
//$result = dbQuery($sql);
//}
$numProduct = dbNumRows($result);
$tnum                = dbNumRows($tresult);
// the product images are arranged in a table. to make sure
// each image gets equal space set the cell width here
$columnWidth = (int)(100 / $productsPerRow);

 

and then lastly I set up my loop on the page to group the products together under their Item:

<?php
if ($numProduct > 0 ) {

    $i = 0;
    $group = "";
    while ($row = dbFetchAssoc($result)) {
   
        extract($row);
        if($Id != $group)
        {
         if($group != ""){
            echo "\n\t</p>\n </td>";
                }
               
        if ($ImagetnURL) {
            $ImagetnURL = $path . 'images/item/' . $ImagetnURL;
        } else {
            $ImagetnURL = $path . 'images/no-image-small.png';
        }
   
        if ($i % $productsPerRow == 0) {
            echo "\n<tr>";
        }

        //here we start with Item info.
        echo "\n <td width=\"$columnWidth%\" align=\"center\" valign=\"top\">";
        echo "\n\t<p>";
        echo "\n\t <a href=\"" . $_SERVER['PHP_SELF'] . "?g=$cgId&c=$catId&p=$ProductId" . "\">";
        echo "\n\t <div class=\"pdimg\"><img src=\"$ImagetnURL\"></div>";
        echo "\n\t  <h4>$ItemName</h4></a>";
           
            $i += 1;
            $group = $Id;
        }
        //Now we move on to the multiple products for the item.
        // if the product is no longer in stock, tell the customer
        if ($QtyInStock <= 0) {
            echo "<br>Out Of Stock";
        }
        if ($Size == 'Varies'){
            $Size = '';
        }
        if ($PackageType=='Bulk') {
            $PackageType = "per lb";
        }
       
        // format how we display the price
        $PriceSell = displayAmount($PriceSell);
        if(isset($_GET['page'])){
        echo "\n\t\t<div class=\"cs\">\n\t\t <a href=\"".$_SERVER['PHP_SELF']."?page=".$_GET['page']."&g=$cgId&c=$catId&p=$ProductId\">$Size $PackageType - $PriceSell</a>\n\t\t</div>";
        }else{               
        echo "\n\t\t<div class=\"cs\">\n\t\t <a href=\"".$_SERVER['PHP_SELF']."?g=$cgId&c=$catId&p=$ProductId\">$Size $PackageType - $PriceSell</a>\n\t\t</div>";       
        }
    } //end the loop
    echo "</p></td>\r\n";
    if ($i % $productsPerRow > 0) {
        echo '<td colspan="' . ($productsPerRow - ($i % $productsPerRow)) . '"> </td>';
    }
   
} else {
?>
    <tr><td width="100%" align="center" valign="center">No products in this category</td></tr>
<?php   
}   
?>
</table>
<p align="center"><?php echo $pagingLink; ?></p>

 

There it is!

 

thanks for the help.