PhpBB Categories

[JavaWizKid]

How can I list all the posts in a category on my homepage using this code designed to show all the posts in a forum?

<?php
// How Many Topics you want to display?
$topicnumber = 5;
// Change this to your phpBB path
$urlPath = "/forum";

// Database Configuration (Where your phpBB config.php file is located)
include 'forum/config.php';

$table_topics = $table_prefix. "topics";
$table_forums = $table_prefix. "forums";
$table_posts = $table_prefix. "posts";
$table_users = $table_prefix. "users";
$link = mysql_connect("$dbhost", "$dbuser", "$dbpasswd") or die("Could not connect");
mysql_select_db("$dbname") or die("Could not select database");

$query = "SELECT t.topic_id, t.topic_title, t.topic_last_post_id, t.forum_id, p.post_id, p.poster_id, p.post_time, u.user_id, u.username
FROM $table_topics t, $table_forums f, $table_posts p, $table_users u
WHERE t.topic_id = p.topic_id AND
t.forum_id = 11 AND
f.forum_id = t.forum_id AND
t.topic_status <> 2 AND
p.post_id = t.topic_last_post_id AND
p.poster_id = u.user_id
ORDER BY p.post_id DESC LIMIT $topicnumber";
$result = mysql_query($query) or die("Query failed");

print "";
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

echo "<a href=\"$urlPath/viewtopic.php?f=$row[forum_id]&t=$row[topic_id]&p=$row[post_id]#p$row[post_id]\" TARGET=\"_blank\">" .$row["topic_title"] ."</a><br/>";
}
print "";
mysql_free_result($result);
mysql_close($link);
?>

[SrWebDeveloper]

Do you want the output broken out like this:

Category Name contains 2 Forums
Forum 1: Name
...Topic link for each post...
Forum 2: Name
...Topic link for each post...

Or do you not care and simply want all: ...Topic link for each post...

??

The latter is easier but it doesn't tell users where topics reside. Unless like I said, you could care less - i.e. you're writing a utility for admin purposes.

No matter what I'll give it a shot if you're patient and allow a few days, once you reply back, of course. I have a phpBB I can use to test.

-jim

[JavaWizKid]

I want to output all the posts in the forums in the category like:
category1
post1
post2
etc...

category2
post1
post2
etc...

I don't want to list the forum names. Thanks

[SrWebDeveloper]

All categories?

[JavaWizKid]

I have about 5 categories with forums in each of them wit posts in the forums. On my html page I will list the categories and just add the appropriate php code under each of them. I've been able to do this with the posts in forums but not the posts in the categories. Am I making sense?

[SrWebDeveloper]

I see what ya did, and follow what you want. ETA 1-2 days from now.

-jim

[JavaWizKid]

Will it just have the category then display all the posts in that category, regardless of what forum they are in. Also can you limit to display the latest 5 posts thanks.

[SrWebDeveloper]

You define a single category ID to the script.
It will display the category name and its 5 most recent topics (linked).

-jim

[JavaWizKid]

Yes :D Thanks!

[SrWebDeveloper]

Here it is, tested and working on my phpBB:

<?php

// Category ID
$categoryID = 1;
// How Many Topics you want to display?
$topicnumber = 5;
// Change this to your phpBB path - do not include a trailing slash!
$urlPath = "/forum";



// Database Configuration (Where your phpBB config.php file is located)
include $_SERVER['DOCUMENT_ROOT']."$urlPath/config.php";

$table_categories = $table_prefix. "categories";
$table_topics = $table_prefix. "topics";
$table_forums = $table_prefix. "forums";
$table_posts = $table_prefix. "posts";
$table_users = $table_prefix. "users";
$link = mysql_connect("$dbhost", "$dbuser", "$dbpasswd") or die("Could not connect to $dbhost");
mysql_select_db("$dbname") or die("Could not select database");

$query = "SELECT c.cat_title, t.topic_id, t.topic_title, t.topic_last_post_id, t.forum_id, p.post_id, p.poster_id, p.post_time, u.user_id, u.username
FROM $table_topics t, $table_forums f, $table_posts p, $table_users u, $table_categories c
WHERE c.cat_id='$categoryID' AND
f.cat_id = c.cat_id AND
t.forum_id = f.forum_id AND
t.topic_id = p.topic_id AND
p.post_id = t.topic_last_post_id AND
p.poster_id = u.user_id AND
t.topic_status <> 2
ORDER BY p.post_id DESC LIMIT $topicnumber";
// print "<pre>$query</pre><br /><br />";
$result = mysql_query($query) or die("Query failed");

