[PHP] Show the latest forum posts on your website.

Having a website is cool, having a forum is fun.. but it's really cool and fun when people post to your forum, and it shows up on your website.. See how to do that here.

For this example I am going to show you how to add the latest posts to your website from a PHPBB forum, like we do on Tutorialized.com.

What you need:
PHP, MySQL, PHPBB, a Website. Smile

I'll assume you already have PHPBB installed and running.

1) Okay, so first you need to setup your database connection in PHP so it can see the PHPBB tables:

Code:

<?php
$server = 'localhost';  // server location
$user = 'db_user';     // db username
$password = 'db_password';   // db password
$db = 'forum_db';  // db name

$link = mysql_connect("$server", "$user", "$password");
mysql_select_db("$db");

Now when we run a mysql query, PHP will know where to look for the tables.

2) Next, we create the query that will read from the PHPBB database the last 10 posts, by title, and sort them from newest to oldest, and then execute it:

Code:

<?php

$query = "select distinct t.topic_id, t.topic_title
        from posts p, topics t
        where p.topic_id = t.topic_id
        order by p.post_time desc
        limit 10";
$result = mysql_query($query);

So what did we do there, well I'll break it down.

We're selecting the topic_id, which is the unique identifier that each topic in PHPBB receives, and the name of the topic, topic_title. Both are located in the "topics" table (may be phpbb_topics in your database).

Next, we join the topic_id in the topics table with the topic_id in the posts table. The posts table contains the unique ids for each post that occurs inside a given topic. In the beginning of the query I also used the term "distinct", this will limit the results to show a topic only once, even if there are 3 new posts under that topic.

Then we tell mysql to sort the results from newest to oldest based on the time the posts took place (post_time), in the posts table.

And finally we tell mysql to only return the first 10 results.

3) So now that we have our results from mysql, we need to display them:

I like to display the results in an ordered list:

Code:

<?php
print "<ul>";
while ($row = mysql_fetch_assoc($result)) {
        $topic_id = $row['topic_id'];
        $topic_title = $row['topic_title'];
        print "<li><a href=\"/forum/viewtopic.php?t=$topic_id\">$topic_title</a></li>";
}
print "</ul>";

Wow, so what the heck did I just do? It's actually really simple!

First, I told php to grab the results from the query we did in step 2 and as long as the results were returning a row of data, to assign those results to the $row variable. The mysql_fetch_assoc function tells php to use the same column names that mysql uses, so topic_id in mysql is put into our $row variable as $row['topic_id']. Ta Da.

Then, for each row of data that mysql passes down to php, I print out the information.. only I do it in a slightly prettier format then just plain text.

Before the while statement, I started a list, and then for each row of data we're printing I put that row of data as an item in the list.

Also, rather then just printing out the title and only listing the last 10 topics.. I make it so the title of each topic has a link that goes to the forum and directly to the topic. This is done by setting your href to point to the "viewtopic.php" script in PHPBB and adding the topic_id to the passed url.

So, when you're all done and you've prettied up the text and added it to your website. Everytime someone posts to a topic in your forum, that topic will show up as the most recent topic and people from your website can just click on the link to be taken to your forum. Fun stuff with just a little code.

[Guest]

[37.736]

Yahoo crawler, Googlebot, Nikolas, Msnbot