News system with comments in PHP

Database

Below is the database table for this code.

CREATE TABLE `news` (
   `id` int(11) NOT NULL auto_increment,
   `postedby` varchar(200) NOT NULL,
   `news` text NOT NULL,
   `subject` varchar(200) NOT NULL,
   `date` int(11) NOT NULL,
   PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `newscomments` (
  `id` int(11) NOT NULL auto_increment,
  `link` int(11) NOT NULL default '1',
  `name` varchar(200) NOT NULL,
  `subject` varchar(200) NOT NULL,
  `comment` text NOT NULL,
  `date` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
)

Viewing comments

In this tutorial we will add a comment system to the basic news tutorial.

mysql_connect('localhost','username','password');
mysql_select_db('db');
$query = mysql_query('SELECT * FROM news ORDER BY id DESC');
while($output = mysql_fetch_assoc($query))
{
   $numberComments = mysql_num_rows(mysql_query("SELECT id FROM newscomments WHERE link = '".$output['id']."'"));
   echo $output['subject'].'<br />';
   echo $output['date'].'<br />';
   echo $output['news'].'<br />';
   echo '<a href="viewcomments.php?id='.$output['id'].'">View Comments</a> - ['.$numberComments.']<hr />';
}

We will include the number of comments alongisde the news items. A link will take the user to a second page that will display the comments.

We will use a query string to pass the id of the news item. If the id is not set then we will redirect the user to the main page.

$id = $_GET['id'];
if(empty($id))
   header('Location: news.php');

If the id variable is set then then we need to check to see if any comments have been posted.

$query = mysql_query("SELECT * FROM newscomments WHERE link = '$id' ORDER BY id DESC");
if (mysql_num_rows($query) == 0)
   echo 'Be the first to add a comment.';

Otherwise if we have comments then we will output them.

while($output = mysql_fetch_assoc($query))
{
   echo $output['subject'].'<br />';
   echo date('d-M-Y', $output['date']).'<br />';
   echo $output['comment'].'<br />';
   echo '<em>Posted by ~ '.$output['name'].'</em><hr />';
}

Now we need to output a form to allow a user to add a comment.

<form id="form1" name="form1" method="post" action="addnews.php?id=<?php echo $id; ?>">
   <input type="text" name="name" id="name" value="Name" /><br />
   <input type="text" name="subject" id="subject" value="Subject" /><br />
   <textarea name="comment" id="comment">Enter your comment</textarea><br />
   <input type="submit" name="submit" id="submit" value="Submit" />
</form>

When the user has added a comment into the form we need to enter it into the database.

$id = $_GET['id'];
if(empty($id))
   header('Location: news.php');

We will again use query strings to pass the id variable, if one is not set then we will redirect the user to the main news page.

if($_POST['submit'])
{
   if (empty($_POST['name']))
      die('Enter a name.');
   else if (empty($_POST['subject']))
      die('Enter a subject.');
   else if (empty($_POST['comment']))
      die('Enter a comment.');

Checks are made to make sure that there are not empty fields otherwise an error message is output.

$postedby = clear($_POST['name']);
$subject = clear($_POST['subject']);
$comment = clear($_POST['comment']);
$date = mktime();
mysql_connect('localhost','username','password');
mysql_select_db('db');
if(mysql_query("INSERT INTO newscomments (id , link, name , subject , comment , date) VALUES
                                         ('', '$id', '$postedby', '$subject', '$comment', '$date')"))
   echo 'Comment Entered.';

We now check the input using the clear function and enter it into the database.

This is very similar to the simple news tutorial however we need one extra query to delete the comments as well as the news.

mysql_query("DELETE FROM newscomments WHERE link = $id");