INTRODUCTION to PHP and Mysql SAMPLE APPLICATION NEWS PUBLISHING SYSTEM

INTRODUCTION TO PHP AND MySQL – SAMPLE APPLICATION – NEWS PUBLISHING SYSTEM

It is now time for you to put your knowledge to use, by building a real-world application that retrieves data from a MySQL database to create a dynamic PHP-based web site. Once you complete this exercise you will have practical, hands-on knowledge of how to use PHP and MySQL together to build usable web applications.

The application here is a news publishing system for a business website - either on an intranet or the Internet. It is intended to provide the organization’s administrative and press personnel with a way to post news items, press releases and articles on the website, and to easily maintain (view, edit and delete) this information.

A MySQL database stores this information, with PHP taking care of retrieving and manipulating the information through a web browser.

This application has two sections - the public section, which consists of the code that displays the latest news and press releases to the site’s visitors, and the private section, which consists of the administration interface for individual editors within the PR department to publish new content to the website.

Both these sections interact with the MySQL database (which contains the actual news stories and press releases).

With this in mind, it should be clear that this application must support the following tasks:

Ø It must be able to display a list of all news items in the database (or the most recent ones), and enable users to view the complete contents of each.

Ø It must let administrators add new items and press releases to the database.

Ø It must enable administrators to edit existing releases, to make corrections or update them with new information.

Ø It must permit the removal of older, out-of-date releases, and news items from the database.

With these requirements in mind, it is time to design the database.

For this exercise, you will need to create a MySQL database on WAMP server (or equivalent) or on a remote server where you can create tables using PHPMyAdmin. The MySQL database is called News – where X is your unique identifier.

Designing the Database

Because the content for the application is stored in a MySQL table, it is important to define exactly what constitutes a press release. If you think about it, you will see that a press release or article can typically be broken down into three subsections - a title, a main body containing the text of the press release or news item, and an information section with the publication date and name of the contact person.

You should access MySQL using the MySQL console (or command line interface) on WAMP or via PHPMyAdmin on the remote server.

If using WAMP (or equivalent), at the mysql prompt, enter:

USE NewsX;

where NewsX refers to the name of the database for which you have complete privileges.

Press the Enter/Return key.

At the mysql prompt, enter:

CREATE TABLE News (ID SMALLINT(5) unsigned NOT NULL auto_increment., Title TEXT NOT NULL, Content TEXT NOT NULL, Contact VARCHAR(255), Timestamp DATETIME DEFAULT '0000-00-00 00:00:00 ' NOT NULL, PRIMARY KEY(ID));

Press the Enter/Return key.

At the mysql prompt, enter:

DESCRIBE News;

Press the Enter/Return key.

You should now populate the table with a couple of dummy records – like the following ones:

At the mysql prompt, enter:

INSERT INTO News (ID, Title, Content, Contact, Timestamp) VALUES('1', 'Megalomaniacs Inc. Is Born', 'EARTH – A new star was born today on the planet third closest to the sun. Megalomaniacs Inc., a venture of WeWantItAll Corp., today threw open its doors for business in the ritzy Jefferson Square business district. Created with the sole goal of colonising every single planet in the known Universe (and beyond), Megalomaniacs Inc, hopes to quickly acquire a monopoly over the vast tracts of uncharted real estate in space. Speaking at a press conference, Megalomaniacs Inc. CEO warned reporters that Megalomaniacs Inc. would "take everything it could, and the some". ', 'Peter Paul (a) ', '2010-04-17 17:29:25');

Press the Enter/Return key.

At the mysql prompt, enter:

SELECT * FROM News;

Press the Enter/Return key to check that the data has been entered.

At the mysql prompt, enter:

INSERT INTO News (ID, Title, Content, Contact, Timestamp) VALUES('2', 'Megalomaniacs Inc. Expands to Mars', 'MARS – As part of its business strategy of "expand and swallow", Megalomaniacs Inc. today announced that it had successfully sent a team of corporate raiders to Mars, in an effort to persuade the inhabitants of that planet to surrender their planet for colonisation. Megalomaniacs Inc. CEO today said that the move was a "friendly overture", but that a failure to comply with the company\'s colonisation plans would result in a "swift and sure eviction of those little green guys". ', 'Tim Jr. (a) ', '2010-04-17 17:13:48');

Press the Enter/Return key.

At the mysql prompt, enter:

SELECT * FROM News;

Press the Enter/Return key to check that two rows of data have been entered.

ALTERNATIVELY, you can create these

Listing and Displaying News Items

You will remember from the requirements discussion that this development effort can broadly be split into two parts. One part consists of the scripts that retrieve the list of newest items from the database and display this list to the user. The other part consists of administrative tools that enable editors to manage this list, enter new information and edit or delete existing information.

Because the first part is simpler, let us get that out of the way first. Two scripts are involved here: list.php, which retrieves a list of the five newest entries in the database, and story.php, which displays the full text for the selected story.

1. Create a new folder in your My PHP Sites folder called News_Publishing_System.

2. Create two new folders within the News Publishing System folder. These folders should be called admin and user.

3. Create a new Dreamweaver site pointing to this folder. You will need to add Remote Info and Testing Server information, and create a new folder on the server News to which you will publish.

4. Open a new PHP file, and enter the following code:

<?php

//database configuration

$host = "localhost";

$user = "XXXXX";

$password = "XXXXX";

$database = "NewsX";

//default contact person

$def_contact = "Johnny Doe (a) ";

5. Save your PHP file as conf.php.

6. Publish your file.

This time we have first created a configuration file to contain the MySQL hostname, the username and the password used by the mysql_connect() function. This file has been included at the top of each script using the include() function.

