Database-Driven Web Application with PHP and MySQL (contd.)

This is part 2 of the tutorial on creating a simple dynamic web application. In the first part of this tutorial we created a database of users and wrote PHP code to display all users and their emails.

Adding Users

Another obvious piece of functionality would be the ability to add data to the database, or modify existing data. The basic structure is the same: (a) create an html form that allows to send all the necessary request data, and (b) create a php page that uses the request data to create an SQL statement (in this case an INSERT, DELETE or UPDATE instead of a SELECT statement) and uses mysql_query to have the DBMS execute that statement.

As an example, we create a form, adduserform.html that has the data for the User_T table:

<html>
<head>
     <title> Add User </title>    
     <link rel="stylesheet" href="mystyle.css">
</head>
<body>

<form action="adduser.php" method="post">
	<p>UserName: 	<input type="text" name="username"></p>
	<p>Password: 	<input type="password" name="password"></p>
	<p>Name: 	<input type="text" name="fullname"></p>
	<p>Admin?: 	<input type="checkbox" name="isadmin"></p>
	<input type="submit" value="Add user">
</form>

</body>
</html>
Download adduserform.html

Notice that we use an input of type checkbox at the end of the form, to display a textbox. We also use an input of type password; this is just a normal textbox, except that it displays * instead of the characters you type.

The php page is relatively simple; the only variable that gets special treatment is isadmin, which needs to be transformed. If the checkbox is not checked, the variable is not sent at all, whereas if it is checked, the string 'on' is sent as its value; so we use isset to check whether it has been sent or not, and we transform that to "true" or "false", since that is what needs to go in the SQL statement.

<?php   include_once ('connect.php');?>
<html>
<head>
     <title> Add User </title>    
     <link rel="stylesheet" href="mystyle.css">
</head>
<body>
<?php

if(isset($_REQUEST['isadmin']))
	$isAdmin='true';
else
	$isAdmin='false';
$username = addslashes($_REQUEST['username']);
$fullname = addslashes($_REQUEST['fullname']);
$password = addslashes($_REQUEST['password']);
$query = "INSERT INTO User_T(username, passwd, fullName, isAdmin) 
		 VALUES('$username', '$password', '$fullname', $isAdmin)";
$result = mysql_query($query); 
if (!$result) 
	die("Query to add user failed");
?>
</body>
</html>
Download adduser.php. Test it by typing the url localhost/adduserform.html (or csdb.csc.villanova.edu/˜yourusername/adduserform.html if working on csdb) in your browser. Enter some data in each field, then check that the user has been added to the database by reloading your userlistcss2.php in your browser.

Notice that the main difference between this page and usercss.php is in the kind of SQL statement: here we create an INSERT statement, and then send it to the DBMS.

Now, the code above doesn't do anything to validate the data. A more robust implementation would look like this:

<?php   include_once ('connect.php');?>
<html>
<head>
     <title> Add User </title>    
     <link rel="stylesheet" href="mystyle.css">
</head>
<body>
<?php

function validIncomingData()
{
	$valid=true;
	if(empty($_REQUEST['username'])) {
		print "<p>Error! Please enter a username</p>";
		$valid=false;
	}
	if(empty($_REQUEST['password'])) {
		print "<p>Error! Please enter a password</p>";
		$valid=false;
	}
	if(empty($_REQUEST['fullname'])) {
		print "<p>Error! Please enter the full name</p>";
		$valid=false;
	}
	if (!$valid) 
		print "<p><a href=\"adduserform.html\">Back to Input Form</a></p>";
	return $valid;
}

function validUser()
{
	$username = addslashes($_REQUEST['username']);
	$query="SELECT fullName
		   FROM User_T WHERE userName='$username'";
	$result=mysql_query($query);    
	if (mysql_num_rows($result)==1) {
		print "<p>Error! User <i>$username</i> already exists</p>";
		print "<p><a href=\"adduserform.html\">Back to Input Form</a></p>";
		return false;
	}
	return true;
}

