[2.0.10] Highlight Topics User Posted In

[Extensions Robot]

MOD Name: Highlight Topics User Posted In
Author: fredol
MOD Description: Will highlight topics in which the user has posted ; can be done either with changind topics pictures, adding a new pic or changing topic's title color

MOD Version: 1.0.1

Download File: Highlight Topics User Posted In v1.0.1.zip
mods overview page: View
File Size: 15725 Bytes

Security Score:

[wGEric]

MOD Validated/Released

Notes:
This MOD makes it so that users know they have posted in a topic by either changing the image, changing the rows color, or putting another image next to the topic title.

[stickerboy]

Hey fredol, just installed this and i'm having a few problems.
I used parts 1 and 3 of the mod in case you wondered, and now i have this error showing up in viewforum.php -

Code: Select all

Could not obtain topic information

DEBUG MODE

SQL Error : 1066 Not unique table/alias: 'p3'

SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_time, p.post_username, p3.poster_id AS my_reply_id FROM (phpbb_topics t, phpbb_users u, phpbb_posts p, phpbb_users u2 LEFT JOIN phpbb_posts p3 ON p3.topic_id = t.topic_id AND p3.post_id != t.topic_first_post_id AND p3.poster_id = 3) LEFT JOIN phpbb_posts p3 ON p3.topic_id = t.topic_id AND p3.poster_id = 3) WHERE t.forum_id = 1 AND t.topic_poster = u.user_id AND p.post_id = t.topic_last_post_id AND p.poster_id = u2.user_id AND t.topic_type = 2 GROUP BY p.post_id ORDER BY t.topic_priority DESC, t.topic_last_post_id DESC 

Line : 305
File : /home/mrikasu/public_html/cherokeered/viewforum.php

I have a quick look over all the code and i'm sure that i sorted it all out, but still no change. it looks like it should work ok though

here's a copy of my viewforum file if you want to have a look yourself -
viewforum.php

*edit* seems my search is knackered aswell.
*edit 2* search fixed, i added after and not before

[MagiCat]

I kindof like it. The version I'm using has a little red dot in the post graphic. On http://www.trolltrain.com/phpbb/viewforum.php?f=22 is the only forum that guests are allowed to post. (our main forum also has some too because one of our admins loves to screw with me by deleting himself and reinstating himself ever few days.)

[fredol]

Hi,

Code: Select all

SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_time, p.post_username, p3.poster_id AS my_reply_id FROM (phpbb_topics t, phpbb_users u, phpbb_posts p, phpbb_users u2 LEFT JOIN phpbb_posts p3 ON p3.topic_id = t.topic_id AND p3.post_id != t.topic_first_post_id AND p3.poster_id = 3) LEFT JOIN phpbb_posts p3 ON p3.topic_id = t.topic_id AND p3.poster_id = 3) WHERE t.forum_id = 1 AND t.topic_poster = u.user_id AND p.post_id = t.topic_last_post_id AND p.poster_id = u2.user_id AND t.topic_type = 2 GROUP BY p.post_id ORDER BY t.topic_priority DESC, t.topic_last_post_id DESC 

Well seems you added two LEFT JOIN instead on just one here ! You have to add only one of them, according to what you want:
the first one will highlight topics where user have replied in
the second will highlight topics where user have posted in, reply or topic first post -- meaning it will highlight all their own topics (even if there are no reply) while the first one will hightlight them only if they have add a reply (besides the first post)

So remove one of thoose LEFT JOIN phpbb_posts p3 line and it should work fine! 8)
BTW you have to use the same one for both times in viewtopic.php (announcements & topics) & in search.php or in won't really make sense

Bye,
fredol

[stickerboy]

Thanks, got it sorted now.
might want to make that a bit more well known though. I didn't see it mentioned in the install guide.
If it's there i'm sorry, is should have looked a bit harder

Glad it's all working now though

*edit* just a little one i thought of, incase anyone wants the image to also be a link to the post.

Code: Select all

# 
#-----[ OPEN ]----- 
# 
templates/subSilver/viewforum_body.tpl
# 
#-----[ FIND ]----- 
# 
#Note: full line is longer
	  <td class="row1" width="100%"><span class="topictitle">{topicrow.NEWEST_POST_IMG}
# 
#-----[ IN-LINE DELETE ]----- 
# 
{topicrow.TOPIC_HIGHLIGHT_USERPOST_IMG}
# 
#-----[ FIND ]----- 
# 
<a href="{topicrow.U_VIEW_TOPIC}" class="topictitle">
# 
#-----[ AFTER, ADD ]----- 
# 
{topicrow.TOPIC_HIGHLIGHT_USERPOST_IMG}
# 
#-----[ SAVE & CLOSE ALL FILES ]----- 
# 
#
#-----EoM-----
#

[MagiCat]

Has anyone came up with any ideas for different graphics to use for subSilver, love the mod but I'm growing to hate the red dot and the other options don't appeal to me either.

