Creating a Simple Weblog in PHP and MySQL

By Michael Moncur (October 10, 2003)

Weblogs are one of the most popular ways of publishing on the Web. While you can use one of the many weblog services, it's also simple to program a weblog yourself. This is a good place to start if you want to add custom features or integrate a weblog into your site.

This tutorial uses PHP (version 3 or 4) and MySQL (version 3.2 or later) to create a minimal but functional weblog. To make sense of it, you should have a bit of experience with both MySQL and PHP.

Creating the Table

To begin, you will need to create the MySQL table to store weblog entries. This example will use a table with the following columns:

entrydate (TIMESTAMP) - this column will be automatically updated with the current date and time each time you add an entry. Since you'll be displaying entries by date, this field will act as the primary key.
entrytitle (VARCHAR) - this column will store the title for each entry.
entrytext (TEXT) - this column will store the text of each entry.

To create the table, you can use the following MySQL command. Enter it using the MySQL command-line interface or a front-end such as PHPmyAdmin. Before typing this command, type a USE command to select the database that you will be using.

CREATE TABLE weblog (
     entrydate TIMESTAMP PRIMARY KEY,
     entrytitle VARCHAR(100),
     entrytext TEXT);

Creating the Add Entry Page

The weblog will use two PHP files: one to display the weblog to visitors, and another to add an entry. The addentry.php file will include the HTML to display a form for you to type an entry as well as the PHP code to store the entry in the database.

The HTML Form

Since this is a very simple weblog, we only need two fields in the form: a text field for the title of an entry, and a text area for the entry itself. You don't need to prompt for the date, since MySQL will assign it automatically. Here is the HTML for the page before adding PHP:

<HTML>
 <HEAD>
  <TITLE>Add a Weblog Entry</TITLE>
 </HEAD>
 <BODY>
  <H1>Add an Entry</H1>
  <form method="POST" action="addentry.php">
   <b>Title:</b><br>
   <input type="text" name="entrytitle"><br>
   <b>Weblog Entry:</b><br>
   <textarea cols="60" rows="6" name="entrytext"> 
   </textarea><br>
  <input type="submit" name="submit" value="Submit">
  </form>
 </BODY>
</HTML>

The PHP Code

Now all you need is some PHP code to detect when the Submit button is clicked and save the entry in the database. Add the following after the <BODY> tag:

 <?php
  if ($HTTP_POST_VARS['submit']) {
    mysql_connect("server","username","password");
    mysql_select_db("dbname");
    $entrytitle=$HTTP_POST_VARS['entrytitle'];
    $entrytext=$HTTP_POST_VARS['entrytext'];
    $query ="INSERT INTO weblog (entrytitle,entrytext)";
    $query.=" VALUES ('$entrytitle','$entrytext')";
    $result=mysql_query($query);
    if ($result) echo "<b>Successfully Posted!</b>";
    else echo "<b>ERROR: unable to post.</b>";
  }
 ?>

This uses the mysql_connect and mysql_select_db functions to connect to the database. Be sure to specify your server, username, password, and database name. It then constructs a MySQL query in the $query variable, using the data entered into the form, and uses the mysql_query function to insert the data.

Testing the Add Entry Page

Now that you've completed the HTML and PHP, you should have the complete addentry.php file.

Download the complete addentry.php file here.

NOTE: This program does not implement any kind of security. You should place addentry.php in a directory that uses HTTP authentication or another method to ensure that unauthorized users cannot add entries.

Creating the Weblog Page

The other major component of the weblog is a PHP program to display the latest entries from the database. This will be the page visitors access to view your weblog. The following is the complete HTML and PHP code for weblog.php:

<html>
 <head><title>Untitled</title></head>
 <body>
  <h1>Weblog Example</h1>
 <dl>
 <?php
   mysql_connect("server","username","password");
   mysql_select_db("dbname");
   $query ="SELECT entrytitle, entrytext,";
   $query.=" DATE_FORMAT(entrydate, '%M %d, %Y') AS date";
   $query.=" FROM weblog ORDER BY entrydate DESC LIMIT 10";
   $result=mysql_query($query);
   while (list($entrytitle,$entrytext,$entrydate) = 
    mysql_fetch_row($result)) {
       echo "<dt><b>$entrytitle ($entrydate)</b></dt>";
       echo "<dd>$entrytext</dd>";
   }
 ?>
 </dl>
 </body>
