Creating a Recent Posts modul

Marked · September 6, 2010

Creating a Recent Posts module

This tutorial shows you the various steps of how to create a little box that displays the latest posts of a forum using MySQL and PHP.

Step 1. Install Sample Data

For this tutorial I will provide you with a .SQL file containing the data we're going to use. If you're attempting this tutorial you should have something like XAMPP installed. I won't go into too much detail on how to install the sample data as this should be known, but I'll briefly go through the steps in XAMPP.

1. Open PHPMyAdmin in your browser (localhost/phpmyadmin/).

2. Create a new database (any name will do) - you should see the form right there on the page

3. Once created it should be selected on the left-hand panel, if not, select it

4. Next select "Import" and import the below .SQL document.

sample data.zip

Or you can run the following in a query:

CREATE TABLE IF NOT EXISTS `members` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(32) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `members`
--

INSERT INTO `members` (`id`, `name`) VALUES
(1, 'Marked'),
(2, 'Leon'),
(3, 'Kiriashi'),
(4, 'Chief');

-- --------------------------------------------------------

--
-- Table structure for table `posts`
--

CREATE TABLE IF NOT EXISTS `posts` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `author_id` int(11) NOT NULL,
 `post_date` varchar(32) NOT NULL,
 `post` varchar(9999) NOT NULL,
 `topic_id` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

--
-- Dumping data for table `posts`
--

INSERT INTO `posts` (`id`, `author_id`, `post_date`, `post`, `topic_id`) VALUES
(2, 1, '1159984164', 'That's right! I'm back! Be afraid, be very afraid! The Dark One has arrived at yet ANOTHER RMXP forum. Mwahahahahahahahahahaha.<br /> <br />', 1),
(3, 2, '1160016909', ' 	Welcome to the new forum SCMike! You are my first new member, and the very first person to post here. You can expect many more updates to the forum.', 3),
(4, 3, '1160016909', ' 	Welcome to the new forum SCMike! You are my first new member, and the very first person to post here. You can expect many more updates to the forum.', 2),
(5, 4, '1160061319', ' 	Yes, here I am, the one... the lord, the mighty warrior... or something lol<br /> <br /> I am at least known as the following names:<br /> <br /> Ravenclaw105<br /> Ravenclaw<br /> Raven<br /> Rave......', 4),
(6, 1, '1160061454', ' 	
[center][size=7][font="Arial"][color="blue"][b]DEM ON AND ANGEL[/b][/size][/color][/font]<br /> [size=5][color="red"][font="Arial"][b]-Forbidden love-[/b][/size][/color][/font][/center]
<br /> <br />......', 5),
(7, 2, '1170888393', ' 	Hi.<br /> <br /> I'm leaving the board, I've removed all info from my game topic and all my resources. You may remove my user as well.<br /> <br /> <br /> Farewell everyone!', 6),
(8, 3, '1170888876', '<img src=''http://www.rmxpunlimited.net/forums/public/st yle_emoticons/<#EMO_DIR#>/biggrin.gif'' class=''bbc_emoticon'' alt='':D'' /> . Well I hope you find a new home. <img src=''http://www.rmxpunlimited.......', 7),
(9, 4, '1160101048', 'Hello all.<br /> <br /> My name's Constance, the advanced scripter of RMXP and well I'm here to help the community.<br /> <br /> Hello everyone. <img src=''http://www.rmxpunlimited.net/forum....', 8),
(10, 1, '1160137123', 'game, JoH: CoC didn't work out because of graphic issues, and the second game didn't work out because of graphic', 9),
(11, 2, '1160156120', ' 	Yeap, I decided I want (but don't need) voice actors for my Keys to Destruction game. First off, here's a link to the Keys of Destruction info topic.<br /> <a href="http://www.rmxpunlimited......', 10);

-- --------------------------------------------------------

--
-- Table structure for table `topics`
--

