Let's say there are 10 messages, I have posted 3 responses onto 7 messages. There are 3 messages that I never responded. The system need to check if the 7 messages receive a new response from other users, the system should not check the 3 other messages.

Question 1 : SELECT msg-id FROM responsesLive WHERE username='{$myUsername}'; //This will output many duplicated msg-id if I sent multiple responses onto a particular message. How to prevent the duplicated msg-id?
Answer : SELECT DISTINCT msg-id FROM responsesLive WHERE username='{$myUsername}';

Question 2 : After I got the msg-id, I need to get the rsp-id of last response in each of the messages. How to do that?
Answer :
$query3 = "SELECT msg-id, response, max(rep-id) FROM responseslive WHERE msg-id in (SELECT DISTINCT msgid FROM responsesLive WHERE username='{$myUsername}') GROUP BY msg-id";
$result3 = mysql_query($query3,$connection) or die (mysql_error());
while($rspinfo = mysql_fetch_array($result3)){
     $latestrsp-id= $rspinfo['max(rsp-id)'];
     echo $latestrsp-id . " | ";

Question 3 : Isn't I need to loop through all the posts that user responded before? Let's say the user responded on 99999 posts before, i need to check all the 99999 posts to see whether any of them receive a new response? Sure it will slow down the entire page. May I know how did facebook.com or plurk.com do that? I saw plurk.com can tell the user when a very very very old post received a new response. Maybe my steps are wrong? I guess what they do are something like this :

Let's say there are 10 messages, Peter posted comments onto 7 messages. Next time when a user post a new comment onto any 3 of the 7 messages, the website will show the count button "3 new comments". When peter click on the button, the website will show the 3 messages which has the new comments.
Step 1 : Select the message id when a user post a new comment onto the message.
Step 2 : Select all username of users that have posted comment onto the message before.
Step 3 : Add username and message id onto table "new_response_count" in database.
Step 4 : Compare logged in username to the username in table of "tblNew_response_count" in database :
If ($username-login == $username-in-new-rsp-count) {
    $query3 = "SELECT msgid FROM tblNew_response_count WHERE username='{$username-login}'";
    $result3 = mysql_query($query3,$connection) or die (mysql_error());
    $numCount = mysql_num_rows($result3);

Step 5 : <a href="javascript:;" id="newrspbtn"><</a>

Step 6 : Pass msgid from php variable to javascript variable then jquery bind click like :
    jQuery.get("showRSPtest.php?lastmsgID=" + love2, function(newitems){

Step 7 : Every 10 seconds will auto reload the function of Step 4.

Note that when the user click the button, two tasks will be carried out : 1) all messages that has new response will be displayed, 2) delete the records (username and msgid) on tblNew_response_count in database.

Posted by Zac1987 on 08 June, 2011


Enter your email address:

Subscribe in a reader

Follow zac1987 on Twitter

Chatting Box


If you feel my website is informative and it is useful for you, please donate some money to support me or buy me a drink. I will continues this good works. Thank you.