logic issue - 'from' and 'to' dates, and reverse date ordering

I've got a database table that logs payments made to organisations. When a payment is made, two dates are logged: the actual date of the payment and, (because payments are usually made about a month in arrears), the 'To' cutoff date: that is, the payment covers a period from the last payment's 'To' date, up until this date. I hope that makes sense.

In addition, payments are categorised. Thus, the 'last payment' means the last payment of that category.

I need to list these payments. For each row of payment detail, I need to display a 'From' and 'To' date.

If I get a list in ascending date order (ie: oldest first), then the matter is simple. As I proceed through the recordset, I note what the 'To' date is for that payment (and which category it is), and use that as the 'From' date next time a payment in that category crops up.

The client now wants it in descending date order :(

I am stumped.

I can see a number of possibilities; they all involve horrendous multiple-looping though recordsets or building fascinatingly intricate arrays of next-date-for-the-last-date-payment-date, etcetera.

Can anyone whose brain isn't quite so fried suggest a simple, elegant and efficient way of looking ahead a few rows to find the next - that is, the previous - payment 'To' date?

:o:(

It would be very helpful if you could possibly provide the tables structure (could be done with SHOW CREATE TABLE) and the queries which are used to select data in ascending order ... If this is possible of course.

I see that in the querying the DB the application logic is used. Probably it could be excluded. In this case ascending and descending order queries could be very similar... It is just a guess though.

Hi,

As @SKDevelopment said, it'll be better if you provide additional info, but this thread (http://www.codingforums.com/showthread.php?t=189258) maybe helpful too.
Regards

Hi - thanks for replies. Table structure as follows:

CREATE TABLE `payments` (
`idPayments` int(10) unsigned NOT NULL auto_increment,
`idOrganisation` int(10) unsigned NOT NULL,
`paymentAmount` float NOT NULL,
`paymentDate` datetime default NULL,
`paymentToDate` datetime default NULL,
`paymentType` varchar(45) NOT NULL,
`paymentDescription` text,
`paymentMethod` varchar(45) default NULL,
PRIMARY KEY (`idPayments`),
KEY `FK_payments_1` (`idOrganisation`),
CONSTRAINT `FK_payments_1` FOREIGN KEY (`idOrganisation`) REFERENCES `organisations` (`idOrganisation`)


Query SQL is:

SELECT
p.idPayments
, p.paymentDate
, p.paymentToDate
, p.paymentReference
, p.paymentMethod
, p.paymentType
, p.paymentAmount
FROM payments p
WHERE idOrganisation = 123
ORDER BY p.paymentDate DESC

Can you clarify - do you want the payments listed in date order regardless of category? So your problem is that as you access each record in reverse order you don't know the start date yet, whereas doing it forwards you did?

The most obvious answer to me if that is it worked fine in ascending order then do it in ascending order but instead of outputting directly to screen push each row into an array then when you are finished pop them back out of the array to screen?

You don't have to process data in the same order it will be output on screen :)

Of course! :p Why does it seem so simple when someone points out the obvious answer: dump the loop output into an array, and loop backwards through that.

Thank you :)

Often the way - I have the same blind moments and I am sure everyone else does too, if it is any consolation :)