In the past few weeks NETTUTS has posted quite a few great tutorials on how to get started using PHP and MySQL to create simple web applications such as a shout box. Today we are going to take our knowledge of PHP and MySQL a step further by creating a simple blog. While there are countless excellent free blog platforms, the purpose of this tutorial is not to make a "better" blog but to use the process of making a blog to learn about advanced database structure techniques and how to use data more efficiently by utilizing objects in PHP.
For this tutorial it is assumed that you have some basic understanding of PHP, MySQL, XHTML, and later on jQuery / JavaScript.
Part 1) Creating our Database
Before we move into our MySQL client and start creating our tables, we should to lay out what we want in our blog. The obvious thing we need to hold is blog posts, and in each post it should contain a title, the post, an author, and the date it was posted.
Now we could just make one table to hold that information, and most likely accomplish creating a basic blog, but with just one table we will not have as much control over our data. For example, we could just store the name of the author in the same table as the blog post, but what if we want to also store the author's email? Sure! We just add another field to our table would be the obvious solution.
The problem comes up when down the road you want to change the email address of that author. Now you have to change it for every single blog post that person has created.
So what we are going to do is make a separate table called "People" and in this, we will store all the information we need from the author - such as emails, URLs, their name, and a unique ID. Then in our blog post table we will point to the person we want using that person's unique ID. This id is referred to as a foreign key and the relationship between the blog post table and the people table is called a one-to-many relationship.
One other thing we want to have in our blog is a tag for each blog post. Again we want to make our database efficient so we will create a separate table for our tags. Now what do we do? Should we create a field in the blog post table that is a foreign key for our tag? The problem with using a one-to-one relationship this time is sometimes blog posts have more than one tag; so again, we will use a one-to-many relationship. To do this we need to create another table that will be called something like "blog_post_tags" that will hold two foreign keys, one will be the blog post's ID and the other will be the tag ID that the blog post is associated with. This way we can assign as many tags as we want to a blog post but can still edit the information about that specific tag across all posts with a simple MySQL query.
Now that we have outlined what we want our database to look like, let's create it. I'll be using PhpMyAdmin since that is the most widely used MySQL admin client. There are a few different naming conventions you can use when creating your database, table, and field names. I personally like to use all lowercase and underscores in place of spaces.
Note: If you do not have PHP and MySQL on your system or a server that can run it, I recommend downloading a stand-alone install of Apache, PHP, and MySQL. MAMP is good for Macs and WAMP is good for PCs.
First we need to create our database, I'm going to call it "nettuts_blog".
Next, we will create our tables; the first will be "blog_posts".
"blog_posts" will have five fields: "id", "title", "post", "author_id", and "date_posted". For "id" we are going to make it the primary key and set it to auto-increment. What this will do is generate our unique id for us. Each time we add a post it will give it a number starting at one and moving up for as many posts as we have.
Now we also need to set the variable type for each field. The id will be set to type int, short for integer, since it can only be a number and we will set the max length to 11. The field "title" will be set to type varchar with a max length of 255. The "post" field will be type "text" and we will not set a max length since posts can be very long. "author_id" will be the same as "id" but we will not set it as our primary key or have it auto increment, and we will set "date_posted" to type "Date".
Our next table will be "people". We are not calling it "authors" because down the road we might want to create the ability to register and post comments and those people would not be considered authors.
"people" will contain five fields also: "id", "first_name", "last_name", "url", and "email".
"id" will be set as an int, the primary key, and to auto increment, the same way we set id of "blog_posts". "first_name", "last_name", "url", and "email" will all be set to type varchar with a max length of 255.
Our next table will be "tags" and will for now contain only two fields: "id" and "name". Down the road we could make this more complex by adding a description but for this tutorial, we will not. As we did before "id" will be set as int, the primary key, and to auto increment. "name" will be type varchar and have a max length of 255.
And our last table, "blog_post_tags", will have only two fields: "blog_post_id" and "tag_id". They will both be set to type int with a max length of 11. As you most likely noticed, we did not set a primary key for this table. This is because we will never be getting data out of this table unless we are asking for a specific blog post or all posts of a specific tag id.
Part 2) Creating our Objects in PHP
Before we start into our actual PHP code we need to create our files and folders, for this tutorial we will have our index.php in our root folder then an includes folder that will hold our CSS style sheet, our JavaScript files, includes.php that will hold references to our objects and MySQL connection, and blogpost.php that will hold our BlogPost obejct.
Now that we have our database set, we need to create the objects in PHP that will handle the data for us. Objects in programming are a way to pull together different attributes (as variables) and methods that all relate to the same thing. Objects also help us organize our programs much more. Before we jump into our objects for our blog, let's just create a simple object so that we can illustrate what they are in a more "real life" term.
Our object will be called "Bike", now there are two types of things each object has, properties and methods. Properties define the object and methods are what the object does. For example our Bike object would have properties like wheel size, number of gears, and perhaps the frame size. For methods we might have something like "Pedal".
Now to move back to our blog, we only need one object for now called "BlogPost". BlogPost will have six properties, id, title, post, author, tags, and date posted. So lets make it in PHP.
To define an object in PHP we define it as a "class". A class is the structure of each object, or as wikipedia describes it, "In object-oriented programming, a class is a programming language construct that is used as a blueprint to create objects. This blueprint includes attributes and methods that the created objects all share." (http://en.wikipedia.org/wiki/Concrete_class). We are going to open up our blogpost.php page and define our first object.
Note: In each section of the tutorial, I'm going to leave out the opening and closing PHP tags; "<?php" and "?>" you will need to include them at the start and end of your document.
1 |
class BlogPost |
2 |
{
|
3 |
} |
In our class we need first define our properties. To do this, we have to create variables - but with "public" in front of them. Just a quick note, if you are using PHP4 then you will need to use "var" instead of "public".
1 |
class BlogPost |
2 |
{
|
3 |
public $id; |
4 |
public $title; |
5 |
public $post; |
6 |
public $author; |
7 |
public $tags; |
8 |
public $datePosted; |
9 |
}
|
Now that we have all our properties defined, we want to define our first method. Methods are also described as functions, but the main difference is that a method is a function inside an object. So all methods are also functions but not all functions are methods.
Our first method will be what is called a constructor; this method is automatically called whenever we make a new instance of the BlogPost object.
The common use of a constructor is so that you can create a new object and set the properties of that object quickly.
So what we want to do is create a new function called __construct() and we are going to pass in five values, id, title, post, author id, and date posted. For each variable name we are going to put "in" before the word so we can tell inside our functions what variables are being passed in and what variables are already present.
1 |
class BlogPost |
2 |
{
|
3 |
public $id; |
4 |
public $title; |
5 |
public $post; |
6 |
public $author; |
7 |
public $tags; |
8 |
public $datePosted; |
9 |
|
10 |
function __construct($inId, $inTitle, $inPost, $inAuthorId, $inDatePosted) |
11 |
{
|
12 |
|
13 |
}
|
14 |
}
|
The problem here is that, with this current code, every time we create a new instance of BlogPost we need to supply all those properties. But what if we want to make a new blog post and have not defined those variables yet? To handle this, we need to "overload" the arguments for our function so that if we call the function and don't pass in one of the arguments, it will automatically set it to the default value.
1 |
function __construct($inId=null, $inTitle=null, $inPost=null, $inPostFull=null, $inAuthorId=null, $inDatePosted=null) |
2 |
{
|
3 |
}
|
As you can see, all that we do to accomplish our task is set each argument to the value "null". Now inside our constructor, we need to set each of our variables to our passed in values. To do this we want to set them to the object we are in right now; We can do this with the "this" keyword. Unlike many other languages to access a property in PHP you use "->" where in most languages (I.E. JavaScript, ASP.NET) you use ".".
1 |
function __construct($inId=null, $inTitle=null, $inPost=null, $inPostFull=null, $inAuthorId=null, $inDatePosted=null) |
2 |
{
|
3 |
$this->id = $inId; |
4 |
$this->title = $inTitle; |
5 |
$this->post = $inPost; |
6 |
}
|
This works for id, title, and post. But what about our other ones? For date we are going to need to reformat the data we got from MySQL to be more readable. That's easily accomplished; We just explode it (also known as splitting in other programming languages) and then put it back together. MySQL gives it to us in this format yyyy-mm-dd, so if we explode it using "-" as our separator we will get an array holding three values. The first will hold our year, the next will hold our month, and the last will be the day. Now all we do is put them back together in whatever format we want. I am going with mm/dd/yyyy.
1 |
$splitDate = explode("-", $inDatePosted); |
2 |
$this->datePosted = $splitDate[1] . "/" . $splitDate[2] . "/" . $splitDate[0]; |
For the author, all we have to do is ask the database for the person with the id of our author ID. We can do this with a basic MySQL query.
1 |
$query = mysql_query("SELECT first_name, last_name FROM People WHERE id = " . $inAuthorId); |
2 |
$row = mysql_fetch_assoc($query); |
3 |
$this->author = $row["first_name"] . " " . $row["last_name"]; |
Left Join
Now the tags will be slightly more difficult. We are going to need to talk to the database, so we need to create a MySQL query. We won't worry about the database connection right now, that will be defined outside of this class. Now all we have is the blog post's ID. We can compare that to the blog posts tags in our blog_post_tags table but then we will only get back the tag's ID and will need to do another query to get the information about the tag. That's no good; We want to be efficient so let's do it in just one query!
To do this we are going to do what is called a left join, this means we are going to also select data from another table but only when it matches the data from the "left" or our other selected data. So first let's just get all tag IDs that are associated with our blog post's ID in the blog_post_tags table.
1 |
$query = mysql_query("SELECT * FROM blog_post_tags WHERE blog_post_tags.blog_post_id = " . $inId); |
Now let's add our left join and tell our query we only want the data in the tags table:
1 |
$query = mysql_query("SELECT tags.* FROM blog_post_tags LEFT JOIN (tags) ON (blog_post_tags.tag_id = tags.id) WHERE blog_post_tags.blog_post_id = " . $inId); |
So now the query is selecting all from the tags and blog_posts_tags tables where, first blog_post_tags.blog_post_id equals the one we provided and then also returns the information about each tag that has that tag_id that is in the same row of data as our blog_post_id.
Now we want to process that data in PHP with a simple while loop. We are going to also create two arrays that will hold our data: one for the tag name, and the other for the tag id. We will also make a string to hold all of our tags. We first will set it to "No Tags" so that if we return no data from our MySQL query, it will return that we have no tags, otherwise that value will be overwritten with the tag names.
1 |
$postTags = "No Tags"; |
2 |
$tagArray = array(); |
3 |
$tagIDArray = array(); |
4 |
while($row = mysql_fetch_assoc($query) |
5 |
{
|
6 |
array_push($tagArray, $row["name"]); |
7 |
array_push($tagIDArray, $row["id"]); |
8 |
}
|
Now we will check to make sure if the array has a length greater then zero (we don't want to perform all of this extra code if we don't have to). Next, for each tag in our tag name array, we are going to concatenate a string of tags. We will use a simple if else statement.
1 |
if (sizeof($tagArray) > 0) |
2 |
{
|
3 |
foreach ($tagArray as $tag) |
4 |
{
|
5 |
if ($postTags == "No Tags") |
6 |
{
|
7 |
$postTags = $tag; |
8 |
}
|
9 |
else
|
10 |
{
|
11 |
$postTags = $postTags . ", " . $tag; |
12 |
}
|
13 |
}
|
14 |
}
|
15 |
$this->tags = $postTags; |
You most likely noticed that we did not use the tag id arrays. We are going to leave those alone for now and come back to them later. We just want to get our blog up and running first.
The last step for our class is to add if statements for each property so that if we pass in nothing, it will not try to set the current object's property to nothing (this will cause an error). Here is the entire BlogPost class with the if statements added:
1 |
<?php
|
2 |
|
3 |
class BlogPost |
4 |
{
|
5 |
|
6 |
public $id; |
7 |
public $title; |
8 |
public $post; |
9 |
public $author; |
10 |
public $tags; |
11 |
public $datePosted; |
12 |
|
13 |
function __construct($inId=null, $inTitle=null, $inPost=null, $inPostFull=null, $inAuthorId=null, $inDatePosted=null) |
14 |
{
|
15 |
if (!empty($inId)) |
16 |
{
|
17 |
$this->id = $inId; |
18 |
}
|
19 |
if (!empty($inTitle)) |
20 |
{
|
21 |
$this->title = $inTitle; |
22 |
}
|
23 |
if (!empty($inPost)) |
24 |
{
|
25 |
$this->post = $inPost; |
26 |
}
|
27 |
|
28 |
if (!empty($inDatePosted)) |
29 |
{
|
30 |
$splitDate = explode("-", $inDatePosted); |
31 |
$this->datePosted = $splitDate[1] . "/" . $splitDate[2] . "/" . $splitDate[0]; |
32 |
}
|
33 |
|
34 |
if (!empty($inAuthorId)) |
35 |
{
|
36 |
$query = mysql_query("SELECT first_name, last_name FROM people WHERE id = " . $inAuthorId); |
37 |
$row = mysql_fetch_assoc($query); |
38 |
$this->author = $row["first_name"] . " " . $row["last_name"]; |
39 |
}
|
40 |
|
41 |
$postTags = "No Tags"; |
42 |
if (!empty($inId)) |
43 |
{
|
44 |
$query = mysql_query("SELECT tags.* FROM blog_post_tags LEFT JOIN (tags) ON (blog_post_tags.tag_id = tags.id) WHERE blog_post_tags.blog_post_id = " . $inId); |
45 |
$tagArray = array(); |
46 |
$tagIDArray = array(); |
47 |
while($row = mysql_fetch_assoc($query)) |
48 |
{
|
49 |
array_push($tagArray, $row["name"]); |
50 |
array_push($tagIDArray, $row["id"]); |
51 |
}
|
52 |
if (sizeof($tagArray) > 0) |
53 |
{
|
54 |
foreach ($tagArray as $tag) |
55 |
{
|
56 |
if ($postTags == "No Tags") |
57 |
{
|
58 |
$postTags = $tag; |
59 |
}
|
60 |
else
|
61 |
{
|
62 |
$postTags = $postTags . ", " . $tag; |
63 |
}
|
64 |
}
|
65 |
}
|
66 |
}
|
67 |
$this->tags = $postTags; |
68 |
}
|
69 |
|
70 |
}
|
71 |
|
72 |
?>
|
Now that our object class is complete, most of the hard stuff is done! Now all we have to do is set up our database connection and the HTML to display our posts!
Part 3) Fetching the Data From MySQL and Displaying it With PHP
Before we do anything we need to set up our includes.php file to hold a reference to our BlogPost object and connect to our MySQL database. First lets include our object with a simple include statement:
1 |
include 'blogpost.php'; |
Now lets add our database connection:
1 |
$connection = mysql_connect("localhost", "username", "password") or die ("<p class='error'>Sorry, we were unable to connect to the database server.</p>"); |
2 |
$database = "nettuts_blog"; |
3 |
mysql_select_db($database, $connection) or die ("<p class='error'>Sorry, we were unable to connect to the database.</p>"); |
Next, we need to retrieve our blog posts from the database. To do this, we're going to make a function that can take up to two arguments. We will overload both of them; so you can call the function with either 0, 1, or 2 arguments.
1 |
function GetBlogPosts($inId=null, $inTagId=null) |
2 |
{
|
3 |
|
4 |
}
|
Inside our function we need to check to see what arguments were passed in and create our MySQL query accordingly.
1 |
function GetBlogPosts($inId=null, $inTagId =null) |
2 |
{
|
3 |
if (!empty($inId)) |
4 |
{
|
5 |
|
6 |
}
|
7 |
else if (!empty($inTagId)) |
8 |
{
|
9 |
|
10 |
}
|
11 |
else
|
12 |
{
|
13 |
|
14 |
}
|
15 |
}
|
So what we are doing here is asking if each argument is not empty, the reason we are using the empty function instead of just doing the standard "!= null" comparison is because empty not only checks if the variable is null, but if it empty as well (I.E. ""). Now we will write a query depending on what variables we have. If we pass in a blog post ID we just want that single blog post, if we give the function a inTagId we want all posts that have that tag, and otherwise we just want all blog posts in our database.
1 |
if (!empty($inId)) |
2 |
{
|
3 |
$query = mysql_query("SELECT * FROM blog_posts WHERE id = " . $inId . " ORDER BY id DESC"); |
4 |
}
|
5 |
else if (!empty($inTagId)) |
6 |
{
|
7 |
$query = mysql_query("SELECT blog_posts.* FROM blog_post_tags LEFT JOIN (blog_posts) ON (blog_post_tags.postID = blog_posts.id) WHERE blog_post_tags.tagID =" . $tagID . " ORDER BY blog_posts.id DESC"); |
8 |
}
|
9 |
else
|
10 |
{
|
11 |
$query = mysql_query("SELECT * FROM blog_posts ORDER BY id DESC"); |
12 |
}
|
The next step is to process the data returned from each query, create the objects, and then add them to an array to return.
1 |
$postArray = array(); |
2 |
while ($row = mysql_fetch_assoc($query)) |
3 |
{
|
4 |
$myPost = new BlogPost($row["id"], $row['title'], $row['post'], $row['postfull'], $row['firstname'] . " " . $row['lastname'], $row['dateposted']); |
5 |
array_push($postArray, $myPost); |
6 |
}
|
7 |
return $postArray; |
Here is the entire includes.php file's code:
1 |
<?php
|
2 |
include 'blogpost.php'; |
3 |
|
4 |
$connection = mysql_connect('localhost', 'username', 'password') or die ("<p class='error'>Sorry, we were unable to connect to the database server.</p>"); |
5 |
$database = "nettuts_blog"; |
6 |
mysql_select_db($database, $connection) or die ("<p class='error'>Sorry, we were unable to connect to the database.</p>"); |
7 |
|
8 |
function GetBlogPosts($inId=null, $inTagId =null) |
9 |
{
|
10 |
if (!empty($inId)) |
11 |
{
|
12 |
$query = mysql_query("SELECT * FROM blog_posts WHERE id = " . $inId . " ORDER BY id DESC"); |
13 |
}
|
14 |
else if (!empty($inTagId)) |
15 |
{
|
16 |
$query = mysql_query("SELECT blog_posts.* FROM blog_post_tags LEFT JOIN (blog_posts) ON (blog_post_tags.postID = blog_posts.id) WHERE blog_post_tags.tagID =" . $tagID . " ORDER BY blog_posts.id DESC"); |
17 |
}
|
18 |
else
|
19 |
{
|
20 |
$query = mysql_query("SELECT * FROM blog_posts ORDER BY id DESC"); |
21 |
}
|
22 |
|
23 |
$postArray = array(); |
24 |
while ($row = mysql_fetch_assoc($query)) |
25 |
{
|
26 |
$myPost = new BlogPost($row["id"], $row['title'], $row['post'], $row['postfull'], $row["author_id"], $row['dateposted']); |
27 |
array_push($postArray, $myPost); |
28 |
}
|
29 |
return $postArray; |
30 |
}
|
31 |
?>
|
Now we can move on to displaying our data, lets open up our index.php file and set up a basic HTML page. Inside our body we'll create a division with an id of "main" that will contain our blog. We will give our blog a title and then have a second div inside main that will be called "blogPosts".
1 |
<div id="main"> |
2 |
<h1>My Simple Blog</h1> |
3 |
<div id="blogPosts"> |
4 |
|
5 |
</div>
|
6 |
</div>
|
Inside our blogPosts div, we'll put in some PHP so that we can display our posts. First we are going to include our includes.php file and then call our GetBlogPosts function with no arguments to get all our blog posts and set that to an array called blogPosts.
1 |
<?php
|
2 |
include 'includes/includes.php'; |
3 |
|
4 |
$blogPosts = GetBlogPosts(); |
5 |
?>
|
Now, we will use a foreach loop to display our blog posts. What a foreach loop does for us right now is takes an array and executes the code in the loop for each item in the array, you could also use a normal for loop to achieve this but a foreach loop requires less code.
1 |
foreach ($blogPosts as $post) |
2 |
{
|
3 |
|
4 |
}
|
Inside the loop, we use $post as the current array item, and since $blogPosts is an array of BlogPost objects we can simply use "->" to access each property that we want. Lets start by just echoing out the title of each blog post to our page and just add a <br/> - as an example.
1 |
foreach ($blogPosts as $post) |
2 |
{
|
3 |
echo $post->title . "<br/>"; |
4 |
}
|
If we go to our database and insert some fake data, then open up index.php in a browser, we will get something like this:
Let's really build our posts in HTML. Each post will be wrapped in a div with a class of "post". Then we will have the post title inside an h1 tag and the actual post inside a <p> tag.</p>
1 |
foreach ($blogPosts as $post) |
2 |
{
|
3 |
echo "<div class='post'>"; |
4 |
echo "<h2>" . $post->title . "</h2>"; |
5 |
echo "<p>" . $post->post . "</p>"; |
6 |
echo "</div>"; |
7 |
}
|
Let's also give each post a footer that will include the post's author, date posted, and the post's tags. We will put all of this information within a span tag with a class "footer".
1 |
foreach ($blogPosts as $post) |
2 |
{
|
3 |
echo "<div class='post'>"; |
4 |
echo "<h1>" . $post->title . "</h1>"; |
5 |
echo "<p>" . $post->post . "</h1>"; |
6 |
echo "<span class='footer'>Posted By: " . $post->author . " Posted On: " . $post->datePosted . " Tags: " . $post->tags . "</span>"; |
7 |
echo "</div>"; |
8 |
}
|
Now let's look at our index.php file again in a browser:
Looks like its all working! Next, let's add some styling.
Much better; you can view this online here or click the big "demo" buttons. Thats it for this tutorial. In a subsequent tutorial, we will add more functionality to our blog, including some nice AJAX functionality!
I hope you now have a better understanding of how object oriented programming works. Even if you end up doing the majority of your development using a framework, its very important to have a core understanding of the fundamentals. If you have any specific questions about this tutorial, don't hesitate to tweet me @benemills. I would like to give a special thanks to James Padolsey for helping me with this article.
Stay tuned for Part 2!
- Subscribe to the NETTUTS RSS Feed for more daily web development tuts and articles.
