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());
confirm_query($result3);
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());
    confirm_query($result3);
    $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 :
$('#newrspbtn').bind('click',function(){
    jQuery.get("showRSPtest.php?lastmsgID=" + love2, function(newitems){
        $('#div123').append(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

0 comments








Enter your email address:

Subscribe in a reader

Follow zac1987 on Twitter

Chatting Box

Donation

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.