Compare two dates - post_date & last_visit

[eevan79]

How to compare two date from different tables? For example I want to show new posts (from posts table) till last login (from users table).

 

Here is my code, but it show all posts:

 

$result2 = mysql_query("SELECT posts.post_topic, posts.post_id, posts.post_content, posts.post_date, posts.post_by, users.user_id, users.user_name, users.user_last_login FROM posts, users WHERE users.user_last_login > posts.post_date ORDER BY post_date DESC");
while($row2 = mysql_fetch_assoc($result2))...

 

Here is time formats from tables:

post.post_date:        2010-06-28 02:44:25

users.user_last_login: 2010-07-05 02:16:38

[eevan79]

Here is how I solve it.

First I select users table

$result = mysql_query("SELECT * FROM users WHERE user_id = ".$_SESSION['user_id']."");...
$lastvisit_day = strtotime(($row['user_last_login']))

 

Than I select posts table:

$result2 = mysql_query("SELECT * FROM posts WHERE post_date >= NOW() - INTERVAL 30 DAY ORDER BY post_date DESC");

and compare dates with php

if ($lastvisit_day < strtotime(($row2['post_date'])))
...

Is there better way?

[Mchl]

$result2 = mysql_query("SELECT posts.post_topic, posts.post_id, posts.post_content, posts.post_date, posts.post_by, users.user_id, users.user_name, users.user_last_login FROM posts, users WHERE users.user_last_login > posts.post_date ORDER BY post_date DESC");
while($row2 = mysql_fetch_assoc($result2))...

 

You have no user_id selected in this query, so it selects for all users' login dates.

 

[eevan79]

SELECT posts.post_topic, posts.post_id, posts.post_content, posts.post_date, posts.post_by, users.user_id, users.user_name, users.user_last_login 

FROM posts, users 

WHERE users.user_last_login > posts.post_date 
AND users.user_id = ".$_SESSION['user_id']." ORDER BY post_date DESC

 

Search found 0 maches.

 

Script in my second post working, but filtered with php. Can I do this in mysql?

 

For example: I have 2 tables: posts and users. I want to select all posts with newer post_date than users.user_last_login date or select post_date between now() and users.user_last_login???

Cant make this to work (exept with php filtering).

[Mchl]

Don't you mean

users.user_last_login < posts.post_date 

?

[eevan79]

Don't you mean

users.user_last_login < posts.post_date 

?

Same result