LITTLEBLACKDOG.COM

Posted: Tue Apr 15, 2008 2:00 pm Post subject: SQL and PHP help

Maybe somebody that is better with SQL/PHP than I am can help me out with this.

I just upgraded the forums on my guild's website to phpbb3.1. I run Joomla as my CMS. I have a forum activity module that shows the 5 most recently active topics. This broke with the switch to phpbb3.1 due to them changing some of the tables around. So I looked around for a compatible one, and found none. So I checked the authors website. Supposedly he's been working on making it compatible for quite awhile now, and that doesn't seem to be happening anytime soon.

So, I browsed around on his forum, and one of the users there made it work. But there is one issue. I have one forum, forum id 24, that only myself and my other guild officers can see/use/even know it exists.

The patched up version of the module pulls topics out of that forum. Granted the user still can't browse that forum since they can't login and get access to it, but I don't even want them to see topic titles. Here is the select statement from the module, if anybody can tell me what to change to make it exclude forum id 24, it would be much appreciated.

Code: Select all




$query [] = "SELECT t.topic_id, u.username AS last_user,


          u.user_id AS first_user_id,


          pt.post_subject AS last_subject,


          p.post_time AS last_time,


          pt.post_text AS last_text,


          u2.username AS first_user,


          u2.user_id AS last_user_id,


                pt2.post_subject AS first_subject,


          t.topic_time AS first_time,


          pt2.post_text AS first_text


        FROM ".$phpbb_prefix."topics AS t


      INNER JOIN ".$phpbb_prefix."forums AS f


        ON t.forum_id = f.forum_id





        INNER JOIN ".$phpbb_prefix."posts AS p


        ON t.topic_last_post_id = p.post_id


        INNER JOIN ".$phpbb_prefix."users AS u


        ON p.poster_id = u.user_id


        INNER JOIN ".$phpbb_prefix."posts AS pt


        ON pt.post_id = t.topic_last_post_id


        INNER JOIN ".$phpbb_prefix."users AS u2


        ON u2.user_id = t.topic_poster


        INNER JOIN ".$phpbb_prefix."posts AS pt2


        ON pt2.post_id = t.topic_first_post_id


        ORDER BY last_time ".$result_order."


        LIMIT 0,".$result_limit.";";

If you want to see the full example, check here:

http://smartshitter.com/phpbb/viewtopic.php?t=50

He also mentions this in the last post:

Quote:
Now, I've used the other part, the AND forum_id IN (). With that, I managed to rule out some forums by placing NOT IN, and adding in the parentheses the forum_id i'm not interested in.

But I have no idea where I'm supposed to put that in the module to make it work. Tried inserting it in various places and it made no difference.

Posted: Wed Apr 16, 2008 4:40 am Post subject:

Just as a quick glance try this

Code: Select all







$query [] = "SELECT t.topic_id, u.username AS last_user,


          u.user_id AS first_user_id,


          pt.post_subject AS last_subject,


          p.post_time AS last_time,


          pt.post_text AS last_text,


          u2.username AS first_user,


          u2.user_id AS last_user_id,


                pt2.post_subject AS first_subject,


          t.topic_time AS first_time,


          pt2.post_text AS first_text


        FROM ".$phpbb_prefix."topics AS t


      INNER JOIN ".$phpbb_prefix."forums AS f


        ON t.forum_id = f.forum_id AND f.forum_id NOT IN (24)





        INNER JOIN ".$phpbb_prefix."posts AS p


        ON t.topic_last_post_id = p.post_id


        INNER JOIN ".$phpbb_prefix."users AS u


        ON p.poster_id = u.user_id


        INNER JOIN ".$phpbb_prefix."posts AS pt


        ON pt.post_id = t.topic_last_post_id


        INNER JOIN ".$phpbb_prefix."users AS u2


        ON u2.user_id = t.topic_poster


        INNER JOIN ".$phpbb_prefix."posts AS pt2


        ON pt2.post_id = t.topic_first_post_id


        ORDER BY last_time ".$result_order."


        LIMIT 0,".$result_limit.";";

It appears it should be on the first inner join.

Hope this helps.

Posted: Wed Apr 16, 2008 5:12 am Post subject:

Worked like a champ, thanks. I just couldn't figure out exactly where to put it, seemed like no matter where I tried it was throwing syntax errors.