MYSQL

Building a Threaded News System With PHP

Ever wanted to build a threaded news system? If so, this article is for you. In it, the Free2Code team show us how to do just that using PHP and MySQL...

Author Info:

By: Free2Code Team

Poor Best

Rating:

/ 28

December 29, 2002

TABLE OF CONTENTS:

· Building a Threaded News System With PHP
· Creating the Tables
· Adding Comments (comment.php)
· Conclusion

Building a Threaded News System With PHP - Creating the Tables
(Page 2 of 4 )

Firstly, the news table:

id INTEGER (11) auto_increment,
author VARCHAR (40),
title VARCHAR (40),
announcement TEXT,
PRIMARY KEY(id)

Now the comments table:

id INTEGER (11) auto_increment,
news_id INTEGER (11),
rel_id INTEGER (11),
replies INTEGER (11),
author VARCHAR (40),
title VARCHAR (40),
comment TEXT,
PRIMARY KEY (id)

The news table is pretty simple. We just store a unique ID for each post along with the name of the person who posted the announcement, the title of the announcement and the announcement itself.

The comments table stores three extra values -- the news_idb, the rel_id and the replies:

news_id: We'll use this value to show which news post a comment belongs to. For example, if a news post has the ID of 1, then any comments with a news_id of 1 will be associated with the news post ID that matches its news_id value.
rel_id: We will use this column to show the parent comment that a “child comment” (remember we’re working with threads) belongs to. For example, if a comment is intended to be a reply to a comment with the ID of 3, then the new comment created will have a rel_id of 3.
replies: We will use this to track how many replies a comment has had.

db_connect.php
Here's the database connection script:

<?php
ini_set('register_globals', 0);
ini_set('magic_quotes_gpc', 0);

require_once 'DB.php';
//require the PEAR::DB classes.

$db_engine = 'mysql'; // database engine
$db_user = 'username'; // your username
$db_pass = 'password'; // your password
$db_host = 'localhost'; // db server
$db_name = 'database'; // select database

// build data source
$datasource = $db_engine.'://'.
$db_user.':'.
$db_pass.'@'.
$db_host.'/'.
$db_name;

// connect to database
$db = DB::connect($datasource);

// if errors die and display error
if(DB::isError($db_object)) {
die($db_object->getDebugInfo());
}

// fetch items from the db as objects
$db->setFetchMode(DB_FETCHMODE_OBJECT);

Adding News Announcements (add.php)
Now we need to create the script that will allow news announcements to be made:

<?php

// we need database operations
require('db_connect.php');
?>
<html>
<head>
<title>add news</title>
</head>
<body>
<h2>Add A News Accouncement</h2>
<?php

// if form has been submitted
if(isset($_POST['submit'])) {
// check they filled everything in.
if(!$_POST['author'] | !$_POST['title'] | !$_POST['announcement']) {
die('Please fill in all the fields.');
}

// get rid of HTML or PHP tags
$_POST['author'] = htmlspecialchars($_POST['author']);
$_POST['title'] = htmlspecialchars($_POST['title']);
$_POST['announcement'] = htmlspecialchars($_POST['announcement']);

// convert text new lines to HTML new lines.
$_POST['announcement'] = str_replace("\
", '<br>', $_POST['announcement']);

// insert into database
$insert = $db->query("INSERT INTO news (author, title, announcement)
VALUES ('".addslashes($_POST['author'])."',
'".addslashes($_POST['title'])."',
'".addslashes($_POST['announcement'])."')");
echo '<p>Cheers '.$_POST['author'].', your announcement has been added, click
<a href="/index.php">here</a> to view it.</p>';
}

// if form hasnt been submitted
else {
// echo it out.
echo '<form action="'.$_SERVER['PHP_SELF'].'" method="post">';
echo 'Name: <input type="text" name="author" maxlength="40"><br>';
echo 'Title: <input type="text" name="title" maxlength="40"><br>';
echo '<textarea name="announcement" rows="20" cols="60"></textarea><br>';
echo '<input type="submit" name="submit" value="Post it"><br>';
echo '</form>';
}
?>
</body>
</html>

Displaying News Announcements (index.php)
Now we write the script that will list the news announcements:

<?php
require('db_connect.php');
?>
<html>
<head>
<title>(Your Site) News</title>
</head>
<body>
<h2>Latest Updates and News</h2>
<?php

// get news announcements and order them in reverse (newest at the top)
$news = $db->query('SELECT * FROM news ORDER BY id DESC');

// if error print out debugging info.
if(DB::IsError($news)) {
die($news->getDebugInfo());
}

// how many news posts?
$num = $news->numRows();

// if more than zero....
if($num > 0) {
// go thru them
while($n = $news->fetchRow()) {
$comments = $db->query("SELECT id FROM comments
WHERE news_id = '".$n->id."'");
if(DB::IsError($comments)) {
die($comments->getDebugInfo());
}
$num_comments = $comments->numRows();
// and echo them out
echo '<h4>'.stripslashes($n->title).' by '.stripslashes($n->author).'
</h4>';
echo '<p>'.stripslashes($n->announcement).'</p>';
echo '<p><a href="/comments.php?id='.$n->id.'&
rid=0">'.$num_comments.'
comments</a></p><hr>';
}
}
else { // if none
// echo out so.
echo '<p>No news announcements yet.</p>';
}
?>
</body>
</html>

Next: Adding Comments (comment.php) >>