PHP/MySQL CMS

spang - January 14, 2006
33347 views

In this tutorial you will be creating a simple CMS (Content Management System). This tutorial will be focused around creating a news CMS in particular but you can customize it to whatever suites you. In order to create this CMS you will need to have a web server running a recent version of MySQL and PHP.

This news CMS will have the following pages:
-dbconnect.php - to connect to the MySQL database
-add.php - to add MySQL content
-edit.php - to edit MySQL content
-view.php - to view MySQL content

The MySQL Table

To start off, we have to create a MySQL table to hold all of our content. This MySQL table will have the variables ID, title, author, date, and, content. The following query creates a table called news in a MySQL database of your choice. Obviously, you can edit the name and variables of the table but you will have to change those in the subsequent pages.

CREATE TABLE `news` (
`ID` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`title` VARCHAR( 250 ) NOT NULL ,
`author` VARCHAR( 250 ) NOT NULL ,
`date` VARCHAR( 250 ) NOT NULL ,

`content` TEXT NOT NULL ,
PRIMARY KEY ( `ID` )
) TYPE = MYISAM

Connect to Your MySQL Table
Now that we have our MySQl table created, we will need some way to connect to it. This is where the file, dbconnect.php, comes in handy. It uses the variables $host, $dbuser, $dbpass, $dbname for your MySQL hostname, username, password, and database, respectively. It also utilizes the functions mysql_connect() and mysql_select_db() to connect to your MySQL server and select your MySQL database, respectively.

Create a file called dbconnect.php and enter the following code:

<?php

$host= "localhost";
$dbuser ="username";
$dbpass = "password";
$dbname = "database";

$connection = mysql_connect($host, $dbuser, $dbpass) or die(mysql_error());
mysql_select_db($dbname) or die(mysql_error());
?>

Add Content to Your MySQL Table

Unless you want to enter your data by hand into phpMyAdmin or the MySQL Command Line, you'll want a file that you can use to add and upload data to your MySQL table. This is where the file, add.php, comes into play. We will be using simple form to add data and a simple PHP form handling application to enter data into your MySQL table.

Create a file called add.php and enter the following code:
<?php
//The following code is the PHP form handling application.
//Connecting to the MySQL database
require('dbconnect.php');

//The current date.
$today = date('n/j/Y');


if($_POST['submit']) {
//Simplifying the variables.
$title = $_POST['title'];
$author = $_POST['author'];
$date = $_POST['date'];
//trim() strips white space from the beginning and end of a line.
$date = trim($date);
$content = $_POST['content'];


//Checks for empty fields or invalid date.
if((empty($title)) OR (empty($author)) OR (empty($date)) OR (empty($content))) {
echo "<center><strong>Please fill in all fields!</strong></center>";

} else {
//explode() separates the date by the '/' character and outputs it to an array.
$explode_date = explode('/', $date);
//checkdate() returns FALSE if the date is invalid.

$check_date = checkdate($explode_date[0], $explode_date[1], $explode_date[2]);
if($check_date == false) {
echo "<center><strong>Invalid date entered!</strong></center>
";
} else {
//htmlspecialchars() converts special characters into HTML entities.
$title = htmlspecialchars($title);
$author = htmlspecialchars($author);

//The MySQL query which will insert content into the table.
$query = "INSERT INTO news (ID, title, author, date, content) VALUES ('', '$title', '$author', '$date', '$content')";
//Executing the query with mysql_query().
mysql_query($query) or die(mysql_error());
echo "<center><strong>News item added!</strong></center>";
}
}
}


//Closing the connection.
mysql_close($connection);
?>

<!-- The following code is the HTML form. It uses the POST method to send data to the form handling application. The default value for the date is the current date. The maximum lengths for the Title and Author fields are 250 characters and 10 characters for the Date field. We will also be using tables for some simple formatting. -->
<form method="post" action="add.php">
<table align="center">
<tr><td align="right">Title:</td><td><input type="text" name="title" maxlength="250" /></td></tr>
<tr><td align="right">Author:</td><td><input type="text" name="author" maxlength="250" /></td></tr>
<tr><td align="right">Date:</td><td><input type="text" name="date" value="<?php echo "$today"; ?>" maxlength="10" /></td></tr>