CREATE TABLE IF NOT EXISTS `topics` (
 `tid` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(32) NOT NULL,
 PRIMARY KEY (`tid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `topics`
--

INSERT INTO `topics` (`tid`, `title`) VALUES
(1, 'Download Manager'),
(2, 'Deaf Felix'),
(3, 'Applications open'),
(4, 'Advanced Message Script - Releas'),
(5, 'Looking For Staff'),
(6, 'A Dragon - Take a look.'),
(7, 'Important'),
(8, 'Soul Spring'),
(9, 'Slowness and Downtimes'),
(10, 'COME ON NOW');

It should end up looking similar to this:

Step 2. Connecting to the Database

Now that we have all our data in the database, we need to connect to it so we can make queries. Most PHP tutorials that you find involving a connection to the database use the PHP mysql_ functions (Example: http://www.w3schools.com/php/php_mysql_connect.asp), but these have been obsolete for 6 years. We are going to use a library called PDO. It's not that hard to use, really.

Recommended reading on PDO: http://www.webdevrefinery.com/forums/topic/1272-your-mysql-code-sucks/

I recommend reading that tutorial, it doesn't take too long. At the least it can been used as a reference.

Alright, now lets get into some code :) First up, we're going to connect to our database. A lot of tutorials have a separate PHP file to connect to their database, but I find this annoying and makes more complicated especially for the less experienced coders. So I'm going to keep it all in one PHP file. So, create your PHP file in htdocs(In XAMPP) or wherever and open it up in your choice of editor, and run it in your browser too.

Alright, here's how we are going to connect to our database:

1.  $host = 'localhost';
2.  $port = 3306; // This is the default port for MySQL
3.  $database = 'tutorial';
4.  $username = 'root';
5.  $password = '';
6.
7.  $dsn = "mysql:host=$host;port=$port;dbname=$database";
8. 
9.  // Connect
10. $db = new PDO("mysql:host=$host;port=$port;dbname=$database", $username, $password);

Lines 1 to 5 are the basic details we need to connect to the database. $database is the name of the database you created in step 1, don't forget to change that. By default in XAMPP, the username is 'root' and there is no password so we set it to blank.

Line 7 basically creates a string from all of our variables. In reality we can use just one line to connect to the database, for example:

$db = new PDO('mysql:host=localhost;port=3306;dbname=tutorial', 'root', '');

Then we connect to the database on Line 10 using all of the information . Pretty simple, right? Now we are ready to execute some queries :).

Step 3. Selecting the Latest Posts from the database

If you have no idea how to write a simple MySQL query then unfortunately you are going to struggle to understand this next part and you may be better off just copying the code from it and moving on to the next step. However I will try explain it the best I can. Of all the tutorials I read about left joins in MySQL queries, I never really understood what was going on, I eventually figured it out myself. Read through it anyway and we'll see how it goes.

In the sample data we have 3 tables: Posts, Members and Topics. If you look at a recent posts module it displays a topic names ordered by the date of the last post in them, and then the members username that made that topic. Therefore, each of these tables have a relationship to each other. Relationships are established through ID's (which is usually the first column of every table). For example, the table 'Posts' has a field called 'author_id'. In the following screenshot from the sample data, I have highlighted all posts by the member who has an ID of 1.

So these particular rows have a relationship with the 'Members' table. Lets have a look at the 'Members' table to make these more clear...

As you can see, the member with an ID of 1 has the username 'Marked'. So what is the point in this relationship? Well, using 'author_id' from Posts, we can select the username from the Members table. And that's basically what I'm trying to explain, we're finding a link between tables and getting various information from both.

Here's one last effort to try and make the idea clearer in your mind.

Alright, see where the Members and Topics table connect to the Posts table? You can see the fields that link them together, allowing us to get the title from Topics and name from Members.

Now, how do we establish this relationship in MySQL? You need to be somewhat familiar with the syntax, good thing is that it's really simple to learn. I'll start really basic... Say you wanted to the latest 5 posts, and only wanted the post and its date. The syntax would be (how to implement this in PHP will be explained after, this is just showing MySQL syntax):

SELECT post,post_date FROM posts ORDER BY post_date DESC LIMIT 5

And this gives us:

SELECT is the syntax used to get information to be used. Pretty simple, it selects fields. Next comes the fields you want to select. Its common to use the wildcard * to select everything. After this is 'FROM [table]' which determines which table we are selecting from. We could stop there and that would work, but we want to order it by most recent. The syntax is 'ORDER BY' followed by the name of the field you want to order it by. In the case we used post_date, but we could also use id. DESC orders by the largest number first, ASC by the lowest number first (if you don't specify, its ASC by default). Next is the limit, this basically sets a cut off point. LIMIT 5 will make it stop at the first 5. If you don't specify, all rows will be selected. You can also do things like LIMIT 5,10 which selects the rows from 5 to 10.

Ok that's a crash course in MySQL syntax. Now lets construct the query we are going to use. But first we need to know about left joins real quick. Recommended reading: http://www.w3schools.com/sql/sql_join_left.asp . In short, a simple left join looks like this:

SELECT posts.post,members.name FROM posts LEFT JOIN members ON members.id=posts.author_id

So as you can see, this is how we establish the relationship between two tables and connect their fields together. In this bit: 'members.id=posts.author_id', both fields relate to the same set of ID's, that is, the ID's of the members. This example query returns the following:

Everything in this step so far has been to try and explain the query we're going to write to get all the information so we can start constructing the module on the page.

Ok, here it is. This will connect all 3 tables together.

SELECT topics.title,topics.tid,members.name,posts.post_date 
FROM topics 
LEFT JOIN posts ON posts.topic_id=topics.tid 
LEFT JOIN members ON members.id=posts.author_id 
ORDER BY posts.post_date DESC 
LIMIT 10

And this gives us:

Step 4. Display the information on the page

Alright, lets get into some PHP. Now we need to run the query using our PDO object we setup in Step 2.

By now you should have the following in your PHP file from step 2:

<?php 
$host = 'localhost';
$port = 3306; // This is the default port for MySQL
$database = 'tutorial';
$username = 'root';
$password = '';

$dsn = "mysql:host=$host;port=$port;dbname=$database";

// Connect
$db = new PDO("mysql:host=$host;port=$port;dbname=$database", $username, $password);
?>

Here is how to execute our query:

//Prepare query
$statement = $db->prepare("SELECT topics.title,topics.tid,members.name,posts.post_date 
FROM topics 
LEFT JOIN posts ON posts.topic_id=topics.tid 
LEFT JOIN members ON members.id=posts.author_id 
ORDER BY posts.post_date DESC 
LIMIT 10");
//Execute query
$statement->execute();

//Start the while loop
while ($result = $statement->fetchObject()) {
   echo $result->title;
   echo "<br />";
}

A lot of this is PDO syntax such as $statement = $db->prepare and $statement->execute(). After we do these we have all our information stored in the $statement variable ready to use.

Next we have our while loop. This allows us to execute some code for each individual row that we selected in our query. We can access each field we selected by using the '$result' variable followed by an '->' followed by the field name. For example, to get the topic ID, I use '$result->tid'.

Your PHP file should now look like this:

<?php 
$host = 'localhost';
$port = 3306; // This is the default port for MySQL
$database = 'tutorial';
$username = 'root';
$password = '';

$dsn = "mysql:host=$host;port=$port;dbname=$database";

// Connect
$db = new PDO("mysql:host=$host;port=$port;dbname=$database", $username, $password);

//Prepare query
$statement = $db->prepare("SELECT topics.title,topics.tid,members.name,posts.post_date 
FROM topics 
LEFT JOIN posts ON posts.topic_id=topics.tid 
LEFT JOIN members ON members.id=posts.author_id 
ORDER BY posts.post_date DESC 
LIMIT 10");
//Execute query
$statement->execute();

//Start the while loop
while ($result = $statement->fetchObject()) {
   echo $result->title;
   echo "<br />";
}
?>

Give that a test. It should look like this in your browser:

Step 5. Inside the While Loop

Now lets work within the while loop and setup all the information we selected. I hope you're familiar with HTML.

Lets wrap every row within a div, and also wrap the entire while loop in a div:

//Start the while loop
?><div style="width:100px"><?
while ($result = $statement->fetchObject()) {
   ?><div></div><?
}
?></div><?

I have mixed html with PHP by ending the php tags and starting them again. There are various ways to mix the two, but I just use this method. I styled the outer div to make it look not so ugly, which really should be done in CSS.

Add the Topic Title

We want this to be linked to the actual topic, so that is why we have selected the ID. Here's how its done:

//Start the while loop
?><div style="width:200px;border:1px solid #000;padding:2px"><?
while ($result = $statement->fetchObject()) {
   ?><div><a href="http://www.rmxpunlimited.net/forums/index.php?showtopic=<?=$result->tid?>"><?=$result->title?></a></div><?
}
?></div><?

This gives us:

So what we have done is linked all of our topic titles to their topics.

I have used:

<?=$result->title?>

But its more common to use:

<? echo $result->title?>

Either is fine.

Add the Author's Name

This is basically the same thing. I forget to add the members ID to the query, so we won't be linking to the members Profile... Here's the code we will add:

<p>By <?=$result->name?></p>

So our while loop now looks like this:

//Start the while loop
?><div style="width:200px;border:1px solid #000;padding:2px"><?
while ($result = $statement->fetchObject()) {
   ?><div><a href="http://www.rmxpunlimited.net/forums/index.php?showtopic=<?=$result->tid?>"><?=$result->title?></a></div>
<p>By <?=$result->name?></p><?
}
?></div><?

Which comes out like this:

Yes, its ugly. But, it has all the info of a pretty module :) Its whats on the inside that counts... er.. yeah.

Add the date

Ok, here's some real PHP stuff. The date are stored in timestamps. Here's one from the sample data '1170888393'. This number is the number of seconds since January 1 1970 until the date of the post. Timestamps are pretty useful actually. There are many ways we can use it to display the time.

For this tutorial we are going to use the PHP date function. Here's the syntax:

string date  ( string $format  [, int $timestamp  ] )

echo date("[a string]",timestamp);

Now the string we need a reference for. There are various combinations we can use which are listed here: http://php.net/manual/en/function.date.php

For this tutorial we wanted the hours followed by the day of the month and month. So from that link we can get the following string: "hA - d M".

So to use the date function in our tutorial, we can write this:

date("hA - d M",$result->post_date)

Lets add this to our while loop:

?><div style="width:200px;border:1px solid #000;padding:2px"><?
while ($result = $statement->fetchObject()) {
   ?><div><a href="http://www.rmxpunlimited.net/forums/index.php?showtopic=<?=$result->tid?>"><?=$result->title?></a></div>
<p>By <?=$result->name?> at <?=date("hA - d M",$result->post_date)?></p><?
}
?></div><?

And this will look like this:

Here is our PHP file at this stage:

<?php 
$host = 'localhost';
$port = 3306; // This is the default port for MySQL
$database = 'tutorial';
$username = 'root';
$password = '';

$dsn = "mysql:host=$host;port=$port;dbname=$database";

// Connect
$db = new PDO("mysql:host=$host;port=$port;dbname=$database", $username, $password);

//Prepare query
$statement = $db->prepare("SELECT topics.title,topics.tid,members.name,posts.post_date 
FROM topics 
LEFT JOIN posts ON posts.topic_id=topics.tid 
LEFT JOIN members ON members.id=posts.author_id 
ORDER BY posts.post_date DESC 
LIMIT 10");
//Execute query
$statement->execute();

//Start the while loop
?><div style="width:200px;border:1px solid #000;padding:2px"><?
while ($result = $statement->fetchObject()) {
   ?><div><a href="http://www.rmxpunlimited.net/forums/index.php?showtopic=<?=$result->tid?>"><?=$result->title?></a></div>
<p>By <?=$result->name?> at <?=date("hA - d M",$result->post_date)?></p><?
}
?></div><?
?>

The next step is to do the CSS and make it all look pretty... but that's for another tutorial. Hope you learned something new from this :)

Blackbound · September 6, 2010

Does this only work on IPB or can this work with PHPBB and SMF as well?

Marked · September 6, 2010

Oh, its not based on any forum software. I made the tables myself, but I think the fields are the same as IPB as I was looking at them when I made the sample data for this tutorial.

Its supposed to teach how to do it with any forum software. They will all have these fields, just named differently.

Sign In

Creating a Recent Posts modul

Recommended Posts

Marked 197

Share this post

Link to post

Share on other sites

Blackbound 4

Share this post

Link to post

Share on other sites

Marked 197

Share this post

Link to post

Share on other sites

Please sign in to comment

Recently Browsing 0 members

Browse