Joined: Aug 28, 2003 Posts: 6373 Location: Vsetin, Czech Republic
Posted:
Fri Aug 12, 2005 5:59 am
I have a forum center block I'm using courtesy of lorkan themes - it came packaged with there theme.
I would like to restrict specific forum categories from showing in the results but have not got a clue when it come to mysql.
I did try to compare it with another center block I have but the sql calls seem completely different.
As the code contains some rather long lines I have a link below to a plain text file is anyone can help me out here.
Only registered users can see links on this board! Get registered or login to the forums!
I noticed this file uses $dbi as against my other I was using for comparison which uses $db - I seem to remember that using $dbi is a bit naughty?
If so, what are my options?
Joined: Aug 27, 2002 Posts: 16987 Location: Kansas
Posted:
Fri Aug 12, 2005 3:19 pm
Around line 37 is
Code:
$result = $db->sql_query( "SELECT t.topic_id, f.forum_name, f.forum_id, t.topic_last_post_id, t.topic_title, t.topic_poster, t.topic_views, t.topic_replies, t.topic_moved_id FROM ".$prefix."_bbtopics t, ".$prefix."_bbforums f where t.forum_id=f.forum_id ORDER BY topic_last_post_id DESC" );
Modify it to
Code:
$catIdsToSkip = '1,2,3';
$result = $db->sql_query( "SELECT t.topic_id, f.forum_name, f.forum_id, t.topic_last_post_id, t.topic_title, t.topic_poster, t.topic_views, t.topic_replies, t.topic_moved_id FROM ".$prefix."_bbtopics t, ".$prefix."_bbforums f where f.cat_id NOT IN($catIdsToSkip) AND t.forum_id=f.forum_id ORDER BY topic_last_post_id DESC" );
Joined: Aug 28, 2003 Posts: 6373 Location: Vsetin, Czech Republic
Posted:
Sat Aug 13, 2005 3:40 am
Thank you for taking the time to look at that!
Unfortunately it didnt work, the result was the block failing to load on the page with the rest of the 'center' page missing after it i.e. no news articles. Left blocks where fine etc.
Looking at your example above, I see on the last line the use of 'where f .cat_id' - as I am trying to learn something in this process, could this be the cause of the problem?
I see no other reference to 'f. cat_id' anywhere - how does the block code know what this is or how to use it?
I am assuming (theres that dangerous word again) the SELECT statement means, 'retrieve from the table' so I would need to add f. topic_id to that ?
Joined: Aug 27, 2002 Posts: 16987 Location: Kansas
Posted:
Sat Aug 13, 2005 10:19 am
Were you this much trouble as a child ?
Code:
<?php
########################################################################
# PHP-Nuke Block: Helius Center Forum Block v.9 #
# Made for PHP-Nuke 6.* and the phpbb2 Nuke port 2.06 only #
# #
# Made by coldblooded http://www.nukemods.com #
########################################################################
# This program is free software. You can redistribute it and/or modify #
# it under the terms of the GNU General Public License as published by #
# the Free Software Foundation; either version 2 of the License. #
# If you modify this, let me know for fun. =) #
########################################################################
## Modified by Gaylen Fraley (aka Raven) 8/13/2005 to allow blocking ##
## certain categories. Also changed $dbi calls to $db ##
########################################################################
if (eregi("block-PH2BLUE_CB-Forums.php",$PHP_SELF)) {
Header("Location: index.php");
die();
}
global $user_prefix, $db, $theme, $sitename, $admin, $bgcolor1, $bgcolor2, $bgcolor4;
$catIdsToSkip = '1,2,3';
$sql = "SELECT t.topic_id, f.forum_id, t.topic_last_post_id, t.topic_title, t.topic_poster, t.topic_views, t.topic_replies, t.topic_moved_id FROM ".$user_prefix."_bbtopics t, ".$user_prefix."_bbforums f where f.cat_id NOT IN($catIdsToSkip) AND t.forum_id=f.forum_id ORDER BY topic_last_post_id DESC";
$result = $db->sql_query($sql);
$result2 = $db->sql_query("SELECT username, user_id FROM ".$user_prefix."_users where user_id='$topic_poster'");
list($username, $user_id)=$db->sql_fetchrow($result2);
$avtor=$username;
$sifra=$user_id;
$result3 = $db->sql_query("SELECT poster_id, FROM_UNIXTIME(post_time,'%m/%d/%Y at %H:%i') as post_time FROM ".$user_prefix."_bbposts where post_id='$topic_last_post_id'");
list($poster_id, $post_time)=$db->sql_fetchrow($result3);
$result4 = $db->sql_query("SELECT username, user_id FROM ".$user_prefix."_users where user_id='$poster_id'");
list($username, $user_id)=$db->sql_fetchrow($result4);
Joined: Aug 28, 2003 Posts: 6373 Location: Vsetin, Czech Republic
Posted:
Sat Aug 13, 2005 10:53 am
I was much worse as a child - apparently I used to like burying my spectacles in the park sandpit and then forgetting where I had buried then - no improvement on my memory since lol.
Joined: Aug 28, 2003 Posts: 6373 Location: Vsetin, Czech Republic
Posted:
Sat Aug 13, 2005 11:13 am
LOL yeah but that might have been cheaper for my mother at the time lol
I think I'll come back to this tomorrow - seems it is just not my day today with one thing or another.....
OK I uploaded this code into a new block after deleting the original from the server.
The block itself load fine and the whole front page loads fine - only there is no content at all inside the center block, that is to say, no forum posts at all.
I tried it with both the forum cat (17) I need to exclude and with '0' and with no value at all.
Just in case, I tried it with IE and FF - see what I mean, not my day today lol.
Many thanks Raven for spending time on this, it IS appreciated but I'll revisit the problem after some sleep, I probbly did something stupid (again) and have not realised yet.
Joined: Aug 27, 2002 Posts: 16987 Location: Kansas
Posted:
Sat Aug 13, 2005 11:17 am
I did not test this at all. I just modified it on the fly. It sounds like you aren't getting any results back on your query. I'll do some testing a little later.
Joined: Aug 28, 2003 Posts: 6373 Location: Vsetin, Czech Republic
Posted:
Sun Aug 14, 2005 2:10 am
Thats much better Raven, thank you very much.
How do I amend that code to show the forum post subject?
At the moment it is displaying a 5 digit number. You can see it here
Only registered users can see links on this board! Get registered or login to the forums!
Joined: Aug 27, 2002 Posts: 16987 Location: Kansas
Posted:
Sun Aug 14, 2005 6:36 am
Quote:
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime!
Reference the following code excerpts
Code:
$sql = "SELECT t.topic_id, f.forum_name, f.forum_id, t.topic_last_post_id, t.topic_title, t.topic_poster, t.topic_views, t.topic_replies, t.topic_moved_id FROM ".$user_prefix."_bbtopics t, ".$user_prefix."_bbforums f where f.cat_id NOT IN($catIdsToSkip) AND t.forum_id=f.forum_id ORDER BY topic_last_post_id DESC";
When using the list function, the variable order must be the same as the variable order in your SELECT statement. If you look closely you will see that the SELECT statement references a column called f.forum_name, yet that column is missing from the list function. So, everything listed after that is 1 column off. Since f.forum_name is never used in that block code, just delete it from your SELECT statement, like this
Code:
$sql = "SELECT t.topic_id, f.forum_id, t.topic_last_post_id, t.topic_title, t.topic_poster, t.topic_views, t.topic_replies, t.topic_moved_id FROM ".$user_prefix."_bbtopics t, ".$user_prefix."_bbforums f where f.cat_id NOT IN($catIdsToSkip) AND t.forum_id=f.forum_id ORDER BY topic_last_post_id DESC";
Now the SELECT and list are in sync. I have corrected the code above also.
View next topic View previous topic
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
![[Valid RSS]](http://www.ravenphpscripts.com/images/valid-rss.png "Validate my RSS feed"){kind=small}
:: 
::
?