Database-Driven Web Application with PHP and MySQL

This tutorial will walk you through the creation of a simple web application in php, that accesses a mysql database. The tutorial focuses on simple ways to implement the application; keep in mind that simplest is not always best, but in this tutorial we focus on simple.

Start by reading the first three sections of this tutorial (up to "Collecting data via HTML forms") to understand the components of a dynamic web application and some basics of the PHP language. Once you are finished, proceed to the application below.

The Application

To get the most out of the tutorial, don't just read it. Execute the programs, use the 'View Page Source' option on your browser to see the html that the php pages are generating, and make changes to the programs to make sure you understand them.

So, what is the application ? We will create a database of users, each of which may have many emails. The SQL code emails_db.sql generates two tables, with the following structure:

Table User_T
usernamepasswdfullNameisAdmin
mdamiandea2ee600c47119051e11a60abccffb1a39bdd2aMirela Damian true
jcullenda2974cdbe5374d8fecee7c5a9ae59e0925c492aJohn Cullen false

Table UserEmail_T
usernameemail
mdamianmirela.damian@villanova.edu
mdamianmdamian@gmail.com
jcullenjc@google.com

Note that passwords are not stored in clear text. Instead, a 40-byte long hash, generated with the SHA (Secure Hash Algorithm) one-way hash function, is stored. Another cryptographic hash method supported by SQL is MD5 (message digest), which produces a 32-byte long hash from the password.

Follow these steps to create the database shown above and run SQL queries using XAMPP. Try each of the following SQL commands in turn, and observe the results:

    SELECT SHA('secretpassword');
    SELECT CHARACTER_LENGTH(SHA('secretpassword'));
    SELECT MD5('secretpassword');
    SELECT CHARACTER_LENGTH(MD5('secretpassword'));

Our application will let us display all users, display the emails for each user, and add users and emails.

Basic PHP Code

Most of our php pages will have the same structure. After connecting to the database, we normally do:
  1. Get a request from the user through an HTML form
  2. Get information from the request (using $_REQUEST)
  3. Process the request (use pg_query)
  4. Produce some output, depending on the request

Basic Webpage Structure

It is usually a good idea to create a template for our web pages; this way, we can just 'Save As' to ensure consistency and save our work. Let us create a template file called empty.php. Because this is a database application, the first thing to do is to include code for connecting to the database. We could just call mysql_connect, but if we ever need to change the password (or the database or user, for that matter) then we would need to change ALL of the php pages, so we better place the code to connect in one file and include it in all the others. We can use the include function (actually, include_once or require_once would probably be safer). So we create a file called connect.php that contains the following code:

<?php
	$username="mdamian";
	$password="abc123";
	$database="mdamian";
	$link = mysql_connect('localhost',$username,$password);
	if (!$link) {
		die('Could not connect to MySQL: ' . mysql_error()); 
	} 
	print 'Connection OK';
	if(!mysql_select_db($database)) 
  		die( "Unable to select database");
?>
Download connect.php. Test it by typing the url localhost/connect.php (or csdb.csc.villanova.edu/˜yourusername/connect.php if working on csdb) in your browser.

And then our php template (called empty.php) would look like this:

<?php   include_once ('connect.php');?>
<html>
<head>
	<title> Php Template </title>
</head>
<body>
<?php
	/* Database processing code here */
?>
</body>
</html>
Download empty.php

Listing Users