</html>

How it Works

The above code includes a simple HTML wrapper and PHP code that includes the following:

The mysql_connect and mysql_select_db functions connect to the database. Be sure to include your correct server, username, password, and database name.
The $query variable is loaded with a SELECT query that returns up to ten of the most recent entries. The DATE_FORMAT function within the query converts the entry_date column's value to a user-friendly date.
The mysql_query function submits the query to the database and stores a pointer to the results in the $result variable.
The while loop uses the mysql_fetch_row statement to retrieve each row of the result. The echo statements display each entry using a simple dictionary list format.

Testing the Weblog Page

To test weblog.php, you should already have added at least one entry using addentry.php. You can copy the code for this file from the listing above or download it. When you load this page, it should display your most recent additions to the weblog.

Download the complete weblog.php file here.

Taking it Further

Of course, this is just a simple illustration of how a weblog based on PHP and MySQL works. It would work fine as a mini-weblog to display the latest news about your site. To build a more complete weblog you may wish to do the following:

Use CSS or HTML to better format the output.
Create a PHP script to display a single entry at a permanent URL.
Add additional fields to the database, such as author's name or subject.
Give the administrator the ability to edit or delete existing entries.
Create a system for visitors to comment on weblog entries.

The author's weblog at www.figby.com began with a simple PHP and MySQL example like this one. It has been further customized with lots of PHP, CSS, and HTML but the fundamental structure remains very similar.

Conclusion

This is a simple example of how powerful MySQL and PHP can be: once you've done the programming, you have a site that you can update by filling out a simple form, without messing with HTML or PHP code. While commercial and open-source weblogs are much more feature-rich, I hope this simple example has given you an idea of how easy it is to develop a custom site from scratch.

