PHP and MySql with date problem...?

In my project I am using a table named "Compensation" like.....

+--------------------------------------------------------------+
| Id  |  receiver_Id   |  compensation   |      date           | 
|--------------------------------------------------------------|
| 1   |  5             |  50%            | 2011-02-15 12:15:00 | 
| 2   |  3             |  40%            | 2011-04-05 18:35:00 |
| 3   |  3             |  30%            | 2011-04-25 06:24:00 | 
| 4   |  5             开发者_如何学JAVA|  45%            | 2011-04-21 19:05:00 |
| 5   |  5             |  60%            | 2011-04-30 12:05:00 |
.......................

Here the date represents that the compensation is changed on that particular date. For receiver 5, the compensation is 50%before Feb 15 2011. And the compensation is45%fromthe date15 Feb 2011 12:15:01 to 21 April 2011 19:05:00`. And so on....

Here When I create invoice for the month APRIL 2011 for the receiver 5, I have to use the compensation as 45% till date 21 April 2011 and for 22 April 2011 to 30 April 2011 I have to use 60% as compensation...

But How to get the compensation for a month or between 2 dates since the compensation may be modified multiple times in a month as id 4 and 5 shows.......

Please help me writing SQL for the above OR I have to make changes on the table structure to make it simple......?

Thanks in advance

This would be much easier when your table tracked both valid-from and valid-to dates:

+--------------------------------------------------------------+---------------------+
| Id  |  receiver_Id   |  compensation   |      date_from      |      date_to        |
|--------------------------------------------------------------|---------------------|
| 1   |  5             |  50%            | 2011-02-15 12:15:00 | 2011-04-21 19:05:00 | 
| 2   |  3             |  40%            | 2011-04-05 18:35:00 | 2011-04-25 06:24:00 | 
| 3   |  3             |  30%            | 2011-04-25 06:24:00 | 0000-00-00 00:00:00 | 
| 4   |  5             |  45%            | 2011-04-21 19:05:00 | 2011-04-30 12:05:00 | 
| 5   |  5             |  60%            | 2011-04-30 12:05:00 | 0000-00-00 00:00:00 |
.......................

Then you can query:

SELECT * FROM compensations
    WHERE (date_to > $start_of_month OR date_to = 0 )
    AND date_from < $end_of_month;

the first sql will fetch the current month/year

the 2nd sql shows it hardcoded with a specific month/year

SELECT * FROM compensations
    WHERE YEAR(date) = YEAR(NOW()) AND MONTH(date) = MONTH(NOW());


SELECT * FROM compensations
    WHERE YEAR(date) = '2011' AND MONTH(date) = '4';

actually your date column looks more like date-time type not date but:

select * from yourtable where date between 'yourfirstdate' and 'yourlastdate'

however be aware that if you specify narrow gap in dates then your query can't return any results (even with Sander Marechal original solution) so a better option would be to check on larger gaps or always query 10 newest rows and apply the date filtering in code level.