That's on the right track. Each page would contain a query saying
$sql = "select from messages m1, messages m2
where m2.replies_to = m1.message_id
and m1.user_id = $logged_in_user_id
and m2.message_created >= $logged_in_user_last_log_in_time";
and loop through the results.
This is an exceptionally expensive query though.
If you're willing to maintain a second table with your scripts and sacrifice a little normalization to gain speed, then you might consider having new messages stuff into a table the date posted and the user_id they should alert (the user's message that the message being posted is a reply to). This would be a faster query. Say the table is like this:
creat table logged_on_alerts (
user_to_alert int not null
, message_id int not null
, time_of_alert
);
When a users logs in you can clean up this table with:
$sql = "delete from logged_on_alerts
where user_to_alert = $logged_on_user_id
and time_of_alert <= sysdate()";
then when someone reply's to this user's message do this insert:
$sql = "insert into logged_on_alerts (
user_to_alert
, message_id
, one_liner
, time_of_alert
) values (
$user_id_of_reply_to_message
, $message_id_of_reply
, '" . addslashes ( $subject_of_reply ) . "'
, sysdate()
)";
then when you load each page, just do this query:
$sql = "select message_id, one_liner
from logged_on_alerts
where user_to_alert = $logged_on_user_id
order by time_of_alert desc";
You could also have this query display links to a clean up page that removes that alert and then redirects to the actual message.
Good luck,
Phillip
