index.php - MySQL

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: