#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    22
    Rep Power
    0

    Cool Calling data from table based on month, 3 month, yearly


    Looking to be able to recall data from a table based on the current month, past 3 months, and past year for the $_SESSION of the current user. Here's what I have so far but it doesn't work:

    PHP Code:
    $result mysql_query("SELECT * FROM invoices WHERE username ='".$_SESSION['username']."'AND invoicedate = DATE_SUB(CURDATE(), INTERVAL 30 DAY)"
    I have also tried:
    PHP Code:
    $result mysql_query("SELECT * FROM invoices WHERE username ='".$_SESSION['username']."'AND MONTH(invoicedate) = MONTH(CURRENT_DATE)"
    I know that the username in the session is being stored and the username is also in the table based upon the current user. The second PHP code actually recalls data, but it recalls the month of June and only one line of the invoice from the month of June, not all 4. The table has the date format of 1/2/2013. Do I need to change the date format?
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    Originally Posted by derektoews
    The table has the date format of 1/2/2013.
    That's not possible -- unless your "dates" are in fact strings. This would be a very bad thing, because you'd circumvent the type system of the database. Strings can hold anything, so you have absolutely no guarantee that your "dates" are actually sensible. They could be complete gibberish like "*"$#1". And your date strings are in fact gibberish, at least to MySQL, which only knows the US format.

    So don't misuse strings for dates. Use the actual date types provided by MySQL.

    Another big problem is that your queries are vulnerable to SQL injections, and the "mysql_" functions are obsolete. Switch to a modern database extension and use prepared statements.
  4. #3
  5. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,997
    Rep Power
    9397
    #1. Are you sure you want it to retrieve invoices that are from exactly 30 days ago? Or ones on or after 30 days ago?

    #2. I'm quite sure that query is not retrieving anything from June since the CURRENT_DATE is in January.

    Dump out the data in the invoices table and check that it has what you expect it to have.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    22
    Rep Power
    0
    Figured out that I just needed to change the date format (thanks to some friendly help) and now I can pull data based on the current month. What I just realized, though, is I'm trying to pull the data from the previous month (so currently I'd be trying to pull data from December, 2012).
    So this works great for pulling data from the current month, but how do you pull data from a previous month?

    PHP Code:
    SELECT invoicenumber FROM invoices WHERE username ='".$_SESSION['username']."'AND MONTH(invoicedate) = MONTH(CURRENT_DATE
  8. #5
  9. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,997
    Rep Power
    9397
    Well, using
    Code:
    CURRENT_DATE - INTERVAL 1 MONTH
    is certainly an option.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    22
    Rep Power
    0
    that works great!

    So then if I understand it correctly, if I say INTERVAL - 8 MONTH then it will select everything from now till 8 months ago? or just what is located at the 8th MONTH?
  12. #7
  13. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,997
    Rep Power
    9397
    Whether it's "from now till" or not depends on the query. With the latest one you've posted it'll only find stuff that happened in that month (of any year).
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    22
    Rep Power
    0
    ..
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    22
    Rep Power
    0
    Ok, so how would I specify a specific year, and also, the query is setup to select all within the past 8 months, but it's not selecting the current month, which is fine for what I'm currently doing, but how would I select just a specific month, say, October of 2011.

    PHP Code:
    SELECT itemprice FROM invoices WHERE username ='".$_SESSION['username']."'AND MONTH(invoicedate) = MONTH(CURRENT_DATE INTERVAL 1 MONTH
  18. #10
  19. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,997
    Rep Power
    9397
    "The past 8 months" is the same as "anytime after 8 months ago".
    Code:
    invoicedate >= CURRENT_DATE - INTERVAL 8 MONTH
    For a specific month of a year you can specify the year as well
    Code:
    MONTH(invoicedate) = 10 AND YEAR(invoicedate) = 2011
    or the clever solution of
    Code:
    invoicedate BETWEEN "2011-10-01" AND "2011-10-99"
    (which I'm pretty sure you can get away with).
    Comparing a DATE_FORMAT() to a string is another option.
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    22
    Rep Power
    0
    gotcha. Makes sense! I've got everything I need figured out for now, thank you very much!

IMN logo majestic logo threadwatch logo seochat tools logo