January 16th, 2013, 06:51 PM
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:
I have also tried:
$result = mysql_query("SELECT * FROM invoices WHERE username ='".$_SESSION['username']."'AND invoicedate = DATE_SUB(CURDATE(), INTERVAL 30 DAY)")
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?
$result = mysql_query("SELECT * FROM invoices WHERE username ='".$_SESSION['username']."'AND MONTH(invoicedate) = MONTH(CURRENT_DATE)")
January 16th, 2013, 07:06 PM
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.
Originally Posted by derektoews
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.
January 16th, 2013, 07:07 PM
#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.
January 17th, 2013, 03:05 PM
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?
SELECT invoicenumber FROM invoices WHERE username ='".$_SESSION['username']."'AND MONTH(invoicedate) = MONTH(CURRENT_DATE)
January 17th, 2013, 03:31 PM
is certainly an option.
CURRENT_DATE - INTERVAL 1 MONTH
January 17th, 2013, 04:02 PM
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?
January 17th, 2013, 04:26 PM
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).
January 17th, 2013, 05:24 PM
January 17th, 2013, 05:31 PM
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.
SELECT itemprice FROM invoices WHERE username ='".$_SESSION['username']."'AND MONTH(invoicedate) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
January 17th, 2013, 06:32 PM
"The past 8 months" is the same as "anytime after 8 months ago".
For a specific month of a year you can specify the year as well
invoicedate >= CURRENT_DATE - INTERVAL 8 MONTH
or the clever solution of
MONTH(invoicedate) = 10 AND YEAR(invoicedate) = 2011
(which I'm pretty sure you can get away with).
invoicedate BETWEEN "2011-10-01" AND "2011-10-99"
Comparing a DATE_FORMAT() to a string is another option.
January 18th, 2013, 09:27 AM
gotcha. Makes sense! I've got everything I need figured out for now, thank you very much!