$counter=0;
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

// Each topic link
$counter++;
echo ($counter==1) ? "<strong>".stripslashes($row[cat_title])."</strong><br /><br />" : "";
echo "<a href=\"$urlPath/viewtopic.php?f=$row[forum_id]&t=$row[topic_id]&p=$row[post_id]#p$row[post_id]\" TARGET=\"_blank\">" .stripslashes($row["topic_title"])."</a><br/>";

}
print "";

mysql_free_result($result);
mysql_close($link);

?>

Instructions:

1) Configure the first 3 variables in the top
2) I displayed the category name in bold for you at the top of the output, you can easily modify the HTML as you see fit in the bottom of the code.

-jim

[JavaWizKid]

I've changed the things you told me to and I get a Query Failed error on my website. Why is this? The Php is correctly pointing to the config.php file.

[SrWebDeveloper]

Could be you have a newer phpBB with different table and field names, as it worked fine on mine.

Make sure (use phpMyAdmin to check):

1) The name of the categories table ends with "categories" (it'll start with the table prefix as set in config.php)

If different, edit this line in the script to the proper value:

$table_categories = $table_prefix. "categories";

2) The name of the fields in that table are "cat_id" for the category ID and "cat_title" for the category title, otherwise replace all instances in the script.

If that doesn't do it, uncomment this line:

// print "<pre>$query</pre><br /><br />";

That way it'll dump the query to the screen so you can copy/paste it into phpMyAdmin SQL editor to see what the error is. Then fix based on that, or copy/paste the error here if you need help.

-jim

[JavaWizKid]

What's PhpMyAdmin? I've never used it before. Do I download it separately?

[SrWebDeveloper]

No wonder you're coming here to find out what queries to use. After you download it, you won't even need us anymore for stuff like this. I'm scratching my head wondering how you wrote the first script without it!

In a nutshell, it's a web based administrative tool for MySQL. You can create/edit the structure of databases, tables and fields, run queries, generate basic PHP code based on queries, optimize or repair tables, backup and restore, adjust user privileges and so on. In your case, you'd use it to open up the phpBB database and examine the table structures so you know all the correct table and field names to use. I used it in the same manner to write your script.

It's free and very easy to use and has plenty of documentation.

Download it here (http://www.phpmyadmin.net/home_page/downloads.php) (read page carefully as to which version to get based on your PHP and MySQL versions)

-jim

[JavaWizKid]

I didn't write it, I copied it off a website and changed it slightly. However it didn't work the way I'd have liked it to so I came here. I'm downloading MyPhpAdmin now and I'll let you know how I get on. Thanks

[JavaWizKid]

I've uploaded PhpMyAdmin to my web host, and I am VERY confused. I've logged in and it says the server isn't responding. Isn't there anything simpler to set up?

[SrWebDeveloper]

I've uploaded PhpMyAdmin to my web host, and I am VERY confused. I've logged in and it says the server isn't responding. Isn't there anything simpler to set up?

It's been only a few minutes, jeesh. Read the documentation, learn about connections to databases and configure the config file for phpmyadmin if you want to run scripts that access MySQL. This really is the simplest open source utility out there, the most popular, and entirely web based so you can remote manage from anywhere. Talk to your webhost as to settings for MySQL, you already know the user and password - it's set in config.php for phpBB! I wouldn't be surprised if your webhost already has phpMyAdmin installed for you somewhere, wouldn't hurt to ask - it's a very common utility on most webhost platforms with PHP and MySQL.

Point is, you need to put some effort into this. I did my part including offering very specific advice on how to fix. I'm quite satisfied I can do no more to assist you than I already have. It's time for me to move on.

-jim

[JavaWizKid]

I'm used to using the command prompt for managing MySQL. I created a website a while ago that used a MySQL database and I used the command prompt to manage it, however it was local. Is there a way I can connect to my database this way. I appreciate your help a lot, however I'm not really wanting to configure more software than I already have. Thanks

[JavaWizKid]

Doesn't matter anymore, I've found the PhpMyAdmin already installed. Thanks for your help :D

[JavaWizKid]

Could be you have a newer phpBB with different table and field names, as it worked fine on mine.

Make sure (use phpMyAdmin to check):

1) The name of the categories table ends with "categories" (it'll start with the table prefix as set in config.php)

If different, edit this line in the script to the proper value:

$table_categories = $table_prefix. "categories";

2) The name of the fields in that table are "cat_id" for the category ID and "cat_title" for the category title, otherwise replace all instances in the script.

If that doesn't do it, uncomment this line:

// print "<pre>$query</pre><br /><br />";