We can start adding php code to list all users in the database. The beginning is the same as in our template; we include connect.php, and place all the html tags. In the php code, we start by defining a function; the function is called DisplayTable and takes as argument the handle returned by mysql_query. As with most languages, this is just the definition of the function, and nothing is done until the function is called.

   <?php   include_once ('connect.php');?>
   <html>
   <head>
  	<title> User Listing </title>
   </head>

   <body>
   <h1>List of Users</h1>
   <?php
   function DisplayTable($result)
   {
	$fields_num = mysql_num_fields($result);
	print "<table><tr>\n";
	/* printing table headers */
  	for($i=0; $i<$fields_num; $i++) {
  		$field = mysql_fetch_field($result);
  		print "<th>{$field->name}</th>";
	}
  	print "</tr>\n";
	/* printing table rows */
  	while($row = mysql_fetch_row($result)) {
  		print "<tr>";
    		/* $row is array... foreach( .. ) puts every element of $row into $cell variable */     
  		foreach($row as $cell)
  			print "<td>$cell</td>";
		/*Alternate code to foreach: for($i=0; $i<$fields_num; $i++)
						  print("$row[$i]\n"); */
 		print "</tr>\n";
	}
 	print "</table>\n";
   }

1:	$query = "SELECT * FROM User_T";
2:	$result = mysql_query($query); /* get handle to result set */
3: 	if (!$result) 
4: 	    		die("Query to show fields from table failed");
5: 	DisplayTable($result);
6: 	mysql_free_result($result);

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

The actual execution starts in line 1. First, we define a string variable $query, containing the query we will execute. There is nothing magic about it yet; it is just a string variable. Its value is the string representation of the SQL command we want to execute, but as far as php is concerned, this is just a string.

In line 2, we pass to mysql_query the string stored in $query; this is the query sent to the database (remember we had already established the connection, in the file we include at the top). We store the returned value in the variable $result.

Note that mysql_query returns a handle (basically an integer identifying a particular resource); this is an opaque reference to the result set of the query; we pass this value to the other mysql functions (mysql_num_fields, mysql_fetch_field, etc).

In line 3, we check whether the return value from mysql_query is false (which indicates that the query failed to execute), case in which we call die to print a message and exit the current script (line 4).

In line 5, we pass the value returned by mysql_query to the DisplayTable function. This function displays the elements in the result set as a table. Finally, we free the memory associated with the identifier $result (line 6). Now would be a good time to execute the userlist.php program, and make sure you understand the .php code and the html that is being generated (use View Page Source from the browser menu to see the latter).

Notes. Make sure to place your .php file in ~/html if on csdb, in C:\xampp\htdocs if using Xampp on Windows, and in Applications/XAMPP/htdocs if using Xampp on Mac. Also make sure you open the form by pointing your browser to http://csdb.csc.villanova.edu/~yourusername/userform.html if on csdb, and to http://localhost/userform.html if on your local machine, and NOT by double-clicking on the name of the file.

Making it Prettier with CSS

We can use Cascading Style Sheets (CSS) to improve the look of our html tables by adding borders, changing background colors, etc. All browsers support CSS today. We will use a very simple CSS stylesheet:

1: h1 {text-align: center;}
   table {
2:	border-width: 2px;
3:	border-style: outset; 
   }
   table th {
4: 	border-width: 1px;
5:	border-style: inset;
6:	background-color: #d0e6ff; 
   }
   table td {
7: 	border-width: 1px;
8:	border-style: inset; 
   }
mystyle.css

The first line says that anything inside an h1 tag should be centered (we use h1 for the title at the top). Lines 2 and 3 say that tables should have a solid 3D outset border around them, 2 pixels wide. Line 4 sets the width of the header cell borders, and line 7 sets the width of the data cell borders. The border-width property does not work if it is used alone; we need to specify what kid of border to display by setting the border-style property (lines 5 and 8). Finally, line 6 specifies a bluish background color for the header elements. In case you wish to dig further into CSS tables, w3schools provides a good reference.

All we need to do now is link this stylesheet to our HTML file, using the tag. The file userlistcss.php below does just that.

   <?php   include_once ('connect.php');?>
   <html>
   <head>
  	<title> User Listing </title>
	<link rel="stylesheet" href="mystyle.css">
   </head>
   /* The rest is same as before */
Download userlistcss.php. Test it by typing the url localhost/userlistcss.php (or csdb.csc.villanova.edu/˜yourusername/userlistcss.php if working on csdb) in your browser.

