Posted by kiryoku on October 19, 2007 at 4:58pm
I would like to display the number of unread posts in a block as seen on http://kepp.net/phpbb3/, but I'm using phpbb2.
Anyone who has the code for it? :)
Categories: phpBB
I would like to display the number of unread posts in a block as seen on http://kepp.net/phpbb3/, but I'm using phpbb2.
Anyone who has the code for it? :)
Comments
// New posts since
// New posts since last visit$query = "SELECT COUNT() FROM {$phpbbcfg['db_posts']} LEFT JOIN {$phpbbcfg['db_users']} ON {$phpbbcfg['db_posts']}.post_time > {$phpbbcfg['db_users']}.user_lastvisit WHERE user_id = ". $user->uid;
$res = db_query($query);
$row = db_fetch_array($res);
$url = $phpbbcfg['path_abs'].'search.php?search_id=newposts&sr=posts';
$list[] = "<a href=\"$url\">Unread posts ({$row['COUNT()']})\n";
return theme('item_list', $list);
I haven't spent much time looking at it, but a variation on this should work in the old module too. Obviously you have no $phpbbcfg variable, so you will have to replace {$phpbbcfg['db_posts']} and the likes with the names of your tables.
Read up a bit on how blocks are defined in the Drupal documentation, then have a look at the Recent Topics block in the old module, and then the new block in the phpBB 3.x module.
I've got it running, but I
I've got it running, but I guess there's something wrong with the query...
In the brackets it says 4, but when I click on it I get three search results with only one being marked as new... In other words the correct output would have been (1).
Any ideas?
Here's the code:
<?php
$phpbb_dbc = _phpbb_db();
$prefix = _phpbb_prefix();
global $user;
$query = "SELECT COUNT(*) FROM phpbb_posts LEFT JOIN phpbb_users ON phpbb_posts.post_time > phpbb_users.user_lastvisit WHERE user_id = ". $user->uid ."";
$res = mysql_query($query);
$row = db_fetch_array($res);
$url = '/forum/search.php?search_id=newposts';
$list[] = "<a href=\"$url\">({$row['COUNT(*)']})";
return theme('item_list', $list);
?>
Good job. So this thing only
Good job. So this thing only shows new topics / posts since you were last logged out, which may not be ideal if your users do not log out. In phpBB 3.x there are two tables, topics_watch and forum_watch, that keep track of what has been viewed.
phpBB 2.x does things differently. If I remember correctly, there is a cookie _data that contains an array with the ids of topics you have viewed in the current session. So I am guessing showing the number of new topics would be to use the count from the code above, and subtract the size of this array.
Posts would be more tricky, you would have to use the contents of the array to filter in the SQL query, but I also think it's less important.
When googling around I found
When googling around I found phpbbfetchall, the solution may be in this part, but while I've tried to copy the mysql select command, I still haven't got the results I wanted. I'll post the solution once I get it running. :)
<?php
###############################################################################
## ##
## phpbb_fetch_newposts() ##
## ------------------------------------------------------------------------- ##
## This function will fetch new postings based on the user session. It's has ##
## the same results as the search.php?search_id=newposts script. ##
## ##
## EXAMPLE ##
## ##
## $newposts = phpbb_fetch_newposts(); ##
## ##
## for ($i = 0; $i < count($newposts); $i++) ##
## { ##
## echo $newposts[$i]['post_text'] . '<br>'; ##
## } ##
## ##
###############################################################################
function phpbb_fetch_newposts()
{
global $CFG, $userdata;
if (!$userdata['session_logged_in'])
{
return;
}
$sql = 'SELECT post_id
FROM ' . POSTS_TABLE . '
WHERE post_time >= ' . $userdata['user_lastvisit'];
$result = phpbb_fetch_rows($sql);
if (!$result)
{
return;
}
$search_ids = array();
for ($i = 0; $i < count($result); $i++)
{
$search_ids[] = $result[$i]['post_id'];
}
$sql = 'SELECT topic_id
FROM ' . POSTS_TABLE . '
WHERE post_id IN (' . implode(', ', $search_ids) . ')
GROUP BY topic_id
ORDER BY post_time DESC';
$result = phpbb_fetch_rows($sql);
if (!$result)
{
return;
}
$topic_ids = array();
for ($i = 0; $i < count($result); $i++)
{
$topic_ids[] = $result[$i]['topic_id'];
}
$result = phpbb_fetch_topics($topic_ids, POSTS_FETCH_LAST);
return $result;
} // end func phpbb_fetch_newposts
?>
When I tried to use
When I tried to use phpbbfetchall with drupal I run into the following problem:
fatal error: Call to a member function sql_query() on a non-object
when looking at the session-file it says the following:
if (!($result = $db->sql_query($sql)))
Does this crash with some function in drupal? This "test" was performed on a non-modded phpbb board with a new Drupal5 installation,
however, my modded phpbb2x board, it tells me about the same problem in another file. When looking at the file it says the same as the code above.
Anyone who knows why Drupal complaints about this? Any way of fixing it so that I can make use of phpbbfetchall? It would solve quite a few problems...
PHP does not support
PHP does not support namespaces, and both phpBB and Drupal have global variables named $db -> collision. Not mut you can do about that, unless you change the name of the variable throughout one of the applications. One of the reasons I haven't tried to truly integrate phpBB and Drupal.
If I can get it to work I
If I can get it to work I wouldn't mind to edit the variables of phpbb if possible.
But I don't really get where the collision occur... I've tried to change the $db variable in all documents to $dbf, but it didn't do the trick.
Have you tried this before? What exactly do I have to change/replace? Is it only the "global $db" variables or is there more?
Thanks alot for your help
Did the error message
Did the error message change? If not you forgot one, maybe (probably) a class definition. It could be in an external library, but I don't recall either of the programs.
I think your time is spent more wisely looking at the search code in phpBB and the stuff I gave you, it will give you much better performance anyway.