469,921 Members | 2,176 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,921 developers. It's quick & easy.

Comparing Two Dates Using PHP from MYSQL Database

Hi GUys,
Im trying to compare two dates in MYSQL. But its not treating the
dates as numbers, but as strings. I try using strtotime but that did
not work.

Basically, if the last comment is newer than the last user login, then
I need for a message to pop up and say New Comments.

If not, then no new comments. The area of this code which is not
working is at the bottom.

Does anyone know how to figure out how to compare these two dates?

mysql_select_db('playlist') or die('Could not select database');
// Performing SQL query
$query = "SELECT *
FROM `Login`
WHERE `loginName` = '$logname'
ORDER BY `loginTime` DESC ";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$row = mysql_fetch_assoc($result);
// Free resultset
//mysql_free_result($result);

// Closing connection
mysql_close($link);

$LastLogin = $row['loginTime'];

//second task is to pull last comment

$link2 = mysql_connect('localhost', 'premium', 'amir18')
or die('Could not connect: ' . mysql_error());

mysql_select_db('premium') or die('Could not select database');
// Performing SQL query
$query2 = "SELECT *
FROM `comments`
WHERE `owner` = '$logname'
ORDER BY `timestamp` DESC ";
$result2 = mysql_query($query2) or die('Query failed: ' .
mysql_error());
$row2 = mysql_fetch_assoc($result2);
// Free resultset
//mysql_free_result($result2);

// Closing connection
mysql_close($link2);

echo "<br>";
//echo $row2['timestamp'];
$LastComment = $row2['timestamp'];

//CONVERT TIMESTAMP INTO a date form
$date = $row2['timestamp'];
$year = substr($date,0,4);
$month = substr($date,4,2);
$day = substr($date,6,2);
$hours = substr($date,8,2);
$minutes = substr($date,10,2);
$seconds = substr($date,12,2);
$timestamp = mktime($hours, $minutes, $seconds, $month,
$day,
$year);
$convertedTime = date("Y n d", $timestamp);
strtotime('$convertedTime');
echo $convertedTime;
ECHO "<BR>";

strtotime('$LastLogin');

echo $LastLogin;
if (strtotime('$convertedTime') > strtotime('$LastLogin')) {
echo "New Comments";
} else {
echo "There are no new comments";
}

?>

Apr 7 '06 #1
2 17891
You can convert mySQL timestamps to UNIX timestamps using mySQL's
UNIX_TIMESTAMP() function and simply compare them in PHP.

When using a mySQL database, you should connect to the database server
once and select the needed database. Then you can perform your queries.
The connection to the server is closed automatically after the script
has finished.

Apr 7 '06 #2
I will try the unix timestamp.

So what you're saying though is, there is no need for mysql_close
($link)

????

Apr 9 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by duikboot | last post: by
2 posts views Thread by Jason Tudisco | last post: by
2 posts views Thread by Duppypog | last post: by
5 posts views Thread by Kermit Piper | last post: by
2 posts views Thread by cyber100 | last post: by
4 posts views Thread by cheryl | last post: by
4 posts views Thread by Michael Sharman | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.