Dealing with dates in mysql?

Question

EDIT: changed the whole question sorry, im trying to deal with the mysql DATETIME values, this is my mysql query:

$result="SELECT u.username, u.picture, m.id, m.user_note, m.reply_id, m.reply_name, m. m.dt
FROM relationships r, notes m, user u
WHERE m.user_id = r.leader
AND r.leader = u.user_id
AND r.listener ='".$_SESSION['user_id']."'
UNION
SELECT username, picture, id, user_note, reply_id, reply_name, dt
FROM user u, notes b
WHERE u.user_id = b.user_id
AND b.user_id = '".$_SESSION['user_id']."'
AND dt < '".$lastmsg."'
ORDER BY dt DESC
LIMIT 10 ";

im trying to find rows from this query older than '$lastmsg' and not $lastmsg itself, i thought about using less than but not equal to operator! im not sure if that was stupid!!!

P.S the query works, but its retriving wrong information!!

Answer 1

Shouldn't the query fragment that looks at the date be

AND dt > '".$lastmsg."'

?

Answer 2

You are only restricting the date on the second half of your UNION. Try this instead:

SELECT u.username, u.picture, m.id, m.user_note, m.reply_id, m.reply_name, m.dt
FROM relationships r, notes m, user u
WHERE m.user_id = r.leader
AND r.leader = u.user_id
AND r.listener ='".$_SESSION['user_id']."'
AND m.dt < '".$lastmsg."'
UNION 
SELECT username, picture, id, user_note, reply_id, reply_name, dt
FROM user u, notes b
WHERE u.user_id = b.user_id
AND b.user_id = '".$_SESSION['user_id']."'
AND dt < '".$lastmsg."'
ORDER BY dt DESC
LIMIT 10