In this tutorial we will look at an easy way to implement news into your website using only 3 different PHP scripts.

Let's start by recognizing what exactly we need in the news module. Obviously we will need a script to retrieve the news elements and post them on the main page. We will also need a script to add news, and finally one to edit or delete news.

1.1 Creating the MySQL tables

We can start by creating the nessecary tables for the news items. Each news item will include a Headline, Poster, Poster Email, Date and Time, and the actual News, plus a unique ID number to identify each news item. Now we are ready to create the tables using the following code:

CREATE TABLE news (
   id smallint(5) unsigned NOT NULL auto_increment,
   headline text NOT NULL,
   story text NOT NULL,
   name varchar(255),
   email varchar(255),
   timestamp datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   PRIMARY KEY (id)
);

With the tables created, we can move on to actual coding.

2.1 Add News to the Database: Creating the Form

Now you can start up Dreamweaver MX and create a new PHP page. The page should be blank, so let's fill it up. We know we need will need a form to collect the data, and a field to populate each column. Here's what I came up with:

<form name="form1" method="post" action="<? echo $PHP_SELF; ?>">
<table width="50%" border="0" cellspacing="0" cellpadding="0">
    <tr>
      <td width="50%">Name</td>
      <td><input name="name" type="text" id="name"></td>
    </tr>
    <tr>
      <td>Email</td>
      <td><input name="email" type="text" id="email"></td>
    </tr>
    <tr>
      <td> </td>
      <td> </td>
    </tr>
    <tr>
      <td>Headline</td>
      <td><input name="headline" type="text" id="headline"></td>
    </tr>
    <tr>
      <td>News Story</td>
      <td><textarea name="story" id="story"></textarea></td>
    </tr>
    <tr>
      <td colspan="2"><div align="center">
          <input name="hiddenField" type="hidden" value="add_n">
          <input name="add" type="submit" id="add" value="Submit">
        </div></td>
    </tr>
</table>
</form>

Now save this as add.php and load it up in your browser. You should see all of the fields, but if you hit submit, nothing happens. This is becuase there isn't any code telling the script to use the information, so it reloads the forms. The hidden field in the form is very important, and we will look at that in the next step. You may have noticed the variable $PHP_SELF, all this does is tell the form that when you hit submit, it will reload the page with the current information. Now lets start coding.

2.2 Add News to the Database: Adding the News

Now we can actually start to code. First, we have to tell the script to do something when the form was submitted. Remember that hidden field? This is where it comes into play. We can use the isset() function to check if the hidden field variable was set or not, and if so, run the script. So we can say this:

