Some guidance needed

[dhimok]

Hello everybody.

 

Can you give me a little help on making pagination on the code below? I am having difficulties when page is on top level then it multiplies page Limit to the number of subcats. So if I have Limit 20 and 10 subcats then it lists 200 items. I want 20. Some guidance will be appreciated thanks.

 

<?php

function get_products($id) {
       

        // ''' pagination '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
$rowsPerPage = 20;					               // rows per page
$pageNum = 1;								// by default we show first page
if(isset($_GET["page"])) $pageNum = $_GET["page"];
$offset = ($pageNum - 1) * $rowsPerPage;		     // counting the offset
// ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

$where = "WHERE category_id = '{$id}' ORDER BY product_sort_order";
$result = mysql_query("SELECT * FROM products {$where} LIMIT $offset, $rowsPerPage");

        $total = mysql_num_rows($result); // how many pages we have when using paging?
$maxPage = ceil($total/$rowsPerPage);

while($row = mysql_fetch_assoc($result)) {
extract($row);
$retval[] = $product_id;
}

//get children
  	$result = mysql_query("SELECT category_id FROM categories WHERE parent_id = {$id}");
  	while ($row = mysql_fetch_assoc($result)) {
    	$retval[] = get_products($row["category_id"]);
	}

return $retval;
}

function flatten_array($value, $key, &$array) {
    if (!is_array($value))
        array_push($array,$value);
    else
        array_walk($value, 'flatten_array', &$array);
}

$array = get_products($_GET['catID']);
$newarray = array();
array_walk($array, 'flatten_array', &$newarray);

foreach($newarray as $key => $value) {
$result = mysql_query("SELECT * FROM products WHERE product_id = '$value'");
$row = mysql_fetch_object($result);
echo '<h3>'.$row->product_name.'</h3>';
}
?>

[Yesideez]

First I can't see you getting the total number of rows as if page mode was turned off.

 

You need this number before you can calculate how many pages there will be.

[dhimok]

Well I did some modifications to the code above. Take a look now

[Yesideez]

In an include somewhere (like where you connect to the database? Define a constant saying how many items per page.

define("INT_PAGESIZE",20);

 

Then we calculate how many pages there are:

  $intTotalMatched=mysql_num_rows(mysql_query("SELECT COUNT(*) FROM `table`"));
  $intTotalPages=ceil($intTotalMatched/INT_PAGESIZE);
  $intStart=(($intPage*INT_PAGESIZE)-INT_PAGESIZE);

$intPage is the current page number

 

Then call the MySQL code with the LIMIT parameters to pageinate:

$row=mysql_fetch_assoc(mysql_query("SELECT * FROM `table LIMIT ".$intStart.",".INT_PAGESIZE));

 

To create an index simply make a for() loop counting from 1 to $intTotalPages

 

EDIT: I mixed single and double quotes in define() - fixed.

[Yesideez]

Just in case you want/need it, here's the rest of the script to make the page index:

        <table width="870" cellspacing="1" cellpadding="0" class="tblmain">
          <tr>
            <td width="30"><?=($intPage>1 ? '<a href="applications.php?page='.($intPage-1).'"><<<</a>' : '<<<')?></td>
            <td><?php for ($i=1;$i<=$intTotalPages;$i++) {echo ($i==$intPage ? '<strong>'.$i.'</strong> ' : '<a href="applications.php?page='.$i.'">'.$i.'</a> ');} ?></td>
            <td width="30"><?=($intPage<$intTotalPages ? '<a href="applications.php?page='.($intPage+1).'">>>></a>' : '>>>')?></td>
          </tr>
        </table>

[sushant_d84]

For your Info....

 

 

Your Query should contain the clause as LIMIT 0,20 

 

select * from tab1 LIMIT 0,20

 

for next page u r query should be

 

select * from tab1 LIMIT 21,39

 

and so on...

 

 

Lets try this and let me know if this useful for you...

 

 

Regards

Sushant

[dhimok]

If you have a look at the function get_products(), you will see that it loops thru children so it adds the Limit to any children that finds. That multiplies the Limit to the number of children. That is my problem. If it was one level loop that will work fine. Thats where I am stuck. Its recursive. I don't know if I am clear enough, hope so