[SOLVED] Results grouped and paginated

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
eyespark
Forum Commoner
Posts: 50
Joined: Tue Jan 24, 2006 7:36 am

[SOLVED] Results grouped and paginated

Post by eyespark »

Hi guys,

I have a pagination problem (yes, I have searched the forums for pagination etc. :D ). 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
Last edited by eyespark on Fri May 11, 2007 6:31 am, edited 3 times in total.
User avatar
kaszu
Forum Regular
Posts: 749
Joined: Wed Jul 19, 2006 7:29 am

Post 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.
eyespark
Forum Commoner
Posts: 50
Joined: Tue Jan 24, 2006 7:36 am

Post 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.
User avatar
kaszu
Forum Regular
Posts: 749
Joined: Wed Jul 19, 2006 7:29 am

Post 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 />';
}
eyespark
Forum Commoner
Posts: 50
Joined: Tue Jan 24, 2006 7:36 am

Post by eyespark »

Sorry, me again. That is totally confusing to me. 8O Could you please explain your code. I can't get this to work as expected. Is there any other way? Thank you
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
eyespark
Forum Commoner
Posts: 50
Joined: Tue Jan 24, 2006 7:36 am

Post by eyespark »

Thank you! Solved!
eyespark
Forum Commoner
Posts: 50
Joined: Tue Jan 24, 2006 7:36 am

Post 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!
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Code: Select all

.. ORDER BY category, name
You can order by multiple columns :wink:
eyespark
Forum Commoner
Posts: 50
Joined: Tue Jan 24, 2006 7:36 am

Post by eyespark »

Aw god! Thanks :oops:
Post Reply