Anyone who has constructed a personal or business web site by writing static HTML pages can tell you that after the initial excitement wears off, they soon realised that they had created an albatross which now hangs around their neck. Maintaining static HTML pages is tedious and error-prone, and requires a level of skill above that of the typical content creator.
In addition, static HTML web sites are passe - albeit not passe and bandwidth-wasting, like so many Flash-animated sites. What people want is interactivity - the ability to query databases of account details, to place orders, to browse through product catalogues and price lists.
Both problems are solved by pulling data out of a database and using it to dynamically generate web pages. In the first case, one can use a content management system - and there are many of these to choose from - which stores the articles, documents, or other content in a database and generates pages by inserting them into template pages. However, for more specialised applications, it is sometimes necessary to custom-write the logic - which is the topic of this article.
The leading platform for creation of simple- to medium-complexity web applications is LAMP - the combination of Linux, the Apache web server, MySQL database and the PHP web scripting language.
Apache dominates the web server market, with over 60% of sites running on it, and over half of those running on Linux as the base operating system.
MySQL is a high-performance relational database. I generally characterize it as somewhat lightweight; this is not to say it is lacking in functionality, but that it lacks many features found in high-end commercial products such as Oracle and IBM's DB2. However, MySQL is very fast at reading the database, which is exactly what is needed for backing web applications where the vast majority of page hits will read the database to generate pages, and relatively few will actually update the database.
PHP is the PHP Hypertext Processor - yet another geeky recursive acronym that doesn't give much away - but it might help you to know that its ancient ancestor is a thing called Personal Home Page. PHP is a tag language - it allows the programmer to insert PHP code inside comments in an otherwise-conventional page of HTML, and the PHP run-time engine on the web server will strip out the code, execute it, and insert any output, leaving only pure HTML to be downloaded to the user's browser. PHP is extremely functional - it provides functions for file access, database access, email, XML, generating graphics and a lot more. Looking at PHP code, it looks rather reminiscent of shell scripting and Perl - programmers who have experience with those languages will have little trouble migrating to PHP.
It's important to realise that PHP code executes on the web server - what the user's web browser sees is straight HTML, although of course, the server can send down some Javascript for client-side validation, etc.
Installation and Configuration
I've been running the various components on Red Hat 9 - they are all found on the Red Hat installation CD's:
Next, decide where you are going to place your PHP scripts. For a system that hosts a single web site, it might be convenient to place the scripts in or under the web server's DocumentRoot (often /var/www/html); however, for a multi-user system, it might be more convenient to put the scripts somewhere under your personal home directory, since this will let you work as an ordinary user - always more secure. For example, in developing the scripts in this article, I actually placed the scripts in a books directory under my home directory, and edited /etc/httpd/conf/httpd.conf to add a line:
Alias /books/ "/home/les/books/" This means that any reference to http://hostname/books/something.php actually uses the file /home/les/books/something.php.
At the very least, any included files that contain user names and passwords should be located outside the web server's directory tree and included via an absolute pathname (see Security, below).
Assuming that you have all the components installed correctly, it's time to start them up. On a Red Hat/Fedora system, you can do this with the following commands:
service mysqld start chkconfig mysqld on service httpd start chkconfig httpd on On some distributions, you might have to start the daemons with a command like:
/etc/rc.d/init.d/mysqld start /etc/rc.d/init.d/httpd start or
apachectl start It's now useful to test that everything is running correctly. Use your favourite editor to create a short PHP script in the root directory of your web server (/var/www/html or wherever):
<HTML> <HEAD> <TITLE>PHP Info . . .</TITLE> </HEAD> <BODY> <?php echo phpinfo(); ?> </BODY> </HTML> Save this as test.php, then go to your web browser and browse to the URL http://localhost/test.php. You should be rewarded with a long page that lists your PHP configuration and other information.
To test your MySQL database, start the MySQL client as root, with the command
mysql For the root account, the default password is blank, so you should (!?!) connect straight away. If you previously installed MySQL and have created an account for yourself, log in as that user with the command
mysql -u username -ppassword In either case, you should see something like
[root@sleipnir html]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 34 to server version: 3.23.58 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> This indicates that you are able to connect to the running MySQL database server.
PHP Documentation
Let's now turn our attention to PHP. First, as you read through the following overview, you'll probably want more detail. You'll find it in the PHP Manual, which you should find on your system. Red Hat-ish systems install it along with the Apache documentation, and you can get to it with your browser, via the URL http://localhost/manual/mod/mod_php4/ . In this article, I'll assume that you're sitting at a single Linux system, but it all works just as well across a network with a remote server or for that matter across the Internet to a virtual or colocated server which you rent for web hosting purposes. If you are working with a remote server, just insert its name in place of localhost in the URL's, and of course, you can SSH into it to use vi to edit the files.
PHP Scripts
PHP is written as code which is embedded within the HTML page. Here's a very simple example:
<HTML> <HEAD> <TITLE>The time is . . .</TITLE> </HEAD> <BODY> <H1>The Time Now . . .</H1> <P>The time now is <?php echo date("h:i:s a"); ?> </BODY> </HTML> Try typing this into your editor and saving it as time.php in the document root directory of your web server, then browsing to http://localhost/time.php. You should see the current time, and if you click on the refresh button of your browser, the time should update.
This example file is mostly extremely simple HTML, except for the bit that starts "<?php" and ends with "?>". Between these two tags is some PHP code, which the web server strips out and executes, replacing it with its own output. Since this script consists of the one line, echo date("h:i:s a"); this is replaced by the time, formatted with hours, minutes, seconds and an AM/PM designator.
Notice that this code is executed on the web server, and not by the browser - if you were to run it on a web server in London, it should show the time in UTC or British Summer Time and not your local time.
Variables and Expressions
Just like the Perl language, which it somewhat resembles, PHP variables are identified by a "$" prefix and must start with a letter or underscore, and then continue with any combination of letters, digits and underscores. Also like Perl, they are case sensitive, and they are weakly typed - no declaration is required, and the type is deduced from the initialization. PHP supports string, integer, float (double), array, object and unknown variable types.
Here's some examples:
$favmag = "PC User"; echo "My favourite magazine is $favmag. <BR>"; # Will print "My favourite magazine is PC User PHP arrays can be treated as conventional numerically-subscripted arrays or as associative arrays (like Perl hashes) in which a text key can be used to extract the corresponding value from the array. For example:
$languages = array("PHP" => "Interpreted","C++" => "Compiled", "C" => "Compiled", "Perl" => "Incrementally Compiled"); foreach ($languages as $language => $type) { echo "$language is $type\n"; } which will print
PHP is Interpreted C++ is Compiled C is Compiled Perl is Incrementally Compiled
in your web page. You can also assign new values into arrays on the fly:
$languages["Java"] = "Bytecode interpreted";
PHP implements expressions in the same way as most other languages, with similar operator precedence (multiplication and division before addition and subtraction, for example). It also supports the ++ and -- pre- and post-increment and -decrement operators found in C, as well as combined operator-assignments like
$counter += 28; which adds 28 to $counter.
Another operator that was purloined from C is the ternary which - $first ? $second : $third - an expression which takes the value of $second or $third depending on whether $first is true or false, respectively. For example:
echo "There " . ($users > 1 ? "are " : "is ") . $users . ($users > 1 ? " users" : " user") . " online<BR>"; Flow Control
Again like Perl, PHP supports similar flow-control constructs to the C programming language. All your favourites are there: if/then/else, while, for, break, continue, switch and so on. There's also foreach (as seen above), which is more like traditional Unix scripting languages.
Here's some code which I've found useful when letting the user browse alphabetically in a database. It prints the letters of the alphabet as buttons (which implies the use of an HTML <FORM> element):
# Write out the letters of the alphabet as buttons for ($letter=65; $letter < 91 ; $letter++) { echo "<TD>"; echo "<FORM ACTION=\"selectvendor.php\" METHOD=\"GET\">"; echo "<INPUT TYPE=\"SUBMIT\" VALUE=\"" . chr($letter) . "\">"; echo "<INPUT TYPE=\"HIDDEN\" NAME=\"surname\" VALUE=\"" . chr($letter) . "\">"; echo "</FORM>"; echo "</TD>"; } Notice how, if you want to output quotes within a string which is itself quoted, you have to escape the meaning of the inner quotes by prefixing them with a backslash. This is just the same as with Javascript, and probably no surprise to web developers.
Functions
PHP allows you to define functions, which you can place in a script and then include into your pages. The syntax for function definition is very straightforward:
function area ($radius) { return 3.141.592654 * $radius * $radius; } You can now invoke the square function from later in a script
echo "The area is " . area($diameter / 2.0);
Getting Information From the Client
This is where HTML forms come in. A FORM has two major attributes - the method and the action. The method sets the Method token in the HTTP request header (see RFC 2068, HTTP 1.1, Section 9.1). Essentially, two methods are supported: GET and POST. Which of these you choose impacts upon both the usability and the security of your application.
If you use GET, then the browser passes the fields of a filled-in form in the URL it sends back to the server. This means that the user will see a URL like "http://yourhost/app/acctdetails.php?acctno=121379" in their browser's URL bar, and they're going to wonder, "I wonder what will happen if I change that to 'acctno=121378'? Will I see someone else's account?" - and of course, they will. However, using GET makes your application easier to write, since you can embed arguments into links, e.g.
echo "<A HREF=\"/app/acctdetails.php?acctno=$acctno\">Click here for account details</A>"; And because links can contain arguments, it means that they can be bookmarked by the user, making it easier for them to return to a page in your application.
Using POST is a bit more awkward, but it has the advantage of passing all CGI arguments in the HTTP request body, where they cannot be seen by the casual user. The down-side is that you can no longer embed arguments into links - rather you must use forms with hidden input elements to pass data back to the server. This also means that the user cannot bookmark personalised pages.
The ACTION attribute is the base part of the URL that will be sent back to the server when the user clicks on the form's submit button. In essence, it's the page to go to next, and it's the name of your PHP script that will process the data entered by the user. So, your HTML FORM element should look something like this:
<FORM ACTION="dosomething.php" METHOD="GET"> . . . Various INPUT, TEXTAREA and/or SELECT elements go in here </FORM> Within a form, you'll need to have various bits of text as prompts, and a combination of INPUT, TEXTAREA and/or SELECT elements for the user to interact with. Most of the common controls found on a form are produced by INPUT tags, as shown in Table 1. You select the type and on-screen appearance of the field with the "TYPE" attribute, and can specify a name and default value. However, if you want to allow the user to enter multi-line text, you'll need to use a TEXTAREA element, and if you want to produce a selection box (listbox) then you'll need the SELECT element.
The TEXTAREA element is used like this:
<TEXTAREA name="bio" ROWS="20" COLS="60"> Please enter your biographical data here. . . </TEXTAREA> which the user will see as a text box with the default text "Please enter your biographical data here. . .".
The SELECT element is a little more complex - it must include multiple OPTION elements that specify the text for the various selectable options in the list box. So a SELECT element might look like this:
<SELECT NAME="renewperiod"> <OPTION VALUE="1">1 year</OPTION> <OPTION VALUE="2">2 years</OPTION> <OPTION VALUE="3" SELECTED>3 years</OPTION> </SELECT> In this example, the list box will display "1 year", "2 years" or "3 years", but because we have specified VALUE attributes for the OPTIONs, it will send back "renewperiod=1", "renewperiod=2" or "renewperiod=3" in a GET method form.
When you design a form in HTML, you need to lay out the various entry controls. There are two ways to do this: use a table or use positioning properties of a cascaded style sheet. In this article, we'll use tables for simplicity, but the real pros would use CSS. The simplest approach is to just use a two-column table in which the first column holds the prompt for each field and the second column holds the entry fields.
So, a completed form will look something like this:
<FORM ACTION="/books/formdemo.php" METHOD="GET"> <TABLE ROWS=4 COLS=6 BORDER=2> <!-- First row: first and last names --> <TR><TD>First Name:</TD><TD COLSPAN=2><INPUT TYPE="Text" NAME="first"></TD> <TD>Last Name:</TD><TD COLSPAN=2><INPUT TYPE="Text" NAME="last"></TD></TR> <!-- Second row: just the street --> <TR><TD>Street:</TD><TD COLSPAN=5><INPUT TYPE="Text" NAME="street" SIZE="60"></TD></TR> <!-- Third row: town, state, postcode --> <TR><TD>Town:</TD><TD><INPUT TYPE="Text" NAME="town"></TD> <TD>State:</TD><TD><INPUT TYPE="Text" NAME="state"></TD> <TD>Postcode:</TD><TD><INPUT TYPE="Text" NAME="postcode"></TD></TR> <!-- Final row: five empty columns and the submit button in the sixth --> <TR><TD COLSPAN=5></TD><TD><INPUT TYPE="submit"></TD></TR> </TABLE> </FORM>
Table 1 - Attributes for use in INPUT tags within a form.
Table 2 - Attributes for use in TEXTAREA tags within a form.
When using a listbox (SELECT element), the various options are specified within the SELECT element as OPTION elements. Each option element can contain the string to be displayed, and can specify a VALUE tag which will be returned when that value is selected. Here's a simple example:
<SELECT name="currency"> <OPTION value="AUD" SELECTED>Australian Dollar</OPTION> <OPTION value="NZD">New Zealand Dollar</OPTION> <OPTION value="USD">US Dollar</OPTION> <OPTION value="GBP">Pounds Sterling</OPTION> </SELECT> If this is used within a GET method form, and the user selects "Pounds Sterling", then the returned URL will contain the string "currency=GBP" as one of its CGI arguments.
Table 3 - Attributes for use in SELECT tags within a form.
Setting Variables in Your Code
Now we come to the next part of the problem - what happens when the user clicks on the submit button? The browser will execute the ACTION specified in the form, and will send back a GET (easier for debugging) or PUT (more secure) request. The named PHP script will execute and you can process the input in some way.
Taking the example form above, we'll work with the simple example of displaying the returned data.
In traditional PHP, any CGI variables returned by the browser automatically became available under the same name in the PHP script. This is extremely convenient, and also incredibly dangerous - a malicious user can simply edit the arguments passed on the end of the URL and manipulate the values of variables that you'd rather he didn't (for example, many programmers use a boolean variable called debug to turn on the display of debugging information - all the attacker has to do is append "&debug=1" onto the end of a URL and your script will obligingly spill the beans about its internal operation).
In order to stop this from working, a new technique was used to pass variables into PHP scripts, and a configuration setting was added to disable the automatic appearance of CGI variables in PHP scripts. If your /etc/php.ini file contains the setting
register_globals = Off then CGI variables are not copied into PHP scripts. This is, in fact, the default setting in most modern installations, and this explains why examples in older PHP books and articles don't work.
Instead, the CGI variables are inserted in an associative array called either $HTTP_GET_VARS[] (for HTTP GET method) or $HTTP_PUT_VARS[] (for HTTP PUT method). You need to copy the values that you want from this variable, like this:
$first = $HTTP_GET_VARS["first"]; $last = $HTTP_GET_VARS["last"]; $street = $HTTP_GET_VARS["street"]; $town = $HTTP_GET_VARS["town"]; $state = $HTTP_GET_VARS["state"]; $postcode = $HTTP_GET_VARS["postcode"]; Notice that we're not copying anything else, so if the user does stick "&debug=1" on the end of the URL, it won't do any harm. (General rule for programmers - never trust those users!). If you are using PHP 4.1.0 or later, you can (and should) use the autoglobal arrays $_GET and $_POST instead, saving yourself some typing.
It's not uncommon, for simple applications, to use the same script file for both processing the input and displaying the form, so the next thing we need to do is to check whether anything was entered, or whether we should just display the entry form. For this example, we can assume that everybody has a surname, and so if data was entered, the last variable will be set and will be non-blank. If that's the case, we'll just format the data for display:
if (isset($last) & $last != "") { # last field is set, so display details print "<P>The data entered was: <BR>"; print $first . " " . $last . "<BR>"; print $street . "<BR>"; print $town . " " . $state . " " . $postcode; } Here's a very simple example that uses the area() function to calculate the area of a circle, which is entered via a form which appears later in the same script:
<?php include "./head.php"; ?> <?php html_header("Area Calculator"); ?> <!-- Variables are returned as $HTTP_POST_VARS["varname"] --> <?php echo ("<p>Local time is " . date("l dS of F Y h:i:s A") . "<BR>"); ?> <P> <?php function area ($radius) { return 3.141592654 * $radius * $radius; } $radius = $HTTP_GET_VARS["radius"]; if (isset($radius) & $radius != "") { echo "The area of a circle with radius $radius is " . area($radius) . "<BR>"; } ?> <FORM ACTION="/books/funcdemo.php" METHOD="GET"> <TABLE ROWS=2 COLS=2 BORDER=0> <TR><TD>Circle radius:</TD><TD><INPUT TYPE="input" NAME="radius"></TD> <TR><TD COLSPAN=2><INPUT TYPE="submit"></TD></TR> </TABLE> </FORM> <?php html_footer(); ?>
Here's another example, with a more complex form:
<?php include "./head.php"; ?> <?php html_header("Simple Form Demo"); ?> <!-- Variables are returned as $HTTP_POST_VARS["varname"] --> <?php echo ("<p>Local time is " . date("l dS of F Y h:i:s A") . "<BR>"); ?> <?php $first = $HTTP_GET_VARS["first"]; $last = $HTTP_GET_VARS["last"]; $street = $HTTP_GET_VARS["street"]; $town = $HTTP_GET_VARS["town"]; $state = $HTTP_GET_VARS["state"]; $postcode = $HTTP_GET_VARS["postcode"]; if (isset($last) & $last != "") { # last field is set, so display details print "<P>The data entered was: <BR>"; print $first . " " . $last . "<BR>"; print $street . "<BR>"; print $town . " " . $state . " " . $postcode; } ?> <FORM ACTION="/books/formdemo.php" METHOD="GET"> <TABLE ROWS=4 COLS=6 BORDER=2> <TR><TD>First Name:</TD><TD COLSPAN=2><INPUT TYPE="Text" NAME="first"></TD> <TD>Last Name:</TD><TD COLSPAN=2><INPUT TYPE="Text" NAME="last"></TD></TR> <TR><TD>Street:</TD><TD COLSPAN=5><INPUT TYPE="Text" NAME="street" SIZE="60"></TD></TR> <TR><TD>Town:</TD><TD><INPUT TYPE="Text" NAME="town"></TD> <TD>State:</TD><TD><INPUT TYPE="Text" NAME="state"></TD> <TD>Postcode:</TD><TD><INPUT TYPE="Text" NAME="postcode"></TD></TR> <TR><TD COLSPAN=5></TD><TD><INPUT TYPE="submit"></TD></TR> </TABLE> </FORM> <?php html_footer(); ?>
Dressing Up Your Pages
If your pages look a little dull, you can liven them up a bit by using some PHP code and a CSS file to insert a common header and footer into every page. I do that by including a file called head.php at the top of each script, and calling the html_header() and html_footer() functions that are defined there. The html_header() function, in turn, imports a cascaded stylesheet:
<?php function html_header($title = "Library Catalogue System") { ?> <HTML> <HEAD> <TITLE> <? echo $title; ?> </TITLE> <LINK rel="stylesheet" type="text/css" HREF="./books.css"> </HEAD> <BODY> <H1> <? echo $title; ?> </H1> <?php } ?> <?php function html_footer() { ?> </BODY> </HTML> <?php } ?> Notice how these PHP functions don't actually consist of much PHP code at all - actually they're mostly straight HTML, except for the opening and closing braces, and a couple of echo statements in the header. You can also see that the html_header() function expects a single argument, the title of the page, and if one is not supplied, it provides a default of "Library Catalogue System".
Connecting to a Database
For the examples that follow, we'll construct a very simple database, which is a simplified version of a library catalogue (It wouldn't take much work to turn this into, say, a CD/DVD catalogue).
First step: design and define the database
For this article, we're going to create a database named "books", which has two tables - one for the books themselves, the other for publishers.
Here are the commands to do this:
create database books; use books; create table publisher ( prfnbr INTEGER NOT NULL AUTO_INCREMENT, coname VARCHAR(40), PRIMARY KEY (prfnbr), KEY (coname)); create table book ( brfnbr INTEGER NOT NULL AUTO_INCREMENT, title VARCHAR(40) NOT NULL, isbn CHAR(15), author VARCHAR(100), prfnbr INTEGER REFERENCES publisher, PRIMARY KEY (brfnbr)); The "create table" statements obviously create the two tables. Each has a primary key column which will be used to uniquely identify books and publishers, and the book table uses the prfnbr column to identify the publisher.
Alternatively, you can use any of several database administration programs to create the database: MyPHPAdmin, MySQLCC, Webmin or Rekall. The latter, particularly, provides a reasonably intuitive and graphical interface.
Step 2: Allow a connection from the PHP code
The PHP code needs an account in order to connect to any databases, so you should create one for it, with a command like
GRANT SELECT,INSERT,UPDATE,DELETE ON books.* TO php@localhost IDENTIFIED BY 'phpuser'; This sets up an account called "php" with the (weak!) password "phpuser" and allows access from the local computer only. If you run the script, create.sql, which accompanies this article, it will do this for you automatically, as well as populating the database with sample data.
The simplest way to connect to this database from within PHP code is to use the mysql_connect() function, which takes the database server hostname, user name and password as arguments. It's best to create a PHP file containing common parameters such as these, along with required functions:
<?php // common_db.php3 $dbhost = 'localhost'; $dbusername = 'php'; $dbuserpassword = 'phpuser'; $default_dbname = 'books'; $MYSQL_ERRNO = ''; $MYSQL_ERROR = ''; function db_connect() { global $dbhost, $dbusername, $dbuserpassword, $default_dbname; global $MYSQL_ERRNO, $MYSQL_ERROR; $link_id = mysql_connect($dbhost, $dbusername, $dbuserpassword); if(!$link_id) { $MYSQL_ERRNO = 0; $MYSQL_ERROR = 'Connection to host $dbhost failed.'; return 0; } else if (empty($dbname) && !mysql_select_db($default_dbname)) { $MYSQL_ERRNO = mysql_errno(); $MYSQL_ERROR = mysql_error(); return 0; } else return $link_id; } function sql_error() { global $MYSQL_ERRNO, $MYSQL_ERROR; if (empty($MYSQL_ERROR)) { $MYSQL_ERRNO = mysql_errno(); $MYSQL_ERROR = mysql_error(); } return "$MYSQL_ERRNO: $MYSQL_ERROR"; } ?> The db_connect() function creates a MySQL database connection and returns a link ID, which is used by the various MySQL functions that will be used to query the database.
Like most other relational databases these days, MySQL uses SQL (Structured Query Language) to both extract and insert data in its databases. Teaching the concepts and syntax of SQL is beyond the scope of this article, but the References and Further Reading section lists some web-based tutorials.
Constructing SQL Statements
The various PHP MySQL functions will take a string containing SQL statement and a database connection ID as parameters. We've already got the connection ID - now we need to construct an SQL query. The easiest way to do this is line by line, using the PHP "." (append) operator, like this example, which extracts information about a specific vendor from a vendor table of a database:
#Construct the SQL query $stmt = "SELECT vtitle, vfirst, vsurname, vaddress, vtown, vstate, vpcode, vtele, vemail, vabn, vcrate"; $stmt .= " FROM vendor"; $stmt .= " WHERE vrfnbr = " . $vrfnbr; Notice how the SELECT statement specifies the fields to extract - I could also have specified "*" to extract all columns of the table. In addition, it uses WHERE clause to restrict the information to just one vendor, based on the value of a PHP variable called $vrfnbr (which is a primary key on the table). Without the WHERE clause, the query would list details for all vendors; with it, we should get only one row returned. Presumably, $vrfnbr was returned as part of an HTTP GET (perhaps a linkl) or POST (from a form).
When trying to formulate SQL statements, it's often convenient to test the statement directly in the mysql client program. You can type in a multi-line statement - it is only submitted for execution when you type ";" at the end of a line and then press Enter:
mysql> SELECT title,isbn,author,coname FROM book,publisher -> WHERE book.prfnbr=publisher.prfnbr -> AND publisher.prfnbr=1; +---------------------------+---------------+------------+---------------+ | title | isbn | author | coname | +---------------------------+---------------+------------+---------------+ | Real World Linux Security | 0-13-046456-2 | Toxen, Bob | Prentice Hall | +---------------------------+---------------+------------+---------------+ 1 row in set (0.00 sec) mysql> You can also use the up- and down-arrow keys to recall previous lines for editing and resubmission - this can save you a lot of typing.
Submitting the SQL Query
This is done with the mysql_query() function, which takes the query string and the connection ID as parameters. It's very important to query the result code of this function (as it is with all these functions) to check whether the query worked, or whether there was some kind of problem with the SQL syntax. The simplest syntax is something like this:
# Get a database connection $dbi = db_connect(); $rc = mysql_query($stmt, $dbi) or die ("Invalid query");
Processing the Returned Results
The value returned by mysql_query() is a result set - one or more rows from the queried tables. In some cases, you will be specifically extracting data for one record only - you can check that this is the case by using the mysql_num_rows() function - but in others, you will want to display a table listing the contents of the result set.
To do this, you will need to loop around, reading each row of the returned results, and formatting them as a row of an HTML table. You can construct the loop by using the PHP for() statement, and within the loop, using any of several different functions which can return a row of the result set
Table 4 - PHP MySQL functions that can return a row of a result set. All return FALSE once the last row of the result set has been returned
After processing the result set, don't forget to free the memory it occupies, with
mysql_free_result($rs);
Inserting Data into a Database
Inserting data is done with the SQL "INSERT INTO" statement. The basic syntax is
INSERT INTO tablename (fieldlist) VALUES (valuelist) where fieldlist and valuelist are comma-delimited lists of column names and values respectively. The gotcha is that string values have to be enclosed in single quotes. This means that the query has to have quotes around the variable names, like this:
$query = "INSERT INTO book (title, author, isbn, prfnbr) "; $query .= "VALUES ('$title','$author', '$isbn', $prfnbr)"; This might look strange, but trust me, it works. The problems arise when you have to deal with names like "O'Reilly", which have quotes (actually, apostrophes) within them - see "Useful Miscellaneous Functions" later for suggestions on how to deal with this.
Another complication is the requirement to have unique values for key fields, such as the brfnbr field. MySQL makes this easy to deal with, by using an AUTO_INCREMENT field for this purpose (see the database creation SQL code above, or the create.sql script). This means that when you insert a row into the book table, the database itself allocates the next value for prfnbr. You can retrieve its value, should you need to know it, with the mysql_insert_id() function.
Deleting Data from the Database
This is done with the SQL "DELETE FROM table" statement. However, note that this statement must be qualified with a "WHERE condition" clause that uniquely identifies one row of the table. If this is not done, then the DELETE statement will delete multiple rows, or even the entire contents of the table - so DELETE must be used with extreme caution and carefully tested.
A block of code that deletes a row might look like this:
$dbi = db_connect(); $stmt = "DELETE FROM vendor "; $stmt .= "WHERE vrfnbr = $vrfnbr"; mysql_query($stmt, $dbi); $num_rows = mysql_affected_rows($dbi); echo "$num_rows vendors have been deleted. <BR>";
Editing a Row
Here, you should use the SQL UPDATE statement. The syntax is:
UPDATE table SET field = value[, field = value] WHERE condition and of course, the condition should generally restrict the update to the single row being edited. (It is possible to do things like "UPDATE product SET price=price*1.1" in order to apply an across-the-board 10% price increase).
Useful Miscellaneous Functions
The mysql_affected_rows() function is a very useful double-check that INSERTS and DELETEs have affected only the desired number of rows
Deal with embedded quotemarks (apostrophes) by using the addslashes() function before using a variable in an SQL INSERT - and do the reverse after SELECTs with stripslashes().
You can also convert any embedded special symbols (like &) into HTML entities (like &) by using the htmlspecialchars() function.
Security
Web applications in general are prime targets for attackers, and there are a few things you should be aware of in developing robust PHP applications. For example, always place sensitive data, such as the database connection's username and password, outside the web server directory tree and include it via an absolute pathname. Also make sure that the filetype is .php and not .txt or some other type that an attacker could view directly - making it a PHP file means that if the attacker attempts to view it, the PHP processor will simply execute it, resulting in some variable assignments and no visible output.
Never trust unverified user input - for example, parse filenames and check for ../../..-style directory traversal manipulations. The same applies to CGI arguments which will be used in database SELECT statements - a crafty attacker will sometimes append additional SQL statements into an input field in order to reveal passwords or even obtain a root privileges shell, in a technique known as SQL injection. The simplest defence against SQL injection is to never use CGI variables directly in constructing SQL queries, but instead to first of all check them for embedded quote marks, and secondly to use type casting or the sprintf() function to force conversion of strings to numeric values (which cannot contain SQL syntax, obviously).
The basic rule is: Always validate user input!
skel.php
This is a skeletal outline of a PHP page which can be used to test SQL queries. It uses the mysql_num_fields() function to size the table correctly and uses the mysql_field_name() function to put the field name at the top of each column, so when trying out SQL queries, you don't need to edit the PHP code that displays the table.
<?php include "./common_db.php"; include "./head.php"; ?> <?php html_header("SQL Query Skeleton"); ?> <!-- Variables are returned as $HTTP_POST_VARS["varname"] --> <?php echo ("<p>Local time is " . date("l dS of F Y h:i:s A") . "<BR>"); ?> <TABLE BORDER=2> <?php # Get a database connection $dbi = db_connect(); # Construct an SQL query $query = "SELECT title AS Title, author AS Author, coname as Publisher"; $query .= " FROM book,publisher"; $query .= " WHERE publisher.prfnbr = book.prfnbr"; $query .= " ORDER BY Title"; # Just do it $rp = mysql_query($query, $dbi) or die ("Invalid query"); # Print the table headers echo "<TR>"; for($j=0; $j < mysql_num_fields($rp); $j++) { print ("<TH>" . mysql_field_name($rp, $j) . "</TH>"); } echo "</TR>"; # Print the rest of the table for($i = 0; $i < mysql_num_rows($rp); $i++) { echo "<TR>"; for ($j = 0; $j < mysql_num_fields($rp); $j++) { print ("<TD>" . mysql_result($rp, $i, $j) . "</TD>"); } echo "</TR>"; } # Free the result set mysql_free_result($rp); # Close the database mysql_close($dbi); ?> </TABLE> <FORM METHOD="POST" ACTION="findbook.php"> <P>Enter a specific title to search for</P> <INPUT TYPE="TEXT" NAME="title" SIZE="40"> <INPUT TYPE="SUBMIT"> </FORM> <?php html_footer(); ?>
References and Further Reading
PHP
The main PHP web site: http://www.php.net/
PHP Builder magazine: http://phpbuilder.com/
The PHP Resource Index: http://php.resourceindex.com/
SQL Tutorials
http://www.sqlcourse.com/
http://www.1keydata.com/sql/sql.html
http://sqlzoo.net/
http://www.geocities.com/SiliconValley/Vista/2207/sql1.html
The Open Web Application Security Project is an excellent resource - and they even have chapters in both Sydney and Melbourne. See http://www.owasp.org
SQL Injection Attacks by Example: http://www.unixwiz.net/techtips/sql-injection.html
This article is based on a presentation given at the IBM p-Series, Linux and Grid Computing Technical University in Lisbon, in November 2003.
Here are the lab exercise files for the "Creating Interactive Web Sites with LAMP (Linux, Apache, MySQL and PHP)" lecture presented at the IBM p-Series, Linux and Grid Computing Technical University in Lisbon on 4th November 2003.
Page last updated: 08/Jun/2005 Back to Home Copyright © 1987-2010 Les Bell and Associates Pty Ltd. All rights reserved. webmaster@lesbell.com.au