Tips on Using PHP/MySQL for Creating Blog

A blog provides us a platform to express and share our thoughts with the rest of the world. Blogging has become easier and there are hundreds of ways to create a blog. But, behind the scenes it is the right blogging platform and technology that makes things look easier and simpler.

Before you start to create a blog, it is highly essential to chart out your expectations and compare them against the capabilities of the technology you choose to create your blog. One of the popular scripting languages that we keep hearing among bloggers is PHP/MySQL. Once you start to develop your blog, you will find the need to go beyond the basic functionalities and customize it as per your needs. Before that, here are some tips to create a PHP/MySQL powered blog.

1. Create your database:

Initially, when you start your blog, all you will need is a table that holds unique id to represent each blog post, blog title, content, date, and the IP address of the person who added the entry. As you develop your blog, you might want to change the basic table structure and add additional tables. For instance, if you want to allow people to register in your blog and post comments, then you might want to add few more tables and normalize all your database tables accordingly. For now, to have basic functionality, you just need a simple table that holds details of each of your blog entry.

To create a table, you can refer to the following code.

{code type=’php’}CREATE TABLE my_blog_tbl(‘post_id’ INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, blog_title VARCHAR(500) NOT NULL, blog_content TEXT NOT NULL, datetime DATETIME NOT NULL);{/code}

2. Write your PHP code:

In this layer, you will create objects to process data fetched from the database. A typical PHP code will contain as many variables as the fields in the database table and queries to fetch the data.

A basic MySQL query to fetch the data from the database is as follows:

{code type=’php’}
$blogquery = mysql_query(“SELECT f_name, l_name FROM BLOG_POSTS WHERE id = ” . $authorId);
$row = mysql_fetch_assoc($blogquery);
$this->author = $row[“f_name”] . ” ” . $row[“l_name”];
{/code}

The next step is to get your includes.php file ready. This is the place where you actually setup your database connection and invoke your PHP code to process the data. You can establish a connection to a MySQL database as follows:

{code type=’php’}
$conn = mysql_connect(“hostname”, “username”, “password”) or die(“Connection to the database cannot be established.”);
$db = “my_blogdb”;
mysql_select_db($db, $conn) or die (“Connection to the database cannot be established.”);
{/code}

Next, you can retrieve data from the database as follows:

{code type=’php’}
$query = my_query(“SELECT * FROM blog_posts WHERE id = ” . $postId . ” ORDER BY postId DESC”);
{/code}

So finally, your includes.php will look something similar to this:

{code type=’php’}
<?php
include ‘blogpost.php’;
$connection = mysql_connect(‘localhost’, ‘username’, ‘password’) …….
// database connection code
function GetBlogPosts($inId=null, $inTagId =null)
{
//database access code
return $postArray;
}
?>
{/code}

3. Display data

Now, you can move on to displaying the data fetched from the database. The first step is to include your includes.php in your index.php, so that you can invoke the functions to retrieve data from the database. If your function returns an array of rows from the database, you can display them as follows.

{code type=’php’}
foreach ($blogPosts as $blogpost)
{
echo $blogpost->title;
}
{/code}

4. Add test data

Next, insert some test data into your database and open the index.php in your browser to see your blog posts. As the page would just display the data in a simple table format, you can customize it using CSS style sheet.

As your blog gets bigger, you will certainly experience the power of PHP/MySQL to customize your blog with ease. You still need to add styling to make your page look better, but PHP/MySQL allows you to include some of the essential functionalities like adding spam protection, comment notification emails, and display an excerpt of a post, emoticons and many more.

Recommended Posts