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!!
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
Shouldn't the query fragment that looks at the date be
AND dt > '".$lastmsg."'
?