<?
if(isset($add_n){
//Run the script
}else{
//Load the form
}
?>

Now for the actual script. We will start by connecting to the database:

The above script connects to the database and selects it, but if it fails to connect, it will produce the error statement. The @ symbol just supresses the default error messages so we can declare our own. Now that we are connected, we can add the data to the database as a new row. The following code will do this painlessly:

$query = "INSERT INTO news(name, email, headline, story, timestamp)VALUES('$name', '$email', '$headline', '$story', NOW())";
$result = @mysql_query($query);
if(!$result){
echo('Error adding news: ' . $mysql_error());
exit();
}else{
mysql_close($link);
echo('Success!<br><a href="add.php">Click here</a> to add more news.<br><a href="edit.php">Click here</a> to edit news.<br><a href="../index.php">Click here</a> to return to the main page.');
}

Now this will create a new row in news with all the data you included in the form. Then it will close the connection and print some links for further navigation, considering everything went well. Now we can look at the final script.

2.3 Add News to the Database: The Final Script

Here is what your add.php should look like:

<body>
<?
if(isset($add_n)){
   $link = @mysql_connect(localhost, username, password);
   if(!$link){
      echo('Error connecting to the database: ' . $mysql_error());
      exit();
   }
   $db = @mysql_selectdb('mydatabase');
   if(!$db){
      echo('Error selecting database: ' . $mysql_error());
      exit();
   }
   $query = "INSERT INTO news(name, email, headline, story, timestamp)VALUES('$name', '$email', '$headline', '$story', NOW())";
   $result = @mysql_query($query);
   if(!$result){
      echo('Error adding news: ' . $mysql_error());
      exit();
   }else{
   mysql_close($link);
   echo('Success!<br><a href="add.php">Click here</a> to add more news.<br><a href="edit.php">Click here</a> to edit news.<br><a href="../index.php">Click here</a> to return to the main page.');
   }
}else{
?>
<form name="form1" method="post" action="<? echo $PHP_SELF; ?>">
<table width="50%" border="0" cellspacing="0" cellpadding="0">
    <tr>
      <td width="50%">Name</td>
      <td><input name="name" type="text" id="name"></td>
    </tr>
    <tr>
      <td>Email</td>
      <td><input name="email" type="text" id="email"></td>
    </tr>
    <tr>
      <td> </td>
      <td> </td>
    </tr>
    <tr>
      <td>Headline</td>
      <td><input name="headline" type="text" id="headline"></td>
    </tr>
    <tr>
      <td>News Story</td>
      <td><textarea name="story" id="story"></textarea></td>
    </tr>
    <tr>
      <td colspan="2"><div align="center">
          <input name="hiddenField" type="hidden" value="add_n">
          <input name="add" type="submit" id="add" value="Submit">
        </div></td>
    </tr>
</table>
</form>
<? } ?>
</body>
</html>

And that's it! Not too hard, now lets look at the edit/delete script.

3.1 Edit/Delete News: Print the News Items on the Page

We should start by retrieving all of the news items from the database, and then print an Edit and Delete link next to each item in order to perform the requested action. First we should declare a variable called a to tell us the action to perform, and if a isn't set, to print all the news items on the page. We can make a basic outline like this:

<?
if(!isset($a){
   // Print news
}elseif($a == 'edit'){
   // Perform edit procedures
}elseif($a == 'delete'){
   // Delete the news item
}
?>

Let's start by print all the news. We should make the database connection (like that seen in the add.php) and select all of the rows. The code will look something like this:

$link = @mysql_connect(localhost, username, password);
if(!$link){
   echo('Error connecting to the database: ' . $mysql_error());
   exit();
}
$db = @mysql_selectdb('mydatabase');
if(!$db){
   echo('Error selecting database: ' . $mysql_error());
   exit();
}
$query = "SELECT id, headline, timestamp FROM news ORDER BY timestamp DESC";
$result = @mysql_query($query);
if(!$result){
   echo('Error selecting news: ' . $mysql_error());
   exit();
}

This selects all the news item from the database, now we have to print it on the page:

if (mysql_num_rows($result) > 0){
    while($row = mysql_fetch_object($result))
    {
    ?>
   <font size="-1"><b><? echo $row->headling; ?></b> <i><? echo formatDate($row->timestamp); ?></i></font>
   <br>
   <font size="-2"><a href="edit.php?a=edit&id=<? echo $row->id; ?>">edit</a> |
   <a href="edit.php?a=delete&id=<? echo $row->id; ?>">delete</a></font>
    <?
}else{
   ?>
   <font size="-2">No news in the database</font>
<? }
   mysql_close($link);
?>

This script takes the results of the query, and prints it out on the page. The while() statement creates a loop until there aren't anymore rows to fetch. mysql_fetch_object() takes the data and processes it into a printable form. The -> operator basically means to take this column from the row in which the while loops is currently in. So now we have all the news items posted on the page with edit and delete links. Lets start editing these news items now.

3.2 Edit/Delete News: Edit the News

This portion of the script will appear when $a is equal to 'edit'. The $id will have been sent with the link, so all we need to do now is fetch the other data that is related with that ID. Here is what it looks like:

if(!isset($update)){
$link = @mysql_connect(localhost, username, password);
if(!$link){
   echo('Error connecting to the database: ' . $mysql_error());
   exit();
}
$db = @mysql_selectdb('mydatabase');
if(!$db){
   echo('Error selecting database: ' . $mysql_error());
   exit();
}
$query = "SELECT name, email, headline, story FROM news WHERE id = '$id'";
$result = @mysql_query($query);
if(!$result){
   echo('Error selecting news item: ' . $mysql_error());
   exit();
}
mysql_fetch_object($result);
?>
<form name="form1" method="post" action="edit.php?a=edit&id=<? echo($id) ?>&update=1">
<table width="50%" border="0" cellspacing="0" cellpadding="0">
    <tr>
      <td width="50%">Name</td>
      <td><input name="name" type="text" id="name" value="<? echo($row->name) ?>"></td>
    </tr>
    <tr>
      <td>Email</td>
      <td><input name="email" type="text" id="email" value="<? echo($row->email) ?>"></td>
    </tr>
    <tr>
      <td> </td>
      <td> </td>
    </tr>
    <tr>
      <td>Headline</td>
      <td><input name="headline" type="text" id="headline" value="<? echo($row->headline) ?>"></td>
    </tr>
    <tr>
      <td>News Story</td>
      <td><textarea name="story" id="story" value="<? echo($row->story) ?>"></textarea></td>
    </tr>
    <tr>
      <td colspan="2"><div align="center">
          <input name="hiddenField" type="hidden" value="update">
          <input name="add" type="submit" id="add" value="Update">
        </div></td>
    </tr>
</table>
</form>
<?
}

This looks very familiar to the add.php page, this time though, we put the values saved in the database into the fields to be edited. You may also noticed that we added an update variable to the edit class, this is so we can tell the script that we are actually updating instead of printing the forms again. The update would look like this:

else{
$query = "UPDATE news SET name = '$name, email = '$email', headline = '$headline', story = '$story', timestamp = NOW() WHERE id = '$id';
$result = @mysql_query($query);
if(!$result){
   echo('Error updating news item: ' . $mysql_error());
   exit();
}else{
   mysql_close($link);
   echo('Update successful!');
}
}

Notice here that the SQL statement WHERE comes after the SET statement. This causes a lot of confusion and frustion in many programmers. That completes the Edit portion of the news, now lets look at deleting news items.

3.3 Edit/Delete News: Delete the News

Deleting news is even easier then updating, or even printing the news. The delete function is a single mysql query that deletes the news item where the ID is equal to the news ID. Here's what it looks like:

$link = @mysql_connect(localhost, username, password);
if(!$link){
   echo('Error connecting to the database: ' . $mysql_error());
   exit();
}
$db = @mysql_selectdb('mydatabase');
if(!$db){
   echo('Error selecting database: ' . $mysql_error());
   exit();
}
$query = "DELETE FROM news WHERE id = '$id'";
$result = @mysql_query($query);
if(!$result){
   echo('Error deleteing news item: ' . $mysql_error());
   exit();
}
mysql_close($link);
echo('News item deleted.');
?>

There, and that completes the entire edit.php script. You can now save it and run it to edit your news items. The full script can be seen below.

3.4 Edit/Delete News: The Full Script

Here it is

<body>
<?
if(!isset($a){
$link = @mysql_connect(localhost, username, password);
if(!$link){
   echo('Error connecting to the database: ' . $mysql_error());
   exit();
}
$db = @mysql_selectdb('mydatabase');
if(!$db){
   echo('Error selecting database: ' . $mysql_error());
   exit();
}
$query = "SELECT id, headline, timestamp FROM news ORDER BY timestamp DESC";
$result = @mysql_query($query);
if(!$result){
   echo('Error selecting news: ' . $mysql_error());
   exit();
}
if (mysql_num_rows($result) > 0){
    while($row = mysql_fetch_object($result))
    {
    ?>
   <font size="-1"><b><? echo $row->headling; ?></b> <i><? echo formatDate($row->timestamp); ?></i></font>
   <br>
   <font size="-2"><a href="edit.php?a=edit&id=<? echo $row->id; ?>">edit</a> |
   <a href="edit.php?a=delete&id=<? echo $row->id; ?>">delete</a></font>
    <?
}else{
   ?>
   <font size="-2">No news in the database</font>
<? }
   mysql_close($link);
}elseif($a == 'edit'){
if(!isset($update)){
$link = @mysql_connect(localhost, username, password);
if(!$link){
   echo('Error connecting to the database: ' . $mysql_error());
   exit();
}
$db = @mysql_selectdb('mydatabase');
if(!$db){
   echo('Error selecting database: ' . $mysql_error());
   exit();
}
$query = "SELECT name, email, headline, story FROM news WHERE id = '$id'";
$result = @mysql_query($query);
if(!$result){
   echo('Error selecting news item: ' . $mysql_error());
   exit();
}
mysql_fetch_object($result);
?>
<form name="form1" method="post" action="edit.php?a=edit&id=<? echo($id) ?>&update=1">
<table width="50%" border="0" cellspacing="0" cellpadding="0">
    <tr>
      <td width="50%">Name</td>
      <td><input name="name" type="text" id="name" value="<? echo($row->name) ?>"></td>
    </tr>
    <tr>
      <td>Email</td>
      <td><input name="email" type="text" id="email" value="<? echo($row->email) ?>"></td>
    </tr>
    <tr>
      <td> </td>
      <td> </td>
    </tr>
    <tr>
      <td>Headline</td>
      <td><input name="headline" type="text" id="headline" value="<? echo($row->headline) ?>"></td>
    </tr>
    <tr>
      <td>News Story</td>
      <td><textarea name="story" id="story" value="<? echo($row->story) ?>"></textarea></td>
    </tr>
    <tr>
      <td colspan="2"><div align="center">
          <input name="hiddenField" type="hidden" value="update">
          <input name="add" type="submit" id="add" value="Update">
        </div></td>
    </tr>
</table>
</form>
<?
}else{
$query = "UPDATE news SET name = '$name, email = '$email', headline = '$headline', story = '$story', timestamp = NOW() WHERE id = '$id';
$result = @mysql_query($query);
if(!$result){
   echo('Error updating news item: ' . $mysql_error());
   exit();
}else{
   mysql_close($link);
   echo('Update successful!');
}
}elseif($a == 'delete'){
$link = @mysql_connect(localhost, username, password);
if(!$link){
   echo('Error connecting to the database: ' . $mysql_error());
   exit();
}
$db = @mysql_selectdb('mydatabase');
if(!$db){
   echo('Error selecting database: ' . $mysql_error());
   exit();
}
$query = "DELETE FROM news WHERE id = '$id'";
$result = @mysql_query($query);
if(!$result){
   echo('Error deleteing news item: ' . $mysql_error());
   exit();
}
mysql_close($link);
echo('News item deleted.');
} ?>
</body>
</html>

4.1 Viewing the News: Viewing News on the Front Page

Believe it or not, you already wrote this script when you wrote the edit portion of edit.php. The code is basically the same without the 'edit | delete' links. Here it is in the complete form.

<html>
<head>
<title>View News</title>
<meta http-equiv="Content-Type" content="text/html; charset="iso"-8859-1">
</head>
<body>
<?
$link = @mysql_connect(localhost, username, password);
if(!$link){
   echo('Error connecting to the database: ' . mysql_error());
   exit();
}
$db = @mysql_selectdb('mydatabase');
if(!$db){
   echo('Error selecting database: ' . mysql_error());
   exit();
}
$query = "SELECT id, headline, timestamp FROM news ORDER BY timestamp DESC";
$result = @mysql_query($query);
if(!$result){
   echo('Error selecting news: ' . mysql_error());
   exit();
}
if (mysql_num_rows($result) > 0){
    while($row = mysql_fetch_object($result))
    {
    ?>
   <font size="-1"><b><? echo $row->headling; ?></b> <i><? echo formatDate($row->timestamp); ?></i></font>
    <?
    }
}else{
   ?>
   <font size="-2">No news in the database</font>
<? }
   mysql_close($link); ?>
</body>
</html>

5.1 Conclusion

So inclusion, we learned how to retrieve add, update, and delete data from a database. As well as retrieving data from the database and displaying it in a loop or as a single item. This concept can also be implemented in other applications as well, download libraries, user databases, etc. If you have any questions, comments, or corrections to this tutorial, please don't hesitate to email me at pferrara@qublux.com .

Written by Peter Ferrara on December 11, 2002

Creating a News Module using PHP and MySQL