Displaying a Particular User

The next step is to display a particular user with all its emails. This entails two queries (one to get the info from the User_T table, and another from the UserEmail_T table). Now this page needs to know who to display the info about, so we need to pass it the username of the desired user.

The simplest way to pass info to a php page is to use an html form; we just need to make sure to point to the right php page in the action parameter, and create fields with the right name. So, a very simple form would look like this:

<html>
<head>
	<title> User Info Form </title>
	<link rel="stylesheet" href="mystyle.css">
</head>
<body>
<form action="usercss.php" method="post">
	<p>Username: <input type="text" name="username"></p<>
	<input type="submit" value="Get Info">
</form>
</body>
</html>
userform.html

When a user fills in the form above and clicks on the "Get Info" (submit) button, the form data is sent to the PHP file usercss.php, which looks like the one below.

   <?php   include_once ('connect.php');?>
   <html>
   <head>
  	<title> User Info </title>
   </head>

   <body>
   <?php
   function DisplayTable($result)
   {
	$fields_num = mysql_num_fields($result);
	print "<table><tr>\n";
	/* printing table headers */
  	for($i=0; $i<$fields_num; $i++) {
  		$field = mysql_fetch_field($result);
  		print "<th>{$field->name}</th>";
	}
  	print "</tr>\n";
	/* printing table rows */
  	while($row = mysql_fetch_row($result)) {
  		print "<tr>";
    		/* $row is array... foreach( .. ) puts every element of $row into $cell variable */     
  		foreach($row as $cell)
  			print "<td>$cell</td>";
 		print "</tr>\n";
	}
 	print "</table>\n";
   }
   
1:	$username = $_REQUEST['username'];
2:	$q1 = "SELECT fullName FROM User_T WHERE username = '$username'";
3:	$r1 = mysql_query($q1); 
4: 	if (!$r1) 
5: 		die("Query to retrieve user failed");
6:	$row = mysql_fetch_row($r1);
7:	print "<h1>$row[0]</h1>";
8: 	$q2 = "SELECT email FROM UserEmail_T WHERE username = '$username'";
9:	$r2 = mysql_query($q2); 
10: 	if (!$r2) 
11: 		die("Query to retrieve user email(s) failed");
12:	DisplayTable($r2);
   	mysql_free_result($r1);
   	mysql_free_result($r2);
   ?>
   </body>
   </html>
Download usercss.php. Test it by typing the url localhost/userform.html (or csdb.csc.villanova.edu/˜yourusername/userform.html if working on csdb) in your browser.

Note the use of the $_REQUEST variable to retrieve information from the form in line 1: we get the username from the form (in the expression $_REQUEST['username'], username is the name of the field on the form), and store it in the variable $username. In line 2, we use this variable value to create a SQL query that will get, from the User_T table, the name corresponding to that username. Line 3 sends that query to the database. Line 6 fetches the resulting row (note that exactly one row should be returned) and line 7 prints the unique column in the row, corresponding to fullName. Line 8 creates another SQL query, this time to get all the emails from the UserEmail_T table that correspond to the username. Line 9 executes that query, and line 12 calls the DisplayTable function to display the email(s) in a table.

This is another good place to try using the userform.html form, inspect the resulting html using "View Page Source", and understand the details of the code that generates it. Again, make sure you open the form by pointing your browser to http://localhost/userform.html (or whatever subdirectory you placed the form in), and NOT by double-clicking on the name of the file.

Security and Validation

Now this page works, but only if it gets the right data; it is not checking whether the user actually exists etc; in fact, we are not even checking whether we are getting any data from the form !

