i try to make some message system
this is the mysql code for the inbox
Code:
SELECT mc.`id` , mc.`created` , mc.`pid` , mc.`from_id` , mc.`to_id` , mp.`subject` , mc.`message` , mp.`hasread`
FROM `jcow_messages` mc
JOIN `jcow_messages` mp ON mc.`pid` = mp.`id`
WHERE mc.`to_id` =1
AND mc.`from_id` >0
AND mc.`pid` >0
GROUP BY mc.`pid`
UNION
SELECT m.`id` , m.`created` , m.`pid` , m.`from_id` , m.`to_id` , m.`subject` , m.`message` , m.`hasread`
FROM `jcow_messages` m
JOIN `jcow_accounts` a ON m.`from_id` = a.`id`
WHERE m.`from_id` >0
AND m.`to_id` =1
AND m.`pid` =0
AND m.`pid` NOT
IN (
SELECT `pid`
FROM `jcow_messages`
WHERE `to_id` =1
AND `from_id` >0
AND `pid` >0
GROUP BY `pid`
)
ORDER BY `id` DESC
it should show all new incoming messages together with reply from older messages order by id (the latest first)
id: message id
pid: is the parent message (0 when new message, higher than 0 is a reply)
from_id: id from the sender (should be higher than 0)
to_id: id from the receiver (in this example 1)
i tried this code, but somehow there a few rows too much in the result
somebody any ideas ?
__________________
Please login or register to view this content. Registration is FREE
Check out the Facebook Clone build with Jcow SNS at
Please login or register to view this content. Registration is FREE
, it is free and it always will be
|