<tr><td align="right">Content:</td><td><textarea name="content" cols="50" rows="10"></textarea></td></tr>
<tr><td> </td><td><input type="submit" name="submit" value="Submit" /><input type="reset" name="reset" value="Reset" /></td></tr>
</table>
</form>

View the Contents of Your MySQL Table
There would be no use of adding content to the MySQL table if you couldn't view it. This is where view.php plays a role. We will simply be querying the contents of the MySQL table and displaying it using a simple PHP script. You will also be able to select a single item.

Create a file called view.php and enter the following code:
<?php
//The following PHP script queries and displays the contents of the MySQL table.

//Connecting to the MySQL database
require('dbconnect.php');

//Should we show a single item or a list?
if($_GET['action'] == "view") {
//Display a single result.
$id = $_GET['id'];
//The MySQL query. Select all from the table news where the ID equals the id sent in URL.

$query = "SELECT * FROM news WHERE ID='$id'";
//Executing the query.
$result = mysql_query($query) or die(mysql_error());
//Displaying the results of the query.
while ($row = mysql_fetch_array($result)) {
//extract() takes an associative array and treats the keys as variable names and values as variable values.

extract($row);
//nl2br() translates all newlines ('n') as HTML tags.
$content = nl2br($content);
echo "<table><tr><td><strong>$title</strong></td></tr>
<tr><td><small>Written by $author on $date</small></td></tr>

<tr><td>$content</td></tr>";
}
} else {
//Since we're not displaying a single result,
//we're going to display a list of results.
//The MySQl query. Selects all from the table news with a limit of 5 results.

$query = "SELECT * FROM news ORDER BY ID DESC LIMIT 5";
//Execute the query.
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
//extract() takes an associative array and treats the keys as variable names and values as variable values.
extract($row);

//nl2br() translates all newlines ('n') as HTML tags.
$content = nl2br($content);
echo "<table><tr><td><strong><a href="view.php?action=view&id=$ID">$title</a></strong></td></tr>
<tr><td><small>Written by $author on $date</small></td></tr>
<tr><td>$content</td></tr>";

}
}

//Close the connection.
mysql_close($connection);
?>

Edit the Contents of Your MySQL Table
Unless you want to edit the contents of your MySQL table in phpMyAdmin or the MySQL Command Line you'll want to have a file like edit.php. With edit.php you'll be able to select your item from a list and edit it using a simple HTML form. Another simple PHP form handler will enter your changes into your MySQl table.


<?php
//The following PHP script allows you to edit the
//contents of your MySQL table.
//Connecting to the MySQL database
require('dbconnect.php');

