Date comparison help

[Septimius]

Hello all, I'm currently having to teach myself PHP on the fly for a project, and have run into a tough problem. Was hoping I could get some assistance.

Currently, I am writing a page that will be taking a Date field from a MySQL Database (datatype of Date), and comparing it to the current date. As of now, I have found how to bring in the time, convert it to a timestamp, and do math/manipulation on it though searches on these forums and others on the Internet.

My problem is that I need to compare JUST the day and month part of the date, which I cannot seem to do with just the timestamp.

Basically, my question is: How can I perform mathematical operations, and comparisons on JUST the day and month part of a date? I'm currently using "strtotime()" to convert the date to a timestamp, and "strftime()" to display it back as a normal readable date.

Thanks for any advice and help.

 

[cLFlaVA]

The getdate() function returns an array of date data which you should be able to use for comparison.

*cLFlaVA
----------------------------
[tt]tastes great, less filling.[/tt]

 

[kenrbnsn]

Look at the date() function. There are also ways of getting just the month or just the day directly from MySQL.

Ken

 

[DRJ478]

You should leave the selection process to the quickest way - namely have it in your SQL statement. You can easily craft a SQL query that only presents records that match the condition you want.
If you help us understand what you want to select, we'll help you generate the appropriate SQL.

 

[Septimius]

Well, I basically need to just bring in an employee ID (emID) and the Month and Day of a field labled 'StartDate' in the DB. Currently I'm saying:

$query = mysql_query("SELECT emID, startDate FROM Eval") or die("Error retrieving data from Eval table.");

$queryResults = mysql_fetch_array($query);

From there I'm working with just startDate, as emID is simply for display.

So I have 2 questions now

1) How could I change this query to give me JUST the day/month field

2) How would I work with that data to compare it to the current Day/Month? Can I use timestamp as I have been and work from there?

 

[Septimius]

Ok, well, currently I'm bringing in the full date using the query below:

$query = mysql_query("SELECT emID, startDate FROM Eval") or die("Error retrieving data from Eval table.");
$queryResults = mysql_fetch_array($query);

My questions are now:

1) How can I edit this query to return JUST the day/month?

2) How would I compare this to the current date to know if it's within 2 weeks of now? Would I use timestamps as I had been doing, or was I going the wrong direction?

 

[DRJ478]

You can add to the query:

SELECT emID, startDate, MONTH(startDate) AS startMonth, DAY(startDate) AS startDay FROM Eval

However, your second question tells me you just want the ones whose start date is within the next two weeks. You can do that in the SQL also with a WHERE clause. It means contructing a start anniversary date that is compared to the current date plus the coming 14 days.
So, to leave you some of this as your contribution to the solution:
You can use STR_TO_DATE() in the SQL clause to generate a new date. You would concatenate the YEAR(NOW()) with startMonth and startDate and create an anniversaryDate.
The comparison then in the WHERE clause is that anniversaryDate>= today and anniversaryDate is < than today+14days. Today is date_format(CURRENT_TIMESTAMP(),'%Y%m%d000000')
All MySQL time/date functions are at:

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

If you need more help, feel free to ask.

 

[Septimius]

Awesome idea... I had thought of trying to add the years using timestamp seconds, but this is much better. However, I'm running into an issue.

I can query the MONTH(startDate) and it works fine, but if I attempt to use DAY(startDate), it gives an error:

"You have an error in your SQL syntax near '(startDate) FROM Eval' at line 1"

And of course, this is difficult to search for as "DAY" is such a common word. Any ideas what I'm missing here?

 

[Septimius]

Never mind.... 'DAYOFMONTH(startDate)' did it. Thanks for the help - I'm sure I'll be back

 

[DRJ478]

Fortunately easy solution:
DAY() is a synonym for DAYOFMONTH(). It is available as of MySQL 4.1.1, so, you must run an "older" version than 4.1.1
Use DAYOFMONTH() and it will work.

 

[Septimius]

haha - yeah, our host is terrible, so an old version of MySQL would not be that shocking. Oh well, gotta work with what they give me I suppose.

 

[DRJ478]

Older" than 4.1.1 isn't really that old. Be kind to your host and let'em be kind to you in reverse!

 

[Septimius]

No, they really are bad. We also host a ton of domains, but we have this particular project outsourced - It's been nothing but trouble with them unfortunately

they're running version 3.23.56 - that's not TOO old is it?

 

[DRJ478]

In my job I have to deal with a product that runs 3.22.16gamma as the backend and license terms do not allow for any changes in code or setup. Meanwhile the product serves 40K users daily...
Under these circumstances 3.23.56 sounds great!

 

[Septimius]

haha - I'll refrain from further complaints then Thanks again for the help. I'll likely be back, but I've got plenty to keep me going for 30 more minutes today till I get off work.