Home arrow MySQL arrow Page 3 - Creating the Blog Script for a PHP/MySQL Blogging System

index.php - MySQL

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.

TABLE OF CONTENTS:
  1. Creating the Blog Script for a PHP/MySQL Blogging System
  2. The Database Tables
  3. index.php
  4. comments.php
By: Jacques Noah
Rating: starstarstarstarstar / 95
October 10, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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:



 
 
>>> More MySQL Articles          >>> More By Jacques Noah
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: