Introduction to Web Database Development in PHP
by K. Yue
1. Introduction
2. PHP
PHP Language Basics
Example:
Consider the dictionary file words.txt (save, do not open), which contains one word per line. Write a standalone PHP program to read a list of characters separated by spaces (through the command line) and print out all words containing every input character.
For examples, if the input characters are "a b c d e f"
The output may be:
There are 3 words containing all supplied characters:
1: barefaced
2: boldface
3: feedback
Another example:
Input: "a e i o u q"
Output:
There are 14 words containing all supplied characters:
1: consequential
2: equatorial
3: equinoctial
4: equitation
5: equivocal
6: equivocate
7: grandiloquent
8: inconsequential
9: liquefaction
10: quasiorder
11: quasiperiodic
12: questionnaire
13: sequestration
14: Sequoia
Solution:
A quick and dirty solution in standalone PHP program follows. Note that this is not the typical way of using PHP as PHP is usually used for Web server-side programming.
word.php.txt (saved as word.php):
To run, for example:
php word.php a b c d e f
or better
c word.php a b c d e f
Note that the program involve the PHP interpreter (word.php.txt, in this example) explicitly.
<?php
// A simple program for processing words in PHP
// Open dictionary file
$dictionary = "words.txt";
$dicth = fopen($dictionary, "r");
if ($dicth == false)
die("unable to create file");
// loop through each element in the $argv array
if (sizeof($argv) == 1)
{ die("usage, for example: c:\\xampp\\php\\php word.php ch1 ch2 ...\n");
}
// Check each array element to contain only one character each.
$i = 1; // skip $argv[0] which is the script name.
while ($i < sizeof($argv)) {
$ch = $argv[$i];
if (strlen($ch) > 1)
{ die("Arguments must be single characters.\n"); }
$chars[$i-1] = $ch;
$i++;
}
$result = "";
$resultCount = 0;
$word = "";
while (!feof($dicth)) {
$word = fgets($dicth);
// Check each word in the dictionary.
// Remove the last character if it is a blank.
$word = chop($word);
foreach ($chars as $ch) {
// For each input character, if it is not found in the word,
// skip the word by going to the next iteration of the
// External loop.
if (! preg_match("/$ch/", $word)) { continue 2; }
}
// $word contains all characters. Add it to the result.
$resultCount++;
$result .= " $resultCount: $word\n";
}
fclose($dicth);
if ($resultCount) {
echo "There are $resultCount words containing all supplied characters:\n$result\n";
}
else {
echo "There are no words containing all supplied characters.\n";
}
?>
Web Development with PHP
Example:
hello.php:
<html>
<head>
<title>Hello world.</title>
</head>
<body>
<?php echo '<p>Hello World from CSCI 5333,</p>'; ?>
</body>
</html>
Web application in PHP:
Predefined Variables
var.php: Dumping available predefined variables
<?php
var_dump(get_defined_vars());
?>
Notes:
var2.php:
<table>
<ul>
<?php
$x = "hello";
foreach (get_defined_vars() as $key => $value) {
if (is_array($value)) {
echo "<li>$key</li>\n";
echo"<ul>\n";
foreach ($value as $in_key => $in_value) {
echo "<li>$in_key: $in_value</li>\n";
}
echo"</ul>\n";
}
else {
echo "<li>$key: $value</li>\n";
}
}
?>
</ul>
</table>
Note:
phpinfo.php: list php info. Use with care.
<?php
phpinfo();
?>
Server2.php:
<table border="1">
<tr><td>Server variables</td><td>Values</td></tr>
<?php
foreach($_SERVER as $key => $value) {
echo "<tr><td>$key</td><td>$value</td></tr>\n";
}
?>
</table>
Note:
Getting input from Web pages
Examples:
get1.php:
<table border="1">
<tr><td>GET Parameter</td><td>Values</ed></tr>
<?php
foreach($_GET as $var => $value) {
echo "<tr><td>$var</td><td>$value</td></tr>\n";
}
?>
</table>
Note:
get2.php: unsafe without input validation.
<?php
echo "Dear " . $_GET['name'] . ", with ID " . $_GET['id'] . ". Welcome, your grade is " . $_GET['grade'] . ".\n";
?>
Examples:
post1.php: with some input validation.
<?php
if (! array_key_exists('name', $_POST)) {
echo <<<__FORM
<form action="post1.php" method="post">
<p>Your name: <input type="text" name="name" /></p>
<p>id: <input type="text" name="id" /></p>
<p>grade: <input type="text" name="grade" /></p>
<p><input type="submit" /></p>
</form>
__FORM;
}
else {
$name = $_REQUEST['name'];
$id = $_POST['id'];
$grade = $_POST['grade'];
echo <<<__WELCOME
Dear $name, with ID $id. Welcome, your grade is $grade.
__WELCOME;
}
?>
Note:
Example: converting word.php from a standalone program to a Web application.
Standalone program:
Web application:
word.php:
Example:
The file webword.php:
<html>
<body>
<?php
// A simple program for processing words in PHP
// Open dictionary file
$dictionary = "words.txt";
$dicth = fopen($dictionary, "r");
if($dicth == false)
die("unable to create file");
if (array_key_exists('chars', $_GET)) {
// Checking is not actually needed as the HTTP parameter is assumed to be validly valued.
$inputChars = $_GET['chars'];
}
$chars = preg_split("//",$inputChars);
$result = "";
$resultCount = 0;
$word = "";
while (!feof($dicth)) {
$word = fgets($dicth);
// Check each word in the dictionary.
// Remove the last character if it is a blank.
$word = chop($word);
foreach ($chars as $ch) {
// For each input character, if it is not found in the word,
// skip the word by going to the next iteration of the
// External loop.
if (! preg_match("/$ch/", $word)) { continue 2; }
}
// $word contains all characters. Add it to the result.
$resultCount++;
$result .= " <li>$word</li>\n";
}
fclose($dicth);
if ($resultCount) {
echo "There are $resultCount words containing all supplied characters:\n<ol>\n$result</ol>\n";
}
else {
echo "There are no words containing all supplied characters.\n";
}
?>
</body>
</html>
3. PHP Database Programming
4. MySQLi
Using classes
Example:
supply1.php: listing the supplier table.
<?php
include('supplydb.php');
$query = <<<__QUERY
select snum, sname, scity, status
from supplier;
__QUERY;
if ($result = $mysqli->query($query)) {
echo "<table border='1'>\n";
echo "<tr><td>SNum</td><td>Supplier Name</td><td>City</ed><td>Status</td></tr>\n";
/* Fetch results */
while ( $row = $result->fetch_assoc() ){
echo "<tr><td> " . $row['snum'] . "</td><td>"
. $row['sname'] . "</td><td>"
. $row['scity'] . "</td><td>"
. $row['status'] . "</td></tr>\n";
}
echo "</table>\n";
/* Destroy the result set and free the memory used for it */
$result->close();
}
$mysqli->close();
?>
Note:
supplydb.php:
You may modify to update other parameters, such as adding port.
<?php
if ($init = parse_ini_file('supplydb.ini')) {
// You may need to use parse_ini_file('supplydb.ini', false, INI_SCANNER_RAW)
// if your password contains special characters.
$mysqli = new mysqli(
$init['host'],
$init['username'],
$init['password'],
$init['database']);
if (mysqli_connect_errno()) {
printf("Can't open MySQL connection. Error code: %s\n", mysqli_connect_error());
exit;
}
}
else {
echo "Database configuration file error. Sorry.";
}
?>
Note:
supplydb.ini:
; MySQL connection string
[mysql]
host = localhost
username = yue
password = nottellingyou
database = supply
Using Prepared Statements
Example:
supply2.php?city=Houston&status=4:
<?php include('supplydb.php') ?>
<?php
/* Primitive program: Get HTTP parameters */
if (!array_key_exists('city', $_GET)) {
echo "Please specify the city name with the URL parameter city.";
exit;
}
$icity = $_GET['city'];
if (!array_key_exists('status', $_GET)) {
echo "Please specify the minimum status with the URL parameter status.";
exit;
}
$istatus = $_GET['status'];
$query =<<<__QUERY
select snum, sname, scity, status
from supplier
where scity = ? and status >= ?
__QUERY;
if ($stmt = $mysqli->prepare($query)) {
$stmt->bind_param('si', $icity, $istatus);
/* execute prepared statement */
$stmt->execute();
$stmt->bind_result($snum, $sname, $city, $status);
$stmt->store_result();
if ($stmt->num_rows > 0) {
echo"<p>Result: Suppliers in $icity with status > $istatus:</p>\n";
echo "<table border='1'>\n";
echo "<tr><td>Supplier Num</td><td>Supplier Name</td><td>City</td><td>Status</td></tr>\n";
/* fetch values */
while ($stmt->fetch()) {
echo "<tr><td> " . $snum . "</td><td>" . $sname . "</td><td>" . $city .
"</td><td>" . $status . "</td></tr>\n";
}
echo "</table>\n";
/* Destroy the result set and free the memory used for it */
$stmt->free_result();
}
else {
echo "No result for $icity with status > $istatus.\n";
}
}
$mysqli->close();
?>
Notes:
Some advantages of prepared statements:
Some disadvantages of prepared statements:
Conclusions:
Full example (from a previous examination question):
Write a PHP program, t2.php, to accept a HTTP GET parameter cid: the film category id. It displays all actors appearing in more than four films in the category in a table. For example, for
actor1.php?cid=1
it should display the following exactly in the browser.
This is because actor NATALIE HOPKINS appears in 6 films in category #1, and so on.
Your program may assume that the HTTP parameter is always correct and there is no need to check for errors in the input. Also, it is fine to display an empty page if no result is found.
Solution:
actor1.php?cid=1:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Simple Actor Query</title>
</head>
<body>
<?php
// Minimally documented.
include('sakiladb.php');
// Get category id using the HTTP GET method.
if (array_key_exists('cid', $_GET)) {
$cid = $_GET['cid'];
}
// Get category name, film counts and actor counts
$query = <<<__QUERY
select concat(a.first_name, ' ', a.last_name) as name,
count(distinct fa.film_id) as count
from film_actor fa, film_category fc, actor a
where a.actor_id = fa.actor_id
and fa.film_id = fc.film_id
and fc.category_id = ?
group by name
having count > 4
order by count desc;
__QUERY;
if ($stmt = $mysqli->prepare($query)) {
$stmt->bind_param('i', $cid);
$stmt->execute();
$stmt->bind_result($name, $count);
$stmt->store_result();
if ($stmt->num_rows > 0) {
echo "<p>Actors appearing in category id #$cid:</p>\n";
echo "<table border=\"1\"><tr><td>Actor</td><td>Number of films</td></tr>\n";
while ($stmt->fetch()) {
echo "<tr><td>$name</td><td>$count</td></tr>\n";
}
echo "</table>";
$stmt->free_result();
}
}
$mysqli->close();
?>
</body>
</html>
Steps by Steps:
[1] Study the output requirements to design the desired output. (May use multiple test cases.)
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Simple Actor Query</title>
</head>
<body>
<p>Actors appearing in category id #1:</p>
<table border="1"><tr><td>Actor</td><td>Number of films</td></tr>
<tr><td>NATALIE HOPKINS</td><td>6</td></tr>
<tr><td>KIRSTEN AKROYD</td><td>5</td></tr>
<tr><td>JON CHASE</td><td>5</td></tr>
<tr><td>SUSAN DAVIS</td><td>5</td></tr>
<tr><td>SEAN GUINESS</td><td>5</td></tr>
<tr><td>AL GARLAND</td><td>5</td></tr>
</table></body>
</html>
[2] Find out what is static and what should be generated dynamically (in boldface below):
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Simple Actor Query</title>
</head>
<body>
<p>Actors appearing in category id #1:</p>
<table border="1"><tr><td>Actor</td><td>Number of films</td></tr>
<tr><td>NATALIE HOPKINS</td><td>6</td></tr>
<tr><td>KIRSTEN AKROYD</td><td>5</td></tr>
<tr><td>JON CHASE</td><td>5</td></tr>
<tr><td>SUSAN DAVIS</td><td>5</td></tr>
<tr><td>SEAN GUINESS</td><td>5</td></tr>
<tr><td>AL GARLAND</td><td>5</td></tr>
</table></body>
</html>
[3] Construct typical SQL statements to generate/support the dynamic content. Category 1 as an example below:
select concat(a.first_name, ' ', a.last_name) as name,
count(distinct fa.film_id) as count
from film_actor fa, film_category fc, actor a
where a.actor_id = fa.actor_id
and fa.film_id = fc.film_id
and fc.category_id = 1
group by name
having count > 4
order by count desc;
It generates:
+-----------------+-------+
| name | count |
+-----------------+-------+
| NATALIE HOPKINS | 6 |
| SEAN GUINESS | 5 |
| AL GARLAND | 5 |
| KIRSTEN AKROYD | 5 |
| JON CHASE | 5 |
| SUSAN DAVIS | 5 |
+-----------------+-------+
6 rows in set (0.01 sec)
[4] Identify the HTTP parameters and write code to get these parameters. In this case, cid:
// Get category id using the HTTP GET method.
if (array_key_exists('cid', $_GET)) {
$cid = $_GET['cid'];
}
[5] In [3], identify the part of the SQL statement that should be based on user inputs instead of fixed values. Replace them by ? used in prepared statements. Note that ? may not be used in certain clauses within a select statement, such as the limit clause.
select concat(a.first_name, ' ', a.last_name) as name,
count(distinct fa.film_id) as count
from film_actor fa, film_category fc, actor a
where a.actor_id = fa.actor_id
and fa.film_id = fc.film_id
and fc.category_id = ?
group by name
having count > 4
order by count desc;
[6] Bind the placeholders of the prepared SQL statement using computation of the HTTP parameters.
$stmt->bind_param('i', $cid);
[7] Execute the SQL statements. Use the results to write PHP code to generate the expected output of [1].
if ($stmt = $mysqli->prepare($query)) {
$stmt->bind_param('i', $cid);
$stmt->execute();
$stmt->bind_result($name, $count);
$stmt->store_result();
if ($stmt->num_rows > 0) {
echo "<p>Actors appearing in category id #$cid:</p>\n";
echo "<table border=\"1\"><tr><td>Actor</td><td>Number of films</td></tr>\n";
while ($stmt->fetch()) {
echo "<tr><td>$name</td><td>$count</td></tr>\n";
}
echo "</table>";
$stmt->free_result();
}
}
[8] Ensure proper house-keeping:
At the beginning:
include('sakiladb.php');
At the end:
$stmt->free_result();
}
}
$mysqli->close();
Full Example (Spring 2015 Homework #6):
Write a very simple toy PHP MySQL Web application, category.php, to allow the manager to show films in a category. There may be a single HTTP GET parameter catid. If no catid parameter exists in the URL query string, the page should display a list of links to the categories.
Note that each category points to the same page with the category id (catid) as a parameter. For example, click on the link for the category Action will submit to the same page:
Note that Action has an category id of 1. Every film in the category is shown in a table, with the number of copies in the inventory as well as how many total number of rentals on the film. For an example, there are 7 copies of the film "SUSPECTS QUILLS" in the inventory. Together, the film has been rented out 30 times.
Note that the table is shown in the descending order of rental counts. Here is another example when the category COMEDY is selected.
Your page should also check for the value of catId. If the catId is not in the database, a brief message and the list of categories should be shown.
You may find the following PHP function useful.
// Return the full URL of the current script, including the query string, .
function selfURL() { // Assume http only
$selfURL = "http://" . $_SERVER["SERVER_NAME"];
if ($_SERVER["SERVER_PORT"] != "80") {
$selfURL .= ":".$_SERVER["SERVER_PORT"];
}
$selfURL .= $_SERVER["REQUEST_URI"];
return $selfURL;
}
Solution:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Sakila Category Management</title>
</head>
<?php
// Prepare the Mysqli connection object $mysqli.
include('sakiladb.php');
// Return the full URL, including the query string
// of the current script.
function selfURL() { // Assume http only
$selfURL = "http://" . $_SERVER["SERVER_NAME"];
if ($_SERVER["SERVER_PORT"] != "80") {
$selfURL .= ":".$_SERVER["SERVER_PORT"];
}
$selfURL .= $_SERVER["REQUEST_URI"];
return $selfURL;
}
// Return a string to display the category links.
function categoryLinks($mysqli) {
$result = "";
$query = <<<__QUERY
select distinct category_id, name
from category
order by name;
__QUERY;
if ($stmt = $mysqli->prepare($query)) {
$stmt->execute();
$stmt->bind_result($catId, $catName);
$stmt->store_result();
$numResult = $stmt->num_rows;
}
if ($numResult == 0) {
$result .= <<<__NO_RESULT
Sorry, the databas is down. Please check later.
__NO_RESULT;
}
else {
$result .= "<h2>You may check the following categories:</h2>\n<ul>\n";
$selfURL = selfURL();
// Remove query string in the URL.
if (strrpos($selfURL, "?")) {
$selfURL = substr($selfURL, 0, strrpos($selfURL, "?"));
}
while ($stmt->fetch()) {
$result .= <<<__ONE_CATEGORY
<li><a href="$selfURL?catid=$catId">$catName</a></li>
__ONE_CATEGORY;
}
$result .= "</ul>\n";
}
$stmt->free_result();
return $result;
}
?>
<?php
// Get HTTP parameter catid.
$catId = "";
if (array_key_exists('catid', $_GET)) {
// For simplification, checking is not needed as the HTTP parameter
// is assumed to be validly valued.
$catId = $_GET['catid'];
}
?>
<body>
<h2>Simple category management module</h2>
<?php
// If there is no category id, list all categories with links.
if ($catId=="") {
echo categoryLinks($mysqli);
echo "</body>\n</html>\n";
exit;
}
// Check the existence of the category id
$query = <<<__QUERY
select distinct name
from category
where category_id = ?;
__QUERY;
if ($stmt = $mysqli->prepare($query)) {
$stmt->bind_param('i', $catId);
$stmt->execute();
$stmt->bind_result($catName);
$stmt->store_result();
$numResult = $stmt->num_rows;
}
if ($numResult == 0) {
// category id does not exist/
echo "<h3>Sorry, the supplied category id, $catId, does not exist.</h3>\n";
echo categoryLinks($mysqli);
echo "</body>\n</html>\n";
exit;
}
// Show information about the category.
echo "<h2>Information about category $catName (id $catId)</h2>\n";
$query = <<<__QUERY
select t1.title, t1.countInventory, t2.countRental
from
(select distinct f.title as title, count(*) as countInventory
from film f, film_category fc, inventory i
where f.film_id = i.film_id
and f.film_id = fc.film_id
and fc.category_id = ?
group by 1) as t1,
(select distinct f.title as title, count(*) as countRental
from film f, film_category fc, inventory i, rental r
where f.film_id = i.film_id
and f.film_id = fc.film_id
and i.inventory_id = r.inventory_id
and fc.category_id = ?
group by 1) as t2
where t1.title = t2.title
order by 3 desc;
__QUERY;
if ($stmt = $mysqli->prepare($query)) {
$stmt->bind_param('ii', $catId, $catId);
$stmt->execute();
$stmt->bind_result($film, $inventoryCount, $rentalCount);
$stmt->store_result();
$numResult = $stmt->num_rows;
}
if ($numResult == 0) {
echo "<p>There is no film in this category.</p>\n";
}
else {
// Display film in the category.
$filmInfo = <<<__HEADING
<p>
<table border="1">
<tr><th>Film</th><th>Number of copies</th><th>Number of rentals</th></tr>
__HEADING;
while ($stmt->fetch()) {
$filmInfo .= " <tr><td>$film</td><td>$inventoryCount</td><td>$rentalCount</td>\n";
}
$filmInfo .= "</table></p>\n";
$stmt->free_result();
echo $filmInfo;
}
?>
</body>
</html>