0

I have this SQL:

$sql="SELECT * 
FROM   table 
WHERE  expiresdate >= Date(Now()) 
       AND expiresdate <= Date_add(Date(Now()), INTERVAL 10 day) 
ORDER  BY expiresdate ASC";

it should basically show all rows in the database that are going to expire within 10 days time however, lets say the expiredate was 2013-03-06 - this row will not display on any day after the expiredate

does anyone have any ideas?

Kermit
  • 33,827
  • 13
  • 85
  • 121

1 Answers1

2

This should be what you need:

SELECT
    * 
FROM
    `table`
WHERE
    expiresdate <= CURDATE() + INTERVAL 10 DAY
ORDER BY
    expiresdate ASC
Michael
  • 11,912
  • 6
  • 49
  • 64
  • Can somebody explain, why the query in question won't work. I understand how the above query works. – robert Mar 07 '13 at 15:09
  • Is your table name actually `table`? If so, you need to escape it as it's a reserved word. – Michael Mar 07 '13 at 15:10
  • no the table name is not table - i was just using this as an example. i will test this code and let you know how i get on - thanks for the responses –  Mar 07 '13 at 15:29
  • By the way, is `expiresdate` a `DATE` field? – Michael Mar 07 '13 at 15:32
  • yeah it is a DATE field - should it not be? i am still getting the same problem :( –  Mar 07 '13 at 15:33
  • What's the problem? You haven't actually explained it clearly. – Michael Mar 07 '13 at 15:34
  • Well - say the expiredate is 2013-03-06 (yesterday) it will display the row from the database 10 days before the expire date but when its actually on the day it doesnt display –  Mar 07 '13 at 15:48
  • I've tested it and it works. If I do the above query it will show those with an expiry date between 2013-03-08 and 2013-03-17 which is expected. What results are you getting? – Michael Mar 07 '13 at 17:01
  • ok so im my database, i have one row with the expiresdate is 2013-03-06 and it is not displaying. this is very odd :/ –  Mar 07 '13 at 17:19
  • That's because it was yesterday. You asked for those that expire between today and 10 days from now. – Michael Mar 07 '13 at 17:20
  • aha sorry - this is my initial question/statement it should basically show all rows in the database that are going to expire within 10 days time however, lets say the expiredate was 2013-03-06 - this row will not display on any day after the expiredate –  Mar 07 '13 at 17:36
  • so basically, i need it to show where the expires date is today + 10 days or if it is in the past –  Mar 07 '13 at 18:06
  • No problem; if you click the tick next to my post you'll mark it as accepted so that others can see that it answered your question. – Michael Mar 07 '13 at 18:42