Creating the Blog Script for a PHP/MySQL Blogging System

In this second part of a three-part series on blogging with PHP and MySQL, we will be looking at the actual blog. The blog will be simplicity itself. I have created a style sheet that will help in making the blog look clean and neat. The blog will be based purely on a open blog system.


A downloadable file for this article is available here.

Introduction

Integrating the login script with the blog should not be a problem for you. In fact all it needs to do is send a user through, if his or her login is valid, and then log the user out. The blog will have a MySQL backend that will store all the articles and related replies.

It will also have a categories table that we will use to store all the different categories in. The categories will help us group the articles together that belong to the same category. This will be achieved by adding a “categoryID” foreign key to the article table.

To retrieve the articles we will mostly use joins in our SQL, as this is the best way to retrieve grouped information, which in our case is absolutely vital to the way the blog structures the articles. We will also retrieve the most recent topics and display them on the side bars for easy access. This will act as a shortcut for the user, when he or she wants a quick view of what the latest messages are about.

The index page sends two values over to the comments page. These values represent the categoryID and the article ID. They will be used to retrieve the article and its replies on the comments page; they will also be used to retrieve the category names, which are related to those articles.

{mospagebreak title=The Database Tables}

Here are the tables that will hold our messages:

Articles tbl:

CREATE TABLE `article` (
`artid` int(5) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default ”,
`title` varchar(255) NOT NULL default ”,
`comments` text NOT NULL,
`date_posted` date NOT NULL default ‘0000-00-00′,
`categoryID` int(4) NOT NULL default ‘0’,
`artchild` int(5) NOT NULL default ‘0’,
PRIMARY KEY (`artid`)
) TYPE=MyISAM AUTO_INCREMENT=30 ;

Most of the fields in the table should be self explanatory, except perhaps the “artchild” column. The artchild column will hold the message ID of a message that users reply to. This is how it works: the first time a article is written, its artchild value will be “0” and it will have an automatically created number. When a response is created to this article the new response article’s artchild will have the auto number value added to its artchild column.

So for example if we wanted to retrieve all the articles related to a certain topic, say topic number eight, then all we need to do is retrieve all articles that have an “artchild” value of eight. The categoryID is the foreign key. It represents the ID of the category name in the category table, which we have not yet talked about. That is presented in the code below:

Categories tbl:

CREATE TABLE `categories` (
`catid` int(5) NOT NULL auto_increment,
`category` varchar(255) NOT NULL default ”,
PRIMARY KEY (`catid`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

This table will hold the category names of the topics that will be used in the blog. The categories will be managed by the administrator in the admin section of the blog.

The Blog

The blog itself will really have only two pages. The index.php page will show a list of all the main topics in the database. The comments.php page will enable you to view comments made to a particular message, and also give you the chance to comment on the article. There will be an additional page, functions.php, which will hold all the functions used in the blog.

The blog will only allow the administrator to introduce new discussions. The actual users will only be able to comment on those discussions. This is because a blog by its very nature is like a web based personal diary, and as with any other diary you don’t let other people write in it unless you personally want them to.

{mospagebreak title=index.php}

When the “index.php” is loaded the following SQL is executed:

<?
include “config.php”;
$query1=”Select *,DATE_FORMAT(date_posted,’%W,%d %b %Y’) as
thedate FROM article INNER JOIN categories ON categoryID=catid
WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY)AND artchild=’0′ ORDER
BY date_posted DESC LIMIT 10 “;
$blogarticles = mysql_query($query1) or die(mysql_error());
$num = mysql_num_rows($blogarticles);
?>

In this SQL the articles are retrieved together with the categories to which they belong. The inner join does the job of linking the articles with their categories. The DATE_SUB() function does the job of retrieving all the messages posted in the last thirty days.

I set the condition of “artchild=0″ because any article whose “artchild” value is greater then zero is a reply. All replies have the same title as the main article to which it is replying. By putting a condition of “0” in the SQL, I only retrieve the main topics. You will be able to view comments made to these topics by clicking on a link called comments, which is displayed with each topic.

To work out whether comments have been made on an article, we run the following SQL:

$getcomments = “SELECT * FROM article WHERE
artchild='”.$row_articles['artid'].”‘”;
if(!$theResult=mysql_query($getcomments)){
echo mysql_error();
}else{
$num_comments=mysql_num_rows($theResult);
echo $num_comments;
}

The ‘”.$row_articles['artid'].”’ refers to the article ID retrieved from “$query1.” Then we just count the number of rows returned as in the line “$num_comments=mysql_num_rows($theResult);” and display that number.

If you look towards the right in the screen shot below, you will notice a list of topics displayed. This is achieved by the following SQL:

$query=”Select *,COUNT(*) FROM article INNER JOIN categories ON
categoryID=catid WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) GROUP
BY title DESC LIMIT 10 “;
$blog = mysql_query($query) or die(mysql_error());
$num_blog = mysql_num_rows($blog);

