Some guidance needed

[dhimo]

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.

<?phpfunction 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>';}?>

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

[justsomeguy]

You'll need to keep track of how many you have in the array already, and stop when you hit the limit. Also, instead of getting all the IDs in the function, and looping through the array of IDs to get the details for each one, just save everything the first time and skip the second lookup, it will cut down a lot on how much time and memory this takes to run.There's a question you need to answer though, if the limit is 20 should it get the first 20 categories, or the first 20 items from the first category, or the first 7 items from the first category, then 8 items from the second, then 5 from the third, or how do you wan it to work? What are you limiting? Are you limiting the number of categories or the number of products?

[dhimo]

Thanks for the good advices. I am limiting the number of products. I guess is best to get the first items from any subcategory so that they sum 20. Not sure what s the best way

[justsomeguy]

Have the array containing the records be a global array, and each time you add a record to it check the length. Also, get rid of the queries in the loop, and just save the entire row in the array so that you don't have to do so much with the database. And the array flattening function isn't necessary for this situation.

<?phpfunction get_products($id) {  global $products;  // ''' 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  // ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''  if (count($products) >= $rowsPerPage)	return;  $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_object($result) && count($products) < $rowsPerPage) {	$products[] = $row;  }  //get children  if (count($products) < $rowsPerPage)  {	$result = mysql_query("SELECT category_id FROM categories WHERE parent_id = {$id}");	while ($row = mysql_fetch_assoc($result) && count($products) < $rowsPerPage) {	  get_products($row["category_id"]);	}  }}global $products;$products = array();get_products($_GET['catID']);foreach ($products as $product) {  echo '<h3>'.$product->product_name.'</h3>';}?>

[dhimo]

I test it and I dont get any results. I print_r($products) and i get this:Array( [0] => 1 [1] => 1 [2] => 1 [3] => 1 [4] => 1 [5] => 1 [6] => 1 [7] => 1 [8] => 1 [9] => 1 [10] => 1 [11] => 1 [12] => 1 [13] => 1 [14] => 1 [15] => 1 [16] => 1 [17] => 1 [18] => 1 [19] => 1)is there something missing?

[justsomeguy]

Yeah, ambiguous parens. Change this line:while($row = mysql_fetch_object($result) && count($products) < $rowsPerPage) {to this:while(($row = mysql_fetch_object($result)) && (count($products) < $rowsPerPage)) {and this:while ($row = mysql_fetch_assoc($result) && count($products) < $rowsPerPage) {to this:while (($row = mysql_fetch_assoc($result)) && (count($products) < $rowsPerPage)) {

[dhimo]

Ok, where to put the pagination nav. Inside the function or outside. Do u have any ideas about this

[justsomeguy]

What do you mean?

[dhimo]

Hello, again. I am bringing back this topic because of a pagination and order by problem.The problem is that if I query the db and ORDER BY price ASC then because of different children then i will get mixed values. First it selects the first child ORDER BY price ASC and so on and in the middle of pagination higher values appear. How can I fix that thanks

[justsomeguy]

I don't understand the problem, if you're using order by the order won't change.

[dhimo]

if a category has children then it orders by1st child2nd child3rd childetc.and it keeps this order even if order by price ascso if 1st child has these prices: 12, 34, 45and 2nd child has these prices: 5, 14, 40and 3rd child has these prices: 34, 140, 240then the order will be like this when still on parent categories:1234455144034140240while it should have been5121434344045140240so thats my problem.I was wondering if it would be possible to use pagination and limit based on array_chunk and array_slice. would that be possible. Like first flatten the array of products and then chunk it. Or it will take to much memory

[justsomeguy]

The first way makes sense to me. It wouldn't make sense to me for the order to be "2nd child", "1st child", "2nd child", "1st child", "3rd child", "2nd child" etc, it makes more sense to me to have all of the children grouped under their parents. You can do whatever you want though, PHP doesn't place limits on things like this, like if it is "possible" to do something like this. If you can design it, then you can do it. You can sort the array if you want it in a different order then what you're getting from the query.

[dhimo]

Ok, I am trying to get total count from table products by using$total = mysql_result(mysql_query("SELECT count(*) FROM products WHERE category_id = '{$id}'"), 0, 0);but I dont get the right count if on a parent idhow can I fix that

[justsomeguy]

That query will select the number of products that match a certain category id, is that what you want to do? If there are products in the database that are in a child category of that id, the query won't automatically add all those up also, all you're telling it is to get the number of elements where the category_id field matches a certain id. If you want to get all products inside all child categories regardless of how many there are you will need a recursive function to add everything up.

[dhimo]

Yeah, thats what I was hoping to get some help about hehe

[justsomeguy]

Well, the get_products function above is a recursive function that gets all of the products in a certain category, including child products. You can take the result of that and re-order it however you want it. You can use the usort function or another user-defined sort function to do that.http://www.php.net/manual/en/function.usort.php