COM271, Week 12

Server-side Programming

Syllabus | Table of Pages | Assignments | References and Useful Links

Server-side Programming with PHP and Databases with MySQL

Using PHP: PHP is a server technology. It parses the text of a page, looking for parts of the page written in PHP language that is embedded in a web page. The page is given a ".php" file extension (instead of, say, "html"), which is enough to cause the server to look for the presense of php code within the page. The server must have PHP installed. You can download a copy of the php engine for free; PHP is an "open source" engine, available at http://www.php.net/: the PHP website also contains manuals, function references, and myriad examples of use of PHP, all contributed by users. Or, you can upload your php page to a php-supporting server, such as the course server, and run the page by calling it with a browser.

In static web sites, such as we have been building all semester, a client browser requests a page from a server. The server send the page, and that is all. In dynamic web sites, using PHP or databases, there are some extra steps taken by the server. The process looks like this:

Client originates request to the web server for page that includes dynamic content.
The web server sees that page includes PHP content and invokes the PHP engine to parse.
If there is any code calling for a connection to a database, the PHP engine detects command to connect to database and a command to execute a query (written as a SQL—Structured Query Language— string) and invokes MySQL server.
MySQL database server returns requested information from the database.
PHP engine uses database results to create HTML output, which is written into the html page and the entire page is then returned to the client.

Client Origination

The client will originate the database request by starting some sort of search. The search may be a user entry into a text field, but more often it is a selection from a list of items that are known to be contained in the database; the list is presented as an html select element, with options often from the database itself, or we set up the form so that a user can enter text, and then we use that text (here, as a search term) for example,
...<select name="searchtype">
<option value="author">Author</option>
<option value="title">Title</option>
<option value="isbn">ISBN</option>
</select>
Search term: <input type="text" name="searchterm" />...

The client form specifies a page on the server (action = ...) to process form input. and also a method (either GET or POST) by which the form data is being sent:

PHP Server Response

The processing page first picks up the information from the client form. Because we specified that we were using the POST method to send data, and we sent information from a form select field named searchtype, we can find the value that we went by looking in an array of values, $_POST, and we can assign this to a php variable. PHP variables are like JavaScript variables in many ways, except that they all (including POST) have to begin with a dollar sign. It looks like this:
<?php
$searchtype=$_POST['searchtype'];
$searchterm=$_POST['searchterm']
?>

Notice the use of the <?php and the ?> characters. This is how we tell the server to start (and stop) looking for php strings that are included on the page. Notice also that the two names of the elements we are looking for, $_POST['searchtype'] and $_POST['searchterm'] were determined by the html name = attributes on the form.

Structured Query Language (SQL) (pronourced "S-Q-L" or "Sequel"):Usually, we use the form information to look for additional information stored in a database. The information from the form is going to be used directly to construct a SQL instruction. Here is an instruction to look into a table that lists books,
($query = "select * from books where ".$searchtype." like '%".$searchterm."%'";).

Databases and Recordsets

Databases allow us to store information in an efficient way. The information is stored in tables, each table containing rows ('records') and columns ('fields'). The tables look like regular Excel spreadsheets. They are actually stored as files is directories, with all of the information in text. Unlike spreadsheets, databases break down information into smaller parts, with the basic intention of reducing the total amount of information stored. The database engine makes this possible. For open source programmers, the standard database is called MySQL ("my S-Q-L" or "my Sequel").

Connecting to the database takes computer power, so that we don't usually stay connected for long (other users will also want to use the same databases). Instead, we connect briefly to the database, tell it what we want, and it returns information into a copy of records. We then use the copy. We call the copy a recordset.

MYSQL Server Connection and Response

The PHP script needs to connect to the database and pass it instructions about what to return. Connection is made by using a PHP connection object mysqli() and assigning it a variable name to serve as a "handle" for the connection. Here is how I would connect to a database that is stored on the same computer (localhost) as my php page:
@ $db = new mysqli('localhost', 'myID', 'myPassWord', 'myDataBaseName');
Instructions are then passed to the object as
$result = $db->query($query);
and the resulting data set is returned to the PHP script to be assigned to the variable $result.

PHP Use of Data Set to Create HTML Stream

The object $result now contains data from the database. Properties of this object include the number of records ($result->num_rows), which is used in a for loop to produce a list of book information. PHP echo commands are used to create an HTML stream that includes whatever information we want to be taken from the named fields in the record (e.g., $row['author'], etc.).

As a final step, once the dataset has been used, a good PHP script will explicitly free up memory used to contain the dataset and close the connection, as
$result->free();
$db->close();

Adding, Deleting, or Modifying Records

You add records to a table using a similar approach. A form is used to collect the information to be added (or you could read from an array, text file, another database, etc.). A connection is made, identically to what we did above, and a query string is composed, looking like INSERT INTO tablename VALUES (comma-delimited values); For example, to insert some new information into a table called books, which contained (in order) fields for ISBN, Author, Title, and Price, you could use this $query:

$query = "insert into books values ('".$isbn."', '".$author."', '".$title."', '".$price."')";

Notice the use of the word INSERT. SQL also contains similar phrases to modify (UPDATE) or drop (DELETE) records from an existing database. That's a bit beyond our current scope, however. Perhaps I will see you in COM372?