count categories

[zzz]

I have a query that outputs items details. Each item is associated with a category. There is a limit of results I can output, because I can only use a certain number of lines on a page.

When I print the results on the page I print a category name, as a title, just once above the items in that category.

Depending on the number of of items in each category I may end up with a different number of categories, which changes the number of lines on a page.

How do I count how many DISTINCT categories are in my results and adjust the LIMIT accordingly.

$sql = ("
	SELECT * FROM myTable
	WHERE memberID= ".$ID." 
	ORDER BY categoryID ASC ");

$limit = 20;

$myResults = $db->get_results($sql.$limit)

It seems like some sort of chicken and the egg problem. Above if what I currently have. It assumes there's only one category 20 + 1 = 21 lines. I need to be able to keep the same number of lines even if the number of the categories shown (unique) is different.

[laserlight]

Instead of retrieving the entire result set in one go, you could iterate over it, and stop when the total number of lines reaches the limit.

[zzz]

Sounds good, but I'm not sure I quite know how to do that. Yes, ideally I need to be able to set a limit and be done, but how do I count the categories that will be part of the output?

For example what I have now looks something like this

itemID | categoryID | memberID

and results could be

1 | 44 | 5
2 | 44 | 5
3 | 77 | 5
4 | 88 | 5
5 | 88 | 5

So in this example I'll have 8 lines.

5 items
3 distinct categories

Also, I need to be careful and not get just another category if it is the last line, because it will not be followed by any items...

[laserlight]

What I have in mind is an algorithm along these lines:

line_count = 0
current_category = none
while line_count < limit and there is a row to retrieve
    if current_row's category is not equal to current_category
        if line_count + 1 < limit
            current_category = current_row's category
            print current_category
        else
            terminate loop
    print current_row
    increment line_count

[zzz]

I sort of started this way (http://phpbuilder.com/board/showthread.php?t=10374960) but was not able to terminate the loop for some strange reason. Then someone suggested going MySQL LIMIT rout and that's what I did. It did help me improve my previously used code but now I'm back in the corner. I'm starting to think that I may need to to run a first query where I will run a loop similar to what you suggest, count rows and categories, and based on that set a new LIMIT and run another query to get exact results. It is not the most efficient way but I can live with it since it is a script not very often used and is designed to build a PDF doc.

[laserlight]

I suggest that you just try again. Perhaps you made some mistake in your earlier code. Now that you are more properly performing a join and order by category, things may be a little different.