Anonymous

function catch_up()
{
    global $CURUSER, $READPOST_EXPIRY;
    $userid = $CURUSER["id"];
    $dt = (get_date_time(gmtime() - $READPOST_EXPIRY));
    $where='';          
    $buffer_insert = array();
    $buffer_update = array();

    if (isset($_GET['forumid']))
        $where = 'forumid='.(int)$_GET['forumid'].' AND ';

    $res = do_mysql_query("SELECT topics.id as id, lastpost, lastpostread, readposts.id as rid FROM topics LEFT JOIN readposts on readposts.userid=$userid and topicid=topics.id where $where lastpost_time>'$dt'") or sqlerr(__FILE__, __LINE__);
                        
    while ($arr = mysql_fetch_assoc($res))
    {                  
      $topicid = $arr["id"];
      $postid = $arr["lastpost"];
      $lastpostread = $arr['lastpostread'];
      $id= $arr['rid'];
      
      if ($id == NULL)
         $buffer_insert[]= '('.$userid.','.$topicid.','.$postid.')';
      else if ($lastpostread < $postid)
         $buffer_update[] = '('.$id.','.$postid.')';
    }
    if (sizeof($buffer_insert)>0)
        do_mysql_query("INSERT DELAYED INTO readposts (userid, topicid, lastpostread) VALUES ".implode(', ',$buffer_insert)) or sqlerr(__FILE__, __LINE__);
    if (sizeof($buffer_update)>0)
        do_mysql_query('INSERT DELAYED INTO readposts (id,lastpostread) VALUES '.implode(', ',$buffer_update).' on duplicate key update lastpostread=values(lastpostread)');
     unset($buffer_insert);
     unset($buffer_update);
}

#AND


btw if anyone followed my post and did this my way (whch is more correct since u dont get the bug in userhistory that i explained and its more efficient)... here is a much much much more efficient catch_up (just remember to add the proper indexes for the select query) wink.gif

Note: IT WILL NOT WORK UNLESS U HAVE THE PROPER FIELDS IN UR TABLES AND U KEEP THEM UPDATED PROPERLY... and no i will not post on how to do that cause it will take too long and i ve already done it for myself tongue.gif .. but its very easy for the intermidiate coder to do it and u will get huge gains on larger forums and more users wink.gif .. if u dont know how to do it then this mod is not 4 u tongue.gif

oh theres also per forum catchup if u want.. u just need to add forum=id in the url.. u can see from the code how it works... very simple... offcourse u can easily adapt it so u can do per post catchup sa well or multiple posts but anyhow, i find it unecessary... per forum is enough for me...
CODE
function catch_up()
{
    global $CURUSER, $READPOST_EXPIRY;
    $userid = $CURUSER["id"];
    $dt = (get_date_time(gmtime() - $READPOST_EXPIRY));
    $where='';          
    $buffer_insert = array();
    $buffer_update = array();

    if (isset($_GET['forumid']))
        $where = 'forumid='.(int)$_GET['forumid'].' AND ';

    $res = do_mysql_query("SELECT topics.id as id, lastpost, lastpostread, readposts.id as rid FROM topics LEFT JOIN readposts on readposts.userid=$userid and topicid=topics.id where $where lastpost_time>'$dt'") or sqlerr(__FILE__, __LINE__);
                        
    while ($arr = mysql_fetch_assoc($res))
    {                  
      $topicid = $arr["id"];
      $postid = $arr["lastpost"];
      $lastpostread = $arr['lastpostread'];
      $id= $arr['rid'];
      
      if ($id == NULL)
         $buffer_insert[]= '('.$userid.','.$topicid.','.$postid.')';
      else if ($lastpostread < $postid)
         $buffer_update[] = '('.$id.','.$postid.')';
    }
    if (sizeof($buffer_insert)>0)
        do_mysql_query("INSERT DELAYED INTO readposts (userid, topicid, lastpostread) VALUES ".implode(', ',$buffer_insert)) or sqlerr(__FILE__, __LINE__);
    if (sizeof($buffer_update)>0)
        do_mysql_query('INSERT DELAYED INTO readposts (id,lastpostread) VALUES '.implode(', ',$buffer_update).' on duplicate key update lastpostread=values(lastpostread)');
     unset($buffer_insert);
     unset($buffer_update);
}


and yea i know u can do it all in one insert tongue.gif .. but i prefere it this way.. u can easily add them both to the same buffer if u want tongue.gif

what i ve done is removed the queries in the loop firstly.. then i used a buffer to store the values to be inserted so i do the whole insert in one query for each type of insert (the latter is similar to doing an update cause it uses the key id (primary) and the former is just a normal insert).. 3 queries in total max regardless of the number of posts u'r going to catchup wink.gif .. and only 2 table locks max.. (or 1 if u add them both to the same buffer)

theres actually one more optimization u can do in the select query in the beginning to speed it up but i ll let u figure that out on ur own wink.gif (u ll have to change the php logic as well a bit but the select will be around 1/4x faster)

Edit: also something else i remembered i did:

CODE
// ..rp..
$new = ($lppostadd > (get_date_time(gmtime() - $READPOST_EXPIRY))) ? (!$a || $lppostid > $a[0]) : 0;
//..rp..

before this theres a query that checks the readposts table which setes the $a variable...

u can do something like this to save a few queries:
CODE
       $dtc = get_date_time(gmtime() - $READPOST_EXPIRY);

        if ($dtc < $tlastposttime) {
                $r = do_mysql_query("SELECT lastpostread FROM readposts WHERE userid=$userid AND topicid=$topicid") or sqlerr(__FILE__, __LINE__);
                $a = mysql_fetch_row($r);
        }
$new= ($lppostadd > $dtc)?!$a || $lppostid > $a[0]:0;

this way if u have say 100 per page and 50 of those threads are older than the $readpost_expiry time u dont need to do those queries as u know there will be nothing new as u cant track them anyhow wink.gif ... so u save a few there (in my tests around 50% of the queries on average)

This post has been edited by jaits: Aug 26 2007, 04:11 AM

#AND

$dtc = get_date_time(gmtime() - $READPOST_EXPIRY);

        if ($dtc < $tlastposttime) {
                $r = do_mysql_query("SELECT lastpostread FROM readposts WHERE userid=$userid AND topicid=$topicid") or sqlerr(__FILE__, __LINE__);
                $a = mysql_fetch_row($r);
        }
$new= ($lppostadd > $dtc)?!$a || $lppostid > $a[0]:0;