MySQL news system

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`)
);

Posting news

We will firstly create the page that will post our news.

<form method="post" action="#">
   Posted By:<br /><input name="postedby" id="postedby" type="Text" size="50" maxlength="50"><br />
   Subject:<br /><input name="subject" id="subject" type="Text" size="50" maxlength="50"><br />
   <textarea name="news" id="news" cols="50" rows="5"></textarea><br />
   <input type="Submit" name="submit" id="submit" value="Enter News">
</form>

This form has 3 fields, a subject field, a message field and finally an author field.

function clear($message)
{
   if(!get_magic_quotes_gpc())
      $message = addslashes($message);
   $message = strip_tags($message);
   $message = htmlentities($message);
   return trim($message);
}

The clear function checks that the input is ready for entering into the database.

if ($_POST['submit'])
{
   if (empty($_POST['postedby']))
      die('Enter a name.');
   else if (empty($_POST['subject']))
      die('Enter a subject.');
   else if (empty($_POST['news']))
      die('Enter an article.'); 

These first set of checks ensure that no empty data is added to the database.

$postedby = clear($_POST['postedby']);
$subject = clear($_POST['subject']);
$news = clear($_POST['news']);
$date = mktime();
mysql_connect('localhost','username','password');
mysql_select_db('db');
if(mysql_query("INSERT INTO news (id , postedby , news , subject , date) VALUES 
              ('', '$postedby', '$news', '$subject', '$date')"))
   echo 'News Entered.';
mysql_close(); 

Finally the data is added to the database.

Editing the news

To edit the news we need to display the form again with the current data to allow the user to change the data.

if(!$_GET['id'])
{
   $query = mysql_query("SELECT * FROM news ORDER BY id DESC");
   echo 'Edit<hr />';
   while($output = mysql_fetch_assoc($query))
      echo $output['subject'].' &raquo; <a href="?id='.$output['id'].'">Edit</a><br />';
}

If the user has not selected a news item to edit we will display a list of the current news items with a link to edit that news item.

if ($_POST['submit'])
{
   $postedby = clear($_POST['postedby']);
   $subject = clear($_POST['subject']);
   $news = clear($_POST['news']);
   $date = mktime();
   $id = $_GET['id'];
   mysql_query("UPDATE news SET postedby='$postedby', news='$news', subject='$subject', date='$date' WHERE id='$id'");
   mysql_close();
   echo 'News Edited.';
}

Now that we have an id for the news item we check if the submit button has been pressed, if it has we enter the new data into the database.

else
{
   $id = $_GET['id'];
   $query = mysql_query("SELECT * FROM news WHERE id='$id'");
   $output = mysql_fetch_assoc($query);

If the submit button has not been pressed we get the data from the database. We now need to display it in a form.

<form method="post" action="?id=<? echo $output['id']; ?>">
   Editing <? echo $output['subject']; ?><hr />
   Posted By:<input name="postedby" id="postedby" type="Text" size="50" maxlength="50" value="<? echo $output['postedby']; ?>">
<br />
   Subject:<input name="subject" id="subject" type="Text" size="50" maxlength="50" value="<? echo $output['subject']; ?>">
<br />
   News:<textarea name="news" cols="50" rows="5"><? echo $output['news']; ?></textarea><br />
   <input type="Submit" name="submit" value="Enter information">
</form>

The form gets its data from the variable $output that has data from the database.

Deleting news

To delete the news we need the id of the news item in the database.

<script type="text/javascript">
function check(id){
   if (confirm("Are you sure you want to delete this news item?"))
   this.location.href = "?id="+id;
}</script>

This code prompts the user, asking them if they want to delete the news item. If so then the id of the news item is passed to the PHP script.


if(!$_GET['id'])
{
   $query = mysql_query('SELECT * FROM news ORDER BY id DESC');
   while($output = mysql_fetch_assoc($query))
      echo $output['subject'].' &raquo; <a href="#" onclick="check('.$output['id'].'); return false;">Delete</a><br />';
}

If the id has not been passed to the script we will display a list of the news item subjects with links to allow the user to delete them.

else
{
   $id = $_GET['id'];
   mysql_query("DELETE FROM news WHERE id = $id LIMIT 1");
   echo 'News Deleted.';
}

If the id has been given then we delete it from the database.

Displaying news

Finally we will display the news.

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))
{
   echo $output['subject'].'<br />';
   echo $output['news'].'<br / >';
   echo date('D-M-Y', $output['date']).'<br / >';
   echo 'Posted by '.$output['postedby'];
   echo '<hr />';
}

This code simply gets each row from the database and displays it to the user.

Downloads

Categories

Tags

Social