Note: Extracting this configuration information into a separate file makes it easier to update the application in case the database username or password changes. Updating a single file is far easier than updating multiple scripts - each with the values hard-wired into it.

7. Open a new PHP file, and enter the following code:

<!doctype html>

<html>

<head>

<title>List New Items</title>

</head>

<body>

<tr>

<td bgcolor="navy"><font size="3" color="white"><b>Megalomaniacs Inc : Press Releases</b></font></td>

</tr>

</table>

<ul>

<?php

//includes

include('../conf.php');

//open database connection

$connection = mysql_connect($host, $user, $password) or die ('Unable to connect to MySQL');

//select database

mysql_select_db($database) or die ('Unable to select database!');

//generate and execute query

$query = "SELECT ID, Title, Timestamp FROM news ORDER BY Timestamp DESC LIMIT 0, 5";

$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());

//if records present

if(mysql_num_rows($result) > 0)

{

//iterate through resultset

//print article titles

while($row = mysql_fetch_array($result))

{

<br />

<br />>

<?php

}

//if no records present display message

else

{

<font size = "2">No press releases currently available</font>

<?php

}

//close database connection

mysql_close($connection);

</ul>

<tr>

</tr>

</table>

</body>

</html>

8. Save your PHP file as user\list.php.

9. Publish your file, and view the published file in a browser.

This script connects to the database, retrieves a set of records and formats them for display in a web browser.

Pay special attention to the SELECT query that retrieves the records from the MySQL table. It contains a DESC clause to order the items in the order of most recent first and a LIMIT clause to restrict the result set to five items only.

Also necessary is to include some code that tells the script what to do if no records are returned by the query. This could happen when the application is installed for the first time and no records are present in the database. Without this code, the generated page would be completely empty - not a nice thing to show to users especially on a potentially high-traffic page. The solution is to use an if() statement to check if any records were returned by the query and display a message if none were returned.

Displaying Story Content

You will notice, from the previous code listing, that every press release title is linked to story.php via its unique ID. The story.php script uses this ID to connect to the database and retrieve the full text of the release.

1. Open a new PHP file, and enter the following code:

<!doctype html>

<html>

<head>

<title> Display Story Content </title>

</head

<body>

<tr>

<td bgcolor = "navy"><font size = "3" color = "white"><b>Megalomaniacs Inc : Press Releases</b></font></td>

</tr>

</table>

<?php

//includes

include('../conf.php');

//check for record ID

if((!isset($_GET['id']) || trim($_GET['id']) == ''))

{

die('Missing record ID!');

}

//open database connection

$connection = mysql_connect($host, $user, $password) or die ('Unable to connect to MySQL');

//select database

mysql_select_db($database) or die ('Unable to select database!');

//generate and execute query

$id = $_GET['id'];

$query = "SELECT Title, Content, Contact, Timestamp FROM news WHERE ID = '$id'";

$result = mysql_query($query)

or die("Error in query: $query. " . mysql_error());

//get resultset

$row = mysql_fetch_array($result);

//display details

if($row)

{

<p><font size = "2">This release was published on <?php echo $row["Timestamp"]; ?>. For more information, please contact <?php echo $row["Contact"]; ?></font></p>

<?php

}

else

{

<p><font size = "2">That release could not be located in our database.</font></p>

<?php

}

//close database connection

mysql_close($connection);

<tr>

</tr>

</table>

</body>

</html>

2. Save your PHP file as user\story.php.

3. Publish your file.

4. View list.php in a browser and click on a title link to display the full text of the release – ie. Content.

Again, extremely simple - connect, use the ID to get the full text for the corresponding item and display it.

At this point, you have a primitive publishing system that can be used to provide users of a web site with news, press releases and other information.

Manipulating News Items

At this point in time, there is no simple way to update the database with new information. To insert or edit information, an administrator needs to know SQL and have access to a MySQL client. This may not always be possible, so it is necessary to also develop a simple, friendly interface for database updates.

Based on the requirements outlined previously, this administration section will consist of at least the following four scripts:

Ø list.php - which lists all press releases currently in the database and lets the administrator select an individual record for an edit or delete operation

Ø edit.php - which enables the administrator to update a record

Ø delete.php - which lets the administrator delete a record

Ø add.php - which enables the administrator to add a new record

Let us look at each of these in turn.

Listing News Items

The list.php script is almost identical to the previous list.php. It displays a list of all press releases currently stored in the database with additional links to edit or delete them.

1. Open a new PHP file, and enter the following code:

<!doctype html>

<html>

<head>

<title>List New Items</title>

</head>

<body>

<tr>

<td bgcolor="navy"><font size="3" color="white"><b>Megalomaniacs Inc : Press Releases</b></font></td>

</tr>

</table>

<?php

//includes

include('../conf.php');

//open database connection

$connection = mysql_connect($host, $user, $password) or die('Unable to connect!');

//select database

mysql_select_db($database) or die('Unable to select database!');

//generate and execute query

$query = "SELECT ID, Title, Timestamp FROM News ORDER BY Timestamp DESC";

$result = mysql_query($query) or die("Error in query: $query. " . mysql_error());

//if records present

if(mysql_num_rows($result) > 0)

{

//iterate through resultset

//display title with links to edit and delete scripts

while($row = mysql_fetch_array($result))

{

<br />

<font size = "2"><a href = "edit.php?id=<?php echo $row["ID"]; ?>">edit</a> | <a href="delete.php?id=<?php echo $row["ID"]; ?>">delete</a></font>

<p>

<?php

}

//if no records present display message

else

{

<font size ="2">No releases currently available</font></p>

<p>

<?php

}

//close connection

mysql_close($connection);

<tr>