Home / News About this Site About the Author Glossary Contact Me Client-Side HTML JavaScript CSS Usability Server-Side PHP MySQL Server Management The Business Search Engines Web Hosting Making Money	Creating a Simple Weblog in PHP and MySQL By Michael Moncur (October 10, 2003) Weblogs are one of the most popular ways of publishing on the Web. While you can use one of the many weblog services, it's also simple to program a weblog yourself. This is a good place to start if you want to add custom features or integrate a weblog into your site. This tutorial uses PHP (version 3 or 4) and MySQL (version 3.2 or later) to create a minimal but functional weblog. To make sense of it, you should have a bit of experience with both MySQL and PHP. Creating the Table To begin, you will need to create the MySQL table to store weblog entries. This example will use a table with the following columns: entrydate (TIMESTAMP) - this column will be automatically updated with the current date and time each time you add an entry. Since you'll be displaying entries by date, this field will act as the primary key. entrytitle (VARCHAR) - this column will store the title for each entry. entrytext (TEXT) - this column will store the text of each entry. To create the table, you can use the following MySQL command. Enter it using the MySQL command-line interface or a front-end such as PHPmyAdmin. Before typing this command, type a USE command to select the database that you will be using. CREATE TABLE weblog ( entrydate TIMESTAMP PRIMARY KEY, entrytitle VARCHAR(100), entrytext TEXT); Creating the Add Entry Page The weblog will use two PHP files: one to display the weblog to visitors, and another to add an entry. The addentry.php file will include the HTML to display a form for you to type an entry as well as the PHP code to store the entry in the database. The HTML Form Since this is a very simple weblog, we only need two fields in the form: a text field for the title of an entry, and a text area for the entry itself. You don't need to prompt for the date, since MySQL will assign it automatically. Here is the HTML for the page before adding PHP: <HTML> <HEAD> <TITLE>Add a Weblog Entry</TITLE> </HEAD> <BODY> <H1>Add an Entry</H1> <form method="POST" action="addentry.php"> <b>Title:</b><br> <input type="text" name="entrytitle"><br> <b>Weblog Entry:</b><br> <textarea cols="60" rows="6" name="entrytext"> </textarea><br> <input type="submit" name="submit" value="Submit"> </form> </BODY> </HTML> The PHP Code Now all you need is some PHP code to detect when the Submit button is clicked and save the entry in the database. Add the following after the <BODY> tag: <?php if ($HTTP_POST_VARS['submit']) { mysql_connect("server","username","password"); mysql_select_db("dbname"); $entrytitle=$HTTP_POST_VARS['entrytitle']; $entrytext=$HTTP_POST_VARS['entrytext']; $query ="INSERT INTO weblog (entrytitle,entrytext)"; $query.=" VALUES ('$entrytitle','$entrytext')"; $result=mysql_query($query); if ($result) echo "<b>Successfully Posted!</b>"; else echo "<b>ERROR: unable to post.</b>"; } ?> This uses the mysql_connect and mysql_select_db functions to connect to the database. Be sure to specify your server, username, password, and database name. It then constructs a MySQL query in the $query variable, using the data entered into the form, and uses the mysql_query function to insert the data. Testing the Add Entry Page Now that you've completed the HTML and PHP, you should have the complete addentry.php file. Download the complete addentry.php file here. NOTE: This program does not implement any kind of security. You should place addentry.php in a directory that uses HTTP authentication or another method to ensure that unauthorized users cannot add entries. Creating the Weblog Page The other major component of the weblog is a PHP program to display the latest entries from the database. This will be the page visitors access to view your weblog. The following is the complete HTML and PHP code for weblog.php: <html> <head><title>Untitled</title></head> <body> <h1>Weblog Example</h1> <dl> <?php mysql_connect("server","username","password"); mysql_select_db("dbname"); $query ="SELECT entrytitle, entrytext,"; $query.=" DATE_FORMAT(entrydate, '%M %d, %Y') AS date"; $query.=" FROM weblog ORDER BY entrydate DESC LIMIT 10"; $result=mysql_query($query); while (list($entrytitle,$entrytext,$entrydate) = mysql_fetch_row($result)) { echo "<dt><b>$entrytitle ($entrydate)</b></dt>"; echo "<dd>$entrytext</dd>"; } ?> </dl> </body> </html> How it Works The above code includes a simple HTML wrapper and PHP code that includes the following: The mysql_connect and mysql_select_db functions connect to the database. Be sure to include your correct server, username, password, and database name. The $query variable is loaded with a SELECT query that returns up to ten of the most recent entries. The DATE_FORMAT function within the query converts the entry_date column's value to a user-friendly date. The mysql_query function submits the query to the database and stores a pointer to the results in the $result variable. The while loop uses the mysql_fetch_row statement to retrieve each row of the result. The echo statements display each entry using a simple dictionary list format. Testing the Weblog Page To test weblog.php, you should already have added at least one entry using addentry.php. You can copy the code for this file from the listing above or download it. When you load this page, it should display your most recent additions to the weblog. Download the complete weblog.php file here. Taking it Further Of course, this is just a simple illustration of how a weblog based on PHP and MySQL works. It would work fine as a mini-weblog to display the latest news about your site. To build a more complete weblog you may wish to do the following: Use CSS or HTML to better format the output. Create a PHP script to display a single entry at a permanent URL. Add additional fields to the database, such as author's name or subject. Give the administrator the ability to edit or delete existing entries. Create a system for visitors to comment on weblog entries. The author's weblog at www.figby.com began with a simple PHP and MySQL example like this one. It has been further customized with lots of PHP, CSS, and HTML but the fundamental structure remains very similar. Conclusion This is a simple example of how powerful MySQL and PHP can be: once you've done the programming, you have a site that you can update by filling out a simple form, without messing with HTML or PHP code. While commercial and open-source weblogs are much more feature-rich, I hope this simple example has given you an idea of how easy it is to develop a custom site from scratch. Back to Top \| Return to PHP index \| Home
	(c) 2003-2005 by Michael Moncur. All rights reserved. No content may be reproduced without explicit consent.