function addUser()
{
	if(isset($_REQUEST['isadmin']))
		$isAdmin='true';
	else
		$isAdmin='false';
	$username = addslashes($_REQUEST['username']);
	$fullname = addslashes($_REQUEST['fullname']);
	$password = addslashes($_REQUEST['password']);
	$query = "INSERT INTO User_T(username, passwd, fullName, isAdmin) 
			 VALUES('$username', SHA('$password'), '$fullname', $isAdmin)";
	$result = mysql_query($query); 
	if($result) 
		print "<p>User <i>$username</i> successfully added</p>";
	else
		print "<p>Adding user <i>$username</i> failed</p>";
	print "<p><a href=\"adduserform.html\">Back to Input Form</a></p>";
}

if (validIncomingData() && validUser())
	addUser();
?>
</body>
</html>
Download addvaliduser.php. Change the reference from adduser.php to addvaliduser.php in your adduserform.html file, then test it by typing the url localhost/adduserform.html (or csdb.csc.villanova.edu/˜yourusername/adduserform.html if working on csdb) in your browser.

Notice that the validIncomingData function checks that all fields are coming in the request, and that they all have a value. In addition, the validUser function checks that the user doesn't already exist in the database.

A Nicer Way of Handling Input Errors

Instead of having two separate web pages, one for the input form and one for the error messages related to the input data, you can combine both into one - resulting in a web page that "sends data to itself". At first, this may seem somewhat confusing but it has many merits beyond just avoiding having two pages for a simple form submission process. Below is the same form as before (now renamed adduserform2.php, because it contains php code), enriched with input validation code that makes sure that all required fields have been entered.

<html>
<head>
     <title> Add User </title>    
     <link rel="stylesheet" href="mystyle.css">
</head>
<body>

<?php
if(isset($_REQUEST['submit'])) {
	$username = addslashes($_REQUEST['username']);
	$password = addslashes($_REQUEST['password']);
	$fullname = addslashes($_REQUEST['fullname']);
	if(!empty($username) && !empty($password) && !empty($fullname)) 
		Header("Location: addvaliduser2.php?username={$username}&fullname={$fullname}&password={$password}");
    	else { $error = true; }
} else {
        $error = false;
        $username = null;
        $password = null;
        $fullname = null;
}
?>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<?php 
	if($error && empty($_REQUEST['username']))
		print "<span style=\"color:red\"><p>Please enter a username</p></span>";
?>
	<p>UserName: 	<input type="text" name="username"  value="<?php echo $username; ?>"></p>
<?php 
	if($error && empty($_REQUEST['password'])) 
		print "<span style=\"color:red\"><p>Please enter a password</p></span>"; 
?>
	<p>Password: 	<input type="password" name="password" value="<?php echo $password; ?>"></p>
<?php 
	if($error && empty($_REQUEST['fullname'])) 
		print "<span style=\"color:red\"><p>Please enter the full name</p></span>";  
?>
	<p>Name: 	<input type="text" name="fullname" value="<?php echo $fullname; ?>"></p>
	<p>Admin?: 	<input type="checkbox" name="isadmin"></p>
	<input type="submit" value="Add user">
</form>

</body>
</html>
Download adduserform2.php

Let us deconstruct this example. First notice that, instead of naming a particular web page, the action attribute of the HTML <form> tag contains the PHP code <?php echo $_SERVER['PHP_SELF']; ?>. When evaluated, the output of this code will be adduserform2.php (the form itself). We may have just as well used the latter explicitly, but the advantage of our method is being able to rename the web page without breaking its functionality.

Let us inspect step-by-step what happens when the PHP server executes the code above. The first block of PHP (on the very top of the file) does not have any effect (and no output) because the user has not yet pressed a submit button (which results in the variable $_POST['submit'] being empty). Therefore, the whole condition evaluates to false and the statements within it are not executed. The PHP code if ($error && empty($_REQUEST['username'])) also evaluates to false, since the variable $error has not been set (default is false). That is why no error message is printed out. The same applies to the input validation for the other fields. The PHP server replaces all the PHP statements with their respective outputs, in this case nothing (with the exception of the "<form action..." attribute). Therefore, only the HTML input form is sent back to the web browser. After receiving the HTML, the web browser renders the page and presents it to the user.

