We help IT Professionals succeed at work.

how can i compare dates in php

amchugh89
amchugh89 asked
on
i have a javascript table that is populated from a tadabase table called "games" with the following query:

$query1="select * from games";
...and then some inserting into the js table

one of the table columns i have is the game_date.
i want to get it so that games that have already happened are no longer in my js table
i want a query something like:
$query1="select * from games WHERE current_date > game_date";

does my game_date have to be of time type?
does it need to be in a specific format?
is there a php function that gets the current date?
how should i go about comparing these 2 dates - i'm a total n00b
Comment
Watch Question

Commented:
The solution depends on your SQL server:

MS SQL: SELECT * FROM [games] WHERE [game_date] > GETDATE()

PostgreSQL: SELECT * FROM games WHERE game_date > now()

MySQL: SELECT * FROM games WHERE game_date > NOW()

PHP:you can use date() to get the current date. (but it's much better to specify the filter in SQL... :) )

(there are other synonims also)

In general you can simple date and timestamp types are comparable.
Commented:
I would suggest reading this article to get familiar with dates in PHP.

http://www.phpfreaks.com/tutorial/working-with-dates-in-php

Commented:
You have posted this question twice. Make sure you look at the answers on both.
Commented:
Hi amchugh89, here are the answers:

1. does my game_date have to be of time type?

Since you are talking about dates and have mentioned about using time type, I would suggest that for an effective comparison, it would help to make it as DATETIME column type. Also there is one another reason why I suggest you DATETIME. Consider there are 10 games scheduled on the same day. If you don't use a time comparison, then you end up with the expired games as well. So if you want to make sure that you get the most current games which have not yet expired, you would need to consider both date+time

2. does it need to be in a specific format?

Yes, when you modify the column type, select "As defined:" option against the Default value. In the box just beneath this, enter: 0000-00-00 00:00:00 (Assuming that you are going to use phpmyadmin)

3. is there a php function that gets the current date?

Yes, there is.
$date = date("Y-m-d H:i:s");
echo $date;

When you are inserting the data in the database after you have modified the column type as I have indicated, you can use:
INSERT INTO games (game_date) VALUES ($date)

4. how should i go about comparing these 2 dates - i'm a total n00b

This can help:
SELECT *  from games WHERE game_date > NOW()

NOW() will include the date and the time at which you ran the query and it will take date+time into consideration while checking. So this will give you all games which are scheduled in the future.

Hope that will make it easy for you and get most appropriate results.

Try that out and let me know if yo have any questions.
If you are using the MySQL DB and game_date field is of date type then try the following query,

SELECT * FROM `games` WHERE game_date >= CURDATE( )
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.