problem - how to search by date

[richrock]

I've got a bit of a head scratcher (for me anyway) -

 

I've set up a table to store all the variable rates and charges, as shown in the describe below:

 

+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(11) | NO   | PRI | NULL    | auto_increment |
| vat         | int(11) | NO   |     | NULL    |                |
| vat_date    | date    | NO   |     | NULL    |                |
| export      | int(11) | NO   |     | NULL    |                |
| export_date | date    | NO   |     | NULL    |                |
| visa        | int(11) | NO   |     | NULL    |                |
| visa_date   | date    | NO   |     | NULL    |                |
| amex        | int(11) | NO   |     | NULL    |                |
| amex_date   | date    | NO   |     | NULL    |                |
| photo       | int(11) | NO   |     | NULL    |                |
| photo_date  | date    | NO   |     | NULL    |                |
| illust      | int(11) | NO   |     | NULL    |                |
| illust_date | date    | NO   |     | NULL    |                |
+-------------+---------+------+-----+---------+----------------+
13 rows in set (0.00 sec)

 

And in each product item there is a 'modified' field, which records when it was bought, using DATETIME.

 

Here's a sample output for the VAT:

 

+----+-------+------------+
| id | vat   | vat_date   |
+----+-------+------------+
|  1 | 17.50 | 2008-01-01 |
|  2 | 17.50 | 2009-01-01 |
|  8 | 15.00 | 2009-01-13 |
+----+-------+------------+
3 rows in set (0.00 sec)

 

 

I can extract dates from the product, no probs.  How would I compare the dates in the VAT range, if for example, the item was bought on 2008-09-01, which would fall between id 1 & 2, and then select the latest value?

 

Please help, this is a bit over my head.  Also not sure if this is a PHP thing (would be nice), or a MySQL query problem...

[dawsba]

SELECT `id` FROM `your_table` WHERE `vat_date` > '2008-09-01' LIMIT 1