[stickerboy]

Has anyone came up with any ideas for different graphics to use for subSilver, love the mod but I'm growing to hate the red dot and the other options don't appeal to me either.

Is the one with the mod not blue? I made mine red in adobe photoshop.
*edit* well i will be blue if you use part 3

[MagiCat]

Is the one with the mod not blue? I made mine red in adobe photoshop. :?
*edit* well i will be blue if you use part 3 ;)

Part 2 uses red dots. Tried part 3 but it makes it hard to read down the list of topics because of the way it indents. Thinking about making a custom graphic combined with an extra column (maybe between the left graphic and the topic title) and have it there.

[drathbun]

Quick question... why the "Group By Post_ID" clause?

You aren't going any other group functions (like count() or sum() and such) and you aren't grouping by the remaining fields in the select...

[stickerboy]

Is the one with the mod not blue? I made mine red in adobe photoshop.
*edit* well i will be blue if you use part 3

Part 2 uses red dots. Tried part 3 but it makes it hard to read down the list of topics because of the way it indents. Thinking about making a custom graphic combined with an extra column (maybe between the left graphic and the topic title) and have it there.

cool, sounds like a not bad idea.
might give it a try myself

[fredol]

Hi,

Quick question... why the "Group By Post_ID" clause?

Sorry for the late answer
The GROUP BY is used in order to avoid the same topics to show up more than once because of the LEFT JOIN I added, since you could have replied more than once to one same topic...

Bye,
fredol

[drathbun]

The GROUP BY is used in order to avoid the same topics to show up more than once because of the LEFT JOIN I added, since you could have replied more than once to one same topic...

But is that really what is going to happen? You're grouping by p.post_id and not p3.post_id which is the user's reply post, correct? If you group by p.post_id you group by the actual post, which is not the same thing as the check for the user replying to the post.

If you want an indication of whether a user has posted in a topic or not all you want is a flag. You don't even want to return the post row information at all, just a notification that it exists or not.

Also, left joins (outer joins) can be a real performance killer. Especially on a large or active board. Have you considered doing something like this?

Code: Select all

Get topics for page (already done, selected into topic_rowset)
Collapse topic_id values into a comma separated list (implode topic_rowset)
Check for user posts in those topics

In other words after getting a list of topics for the page you could run a second query that checks and sets a flag for the logged in user. An advantage is that you could check to see if the user is logged in before running this extra query, and eliminate the outer join if you don't actually need it. Assuming that you have a set of topic ID values in a string $topic_list then this is what would work:

Code: Select all

if ($userdata['session_logged_in'])
{
$sql = 'SELECT p.topic_id, sign(post_id)
FROM ' . POSTS_TABLE . ' p
where p.topic_id in ' . $topic_list . '
and p.poster_id = ' . $userdata['user_id'];

blah blah execute and test sql here

The results of that query would be an array of topic_id values with a flag of 1 where the user has responded. Now in the topic loop you simply check to see

Code: Select all

if ($topic_posted['topic_id'] == 1) {replace image as appropriate}

Doing it this way means that 1. There's no check run if the user isn't logged in, and 2. You eliminate an outer join which should help performance.

On my board I reduced the page generation times from 2 seconds down to 0.05 seconds by eliminating one outer join. I really dislike outer joins, and try to eliminate them whenever possible. So while it may seem to be less efficient to run a second query, in this case I believe that it should help quite a bit.

On a small board it may not be noticable.

In either case, I am not convinced that your SQL works the way you intended it to work.

[micron]

Hi!
I get an error:

Code: Select all

SQL Error : 1064 You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ') WHERE forum_id = 1' at line 3

SELECT * FROM (phpbb_forums LEFT JOIN phpbb_posts p3 ON p3.topic_id = t.topic_id AND p3.poster_id = ) WHERE forum_id = 1

Line : 72
File : X:\path\to\phpbb\viewforum.php

This is a part of code

Code: Select all

66:$sql = "SELECT *
67:FROM (" . FORUMS_TABLE . "
68:LEFT JOIN " . POSTS_TABLE . " p3 ON p3.topic_id = t.topic_id AND p3.poster_id = " . $userdata['user_id'] . ")
69:WHERE forum_id = $forum_id";
70:if ( !($result = $db->sql_query($sql)) )
71:{
72:message_die(GENERAL_ERROR, 'Could not obtain forums information', '', __LINE__, __FILE__, $sql);
73:}

As you can see, an $userdata['user_id'] expression appears to be empty.

[drathbun]

On my board I reduced the page generation times from 2 seconds down to 0.05 seconds by eliminating one outer join. I really dislike outer joins, and try to eliminate them whenever possible. So while it may seem to be less efficient to run a second query, in this case I believe that it should help quite a bit.

Tested, and it doesn't impact performance on my board at all. I used the original code as posted, and my alternate idea, and both performed equally well. Just thought I would post a note about that. 8)