Again this SQL just retrieves the articles from the database and joins the category information on the articles. We need the category info because we are going to show the title of the articles and in what category they were posted.

Here’s a screen shot of the index page:

{mospagebreak title=comments.php}

The comments page has three main functions:

  • Display the main article.
  • Display the comments made to the article.
  • Display a form for new comments to be added.

When you click on the comments link in the index page, two numbers are sent over to the comments page: the article id and the categoryID. The comments page uses the article id to retrieve the main article and then to retrieve all the comments made to that article. Here’s the SQL that does the job:

//retrieve the main article…
if(isset($_GET['aid'])){
$_SESSION['aid']=$_GET['aid'];
$getarticle=”SELECT * FROM article WHERE artid = “.$_GET['aid'].”
ORDER by date_posted ASC”;
if(!$result = mysql_query($getarticle)){
echo mysql_error();
}else{
$num=mysql_num_rows($result);
}
//retrieve all comments made to this article
$getcomments=”SELECT * FROM article WHERE artchild = “.$_GET
['aid'].” ORDER by date_posted ASC”;
$getcomments_result = mysql_query($getcomments);
$comment_num=mysql_num_rows($getcomments_result);
}

Both queries are straightforward. One uses the $_GET[‘aid’] link to retrieve the main article and the other uses the same value to retrieve the comments.

The form enables a user to make comments on the main article. Here’s the SQL that handles the form data:

if(isset($_POST['theComment'])){
$query = “INSERT INTO article SET name='”.$_POST
['name'].”‘,title='”.$_POST['theTitle'].”‘,comments='”.$_POST
['comment'].”‘,”;
$query .=”date_posted=NOW(),categoryID='”.$_POST
['CID'].”‘,artchild='”.$_POST['theID'].”‘”;
if(!mysql_query($query)){
echo mysql_error();
}else{
$getarticle=”SELECT * FROM article WHERE artid = “.$_SESSION
['aid'].” ORDER by date_posted ASC”;
if(!$result = mysql_query($getarticle)){
echo mysql_error();
}else{
$num=mysql_num_rows($result);
}
}
//retrieve all comments made to this article
$getcomments=”SELECT * FROM article WHERE artchild = “.$_SESSION
['aid'].” ORDER by date_posted ASC”;
$getcomments_result = mysql_query($getcomments);
$comment_num=mysql_num_rows($getcomments_result);
}

When the form is submitted, it sends a value over to the form handler script. This value is then used in the code above. The code has two functions:

  • Insert form data into the database.
  • Retrieve the main article and comments.

The form has a couple of hidden fields that contain data, such as the title of the main article, the category ID, and so forth. All of these are used in the insert query as shown below:

if(isset($_POST['theComment'])){
$query = “INSERT INTO article SET name='”.$_POST
['name'].”‘,title='”.$_POST['theTitle'].”‘,comments='”.$_POST
['comment'].”‘,”;
$query .=”date_posted=NOW(),categoryID='”.$_POST
['CID'].”‘,artchild='”.$_POST['theID'].”‘”;
if(!mysql_query($query)){
echo mysql_error();

Here’s an output of the comments page:

Conclusion

That’s it for the blog part of the series. In the next part, we will create the administration side of the blog.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan