Network Computingwww.networkcomputing.com

• PHP syntax
• PHP handling of HTML form variables
• PHP interaction with mySQL

If the user selects the "Submit Order" button, the data is added to an order database, and the user is once again presented with a blank order screen. If the user selects the "View Orders" button, a list of the current orders from the orders database is presented in table format:

To get started, let's first create the database and tables.

Creating a Database

Code Sample 1: mug_world.sql contains the necessary SQL statements to create our database, create a few tables and a special user for access from the Web server and add a few records to the tables. All words following a pound sign (#) are considered comments by the mySQL parser (mySQL supports other comment syntax, as well). The command:

	shell> mysql -u root -p < mug_world.sql

The database/table creation and insert statements should be familiar, standard SQL. The grant statements shown create a special user (webuser) that may execute SELECT statements against all tables of the new database, and INSERT/UPDATE statements against the mug_world.orders table. The "FLUSH PRIVILEGES" statement causes the mySQL daemon to reload the permissions information, which is necessary on a running server. Our PHP script will access the database as this special user to keep the security risk as low as possible.

Many applications construct SQL statements directly from user input. User input should always be handled with care, but when an application is allowing a user read/write access to sensitive data, extra attention is in order. Explore how a user might be able to enter data into the application's forms that might result in an unexpected query being executed against the database. Be especially cautious with quotes and parens in form data fields. Frequently these characters will break queries, if not allow the user actually cause damage.

That said, our sample application does no checking on user input. Nor does it insist that there is any value in the "Name" field at all. Experiment with different values in the name field to get a feel for how events might go wrong.

PHP Basics

At the top of the script, we set a few global variables defining the name of the current script, the name of the database that the application uses, and the name and password of the special user we defined above. Note that variables are specified with a leading dollar sign ($), as in Perl or shell scripts. Semi-colons mark the end of statements.

Three functions are defined next, which do the work of querying the database and producing the output for Mug World. Function syntax is similar to JavaScript, the word 'function' followed by parens containing optional parameters. Note in the first line of each function, global variables must explicitly be imported, else any variable is assumed to be local in scope.

PHP makes dealing with form data easy: it converts the form elements into PHP variables. The variables created inherit the name of the form element, and have the value specified by the user (or the default of the element, if the user did not change its value). Elements for which multiple values can be selected, such as checkboxes or multiple select boxes, are returned as arrays. With proper naming, groups of related elements can be automatically converted into associative arrays.

The main routine consists of a switch statement a la C, that switches on the value of a variable named $button, not coincidently the same name as the "Submit" element name on our form. When the "Submit Order" button is clicked, we are also passing the values of $name and $product to the save_order() function, which correspond to the "name" and "product" form elements on the order page.

The functions view_products() and view_orders() produce HTML output based on our SQL queries. In this case, each function uses echo statements to send output to the browser. There are many ways to format output for the client, including C-like printf() functions and Perl-like print() functions. Additionally, nything that isn't included in a <?php ... ?> block is sent to the client unmodified. In this example we start an HTML table, then loop through the record set. We start a row at the beginning of each loop with the <TR> tag, then echo each field value surrounded by <TD>Š </TD> table data tags, ending the loop with the closing </TR> end row tag. Ordered and unordered lists could be easily generated in the same fashion.

Anatomy of a Database Query

• Make a connection to the database server
• Select a database
• Send an SQL query
• Use/check results
• Close connection

Next the database is selected with mysql_select_db(). An SQL query constructed and executed with the mysql_query() function. mysql_query() returns a handle to the result set for successful SELECT queries. We're using with mysql_fetch_array() in a while loop to cycle through the rows of the result set and produce our HTML table rows. mysql_fetch_array() places the fields of each row into a convenient associative array with key values corresponding to the column names selected in the source query.

Finally, the result set is freed with mysql_free_result() and the database connection closed with mysql_close().

The save_order() function in our sample demonstrates an INSERT statement. In this case, mysql_query() does not return a result set, but TRUE or FALSE (PHP predefined values) to indicate success or failure, respectively. Since the "order_num" field in the database is defined as an AUTO_INCREMENT field, we can use the mysql_insert_id() function to retrieve the value of the last inserted record for future use. The number of the last inserted record number is our order number, in this case, so we may want to store it in a cookie, or as a hidden variable in subsequent forms for session tracking.

The PHP die() function is called if any of the mySQL functions fail to return the desired results. Die() prints a simple HTML page containing the text passed to it as an argument, and stops script execution immediately. mysql_error() returns the text message corresponding to the last mysql function failure--very handy for debugging.

In Closing

Page 1 | 2 | 3 | 4 | 5 | Next page