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.
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 change 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
| username | passwd | fullName | isAdmin |
|---|---|---|---|
| mdamian | dea2ee600c47119051e11a60abccffb1a39bdd2a | Mirela Damian | true |
| jcullen | da2974cdbe5374d8fecee7c5a9ae59e0925c492a | John Cullen | false |
Table UserEmail_T
| username | |
|---|---|
| mdamian | mirela.damian@villanova.edu |
| mdamian | mdamian@gmail.com |
| jcullen | jc@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. 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.
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"); ?>
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>
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>
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).
C:/wamp/www directory. You will also need to launch the file via a url (e.g.: http://localhost/userlist.php) in the browser address bar, not by double-clicking an icon or via the
File->Open menu option. In each of the two latter cases, the .php file is not being processed by the web server and thus not being routed to PHP for parsing/execution.
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;
}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 */
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>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>
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 of C:/wamp/www you placed the form in), and NOT by double-clicking on the name of the file.
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>
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.
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>
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>
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>
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>
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.
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;
?>
<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>
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>
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).
Error! Please enter a username Error! Please enter a password Error! Please enter the full name Back to Input Form |
addvaliduser.php addvaliduser2.php