[Wolves] mysql select help

Mark Rogers mark at quarella.co.uk
Mon Mar 12 16:45:16 UTC 2012


On 08/03/12 13:11, Wayne Morris wrote:
> The problem is I don't know the next date (it is a timestamp) and I don't 
> want the stuff AFTER the date, its basically a simple
> calendar preview - your next day with stuff to do is this date, and contains 
> these items eg
>
> Fri 9th apples, pears , bananas
> tues 16th eggs, beans
> thurs 18th turkey, ham
>
> I just want to click and get "apples etc" if I click on thurs 8th., and if 
> clicked on sat 1Oth get eggs,beans, with your first I would get all (as i do 
> with current set up),
> or i'd need to put a date in a search box (which i've also got).
(and also)
> Sorry, just checked my database and i was thinking of another field which 
> was a timestamp - this is a simple 'date' field. 

     SELECT mydate
     FROM mytable
     WHERE mydate > DATE_FORMAT(NOW(), '%Y-%m-%d')
     ORDER BY mydate ASC
     LIMIT 1

.. would get you the date you want (ie the next day something happens). (I 
think you would get away with just "WHERE mydate > NOW()", you'd have to try it.)

So:

     SELECT * FROM mytable
     WHERE mydate = (
         SELECT mydate
         FROM mytable
         WHERE mydate > DATE_FORMAT(NOW(), '%Y-%m-%d')
         ORDER BY mydate ASC
         LIMIT 1
     )

.. should get you the data you want.

I haven't tested that!

-- 
Mark Rogers // More Solutions Ltd (Peterborough Office) // 0844 251 1450
Registered in England (0456 0902) 21 Drakes Mews, Milton Keynes, MK8 0ER




More information about the Wolves mailing list