Convert MySQL timestamp field to a Unix timestamp

[MartyMatthews]

I have a process where I want to compare the current date with an expiration date that is so many months after a registration date. The registration date is stored in a MySQL timestamp format. I want to convert this to a Unix timestame so I can do the comparison with Time(). I have tried both the MySQL UNIX_TIMESTAMP and the PHP getTimestamp() functions and cannot get them to work. I have tried UNIX_TIMESTAMP both in  the original SELECT statement and as a stand alone one. I also get a divide-by-zero error with the date_default_timezone_set function. I have enclosed screenshots of the code and the result of the echo at the bottom. THANKS!

[bregent]

I'm confused at what you are doing. Why not just use a MySQL date arithmatic function like DATE_ADD() to add the number of months to the reg date to arrive at the expiration date:

SELECT DATE_ADD (reg_date, INTERVAL ss_nomonths MONTH) as ExpireDate

or use a PHP date math function?

[David_Powers · Accepted solution]

You're making it far too complicated.

SELECT TO_DAYS(reg_date) - TO_DAYS(CURDATE()) AS daysleft

FROM ssregis

WHERE. . .

That will give you the number of days left until the registration expires. If it's less than 0, redirect to the renewal page.

By the way, you're laying yourself wide open to SQL injection attacks by failing to escape the input from the query string. At the very least, you should be doing this:

$entered_ss_id = mysql_real_escape_string($_GET['record_id']);

$entered_email = mysql_real_escape_string($_GET['email_address']);

However, since it looks as though you're hand-coding the script, you should be using MySQL Improved rather than PHP's deprecated MySQL functions. See http://docs.php.net/manual/en/mysqlinfo.api.choosing.php.

[MartyMatthews]

THANKS a lot to both Bregent and David! I implemented both of  David's suggestions and they worked like a charm. I particularly appreciated the specific implementation into my code.

Can you tell me why

date_default_timezone_set(America/Vancouver);

gives me a divide by zero error?

Thanks again!

[David_Powers]

MartyMatthews wrote:

Can you tell me why

date_default_timezone_set(America/Vancouver);

gives me a divide by zero error?

The timezone identifier must be a string:

date_default_timezone_set('America/Vancouver');

[MartyMatthews]

Thanks David! I shouldn't have had to ask!

[David_Powers]

No problem. It's easy to miss something like that. The code looked correct, and it wasn't immediately obvious that the quotes were missing.

[MartyMatthews]

I appreciate your willingness to answer such a simple question!