Need to limit records to those made this month.

schwim

Hi there everyone,

I want to alter a donations system to print only the records made in the current month. Here's the date format that is getting put into the db:

17:56:13 Feb 05, 2010 PST

Here's my current sql command:

$sql="SELECT user_id,mc_gross FROM donation_paypal ORDER BY id DESC LIMIT 10 ";;

How would I go about selecting records that have a date in the current month?

Thanks very much for any thoughts you might have,
json

halojoy

Are you using MySQL?

Here are all the many Date & Time functions in MySQL:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

schwim

Hi there halojoy,

Yes, I'm using MySQL.

thanks,
json

bradgrafelman

You should store data in the MySQL table using MySQL's DATETIME column type, that way you can use the functions halojoy pointed you to.

halojoy

Yes, I agree very much with bradgrafelman

There are only 2 formats I can think of storing DateTime in MySQL (or other db).
For PHP that is.

1. Unix Timestamp.
Almost every PHP function for Date and Time is dealing with Unix Timestamp
coming from [man]time/man function.

2. The format used by MySQL:
'YYYY-MM-DD HH:MM:SS' = '2007-05-23 09:15:28' (UTC)

Why are they so good formats?
Because they are easy to compare straight away, like comparing number 1 with 2.
Unix Timestamp time() actually IS A NUMBER of seconds.

But also '2007-05-23 09:15:28' is easy to compare with '2007-04-23 09:15:28'.
There are string compare functions that will work sometimes.
But most of all MySQL have many DB-functions that can be used in SQL Query.
And most of them are based on the format: '2007-05-23 09:15:28'

When using non-standard formats, you can and will get into headaches.
Problems will come. You will have to try to find difficult solutions.
PHP & MySQL sets our standards.
Best is to stay close to those formats.

schwim

Hi there Brad,

Well, I've created a column with the timestamp structure. The only problem I'm having now is figuring out how to populate it. I've tried both DateTime() and 'DATETIME: Auto CURDATE()', both of which are leaving me with dates of 0000-00-00 00:00:00

$query="INSERT INTO donation_paypal (user_id,mc_gross,date_time,protection_eligibility,address_status,mc_fee) VALUES ('".$_REQUEST['custom']."','".$_REQUEST['mc_gross']."','DATETIME: Auto CURDATE()','".$_REQUEST['protection_eligibility']."','".$_REQUEST['address_status']."','".$_REQUEST['mc_fee']."',NOW())";
$results=mysql_query($query) or die(mysql_error());

How should I go about populating this column?

thanks,
json

bradgrafelman

Err.. where in the world did you find 'DATETIME: Auto CURDATE()' ? And DateTime() isn't a valid MySQL function. Also, why is there a ",NOW()" at the end of your query even though it doesn't match up with your column list?

The NOW() function would be the proper one to use, since it returns the current date and time in the 'YYYY-MM-DD HH:MM:SS' format that MySQL uses for DATETIME columns. Also note that you could use a TIMESTAMP column instead and set it's default value to CURRENT_TIMESTAMP - that way you wouldn't even list it in your INSERT queries at all.

schwim

Okeedoke. With your assistance, I've got a current datetime in the new records. Now I need to find out how to get this:

$sql="SELECT user_id,mc_gross FROM donation_paypal ORDER BY id DESC LIMIT 10 ";;

to return only the records of the current month and where 'display'=1(one of the columns in the records is a tinyint called 'display' that will always be either 0 or 1).

If you thought I was doing some screwed up stuff with the last part of this problem, you'd have a conniption over what I'm doing now.

I'd greatly appreciate any help you can provide.

thanks,
json

bradgrafelman

The MySQL functions you'll want are CURDATE(), MONTH(), and YEAR(). Something like:

WHERE YEAR(CURDATE()) = YEAR(dateField) AND MONTH(CURDATE()) = MONTH(dateField)

You could also use some string manipulation; something like:

WHERE LEFT(CURDATE(), 7) = LEFT(dateField, 7)

Then again, that solution would break after December in the year 9999. :p

schwim

Hey there Brad,

Although I'm not planning on having to deal with the site past 9999, I ran with your first example. Playing with the date string, it seems to be working fantastically. I very much appreciate the help. I also used it for the accompanying sum.

thanks,
json

bradgrafelman

Don't forget to mark this thread resolved (if it is) using the link on the Thread Tools menu above.

schwim

Hi there Brad,

I wanted to ask one more question before I marked it resolved. It just took me longer to implement the next part than I had planned.

I've set up a form for submitting donations manually. I used a pretty nifty calender form element to quickly insert dates for the donations. One of it's claims to fame is the ability to store in MySQL YYYY-MM-DD, but this leads me to my question.

I'm sorting by the datetime column.

Question 1: Do I need to insert a dummy time or will YYYY-MM-DD drop into the mysql datetime column without a time attached to it?

Question 2: I'll have multiple entries with the same date. Will this cause a problem in sorting? It's not critical in which order those with the same date are returned, but I didn't know if this was poor form to sort items where some had matching values.

Thanks very much for your help,
json

johanafm

schwim;10942190 wrote:
Question 1: Do I need to insert a dummy time or will YYYY-MM-DD drop into the mysql datetime column without a time attached to it?

When only date is given, time is set to 00:00:00

schwim;10942190 wrote:
Question 2: I'll have multiple entries with the same date. Will this cause a problem in sorting? It's not critical in which order those with the same date are returned, but

Sorting datetimes where all times are 00:00:00 is no different from sorting VARCHAR(10) WHERE the last 5 chars are always the same character.
So with 5 2010-01-01 dates, those can appear in any internal order. If you do need them in some specific order, you would need to sort on an additional column, such as name.

schwim;10942190 wrote:
I didn't know if this was poor form to sort items where some had matching values.

If you by "some had matching values" mean that several rows have identical information in the column(s) you order by, then no. Usually you can add an additional field to the order by clause.

If you have all the data you need to display allready, and some of them have identical information it's fine as far as sorting go, but you might want to add a GROUP BY for all present fields, and add a count(*) to the selected fields. That way you don't present identical data several times to the user, but rather an extra column indicating that there are N such identical records.