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 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 (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

 

[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, 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