[wzeller]

I have a table where each row is a line item of an order. Some orders have just one row, others might have twenty rows or more.

The structure is basically like this: (I'm removing all the extraneous columns for this discussion)

detailnum orderid productid quantity
	 2394   49492	  3912		2
	 2395   49492	  4582		1
	 2396   49492	  1392		4
	 2397   49493	  2921		1
	 2398   49494	  3912		7
	 2399   49494	  1392		4
and so on...

This goes on for hundreds of thousands of rows. Detailnum is the primary key, and if a result set is ordered by detailnum then all the orders "stay together". (There is never a time when two orders would be mixed within their own ranges of detailnum.)

What I want to do is basically a "select * from orders order by detailnum limit 50" where the last records only show up if the next row isn't part of the same order.

In other words, show up to the last 50 records without cutting an order in half. For example, if the above list of values were the entire table and I did a "select * from orders order by detailnum limit 5", then I would get the first line item of order #49494 but not the second one. So I would want it to actually only show the first 4 records. (Or, alternatively, add enough records to complete the last order being shown.)

One analogy to illustrate what I'm trying to do is widow/orphan control in a word processor where it avoids splitting paragraphs across a page. I want to avoid splitting orders across pages.

I'm doing all this in PHP.

Any ideas?

Wayne

[Spider]

You could do this by selecting everything (i.e. don't use a mySQL limit), and then use PHP to display the first 50 results, and then any additional results which are part of the same order as the 50th result.

Alternatively you could select 51 results, and use PHP to remove all of the results with the same order number as the 51st result.

It's really up to you

The PHP for both of these approaches would be relatively simple, but if you have a bash at it and can't figure it out I'd be happy to lend a hand. I've done some similar things with mySQL + PHP myself.

[wzeller]

Thanks for the suggestion.

It actually gave me another idea: I'll loop through the limited result set and pull the highest orderid. Then, when it loops through it again for executing the display code, I'll have it not show records whose ID equal the highest id. In that display code, I'll have it pull the detailnum of each line it successfully displays so that at the end of the loop I'll have the maximum displayed detailnum. Then my "next" link can reference that as a starting place for the next page.

Thanks for kicking my brain into gear!

Wayne