//Should we show a single item or a list?
if($_POST['edit']) {
//Simplifying the variables.

$id = $_POST['id'];
$title = $_POST['title'];
$author = $_POST['author'];
$date = $_POST['date'];
//trim() strips white space from the beginning and end of a line.
$date = trim($date);

$content = $_POST['content'];

//Checks for empty fields or invalid date.
if((empty($title)) OR (empty($author)) OR (empty($date)) OR (empty($content))) {
echo "<center><strong>Please fill in all fields!</strong></center>
";

} else {
//explode() separates the date by the '/' character and outputs it to an array.
$explode_date = explode('/', $date);
//checkdate() returns FALSE if the date is invalid.
$check_date = checkdate($explode_date[0], $explode_date[1], $explode_date[2]);
if($check_date == false) {

echo "<center><strong>Invalid date entered!</strong></center>
";
} else {
//htmlspecialchars() converts special characters into HTML entities.
$title = htmlspecialchars($title);
$author = htmlspecialchars($author);


//The MySQL query which will update the content in the table.
$query = "UPDATE news SET title = '$title', author = '$author', date = '$date', content = '$content' WHERE ID = '$id'";
//Execute the query.
$result = mysql_query($query) or die(mysql_error());
echo "<center><strong>News item modified!</strong></center>";

}
}
} elseif($_GET['action'] == "edit") {
//Display a single result.
$id = $_GET['id'];
//The MySQL query. Select all from the table news where the ID equals the id sent in URL.

$query = "SELECT * FROM news WHERE ID='$id'";
//Executing the query.
$result = mysql_query($query) or die(mysql_error());
//Displaying the results of the query.
while ($row = mysql_fetch_array($result)) {
//extract() takes an associative array and treats the keys as variable names and values as variable values.

extract($row);
?>
<form method="post" action="edit.php">
<table align="center">
<tr><td align="right">Title:</td><td><input type="text" name="title" value="<?php echo "$title"; ?>" maxlength="250" /></td></tr>
<tr><td align="right">Author:</td><td><input type="text" name="author" value="<?php echo "$author"; ?>" maxlength="250" /></td></tr>
<tr><td align="right">Date:</td><td><input type="text" name="date" value="<?php echo "$date"; ?>" maxlength="10" /></td></tr>
<tr><td align="right">Content:</td><td><textarea name="content" cols="50" rows="10"><?php echo "$content"; ?></textarea></td></tr>

<tr><td> </td><td><input type="hidden" name="id" value="<?php echo "$ID"; ?>" /><input type="submit" name="edit" value="Modify" /><input type="reset" name="reset" value="Reset" /></td></tr>
</table>
</form>
<?
}
} else {
//Since we're not displaying a single result,
//we're going to display a list of results.

//The MySQl query. Selects all from the table news.
$query = "SELECT * FROM news ORDER BY ID DESC";
//Execute the query.
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
//extract() takes an associative array and treats the keys as variable names and values as variable values.

extract($row);
echo "<table><tr><td><strong><a href="edit.php?action=edit&id=$ID">$title</a></strong></td></tr>
<tr><td><small>Written by $author on $date</small></td></tr>

<tr><td><strong><a href="delete.php?id=$ID">DELETE</a></strong></td></tr>



Delete Entries from your MySQL Database

And finally, the last page, which deletes entries from the MySQL database. Another simple form handler with another form that confirms whether or not to delete the entry.


<?php
//The following PHP script deletes entries from your MySQL database..
//Connecting to the MySQL database
require('dbconnect.php');

//Naming some variables here.
$id = $_GET['id'];
$id2 = $_POST['id'];


if($_POST['deny']) {
//We shall not delete the entry, my lord.
$path = "http://".$_SERVER['HTTP_HOST']."/edit.php";
header("Location: $path");
exit;
} elseif($_POST['confirm']) {
//We shall proceed to delete!
//Naming some variables here.

//The MySQl query. Deletes an entry from the database.
$query = "DELETE FROM news WHERE ID = '$id2'";
//Execute the query.
$result = mysql_query($query) or die(mysql_error());
echo "The entry has been deleted";
header("Refresh: 2; edit.php");
} else {
//OK, we've ID'd the thing, can we proceed Cap'n?
?>

<!-- Some tables for formatting and a form to confirm the deletion.-->
<table align="center">
<form action="delete.php" method="post">
<tr><td>Do you really want to delete this entry?</td></tr>
<tr><td><input type="hidden" name="id" value="<?php echo "$id"; ?>" /><input type="submit" name="confirm" value="Yes" /><input type="submit" name="deny" value="No" /></td></tr>
</form>
</table>
<?php
}
?>


Well there you have it, a simple PHP/MySQL CMS. If you liked this tutorial please link back to me (http://wd.spangsolutions.com) or give me some credit (even though most of you probably won't). Also, don't forget to join the forums. If you have any questions, don't be afraid to post them in the forums!

17 Comments

Dean Hopkins said
March 21, 2006 at 1:09 PM

Awesome tutorial, thanks alot ;)

Rui said
March 21, 2006 at 1:59 PM

great

Josh Buckley said
March 21, 2006 at 2:20 PM

really helpful, recommended

Tony Bob said
March 21, 2006 at 8:12 PM

Im converting this tutorial into a tutorial cataloging sysytem and i need help on the basic code to get what you already have working. Without any modifications yet i cant get add.php to work. I get this error "Parse error: syntax error, unexpected '>' in /home/tonybobd/public_html/tuts/add.php on line 28" Any ideas.

ryan said
March 22, 2006 at 11:17 AM

this is a great tutorial, just the help i needed to get a blog system up. there were a few parse errors i ran into but most were just because some brackets were missing. still one of the best tutorials i've seen, highly recommended

bassem said
March 27, 2006 at 8:23 AM

it gives me error :
Parse error: parse error in c:\phpdev\forum\myproject\add.php on line 28

James said
March 29, 2006 at 2:31 AM

it gives me error :
Parse error: parse error in c:\phpdev\forum\myproject\add.php on line 28

Venomsnake said
March 29, 2006 at 7:28 PM

What is line 28 james?

Koosh said
March 31, 2006 at 8:43 AM

Parse error: parse error, unexpected $ in /home/---/public_html/---/edit.php on line 45

and thats line:

<tr><td><input type="hidden" name="id" value="<?php echo "$id"; ?>" /><input type="submit" name="edit" value="Modify" /><input type="reset" name="reset" value="Reset" /></td></tr></table></form>

any ideas to fix this please?

spang said
March 31, 2006 at 3:00 PM

Thanks for all the comments. I'm going to fix all of the errors in the tutorial shortly. If you have any other questions just email me at spangsolutions[at]gmail.com

Ton said
April 5, 2006 at 12:38 PM

Just go in add.php to line 22 and change
echo "<center><strong>Please fill in all fields!</strong></center>

into

echo "<center><strong>Please fill in all fields!</strong></center>";

Ton said
April 5, 2006 at 12:39 PM

something wrent wrong with my comment

well at the end of line 22 add ";

spang said
April 13, 2006 at 7:17 PM

I've changed the error that everyone is reporting on line 22 of add.php. If you find any more errors comment on them and I'll change them as soon as I can. Thanks.

0re0 said
October 3, 2006 at 6:44 PM

im getting an error on the edit.php . And i dont know why.Its on the very last line where you didn't close out the code..for example

<tr><td><small>Written by $author on $date</small></td></tr>

("<tr><td><strong><a href=\"delete.php?id=$ID\">DELETE</a></strong></td></tr>

i closed this part out like this

<tr><td><small>Written by $author on $date</small></td></tr>

<tr><td><strong><a href=\"delete.php?id=$ID\">DELETE</a></strong></td></tr>"; ?>

and im gettin errored

0re0 said
October 3, 2006 at 6:45 PM

sorry aobut that,i didnt realize this form would strip the code out

Bongo Joe said
January 14, 2007 at 3:27 AM

In view.php its says
Parse error: parse error, unexpected $end in D:\Programmer\xampp\htdocs\newssystem\view.php on line 51 and in edit.php it says
Parse error: parse error, unexpected T_STRING, expecting ',' or ';' in D:\Programmer\xampp\htdocs\newssystem\edit.php on line 86

spang said
January 15, 2007 at 7:00 PM

Try pasting the code in view.php now. For some reason there were some extra apostrophes in there.

Post a Comment

Author

Url

Validation

Comment
Smilies:

BBCode:
[FONT] Arial Arial Black Arial Narrow Book Antiqua Century Gothic Comic Sans MS Courier New Fixedsys Franklin Gothic Medium Garamond Georgia Impact Lucida Console Lucida Sans Unicode Microsoft Sans Serif Palatino Linotype System Tahoma Times New Roman Trebuchet MS Verdana [SIZE] 6pt 8pt 10pt 12pt 14pt 16pt 18pt 20pt 22pt 24pt 26pt [COLOR] Black Sienna Dark Olive Green Dark Green Dark Slate Blue Navy Indigo Dark Slate Gray Dark Red Dark Orange Olive Green Teal Blue Slate Gray Dim Gray Red Sandy Brown Yellow Green Sea Green Medium Turquoise Royal Blue Purple Gray Magenta Orange Yellow Lime Cyan Deep Sky Blue Dark Orchid Silver Pink Wheat Lemon Chiffon Pale Green Pale Turquoise Light Blue Plum White