PHP Web Blog – Part 1

In this introductory article, we will look into the use of many different aspects of PHP in order to create a fully functional web application. This article will cover the 5 most common functions that a PHP-to-MySQL relationship will generate.

The web application we will cover is what is commonly known as a “Web Blog”. The first step is to get the MySQL database ready to receive and store the data. I won’t cover the different ways this can be done, but for ease of setup, a query to create this table is provided below.

CREATE TABLE blog (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, title 
VARCHAR(100) NOT NULL, entry TEXT NOT NULL, date_entered DATETIME NOT NULL)

Since we will be creating multiple pages, all of which access the same SQL database, we shall create a function that we will keep in a separate file. This will allow for greater security. I would recommend leaving includes in an offline folder or if no other choice is available, a password-protected folder on the web server.

Of importance to note, the error messages that MySQL is capable of generating are not reported for security reasons. These errors can be generated using the ‘mysql_error()’ function; simply replace the function command and errors will be generated. This is a great idea if you’re having connection problems or the queries are generating no data.

The file we mentioned above will be created, and will be called ‘sql_connect.inc’. The code for that file is listed below.

<?php
************************************************************************
* Function to establish a connection to the local MySQL server         *
* This function takes the database name and establishes the connection *
* without giving any error output                                      *
************************************************************************
function sql_connect ($database) { //Function to connect to MySQL server
	if ($dbc = mysql_connect ('server', 'username', 'password')) { 
		if (!@mysql_select_db("$database")) {  
			die ();
//If the database selection fails, the script will die.
		}
	} else { 
		die ();
//If the SQL server could not be contacted the script will die.
	}
}
?>

This function takes several steps. Its first task is to connect to the mySQL server using the appropriate server, username and password. If it is unable to connect, it will stop the entire script from running by using the ‘die’ command. If the SQL server can be reached, an attempt is made to connect to the specified database. Again, if the database cannot be reached, the script is stoped using the ‘die’ command.

Now that we have our database, our connection ready and it is time to create the first page. This page will display the entries directly on your homepage, accessible to everyone who visits the site. While working through the creation of this page, we will learn how to send a basic query to the mySQL server, obtain those results back from the server, then use a ‘while’ loop to process that data into HTML code to be displayed.

The include below is required for this script, so if it can’t be found, the script will not run. This will help to protect your php code by not revealing pieces of your site if a section get moved or deleted.

<?php
require (‘/some/local/path/sql_connect.inc’);

We createed our query to pull all the data from the database, sorting it by newest first to oldest last. Only the required fields are requested from the mySQL server so that data transfers between the sql server and the php server are kept to a minimum. Additionally, with less data to handle, the php script will run a bit faster.

$query = "SELECT `title` , `entry` , `date_entered` , `user` FROM blog ORDER 
BY date_entered DESC";

The query has now been created and sent to the SQL server. The results are returned in the array called ‘$r’. The ‘else’ part of this statement will use ‘die’ to stop the script if there is a problem with the query being properly completed.

If ($r = mysql_query ($query)) { 
//sending the query to the mySQL server

The results (which are now in an array) are then taken and put through a ‘while’ loop. This will take the data from each line of the aray and loop through it, processing the data row by row. The ‘mysql_fetch_array’ function is used here so that each row will be processed in turn. The array pointer for $r then gets incremented to the next row. When the mysql_fetch_array encounters the end, it returns a null indicator which ends the while loop.

	While ($row = mysql_fetch_array($r)) { 
	//inputs the data into the table
	//The while loop will go through the entire array

The next lines take the data from the various row elements and formats it if necessairy. At this time, we also place the data into specific variables so that any formating features of the text can be stored. We also see two new functions used here: the ‘date’ function and the ‘nl2br’ function.

The date funtion will allow the formating of a timestamp (epoch in our case). This gets formatted into a display that is easy to read. The second function, nl2br, will take the carriage return character that gets stored in the mySQL database and convert it into a <br> so it displays properly on the page.

$date_entered = date ('F j g:i A', $row['date_entered']);
//The time data is being formated for a proper display to the page
		
$user = $row['user'];
$title = $row['title'];
$entry = nl2br ($entry);

The HTML code is now created for the individual tables so that they can be displayed on the front page of the website. Individual tables are used in this example, however it is possible to create this with one large table.

Using php, it’s possible to close and open php tags throughout the page yet keep the script continuous. This aspect gets used here to keep our code looking clean and easy to read. A ‘print’ or ‘echo’ could also be used for each line instead.

?>
<table align="center" border="0" width="80%">
	<tr>
		<td>
	<b><?php echo $title; ?></b>- 
Posted by: <b><?php echo $user; ?></b>
		</td>
		<td>
		<div align="right"><?php echo $date_entered; ?></div>
			</td>
			</tr>
			<tr>
		<td colspan="2">
		<?php echo $entry; ?>
			</td>
			</tr>
		</table><br />
		<?php }

   } else { //query did not run.
        die ();
        }

Being considerate to the mySQL server, rather then letting the session time out, we will properly close our connection to the server in order to free resources and not bog down the SQL server.

	mysql_close();
?>

The front page has now been created, with its completion we have learned how to create and use a custom function. We put this function into a seperate file to allow the reuse of the code for this page and future pages. An include was then used to add this code to the page. The function was created to allow a connection to the mySQL server and relevant database. Two mySQL specific functions were used: ‘mysql_connect’ and ‘mysql_select_db’ to establish this connection.

Further along in the page, some built-in string manipulation commands were used. These functions formated the data obtained from the database into HTML and people friendly formats, simplifying the task of getting the data ready for display. A while loop was then implemented to cycle through the query results.

The next article will cover the creation of the administration section of this application, including the add, edit and delete pages. See you then!!