Page 1 of 1
[SOLVED] Results grouped and paginated
Posted: Sun May 06, 2007 6:00 am
by eyespark
Hi guys,
I have a pagination problem (yes, I have searched the forums for pagination etc.

). Here is a scenario:
In my database I have a table PRODUCTS with around 1000 records. Each product belongs to a category; there are 18 categories.
table structure:
product_id
category_id
product_name
I want to paginate results (for example let us say 5/page) gruped by categories. So the output could be something like this:
page 1
category chairs
chair 1
chair 2
chair 3
category lights
light 1
light 2
page 2
category lights
light 3
light 4
light 5
light 6
category beds
bed 1
page 3
category beds
bed 2
bed 3
category ....
Could you please help me with that? Thanks
Posted: Sun May 06, 2007 6:34 am
by kaszu
Code: Select all
if (isset( $_GET['page'] ))
$page = intval($_GET['page']);
else
$page = 0;
$count_per_page = 5;
$start = $page * $count_per_page;
//Order by category and limit for pagination
$sql = 'SELECT * FROM products LIMIT '.$start.', '.$count_per_page.' ORDER BY category_id';
$res = mysql_query( $sql );
$results = Array();
if ($res)
{
while ( $row = mysql_fetch_assoc( $res ))
{
$results[] = $row;
}
}
Results will be ordered by category, you must check when current category ends and starts new to display category name.
Posted: Sun May 06, 2007 7:32 am
by eyespark
I figured that much myself, but thanks for your input anyway. The real problem (for me) is that results from one category can be listed on more than one page and the name/id of that category must be displayed on all these pages.
Posted: Sun May 06, 2007 12:54 pm
by kaszu
Then in addition to my previous code:
Code: Select all
$category_name_list = Array( ... ); // List of all category names
$prev_category = -1; // Variable to check if category has changed
foreach ( $results as $item )
{
// If category is not the same as for previous item (or this is first item), then output category name
if ( $item['category_id'] != $prev_category )
{
$prev_category = $item['category_id'];
echo '<b>'.$category_name_list[$prev_category].'</b><br />';
}
// Output item
echo $item['product_name'].'<br />';
}
Posted: Sun May 06, 2007 2:04 pm
by eyespark
Sorry, me again. That is totally confusing to me.

Could you please explain your code. I can't get this to work as expected. Is there any other way? Thank you
Posted: Sun May 06, 2007 3:17 pm
by John Cartwright
The comments are very self explanatory, although the reason it's not working is most likely because you have not ordered by `category_id` in your query. If ordering by category_id is not possible, what you want to do is put the results into a new array, using the category id as the key, then on each new iteration check for the existence of the key against the category id, and if exists simply add to the stack. If not, create a new key.
Posted: Sun May 06, 2007 5:24 pm
by eyespark
Thank you! Solved!
Posted: Thu May 10, 2007 3:51 pm
by eyespark
Khm, sorry to reopen this topic. I have one more question:
Now I have my items sorted by category and paginated as described above. What if I wanted my items inside each category to by futher sorted by name (alphabetically)? Thanks in advance!
Posted: Thu May 10, 2007 5:27 pm
by John Cartwright
You can order by multiple columns

Posted: Fri May 11, 2007 6:31 am
by eyespark
Aw god! Thanks
