SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Nottingham England
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    data calculation in PHP

    I am currently storing quotes in a mysql database using a standard Y-m-d (2007-05-23) format.

    I want to be able to produce a list of quotes from the database but only ones which are less than 365 days old.

    To produce the list is easy

    select * from quotes

    but how do I limit it by date based on my current method of storing the dates?

    Its probably really easy but help would be appreciated. I have checked the PHP site but can't seem to find something that exactly fits the bill.
    It was working when I left last night!

  2. #2
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,788
    Mentioned
    151 Post(s)
    Tagged
    3 Thread(s)
    Let mysql do the work for you rather than php.
    Code:
    select * from 
    	quotes
    where
    	DATE_SUB(CURDATE(),INTERVAL 365 DAY)
    REF: http://dev.mysql.com/doc/refman/5.0/...ction_date-sub
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Nottingham England
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply, unfortunately I think I must be in ultra thick mode today because despite the logic of your reply I can't make it work.

    The quotes table has a date field so if I wanted to select a quote by date I would use the query -

    select * from quotes WHERE quoteDate = '2007-08-09' or something like that.

    What I actually want to achieve is a similar statement but selecting all quotes where the date is less than 365 days old but the month = "July". So I need to be able to check other parameters as well as the 365 days bit, therefore I don't think the date_sub function does it. (The reason for the 365 days bit is because the database has been running longer than a year and I need to limit search by month queries to a maximum of 12 months or 365 days to keep it within the current trading period).

    I didn't explain myself very well the first time around, hope this is better.

    Thanks again
    It was working when I left last night!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •