Building a data-driven web site (reposted)

this was originally posted last month, but got lost somehow, so here it is again in all its warty glory...

It's been a while, so this is likely to be a lengthy post...

To start off, we're going to look a the topography of our data-driven book site: a page that lists all the authors, with links from each author's name to a page that lists the books written by that author. For maintenance, we'll add pages that allow author records to be added, edited and deleted.

Before we get stuck into this, we'll need to know how to communicate with our database using SQL (structured query language) statements. Have a look at the SQL CHEAT SHEET for some examples of SQL that select, update, insert and delete records from a database. You can experiement with these in myPhpAdmin by opening a SQL window and typing in commands.

Master/Detail Pages

For our first page we're just going to display a list of authors. The pseudocode for this operation would read something like:
connect to the database engineselect a database to useexecute a SQL statement to retrieve some records from the databasedisplay each record on the web pageand the php code to accomplish this might look something like:

<?php

// connect to the database engine
@ $db = mysql_pconnect('localhost','username','password');
// do some error checking
if (!$db) {
echo 'error: couldnt connect to the database';
exit;
}
else {
//echo ' all ok with connection';
}

// select a database on the database server
mysql_select_db('your_db',$db);

// execute a SQL query to select records from the authors table
$result = mysql_query('SELECT * FROM authors');

// display each record on the web page
$numresults = mysql_num_rows($result);
//echo $numresults." records found";
for ($i=0; $i<$numresults; ++$i) {
$row = mysql_fetch_assoc($result);
echo $row['firstname'].' '.$row['surname'].'<br />'';
}

?>


So far, so good. But we want more, vis: links from the author names to a page that lists all the books they've written, so we'd need to update the echo statement to look more like:
echo '<a href="titles.php?authorID='.$row['authorID'].'">'.$row['firstname'].' '.$row['surname'].'</a><br />'';Wha? I hear you ask...

Quite simple really; we've wrapped an anchor tag around our author names, linking them to (an as yet unwritten) page: titles.php, which will list all the books written by that author.

So that our new page can list just one author, we need to give it a hint about what author we're interested in, and we do this by appending the authorID to the URL: ?authorID=nnn, where nnn is grabbed from the database and pasted into the link, a unique ID for each author.

At the other end (on the titles.php page), our script will grab this ID using $_GET and use it to build an SQL statement which gets just those books written by our author. Something like:

<?php

// connect to the database engine
@ $db = mysql_pconnect('localhost','username','password');
// do some error checking
if (!$db) {
echo 'error: couldnt connect to the database';
exit;
}
else {
//echo ' all ok with connection';
}

// select a database on the database server
mysql_select_db('your_db',$db);

// execute a SQL query to select records from the authors table
$result = mysql_query('SELECT * FROM titles where authorID = '.$_GET['authorID']);

// display each title on the web page
$numresults = mysql_num_rows($result);
//echo $numresults." records found";
for ($i=0; $i<$numresults; ++$i) {
$row = mysql_fetch_assoc($result);
echo $row['title']<br />'';
}

?>

Note the php which grabs our authorID from the URL and sticks it onto the end of our SQL statement:
'SELECT * FROM titles where authorID = '.$_GET['authorID']
Nice and easy, yes? On to...

Adding New Records

