Chapter 12 - Finishing Touches

This application is far from fully working, let alone finished. We need to add some basic functionality still - add and edit wishlists, and add and edit the items in those wishlists, at the very least.

Adding a "Settings" page would be a good start, so the user can view and edit their current settings when they log in. Put a link in the head() function, next to the "Logout" link, so that the user can view their settings. A little bit of CSS can mark this header as being different, too - we'll just make the text slightly smaller than the regular text.

head() function in functions.php

echo "<div class='userlinks'>Welcome, " . $_SESSION['name'] . ". (<a href='/logout.php'>Logout</a> <a href='/settings.php'>Settings</a>div>

style.css stylesheet

div.userlinks { font-size: 10px; }

Showing and Adding WishLists

settings.php summarises the current wishlists of the logged-in user, and allows them to add a new list. To do this, it starts by checking if the "Add Wishlist" button has been pressed - if it has, then "if (isset($_POST['newlist']))" evaluates to true, and the new list is inserted into the database. At the bottom of the page, a simple form is displayed which allows them to enter the name for a new list, and create it by pressing that "Add Wishlist" button.

In the middle of the file, the database is queried, and the name and number of items are displayed for each of the user's lists in a table.

New File: settings.php

<?php session_start();
/* Read in the generic functions and display header */
virtual("/functions.php");
head();
$loginid=$_SESSION['userid'];

// Connect to the database
dbcon();
if (isset($_POST['newlist'])) {
$query="insert into wishlist (name, user_id) values ('" . mysql_real_escape_string($_POST['name']) . "', '" . $loginid . "');";
$result=mysql_query($query);
if ($result) echo "<p>Wishlist added.</p>";
else echo "<p>An error occurred.</p>";
}

// Show the user their current list(s)
echo "<h1>Your Wishlists</h1>";
$query="select * from wishlist where user_id='" . $loginid . "';";
$result=mysql_query($query);
if (mysql_num_rows($result)==0) {
echo "<p>You do not yet have any current wishlists defined.</p>";
} else {
echo "n<table>n";
while ($row=mysql_fetch_assoc($result)) {
$listname=$row['name'];
$listid=$row['id'];
$itemquery="select id from item where wishlist_id='" . $listid . "';";
$itemresult=mysql_query($itemquery);
$count=mysql_num_rows($itemresult);
echo " <tr><td><a href='/wishlist.php?list=" . $listid . "'>" . $listname . "</a></td><td>" . $count . " item(s)</td></tr>n";
}
echo "</table>n";
}
echo "<form action='/settings.php' method='post'>";
echo "Name of List: <input type=text name=name>";
echo "<input type=submit name=newlist value='Add Wishlist'>";
echo "</form>";

/* Close off tidily */
foot();
?>

Here, John has one list containing two items. The title of the list ("Test WishList") is a link to /wishlist.php?list=1, which displays the list of items in that wishlist.

When he enters the name of a new list ("New List"), that is created when he presses the "Add Wishlist" button. It is immediately shown on the refreshed page, since the database update is done earlier in settings.php than the listing of the user's wishlists.

Adding an Item to a WishList

Adding a new item to a WishList should be pretty simple; we've already got wishlist.php to display a wishlist, and we can determine who (if anybody) the user is logged in as, so if you're looking at one of your own lists, a simple form at the bottom of the page will allow you to add a new item to the list.

To determine if the current user is the owner of a list, a new function in functions.php is needed. wishlistowner() returns the ID of the owner of a given list. This can be compared with the ID of the currently logged-in user to see if they own the list that they are viewing.

function wishlistowner($listid)
{
$query="select user_id from wishlist where id='" . $listid . "';";
$result=mysql_query($query);
$row=mysql_fetch_assoc($result);
return $row['user_id'];
}

wishlist.php can then be modified to check this attribute, and modify its display accordingly:

// Are we the owner?
$owner=wishlistowner($listid);
$mylist=($loginid==$owner ? 1:0);

Then after displaying the contents of the list, this conditional code can be added to wishlist.php:

if ($mylist==1) {
// If this is our list, we can add items to it.
echo "<h2>Add New Item To List</h2>";
echo "<form action=" . $_SERVER['REQUEST_URI'] . " method='post' enctype='multipart/form-data'>";
echo "<input type=hidden name=listid value='" . $listid . "'>";
echo "<input type='hidden' name='MAX_FILE_SIZE' value='30000' />";
echo "<table>";
echo "n <tr><td>Name:</td><td><input type=text name=title></td></tr>";
echo "n <tr><td>URL:</td><td><input type=text name=url></td></tr>";
echo "n <tr><td>Price:</td><td><input type=text name=price></td></tr>";
 echo "n <tr><td>Image:</td><td><input type=file name=image></td></tr>";
echo "</table>n<input type=submit name=additem value='Add Item'>";
echo "</form>";
}

Notice that to upload an image, "<input type=file>" is used. The contents of the file will be uploaded from the user's desktop PC to the web server as part of the form data. PHP has a special variable called $_FILES[] which makes it easy to deal with files uploaded as part of form submission.

At the top of wishlist.php, some new code to handle the new item needs to be added. I have left two commented-out pre-formatted lines in there, which show the contents of the FILES and POST arrays; this is very helpful for debugging. Particularly with more complex things like this, it can be useful to see what the server has actually received. It can also be useful to watch the Apache error log; on RedHat, this is at /var/log/httpd/error_log by default.

if (isset($_POST['additem'])) {
$owner=wishlistowner($_POST['listid']);
if ($owner != $loginid) die("This is not your list.");
$title=mysql_real_escape_string($_POST['title']);
$url=mysql_real_escape_string($_POST['url']);
$price=mysql_real_escape_string($_POST['price']);
// echo "<pre>F:"; print_r($_FILES); echo "</pre>";
// echo "<pre>P:"; print_r($_POST); echo "</pre>";
$imagefile = fopen($_FILES['image']['tmp_name'],"rb");
// Hide errors from fread in case there is nothing to read (invalid filename entered).
$image = addslashes(@fread($imagefile,filesize($_FILES['image']['tmp_name'])));
$imagetype=$_FILES['image']['type'];
$query="insert into item(title, url, price, image, imagetype, wishlist_id) ";
$query .= "values ('" . $title . "', '" . $url . "', '" . $price;
$query .= "', '" . $image . "', '" . $imagetype . "', '" . $_POST['listid'] . "');";
$result=mysql_query($query);
if ($result)
echo "<h1>New item added to database</h1>";
else {
echo "<h1>Failed to add new item to database</h1>";
echo "<pre>" . mysql_error() . "</pre>";
}
}

John is a man of simple needs - he likes Linux distributions and the occasional plush toy. Every now and then, he decides that what his life is really missing is a new sports car, so he adds a new item to his list, and chooses an image (ferrari.jpg) on his local PC. This is then sent to the web server along with the rest of the data. The MAX_FILE_SIZE form element tells the browser the largest file that the server will accept; this is of course no guarantee that the browser won't allow John to send a larger file anyway, but it should save the user from spending ages uploading a huge file only to be told that it's not acceptable.

When John presses the "Add Item" button, the page is reloaded, and the new item is added to the list.

To edit an item, we need to do something similar to the code which added a new WishList. In the showitem() function, we just work out whether the item is in the user's wishlist, and if it is, then we add a form to the HTML page allowing them to modify the values. The updates can go to item.php which will be modified to process the new information and update the database accordingly. The showitem function can then be called again to show the user their new changes to the item.

$loginid=$_SESSION['userid'];
// Are we the owner?
$owner=wishlistowner($itemrow['wishlist_id']);
if ($loginid == $owner) {
echo "<h3>Update Item</h3>
echo "<form action=" . $_SERVER['REQUEST_URI'] . " method='post' enctype='multipart/form-data'>
echo "<input type=hidden name=itemid value='" . $itemrow['id'] . "'>
echo "<input type='hidden' name='MAX_FILE_SIZE' value='30000' />
echo "<table>
echo "n <td>Name:</td><td><input type=text name=title value='" . $itemrow['title'] . "'></td></tr>
echo "n <tr><td>URL:</td><td><input type=text name=url value='" . $itemrow['url'] . "'></td></tr>
echo "n <tr><td>Price:</td><td><input type=text name=price value='" . $itemrow['price'] . "'></td></tr>
echo "n <tr><td>Image:</td><td><input type=file name=image></td></tr>
echo "</table>n<input type=submit name=updateitem value='Update Item'>
echo "</form>
}

