Comparison?

[ArcGames]

What I am trying to do is compare dates using php and a mysql database. I want to take today's date and compare it with a date stored in a mysql database (in date format), and if today's date is 14 days more then the date taken from the database echo a certain response on a webpage, or if its less then i need it to echo a certain response message on a webpage. Please tell me if this code will work, if not please help me fix it, please?

<?php

$CurDate = date("Y").date("d").date("m");

// Make a MySQL Connection
mysql_connect("localhost", "my_username", "my_password") or die(mysql_error());
mysql_select_db("my_database") or die(mysql_error());

$SignupDate_A = mysql_query("SELECT pdate FROM my_table") or die(mysql_error());

$SignupDate_B = strtotime($SignupDate_A);
$SignupDate = $SignupDate_B + 14;

if ($CurDate <= $SignupDate)
{
echo "You are below the expiration date";
}
elseif ($CurDate > $SignupDate)
{
echo "The Date has expired";
}

?>

Thanks!

[Houdini]

MySQL stores dates as yyyymmdd so you need to alter your $CurDate variable, and I would change the way you assign it also to this:

$CurDate = date("Ymd");

you just had a lot of unneccessary concatenation the way you were doing the assignment. Try that change and see what happens.

[Installer]

Actually, according to the MySQL manual:

MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format.

and the retrieved format is the one you work with.

Something like this should work:

$CurDate = strtotime('today');
$result = mysql_query("SELECT UNIX_TIMESTAMP(DATE_ADD(pdate, + INTERVAL 14 DAY)) as signup_date FROM my_table") 
    or die(mysql_error());
$row = mysql_fetch_assoc($result);
if ($CurDate <= $row['signup_date']) {
    echo "You are below the expiration date";
} else {
    echo "The Date has expired";
}

[Houdini]

While what was stated from the MySQL site is true it can be misleading here is yet another quote.

The DATE type is used when you need only a date value, without a time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'

if you use

$CurDate = date("Ymd");
//outputs 20060621
//or
$CurDate = date("Y/m/d");
//outputs 2006/06/21
$CurDate = date("Y m d");
//outputs 2006 06 21
$CurDate = date("Y-m-d");
//outputs 2006-06-21

All the above are valid and the first will be interpreted correctly also. The PHP Date or Y give the year in four digits, the m gives the month in two and the d gives the date in two digits so the code I gave is just fine for MySQL.

This line though produces a timestamp, run it and see

$CurDate = strtotime('today');
echo $CurDate;
// outputs 1150866000 

[Installer]

Yeah, I know all those formats are valid for entry into a db, MySQL being flexible with date entry formats. I was referring only to the format received from a query, which is "yyyy-mm-dd". You said "MySQL stores dates as yyyymmdd...". (I think for practical purposes we can equate "store" with "retrieve".) I also know the line "$CurDate = strtotime('today');" produces a valid timestamp; that why I used it in the code I posted (which I did "run and see" before posting). 🙂

Edit: For my code to work, "$CurDate" needs to be a timestamp. I should have renamed it to e.g. "$cur_date_ts".

I don't see how what I quoted is "misleading". What you quoted (from the manual) says the same thing.

[ArcGames]

I only have 3 fields in this database and they are named, "pdate", "p_name", and "p_age". For some reason this code is not working. I get this error when I run it.

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*, + INTERVAL 14 DAY)) as pdate FROM mydates' at line 1"

Here is how I have the code
<?
/ Make a MySQL Connection
mysql_connect("localhost", "my_username", "my_password") or die(mysql_error());

mysql_select_db("my_database") or die(mysql_error());

$CurDate = strtotime('today');
$result = mysql_query("SELECT UNIX_TIMESTAMP(DATE_ADD(pdate, + INTERVAL 14 DAY)) as signup_date FROM my_table")
or die(mysql_error());
$row = mysql_fetch_assoc($result);
if ($CurDate <= $row['signup_date']) {
echo "You are below the expiration date";
} else {
echo "The Date has expired";
}
?>

Please help?

PHP version 4.4.1
MySQL version 4.1.19

[ArcGames]

Please help?

[Installer]

Sorry, my error. Your query should be:

$result = mysql_query("SELECT UNIX_TIMESTAMP(DATE_ADD(pdate, INTERVAL 14 DAY)) as signup_date FROM my_table")
    or die(mysql_error());