That way it'll dump the query to the screen so you can copy/paste it into phpMyAdmin SQL editor to see what the error is. Then fix based on that, or copy/paste the error here if you need help.

-jim


There is no category in the table structure, they are down as forums. Should I just make the PHP code list the posts in the forum with the id of the category?

[angelwatt]

There is no category in the table structure, they are down as forums. Should I just make the PHP code list the posts in the forum with the id of the category?

You say there's no category, but you have an id for the category in the forum. Obviously that id has to point somewhere. Perhaps it isn't an obvious name like category, or perhaps you've overlooked it.

According to this site (http://www.phpbbdoctor.com/doc_columns.php?id=3), the table should be named, phpbb_categories.

[JavaWizKid]

Nope, I don't have that.

[SrWebDeveloper]

Need to add this, for posterity:

1) You missed it and need to continue looking
2) You installed a phpBB hack which modified the original table structure
3) You are using a release candidate or beta or maybe some special version that has been altered by a hacker or skin developer

If either of the latter two, you're on you're own and the fix involves the exact advice I gave earlier to research the table/field structure via phpMyAdmin.

Based on my testing and also the web page that angelwatt listed, the table and field names I added to your script are correct for a standard phpBB and the script is performing to your specs.

-jim

[JavaWizKid]

I haven't missed it because I used ctrl f to search for it and it wasn't there.
I haven't installed ant PhpBB hacks, it is a clean install which my web host performed for me.
I don't think I'm using a beta. How can I check, also should I try a reinstall to see if it fixes anything?

[SrWebDeveloper]

Don't reinstall anything, please.

Version info is in the footer, and if not there due to hacking it should also be in the footer of the Admin CP for sure.

Go into phpMyAdmin, select the database used by your phpBB. Then select the structure tab. Look for the icon/link that says "Data Dictionary" click on that, copy/paste and zip it up or name as phpbb.txt and file attach so I can have a look. if you can't find that icon, use the print view icon instead and do the same. The data dictionary lists every table and field in one report so I prefer that one. If I find a category table there then I will ask you to write me a check for $500.00 US. Fair enough?

(I'm kidding about the check, but not the rest) ;)

-jim

[JavaWizKid]

I've attached it for you. I looked again and couldn't find it. I bet you somehow it's right in front of my eyes lol.

[SrWebDeveloper]

Thanks for sending. I figured out what's wrong.

I downloaded phpBB 3.0.4 which is the latest stable release and examined the database schema files via phpMyAdmin and compared with yours. They matched so your database is perfect, but nothing matched mine. Meaning....

The folks at phpBB have completely re-designed the category system in this series, removing the old table. The other tables for topics, forums and posts are nearly identical - but not for categories. Your original script worked on my phpBB, for example. I suspect this change is very recent because the documentation angelwatt found clearly is outdated already, and so is my phpBB which isn't that old. phpBB is entitled to improve their product, but at the same time hackers need to re-learn the new way of doing things too.

I looked at the new database format, and it's not obvious how categories are handled internally. I won't be upgrading so you need to approach one of the phpBB hack sites like this one (http://www.phpbb.com/community/viewforum.php?f=81) and ask anyone who is knowledgeable of the new format and willing to write you a new script that works specifically on 3.0.4 - be sure to mention the version as it matters!

If anyone else is following this, feel free to help if you have the latest installed and are familiar with database concepts.

-jim

[JavaWizKid]

I've submitted a request: Here (http://www.phpbb.com/community/viewtopic.php?f=72&t=1453625)

[SrWebDeveloper]

Also consider using the link I provided you and posting to the "3.0.x Mod Requests" forum there. That's where I would post on said issue.

Because your original script worked on my phpBB, I could not have foreseen this "complication" simply by introducing a category constraint, and phpBB has used the same category format for roughly 8 years. Until, of course, now! Sorry if I seemed to get a little miffed, it was totally perplexing at the time and a complete surprise you couldn't get it to work considering I tested it, blah blah blah.

Glad we got to the bottom of it, eventually, enjoy your shiny new phpBB and hope someone out there helps you out soon. Cheers and good day.

:cool:

-jim

[JavaWizKid]

That is where my link is pointing to :) Thanks for the help.

[JavaWizKid]

I still haven't had a reply but I've made a work around. I display each forum underneath each other under a piece of html stating its category. It does its job. :) I'm now wondering how I can add the actual post content to the homepage. I'm going to replace my wordpress with forum posts on the homepage. Any ideas thanks. :D

[JavaWizKid]

Anyone know?

[JavaWizKid]

Is it possible?