[2.0.10] Highlight Topics User Posted In

The cleanup is complete. This forum is now read only.

Rating:

Excellent!
13
76%
Very Good
3
18%
Good
1
6%
Fair
0
No votes
Poor
0
No votes
 
Total votes: 17

Extensions Robot
Extensions Robot
Extensions Robot
Posts: 27981
Joined: Sat Aug 16, 2003 7:36 am

[2.0.10] Highlight Topics User Posted In

Post by Extensions Robot » Mon Jul 05, 2004 3:48 pm

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:
Last edited by Extensions Robot on Mon Apr 30, 2007 12:31 am, edited 1 time in total.
(this is a non-active account manager for the phpBB Extension Customisations Team)

wGEric
Former Team Member
Posts: 8805
Joined: Sun Oct 13, 2002 3:01 am
Location: Friday
Name: Eric Faerber
Contact:

Post by wGEric » Sat Aug 21, 2004 4:04 am

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.

User avatar
stickerboy
Former Team Member
Posts: 7349
Joined: Mon Mar 29, 2004 2:27 pm
Location: Airdrie, UK (127.0.0.1)
Name: Kenny Cameron
Contact:

Post by stickerboy » Sun Aug 22, 2004 11:25 am

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 :oops:

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. :oops:
*edit 2* search fixed, i added after and not before :wink:
I'm a web-designing code-decrypting tech-support musician
|| Twitter || Flickr || phpBB Snippets ||
Formerly known as cherokee red

MagiCat
Registered User
Posts: 131
Joined: Sat Dec 08, 2001 5:51 pm
Location: Sherman Oaks, CA
Contact:

Post by MagiCat » Sun Aug 22, 2004 11:52 am

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
Registered User
Posts: 188
Joined: Mon May 31, 2004 7:02 pm
Contact:

Post by fredol » Sun Aug 22, 2004 12:12 pm

Hi,
cherokee red wrote:

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:
:arrow: the first one will highlight topics where user have replied in
:arrow: 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

User avatar
stickerboy
Former Team Member
Posts: 7349
Joined: Mon Mar 29, 2004 2:27 pm
Location: Airdrie, UK (127.0.0.1)
Name: Kenny Cameron
Contact:

Post by stickerboy » Sun Aug 22, 2004 12:40 pm

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 :wink:

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-----
#
I'm a web-designing code-decrypting tech-support musician
|| Twitter || Flickr || phpBB Snippets ||
Formerly known as cherokee red

MagiCat
Registered User
Posts: 131
Joined: Sat Dec 08, 2001 5:51 pm
Location: Sherman Oaks, CA
Contact:

Post by MagiCat » Wed Aug 25, 2004 12:43 pm

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.

User avatar
stickerboy
Former Team Member
Posts: 7349
Joined: Mon Mar 29, 2004 2:27 pm
Location: Airdrie, UK (127.0.0.1)
Name: Kenny Cameron
Contact:

Post by stickerboy » Wed Aug 25, 2004 9:38 pm

MagiCat wrote: 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 ;)
I'm a web-designing code-decrypting tech-support musician
|| Twitter || Flickr || phpBB Snippets ||
Formerly known as cherokee red

MagiCat
Registered User
Posts: 131
Joined: Sat Dec 08, 2001 5:51 pm
Location: Sherman Oaks, CA
Contact:

Post by MagiCat » Wed Aug 25, 2004 11:55 pm

cherokee red wrote: 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.

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Post by drathbun » Thu Aug 26, 2004 12:05 am

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...
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

User avatar
stickerboy
Former Team Member
Posts: 7349
Joined: Mon Mar 29, 2004 2:27 pm
Location: Airdrie, UK (127.0.0.1)
Name: Kenny Cameron
Contact:

Post by stickerboy » Thu Aug 26, 2004 12:11 am

MagiCat wrote:
cherokee red wrote: 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 :wink:
I'm a web-designing code-decrypting tech-support musician
|| Twitter || Flickr || phpBB Snippets ||
Formerly known as cherokee red

fredol
Registered User
Posts: 188
Joined: Mon May 31, 2004 7:02 pm
Contact:

Post by fredol » Sat Aug 28, 2004 3:14 pm

Hi,
drathbun wrote: Quick question... why the "Group By Post_ID" clause?

Sorry for the late answer :roll:
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

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Post by drathbun » Sat Aug 28, 2004 6:07 pm

fredol wrote: 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.
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

micron
Registered User
Posts: 10
Joined: Sun Aug 29, 2004 12:27 pm

Post by micron » Sun Aug 29, 2004 12:33 pm

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.

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Post by drathbun » Sun Aug 29, 2004 1:42 pm

drathbun wrote: 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)
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

Post Reply

Return to “[2.0.x] MOD Database Cleanup”