Also, since the page uses data from the form directly into the query, a malicious user may enter invalid data that closes the ', adds a semicolon, and then another SQL command that changes your database, or reveals more info than it should (as a challenge, you may try to actually create such a string and type it into the form). In Security parlance, this is called a SQL injection attack. An easy way to avoid most of such attacks is to remove the ' character from the string. PHP has a function called addslashes that does just that (actually, it replaces the ' character with \', which guarantees that it enters your database as data instead of being a metacharacter).

So a better implementation would look like this:

   <?php   include_once ('connect.php');?>
   <html>
   <head>
  	<title> User Info </title>
   </head>

   <body>
   <?php
   function DisplayTable($result)
   {
	$fields_num = mysql_num_fields($result);
	print "<table><tr>\n";
	/* printing table headers */
  	for($i=0; $i<$fields_num; $i++) {
  		$field = mysql_fetch_field($result);
  		print "<th>{$field->name}</th>";
	}
  	print "</tr>\n";
	/* printing table rows */
  	while($row = mysql_fetch_row($result)) {
  		print "<tr>";
    		/* $row is array... foreach( .. ) puts every element of $row into $cell variable */     
  		foreach($row as $cell)
  			print "<td>$cell</td>";
 		print "</tr>\n";
	}
 	print "</table>\n";
   }
   
1: if(!empty($_REQUEST['username'])) {
2:	$username=addslashes($_REQUEST['username']);
3:	$q1 = "SELECT fullName FROM User_T WHERE username='$username'";
4:	$r1 = mysql_query($q1);  
5:	if (mysql_num_rows($r1)==1) { /* user exists */ 
6:		$row = mysql_fetch_row($r1);
7:		print "<h1>$row[0]</h1>";
8:		$q2 = "SELECT email FROM UserEmail_T WHERE username='$username'";
9:		$r2 = mysql_query($q2); 
10:		if (!$r2) 
11:			die("Query to retrieve user email(s) failed");
12:		DisplayTable($r2);
13:		mysql_free_result($r2);    
	} else 
14:		print "Sorry user does not exist"; 
15:	mysql_free_result($r1);    
    } else 
16:	print("Please provide a username."); 
   
  ?>
  </body>
  </html>
Download usersecurecss.php

Here in line 1 we use the empty function to check whether the form field username contained any input; this function returns true if a variable is undefined, empty, false, null, or 0, and is commonly used when dealing with HTML forms. We then use addslashes in line 2, to sanitize the data and avoid the SQL injection attack. In line 5, we check the number of rows returned by our first query; if there is no user with that username, then 0 rows are returned.

Sending Data Inside a Link

Now, our userlistcss.php page displays all users; it would be nice if we could add a link there to display the emails for each particular user. When using the form above, the variable name and value gets added to the url, which looks like "http://localhost/usercss.php?username=mdamian"; the variable username with value mdamian is being sent to usercss.php. So we can use php to create a link of that form, and display the emails for the appropriate user.

Below is userlistcss2.php, that does just that.

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

  <body>
   <h1>List of Users</h1>
   <?php
   function DisplayTableLink($result)
   {
	$fields_num = mysql_num_fields($result);
	print "<table><tr>\n";
	/* printing table headers */
  	for($i=0; $i<$fields_num; $i++) {
  		$field = mysql_fetch_field($result);
  		print "<th>{$field->name}</th>";
	}
	print "<th>Actions</th>"; /* extra column for email link */
  	print "</tr>\n";
	/* printing table rows */
  	while($row = mysql_fetch_row($result)) {
  		print "<tr>";
    		/* $row is array... foreach( .. ) puts every element of $row into $cell variable */     
  		foreach($row as $cell)
  			print "<td>$cell</td>"; 
		print"<td><a href=\"usercss.php?username=$row[0]\">View emails</a></td>";
 		print "</tr>\n";
	}
 	print "</table>\n";
   }
1:	$query = "SELECT username, fullName FROM User_T"; 
2:	$result = mysql_query($query); /* get handle to result set */
3: 	if (!$result) 
4: 	    	die("Query to show fields from table failed");
5: 	DisplayTableLink($result);
6: 	mysql_free_result($result);

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