To add a new record we need to write a form with text inputs for each field in the table, and link to this new page from our original authors list. So, to start we'd add a little line of HTML to our authors.php page:
<a href="admin/authoradd.php">Add a new author</a>(note, we're putting our administration pages in a new sub-directory called admin. More on why later)

The form itself is rather simple, just asking for the firstname and lastname of our new author (we don't need to get an authorID because this is an autonumber field and the database will do this for us). Something like this should do the trick:

<h1>Add a New Author</h1>
<form method="post" action="authoradd2.php">
<table border="0" cellpadding="3" >
<tr>
<td>First Name:</td>
<td><input type="text" name="firstname" /></td>
</tr><tr>
<td>Surname:</td>
<td><input type="text" name="surname" /></td>
</tr><tr>
<td></td>
<td><input type="submit" value="Add Author" /></td>
</tr>
</table>
</form>

For simplicity the form will submit (POST) to a second php page: authoradd2.php which will do the actual work of creating the new record. The pseudocode for this operation would be:
Get the form elements into variablesConstruct an SQL statement from these to insert a new recordConnect to the database engineSelect a databaseExecute the SQL statementRedirect back to the authors list (authors.php) to display the new authorand the code might look something like:

<?php
// get the firstname and surname from the form
$firstname = $_POST['firstname'];
$surname = $_POST['surname'];

// build the SQL statement which will insert a record
$sql = "INSERT INTO authors (firstname, surname) VALUES (' ".$firstname." ', ' ".$surname." ') ";

// connect to the database engine
@ $db = mysql_pconnect('localhost','username','password');
// do some error checking
if (!$db) {
echo 'error: couldnt connect to the database';
exit;
}
else {
//echo ' all ok with connection';
}

// select a database on the database server
mysql_select_db('your_db',$db);

// execute the SQL statement
$result = mysql_query($sql);
// check that it ran correctly
if (!$result) {
die('Invalid query: ' . mysql_error());
}

// redirect to the authors list to show the results
header("location: ../authors.php");

?>

let's move on now to...

Editing a Record

This is really a combination of things we've already done. Firstly, we need to add an 'Edit' link next to each author's name (which must include the authorID in the URL). This would mean changing our authors.php file so that each author would be displayed as:

echo '<a href="titles.php?authorID='.$row['authorID'].'">'.$row['firstname'].' '.$row['surname'].'</a><a href="admin/authoredit.php?authorID='.$row['authorID'].'">edit</a><br />'';

The important new part being:

<a href="admin/authoredit.php?authorID='.$row[authorID].'">edit</a>

And the new authoredit.php page? It would take the authorID, select the author record from the database, and populate a form with these values, allowing the user to edit them. Someting like:

<?php

// connect to the database engine
@ $db = mysql_pconnect('localhost','username','password');
// do some error checking
if (!$db) {
echo 'error: couldnt connect to the database';
exit;
}
else {
//echo ' all ok with connection';
}

// select a database on the database server
mysql_select_db('your_db',$db);

// execute a SQL query to select the author record from the authors table
$result = mysql_query('SELECT * FROM authors where authorID = '.$_GET['authorID']);

?>

<h1>Edit Author</h1>
<form method="post" action="authoredit2.php">
<table border="0" cellpadding="3" >
<tr>
<td>First Name:</td>
<td><input type="text" name="firstname" value="<?php echo $row['firstname'] ?>" /></td>
</tr><tr>
<td>Surname:</td>
<td><input type="text" name="surname" value="<?php echo $row['surname'] ?>"/></td>
</tr><tr>
<td><input type="hidden" name="authorID" value="<?php echo $row['authorID'] ?>" /></td>
<td><input type="submit" value="Add Author" /></td>
</tr>
</table>
</form>

One trick to note: the authorID is being passed to authoredit2.php as a hidden field:

<input type="hidden" name="authorID" value="<?php echo $row['authorID'] ?>" />

We don't want the end user to edit this value, but need to send it through so it can be used when updating the record. Once submitted the form calls authoredit2.php, which performs the following operations:
Retrive the form valuesBuild an SQL statement from them to update the recordConnect to the database engineSelect a databaseExecute the SQL statementRedirect the user to the authors list to display the resultsI won't bore you with a full listing, just the first 2 steps:

// get the firstname, surname and authorID from the form
$firstname = $_POST['firstname'];
$surname = $_POST['surname'];
$authorID = $_POST['authorID'];

// build the SQL statement which will insert a record
$sql = "UPDATE authors SET firstname = '".$firstname."', surname = '".$surname."' WHERE authorID = ".$authorID;

The biggest trick here is to remember to wrap any text strings inside single quotes for the SQL statement. This can call for some tricky double quote stuff in the php. Perhaps a view of the final SQL will give you an idea of what we're aiming at here. If, for example, I wanted to author number 1 in the database to assume my name the SQL would look like this:
UPDATE authors SET firstname = 'Peter', surname = 'Shanks' WHERE authorID = 1
Nearly done now, just one more thing:

Deleting Records

Lucky for us, this is really the easiest operation so far. All we need do is add a little code right next to our edit links on the authors.php page:


<a href="admin/authordelete.php?authorID='.$row['authorID'].'">del</a>

As with the edit link, we're passing each author's ID in with the URL. This time we don't need a form, just the php to build the SQL and execute it before redirecting us back to the author list page:

<?php
$authorID = $_GET['authorID'];
$sql = "DELETE FROM authors WHERE authorID = ".$authorID;
require("../connection.php");

$result = mysql_query($sql);
if (!$result) {
die('Invalid query: ' . mysql_error());
}

header("location: ../authors.php");
?>

Of note here: I got sick of writing the database connection and selection code over and over. Instead I put it in a seperate file: connection.php, which I included in this one with the php function: require("../connection.php");

And that's it - we've built a fully functional, data-driven set of web pages. If you want to practice your skills further, add some pages which allow you to view book details when you click on titles, and add, edit and delete titles from the database.