Hey guys, I'm just wondering if anybody has any idea on this.. i'll try to illustrate it out...

i'm trying to have a notification feature similar to what forums like this one has... everytime i come back, the threads with recent posts gets bolded.. I'm wondering how abouts to achieve this resource efficiently


My DB Stucture (relavent)

Table of Posts (Similar to threads)
Table of Comments (Similar to posts of threads)
Table of Members
Table of Log in times

So as a user if I created a post and people comment on the post I would be notified of comments and how many comments has been made... so the idea I had was that:

Everytime I logged in or reloads a page I would get my last login time and compare it to all comments of posts I've made... something like: (not sure if it works just thinking off the top of my head).

$get_notify = "SELECT * FROM table_of_comments WHERE post_id='(SELECT * FROM table_of_posts WHERE mem_id='my_id')' AND post_time>'$last_login_time'";


But I was wondering if running this query every page load would bog down load time and if there's a better way in which php forums do this...

Not like that.

For the case of "Bold-Titles contain Unread Comments":
It just compares, if you were the last poster of a thread or not. If not, it bolds the thread title and if not, it leaves it regular-weighted.

For the case of "Bold-Titles contain New Threads (which are not even viewed)":
It just logs all the visitors of a thread in a table. May be 4-5 columns. ThreadID, VisitorID, Time, IP etc. It checks if your userid and the threadID simultaneously exist in a single row or not (a simple WHERE userID=condition AND threadID=condition query). If matches found, threads are regular-weighted, if not they are made bold.

I hope, you understand my clarification.

thanks techbongo... you're right, I suppose i haven't used too many forums... as I noticed that once i have checked a thread the bold goes away and if i visit a new forum for the first time all the threads are bolded...

i suppose that would be a bad comparison, I suppose it would be more like the numbered notification from Facebook, but not in real-time (as i noticed that facebook's number changes immediately)

Something like this may work, and should return a dynamic field 'contains_new_posts' for each row.

SELECT top.*,
  IF(top.mem_id = $my_id AND toc.post_time > $last_login_time, 1, 0) AS contains_new_comments
FROM table_of_posts AS top
  LEFT JOIN table_of_comments AS toc ON (toc.post_id = top.post_id)
WHERE 1

so i should run query everytime? like in the header include script... well the member's header script, i'm just worried about slowing down the whole site with lots of queries...

still very new to programming structure and best practices

You would have to unless you have some type of caching system, but even that might miss out on new comments. DB queries don't really slow down the site unless the db structure is innefficient and the scripting is innefficient too. This site probably has close to approx 50-100 queries for each page request.

thanks mgraphic, that answers a lot of my concerns...

A site that I work on (rugsale.com) has the following on just a visit to the home page (time in seconds):

Parse Time: 0.261 - Number of Queries: 73 - Query Time: 0.0425064452515