Suppose that the user fills in the username, but leaves the other fields blank, and presses the submit button. The web browser sends the form data to the web server, which passes it on to the php server (more specifically to the script called adduserform2.php as specified by the <form action...> attribute. Now, the condition in the first PHP block evaluates to true (because the submit button has been pressed). Therefore the following lines of code are executed. Because no password has been entered by the user, the expression !empty($password) evaluates to false and the statement Header("Location: addvaliduser2.php") is not executed. This statement would send the user to a different page, namely addvaliduser2.php (using a so-called HTTP header redirect), if all fields would have been filled in. However, due to the else clause, the variable $error is set to the value true.

Within the HTML <input> tag the value attribute is set to <?php echo $username; ?>, which will output the text the user entered as the username (so the user won't have to re-enter it). Now, that the variable $error is true and no password had been entered by the user, the condition if ($error && empty($_REQUEST['password'])) evaluates to true, and the error message is outputted. After all the PHP code has been executed on the server, the resulting HTML is once more returned to the web browser which displays it to the user.

Finally, let's assume that the user now fills in all fields and submits the form. The cycle repeats once more. The PHP server receives the form data, and because all fields have been entered, it redirects the user to the web page addvaliduser2.php, with the input values attached as parameters. Technically, this last step is a bit (but not much) more complicated. The server actually sends a response to the web browser telling it to fetch addvaliduser2.php. The web browser sends a request for addvaliduser2.php to the web server, which processes it and sends back the HTML, which the browsder displays to the user. That's all. :)

Note that often you can only use HTTP header redirects before any HTML has been outputted. That's why the PHP redirect code appears at the very top of the file.

<?php   include_once ('connect.php');?>
<html>
<head>
     <title> Add User </title>    
     <link rel="stylesheet" href="mystyle.css">
</head>
<body>
<?php

function validUser()
{
	$username = addslashes($_REQUEST['username']);
	$query="SELECT fullName
		   FROM User_T WHERE userName='$username'";
	$result=mysql_query($query);    
	if (mysql_num_rows($result)==1) {
		print "<p>Error! User <i>$username</i> already exists</p>";
		print "<p><a href=\"adduserform.html\">Back to Input Form</a></p>";
		return false;
	}
	return true;
}

function addUser()
{
	if(isset($_REQUEST['isadmin']))
		$isAdmin='true';
	else
		$isAdmin='false';
	$username = $_REQUEST['username'];
	$fullname = $_REQUEST['fullname'];
	$password = $_REQUEST['password'];
	$query = "INSERT INTO User_T(username, passwd, fullName, isAdmin) 
			 VALUES('$username', SHA('$password'), '$fullname', $isAdmin)";
	$result = mysql_query($query); 
	if($result) 
		print "<p>User <i>$username</i> successfully added</p>";
	else
		print "<p>Adding user <i>$username</i> failed</p>";
	print "<p><a href=\"adduserform.html\">Back to Input Form</a></p>";
}

if (validUser())
	addUser();
?>
</body>
</html>
Download addvaliduser2.php. Test it by typing the url localhost/addserform2.html (or csdb.csc.villanova.edu/˜yourusername/adduserform2 if working on csdb) in your browser.

The validation method coded above is depicted in the right column of the table below (compare it with the first validation method, depicted in the first two columns).

UserName:

Password:

Name:

Admin?:

Error! Please enter a username

Error! Please enter a password

Error! Please enter the full name

Back to Input Form

Please enter a username

UserName:

Please enter a password

Password:

Please enter the fullname

Name:

Admin?:

addvaliduser.php addvaliduser2.php

Deleting and Updating Users

The userlistcss2.php page (see the first part of the tutorial) displays all users, with links to their emails:

User List
usernamefullNameActions
jcullenJohn CullenView emails
mdamianMirela DamianView emails

To Do:

  1. Add another link Delete to each row in the table whose action is to delete the corresponding user (and associated emails) from the database.
  2. Add another link Edit to each row in the table whose action is to enable the user to modify the data in that row.