[RESOLVED] Month / $date relationship

vinny199

Hi,

I'm a bit stuck here.

I have my events dates stored in my table as YYYY-MM-DD

on the front end of the website, I can match / retrieve those dates using:

$today = date("Y-m-d");

all fine.

However, I have an "event search" where the visitors only select a particular month to view events.

Fors instance, they may select "January" and it will parse a form variable $month as $month = 01

Now i need to retrieve all events from my database where MM = 01, ignoring the day and year part of the stored date.

I'm completely stuck as to what my recorset needs to be to make that happen.

Can you please help?

Many thanks,

Vinny

Installer

See MONTH(), one of the MySQL Date and Time Functions.

vinny199

Hi,

Thanks for your reply.

i understand this.

I see that i vould extract the month from a full date.

However, this is not really what I need to do here (I think, am i wrong?)

I am already parsing a for variable in a month format (01 for January)

Where I'm stuck is my recordset statment to match that value against the full dates stored in my database (for instance, a record has a date of 2009-01-31)

how can I state that $month (01) matches the month in my full date and therefore display this record?

Do i make sense?

Thanks,

Vinny

Installer

If I understand correctly

i need to retrieve all events from my database where MM = 01

then something like this should work:

$month_number = '01';
$query = "SELECT * FROM my_table WHERE MONTH(event_date) = '" . int_val($month_number) . "'";

Or am I still missing the question?

Edit: This should work as well:

$month_number = '01';
$query = "SELECT * FROM my_table WHERE MONTH(event_date) = $month_number";

I also changed "==" to "=" in the first one.

vinny199

Thank you,

I got some luck using the secong one slightly modified:

 "SELECT * FROM `data` WHERE ride_start >= '".$today."' AND MONTH(ride_start) = '" .$month_number. "' ORDER BY `data`.ride_start ASC";

However, it does not actually retieve the cycling ride events in January.

Instead I get a default event "1st january 1970" which does not exsit in my database.

I have a feeling it may be because my Event date stored in the database $ride_start is written in this format 2009-01-27 which does not match excatly a date() formating.

could that be the reason?

thanks again for your help,

Vinny

TheStalker

could you not add the day and year to your var e.g if they selected feb have a var thats $month = 2009-02-01

bradgrafelman

For one, MySQL has a DATE_FORMAT() function that will allow you to 'SELECT' the date in whichever format you like.

If $today is simply a call to PHP's date() to get the date, you could also replace that with:

WHERE ride_start >= CURDATE()

Installer

If you want results relative to the current date, do this:

$query = "SELECT * FROM `data` 
          WHERE ride_start >= 'CURDATE()' AND MONTH(ride_start) = '" .$month_number. "' 
          ORDER BY `data`.ride_start ASC";

Else if you want results relative to a different date, just convert the input date format to MySQL format. e.g.:

$date = '2nd February 2009';
$redate = date('Y-m-d', strtotime($date));
$month_number = date('n', strtotime($date));
$query = "SELECT * FROM `data` 
          WHERE ride_start >= '" . $redate . "' AND MONTH(ride_start) = '" . $month_number . "' 
          ORDER BY `data`.ride_start ASC";

bradgrafelman

One minor (and one major) syntax fix for Installer's suggestion:

WHERE ride_start >= 'CURDATE()'

must be:

WHERE ride_start >= CURDATE()

while

AND MONTH(ride_start) = '" .$month_number. "'

could be:

AND MONTH(ride_start) = " .$month_number. "

since it's a numeric value (although not required, I don't believe).

Installer

True, either will work, but the returns will be in different formats:

Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context

Installer

I misread your post. CURDATE() should not, of course, be enquoted when used in this way.

The whole thing done right, I hope:

$query = "SELECT * FROM `data` 
          WHERE ride_start >= CURDATE() AND MONTH(ride_start) = " . $month_number . " 
          ORDER BY `data`.ride_start ASC";

$date = '2nd February 2009';
$redate = date('Ymd', strtotime($date));
$month_number = date('n', strtotime($date));
$query = "SELECT * FROM `data` 
          WHERE ride_start >= " . $redate . " AND MONTH(ride_start) = " . $month_number . " 
          ORDER BY `data`.ride_start ASC";

vinny199

Thank you both!

thanks Installer, that's the one that worked for me:

Installer;10900785 wrote:
I misread your post. CURDATE() should not, of course, be enquoted when used in this way.

The whole thing done right, I hope:
$query = "SELECT * FROM `data` 
          WHERE ride_start >= CURDATE() AND MONTH(ride_start) = " . $month_number . " 
          ORDER BY `data`.ride_start ASC";

Vinny