Results grouped and paginated

[mihail]

Hi guys,

 

I have a pagination problem (yes, I have searched the forums for pagination etc. Very Happy ). 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

[cheesywan]

Hi,

I have used the SQL "SELECT DISTINCT" to do pretty much what you are wanting to do. it works perfict.

1. SELECT DISTINCT 'catagory' FROM 'yourTable' this will give you a list of catagorys.

2. use the mysql_fetch_row() to cycle thru each catagory doing a "SELECT 'item' FROM 'yourTable' WHERE 'catagory' = $catagory.

I'm probably not very clear but maybe this will help.

Old Newguy

 

[mihail]

Well, yeah, that is basic knowledge. But thanks anyway. Now you have to paginate results. The real problem 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.

[Barand]

Use a single query joining product, category tables.

 

SELECT p.product_name, c.category_name

FROM product p INNER JOIN category c ON p.category_id = c.category_id

ORDER BY c.category_name, p.product_name

LIMIT $start, $perpage.

 

Then test for change in category name. If it has changed, output name then output product