Then a change to item.php is required to interpret the updated details. The implementation of this is almost identical to adding an item to the wishlist, except that the SQL query uses the keyword "UPDATE" rather than "INSERT". Also, although the user could edit the text easily, the image is treated slightly differently. If a new image is uploaded, then it is added to the update command to be passed to the database. Otherwise, the image and imagetype fields are ignored, so that they remain unchanged in the database.

if (isset($_POST['updateitem'])) {
// User has submitted a change
$loginid=$_SESSION['userid'];
$itemid=filter_var($_POST['itemid'], FILTER_SANITIZE_NUMBER_INT);
// Do some sanity and security checks...
$query="select wishlist_id from item where id='" . $itemid . "';";
$result=mysql_query($query);
$ownerresult=mysql_fetch_assoc($result);
// We can now find the real owner of the item being edited.
// A naughty user could have spoofed the itemid, but we will only
// update it if it really is an item which belongs to the logged-in user.
$realowner=wishlistowner($ownerresult['wishlist_id']);
if ($loginid == $realowner) {
// Okay, we can update the item.
$title=mysql_real_escape_string($_POST['title']);
$url=mysql_real_escape_string($_POST['url']);
$price=mysql_real_escape_string($_POST['price']);

$query="update item set title='" . $title . "', url='" . $url . "', price='" . $price . "'";
// If they've uploaded a new image too, we'll change the image in the database. Otherwise, leave it.
$imagefile = fopen($_FILES['image']['tmp_name'],"rb");
// Hide errors from fread in case there is nothing to read (invalid filename entered).
$image = addslashes(@fread($imagefile,filesize($_FILES['image']['tmp_name'])));
$imagetype=$_FILES['image']['type'];

if ($image!=NULL)
$query .= ", image="" . $image . "", imagetype='" . $imagetype . "'";
$query .= " where id='" . $itemid . "';";
$update=mysql_query($query);
if ($update!=TRUE) {
cleandie("Failed to update record.");
}
} else {
cleandie("Something went wrong. That does not appear to be your item.");
}
}

In the screenshot below, John changes the price from $25 to $250, and the picture of a stuffed GNU with the FSF's standard GNU image:

After updating the details, the page is reloaded, the database is updated, and the new status of the item is shown.

Conclusion

There are many more things that this application could do - friends Reserving items and marking them as Bought, Searching the database for users and their items, notifying subscribed users of changes, and many more. Hopefully though, this has given you a taste of what can be done by PHP and MySQL together, and how such things can be achieved.

We have seen and used the mysql command-line tool and phpMyAdmin for manipulating the database structure and contents. We have used PHP commands to query and update the database. We have seen how the PHP code itself is given a lower level of access (wishuser) than the full DBA access to restrict the application from inadvertently making more changes to the database than it needs to do.

We have used some basic cryptography and security measures, and considered how some apparently sufficient ways of obscuring sensitive data are vulnerable to attack, along with ways of mitigating that threat. Security is something that needs to be designed into a system from the start; when it's bolted on afterwards it tends to not quite fit snugly, and the resulting gaps can allow attackers to break the whole system apart.

We have also looked at database structure, how indexes are used, and how One-to-Many and Many-to-Many relationships work. This won't make you an expert DBA or architect, but it's enough to build a simple application, and hopefully enough to enable you to have a useful conversation with somebody who is an expert.

It is my hope that this book will be a useful guide to a relatively wide range of topics, from the initial installation and configuration of the software stack, down to designing and implementing an application along with its accompanying database system. Whilst the expectation is not that this will make you an expert in any of these topics, it should give you a foothold to get more deeply into any or all of them as you see fit. For the other parts, even if you have no interest in database design, or operating system installation, or any of the other topics, a general understanding of what is involved and the kinds of things to be considered is a useful thing to have when dealing with people who do specialize in those areas.

By The Same Author

Steve Parker is also the author of the Shell Scripting Tutorial, and the Wiley book Shell Scripting - Expert Recipes for Linux, Bash and more.