PhpBB Categories

Discussion in 'Web Design and Development' started by JavaWizKid, Feb 16, 2009.

  1. JavaWizKid macrumors 6502a

    Joined:
    Sep 18, 2008
    #1
    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:
    <?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($resultMYSQL_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);
        
    ?>
     
  2. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #2
    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
     
  3. JavaWizKid thread starter macrumors 6502a

    Joined:
    Sep 18, 2008
    #3
    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
     
  4. JavaWizKid thread starter macrumors 6502a

    Joined:
    Sep 18, 2008
    #5
    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?
     
  5. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #6
    I see what ya did, and follow what you want. ETA 1-2 days from now.

    -jim
     
  6. JavaWizKid thread starter macrumors 6502a

    Joined:
    Sep 18, 2008
    #7
    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.
     
  7. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #8
    You define a single category ID to the script.
    It will display the category name and its 5 most recent topics (linked).

    -jim
     
  8. JavaWizKid thread starter macrumors 6502a

    Joined:
    Sep 18, 2008
  9. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #10
    Here it is, tested and working on my phpBB:

    PHP:
    <?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($resultMYSQL_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
     
  10. JavaWizKid thread starter macrumors 6502a

    Joined:
    Sep 18, 2008
    #11
    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.
     
  11. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #12
    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:

    PHP:
    $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:

    PHP:
    // 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
     
  12. JavaWizKid thread starter macrumors 6502a

    Joined:
    Sep 18, 2008
    #13
    What's PhpMyAdmin? I've never used it before. Do I download it separately?
     
  13. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #14
    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
     
  14. JavaWizKid thread starter macrumors 6502a

    Joined:
    Sep 18, 2008
    #15
    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
     
  15. JavaWizKid thread starter macrumors 6502a

    Joined:
    Sep 18, 2008
    #16
    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?
     
  16. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #17
    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
     
  17. JavaWizKid thread starter macrumors 6502a

    Joined:
    Sep 18, 2008
    #18
    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
     
  18. JavaWizKid thread starter macrumors 6502a

    Joined:
    Sep 18, 2008
    #19
    Doesn't matter anymore, I've found the PhpMyAdmin already installed. Thanks for your help :D
     
  19. JavaWizKid thread starter macrumors 6502a

    Joined:
    Sep 18, 2008
    #20
    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?
     
  20. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #21
    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.
     
  21. JavaWizKid thread starter macrumors 6502a

    Joined:
    Sep 18, 2008
  22. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #23
    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
     
  23. JavaWizKid thread starter macrumors 6502a

    Joined:
    Sep 18, 2008
    #24
    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?
     
  24. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #25
    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
     

Share This Page