Web Database Applications with PHP, and MySQL

Content preview·Buy reprint rights for this chapter

With the growth of the Web over the past decade, there has been a similar growth in services that are accessible over the Web. Many new services are web sites that are driven from data stored in databases. Examples of web database applications include news services that provide access to large data repositories, e-commerce applications such as online stores, and business-to-business (B2B) support products.

Database applications have been around for over 30 years, and many have been deployed using network technology long before the Web existed. The point-of-service systems used by bank tellers are obvious examples of early networked database applications. Terminals are installed in bank branches, and access to the bank's central database application is provided through a wide area network. These early applications were limited to organizations that could afford the specialized terminal equipment and, in some cases, to build and own the network infrastructure.

The Web provides cheap, ubiquitous networking. It has an existing user base with standardized web browser software that runs on a variety of ordinary computers. For developers, web server software is freely available that can respond to requests for both documents and programs. Several scripting languages have been adapted or designed to develop programs to use with web servers and web protocols.

This book is about bringing together the Web and databases. Most web database applications do this through three layers of application logic. At the base is a database management system (DBMS) and a database. At the top is the client web browser used as an interface to the application. Between the two lies most of the application logic, usually developed with a web server-side scripting language that can interact with the DBMS, and can decode and produce HTML used for presentation in the client web browser.

We begin by discussing the three-tier architecture model used in many web database applications. We then introduce the nature of the Web and its underlying protocols and then discuss each of the three tiers and their components in detail. Hugh and Dave's Online Wines, our case study application, is introduced at the end of this chapter. We refer to it frequently throughout the course of the book and use it as a model to illustrate the construction of a web database application.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

This book describes web database applications built around a three-tier architecture model, shown in Figure 1-1. At the base of an application is the database tier, consisting of the database management system that manages the database containing the data users create, delete, modify, and query. Built on top of the database tier is the complex middle tier , which contains most of the application logic and communicates data between the other tiers. On top is the client tier , usually web browser software that interacts with the application.

Figure 1-1: The three-tier architecture model of a web database application

The formality of describing most web database applications as three-tier architectures hides the reality that the applications must bring together different protocols and software. The majority of the material in this book discusses the middle tier and the application logic that brings together the fundamentally different client and database tiers.

When we use the term "the Web," we mean three major, distinct standards and the tools based on these standards: the Hypertext Markup Language (HTML), the Hypertext Transfer Protocol (HTTP), and the TCP/IP networking protocol suite. HTML works well for structuring and presenting information using a web browser application. TCP/IP is an effective networking protocol that transfers data between applications over the Internet and has little impact on web database application developers. The problem in building web database applications is interfacing traditional database applications to the Web using HTTP. This is where the complex application logic is needed.

The three-tier architecture provides a conceptual framework for web database applications. The Web itself provides the protocols and network that connect the client and middle tiers of the application; that is, it provides the connection between the web browser and the web server. HTTP is one component that binds together the three-tier architecture. A detailed knowledge of HTTP isn't necessary to understand the material in this book, but it's important to understand the problems HTTP presents for web database applications. The HTTP protocol is used by web browsers to request resources from web servers, and for web servers to return responses. (A longer introduction to the underlying web protocols—including more examples of HTTP requests and responses—can be found in Appendix B.)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The client tier in the three-tier architecture model is usually a web browser. Web browser software processes and displays HTML resources, issues HTTP requests for resources, and processes HTTP responses. As discussed earlier, there are significant advantages to using a web browser as the thin-client layer, including easy deployment and support on a wide range of platforms.

There are many browser products available, and each browser product has different features. The two most popular windowing-based browsers are Netscape and Internet Explorer. While we won't describe all the features of web browsers, they have a common basic set:

All web browsers are HTTP clients that send requests and display responses from web servers (usually in a graphical environment).
All browsers interpret pages marked up with HTML when rendering a page; that is, they present the headings, images, hypertext links, and so on to the user.
Some browsers display images, play movies and sounds, and render other types of objects.
Many browsers can run JavaScript that is embedded in HTML pages. JavaScript is used, for example, to validate a <form> or change how a page is presented based on user actions.
Selected web browsers can run components developed in the Java or ActiveX programming languages. These components often provide additional animation, tools that can't be implemented in HTML, or other, more complex features.
Several browsers can apply Cascading Style Sheets (CSS) to HTML pages to control the presentation of HTML elements.

There are subtle—and sometimes not so subtle—differences between the capabilities different browsers have in rendering an HTML page. Lynx, for example, is a text-only browser and doesn't display images or run JavaScript. MultiWeb is a browser that renders the text on a page as sound—the spoken word—providing web access for the vision-impaired. Many subtle but annoying differences are in the support for CSS and the features of the latest HTML standard, HTML 4.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In most three-tier web database systems, the majority of the application logic is in the middle tier. The client tier presents data to and collects data from the user; the database tier stores and retrieves the data. The middle tier serves most of the remaining roles that bring together the other tiers: it drives the structure and content of the data displayed to the user, and it processes input from the user as it is formed into queries on the database to read or write data. It also adds state management to the HTTP protocol. The middle-tier application logic integrates the Web with the database management system.

In the application framework used in this book, the components of the middle tier are a web server, a web scripting language, and the scripting language engine. A web server processes HTTP requests and formulates responses. In the case of web database applications, these requests are often for programs that interact with an underlying database management system. The web server we use throughout this book is the Apache Software Foundation's Apache HTTP server, the open source web server used by more than 60% of Internet connected computers.

We use the PHP scripting language as our middle-tier scripting language. PHP is an open source project of the Apache Software Foundation and, not surprisingly, it is the most popular Apache HTTP server add-on module, with around 40% of the Apache HTTP servers having PHP capabilities. PHP is particularly suited to web database applications because of its integration tools for the Web and database environments. In particular, the flexibility of embedding scripts in HTML pages permits easy integration with the client tier. The database-tier integration support is also excellent, with more than 15 libraries available to interact with almost all popular database management systems.

Web servers are often referred to as HTTP servers. The term "HTTP server" is a good summary of their function: their basic task is to listen for HTTP requests on a network, receive HTTP requests made by user agents (usually web browsers), serve the requests, and return HTTP responses that contain the requested resources.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The database tier is the base of a web database application. Understanding system requirements, choosing database-tier software, designing databases, and building the tier are the first steps in successful web database application development. We discuss techniques for modeling system requirements, converting a model into a database, and the principles of database technology in Appendix C. In this section, we focus on the components of the database tier and introduce database software by contrasting it with other techniques for storing data. Chapter 3 covers the standards and software we use in more detail.

In a three-tier architecture application, the database tier manages the data. The data management typically includes storage and retrieval of data, as well as managing updates, allowing simultaneous, or concurrent, access by more than one middle-tier process, providing security, ensuring the integrity of data, and providing support services such as data backup. In many web database applications, these services are provided by a RDBMS system, and the data stored in a relational database .

Managing relational data in the third tier requires complex RDBMS software. Fortunately, most DBMSs are designed so that the software complexities are hidden. To effectively use a DBMS, skills are required to design a database and formulate commands and queries to the DBMS. For most DBMSs, the query language of choice is SQL. An understanding of the underlying architecture of the DBMS is unimportant to most users.

In this book, we use the MySQL RDBMS to manage data. Much like choosing a middle-tier scripting language, there are often arguments about which DBMS is most suited to an application. MySQL has a well-deserved reputation for speed, and it is particularly well designed for applications where retrieval of data is more common than updates and where small, simple updates are the general class of modifications. These are characteristics typical of most web database applications. Also, like PHP and Apache, MySQL is open source software. However, there are down sides to MySQL we'll discuss later in this section.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The principles of web database applications are illustrated in practice throughout this book with the running example of Hugh and Dave's Online Wines. We refer to it as the winestore throughout the book.

The winestore application has many components of a typical web database application, including:

Web pages populated with data from a database
User-driven querying and browsing, in which the user provides the parameters that limit the searching or browsing of the database
Data entry and validation. HTML <form> widgets collect data, and JavaScript client-side scripts and PHP server-side scripts perform validation.
User tracking; that is, session management techniques that add state to HTTP
User authentication and management
Reporting

Let's take a look at the scope of the winestore and the system functional requirements. (The process of modeling these requirements with relational database entity-relationship (ER) modeling and converting this model to SQL statements is the subject of Appendix C. The completed winestore ER model and the SQL statements to create the database can be found in Chapter 3. We use the winestore components as examples beginning in Chapter 4. Completed components of the winestore application are discussed in Chapter 10 to Chapter 13.)

Hugh and Dave's Online Wines is a fictional online wine retailer. In this section, we briefly detail the aims and scope of the winestore and then discuss the system requirements derived from these. We also introduce the technical components of the winestore and point to the chapters in the book where these components are discussed in detail. We conclude with a discussion of the shortcomings of the winestore and what isn't covered in this book. The completed winestore described in this section can be accessed via this book's web site.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In this chapter, we introduce the PHP scripting language. PHP is similar to high-level languages such as C, Perl, Pascal, FORTRAN, and Java, and programmers who have experience with any of these languages should have little trouble learning PHP. This chapter serves as an introduction to PHP; it's not a programming guide. We assume you are already familiar with programming in a high-level language.

The topics covered in this chapter include:

PHP basics, including script structure, variables, supported types, constants, expressions, and type conversions
Condition and branch statements supported by PHP, including if, if...else, and the switch statements
Looping statements
Arrays and array library functions
Strings and string library functions
Regular expressions
Date and time functions
Integer and float functions
How to write functions, reuse components, and determine the scope and type of variables
An introduction to PHP object-oriented programming support
Common mistakes made by programmers new to PHP, and how to solve them

Programmers new to PHP should read Section 2.1, which describes the basic structure of a PHP script and its relationship to HTML, and includes discussion of how PHP handles variables and types. The two sections that follow, Section 2.2 and Section 2.3, deal with conditional statements and looping structures and should be familiar material. We then present a short example that puts many of the basic PHP concepts together.

The remainder of the chapter expands on the more advanced features of PHP, presents a reference to selected library functions, and discusses some of the common mistakes that programmers make when learning PHP. This material can be examined briefly, and used later as a reference while reading Chapter 4 to 13 and while programming in PHP. However, programmers new to PHP should consider reading the beginning of the Section 2.5 and Section 2.6 sections to understand the way PHP supports these concepts, as there are important differences from other languages.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The current version of PHP is PHP4, which we call PHP throughout this book. The current release at the time of writing is 4.0.6.

PHP is a recursive acronym that stands for PHP: Hypertext Preprocessor ; this is in the naming style of GNU, which stands for GNU's Not Unix and which began this odd trend. The name isn't a particularly good description of what PHP is and what it's commonly used for. PHP is a scripting language that's usually embedded or combined with HTML and has many excellent libraries that provide fast, customized access to DBMSs. It's an ideal tool for developing application logic in the middle tier of a three-tier application.

Example 2-1 shows the first PHP script in this book, the ubiquitous "Hello, world." When requested by a web browser, the script is run on the web server and the resulting HTML document sent back to the browser and rendered as shown in Figure 2-1.

Figure 2-1: The rendered output of Example 2-1 shown in the Netscape browser

Example 2-1. The ubiquitous Hello, world in PHP

<!DOCTYPE HTML PUBLIC
   "-//W3C//DTD HTML 4.0 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd" >
<html>
<head>
  <title>Hello, world</title>
</head>
<body bgcolor="#ffffff">
  <h1>
  <?php
    echo "Hello, world";
  ?>
  </h1>
</body>
</html>

Example 2-1 illustrates the basic features of a PHP script. It's a mixture of HTML—in this case it's mostly HTML—and a PHP script. The script in this example:

<?php
  echo "Hello, world";
?>

simply prints the greeting, "Hello, world."

The PHP script shown in Example 2-1 is rather pointless: we could simply have authored the HTML to include the greeting directly. Because PHP integrates so well with HTML, using PHP to produce static strings is far less complicated and less interesting than using other high-level languages. However, the example does illustrate several features of PHP:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The control structures in PHP are similar in syntax to those in other high-level programming languages.

Conditionals add control to scripts and permit branching so that different statements are executed depending on whether expressions are true or false. There are two branching statements in PHP: if, with the optional else clause, and switch, usually with two or more case clauses.

The if statement conditionally controls execution and its use in PHP is as in any other language. The basic format of an if statement is to test whether a condition is true and, if so, to execute one or more statements.

The following if statement executes the echo statement and outputs the string when the conditional expression, $var is greater than 5, is true:

if ($var > 5)
  echo "The variable is greater than 5";

The if statement executes only the one, immediately following statement.

Multiple statements can be executed as a block by encapsulating the statements within braces. If the expression evaluates as true, the statements within braces are executed. If the expression isn't true, none of the statements are executed. Consider an example in which three statements are executed if the condition is true:

if ($var > 5)
{
  echo "The variable is greater than 5.";
  // So, now let's set it to 5
  $var = 5;
  echo "In fact, now it is equal to 5.";
}

The if statement can have an optional else clause to execute a statement or block of statements if the expression evaluates as false. Consider an example:

if ($var > 5)
  echo "Variable greater than 5";
else
  echo "Variable less than or equal to 5";

It's also common for the else clause to execute a block of statements in braces, as in this example:

if ($var > 5)
{
  echo "Variable is less than 5";
  echo "-----------------------";
} 
else
{
  echo "Variable is equal to or larger than 5";
  echo "-------------------------------------";
}

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Loops in PHP have the same syntax as other high-level programming languages.

Loops add control to scripts so that statements can be repeatedly executed as long as a conditional expression remains true. There are four loop statements in PHP: while, do...while, for, and foreach. The first three are general-purpose loop constructs, and foreach is used exclusively with arrays.

The while loop is the simplest looping structure but sometimes the least compact to use. The while loop repeats one or more statements—the loop body—as long as a condition remains true. The condition is checked first, then the loop body is executed. So, the loop never executes if the condition isn't initially true. Just as in the if statement, more than one statement can be placed in braces to form the loop body.

The following fragment illustrates the while statement by printing out the integers from 1 to 10 separated by a space character:

$counter = 1;
while ($counter < 11)
{
  echo $counter;
  echo " ";
  // Add one to $counter
  $counter++;
}

The difference between while and do...while is the point at which the condition is checked. In do...while, the condition is checked after the loop body is executed. As long as the condition remains true, the loop body is repeated.

You can emulate the functionality of the while example as follows:

$counter = 1;
do
{
  echo $counter;
  echo " ";
  $counter++;
} while ($counter < 11);

The contrast between while and do...while can be seen in the following example:

$counter = 100;
do
{
  echo $counter;
  echo " ";
  $counter++;
} while ($counter < 11);

This example outputs 100, because the body of the loop is executed once before the condition is evaluated as false.

The do...while loop is the least-frequently used loop construct, probably because executing a loop body once when a condition is

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In this section, we use the techniques described so far to develop a simple, complete PHP script. The script doesn't process input from the user, so we leave some of the best features of PHP as a web scripting language for discussion in later chapters.

Our example is a script that produces a web page containing the times tables. Our aim is to output the 1-12 times tables. The first table is shown in Figure 2-2 as rendered by a Netscape browser.

Figure 2-2: The output of the times-tables script shown rendered in a Netscape browser

To begin the development, we need to design how the output should appear and, therefore, what HTML needs to be produced. If we use simple HTML markup, the first 12 lines of the HTML produces Example 2-3 as follows:

<html>
<head>
  <title>The Times-Tables</title>
</head>
<body bgcolor="#ffffff">
<h1>The Times Tables</h1>
<p><b>The 1 Times Table</b>
<br>1 x 1 = 1
<br><b>2 x 1 = 2</b>
<br>3 x 1 = 3
<br><b>4 x 1 = 4</b>
<br>5 x 1 = 5

The script produces this output using a mixture of HTML and an embedded PHP script.

The completed PHP script and HTML to produce the times tables are shown in Example 2-3. The first nine lines are HTML that produces the <head> components and the <h1>The Times Tables</h1> heading at the top of the web page. Similarly, the last two lines are HTML that finishes the document: </body> and </html>.

Between the two HTML fragments that start and end the document is a PHP script to produce the times-table content and its associated HTML. The script begins with the PHP open tag <?php and finishes with the close tag ?>.

Example 2-3. A script to produce the times tables

<!DOCTYPE HTML PUBLIC
   "-//W3C//DTD HTML 4.0 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd" >
<html>
<head>
  <title>The Times-Tables</title>
</head>
<body bgcolor="#ffffff">
<h1>The Times Tables</h1>
<?php
  // Go through each table
  for($table=1; $table<13; $table++) 
  {  
    echo "<p><b>The " . $table . " Times Table</b>\n";

    // Produce 12 lines for each table
    for($counter=1; $counter<13; $counter++)
    {
      $answer = $table * $counter;

      // Is this an even-number counter?
      if ($counter % 2 == 0)
        // Yes, so print this line in bold
        echo "<br><b>$counter x $table = " . 
             "$answer</b>";

      else
        // No, so print this in normal face
        echo "<br>$counter x $table = $answer";
    }
  }
?>
</body>
</html>

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Arrays in PHP are sophisticated and more flexible than in many other high-level languages. An array is an ordered set of variables, in which each variable is called an element. Technically, arrays can be either numbered or associative , which means that the elements of an array can be accessed by a numeric index or by a textual string, respectively.

In PHP, an array can hold scalar values—integers, Booleans, strings, or floats—or compound values—objects and even other arrays, and can hold values of different types. In this section, we show how arrays are constructed and introduce several useful array functions from the PHP library.

PHP provides the array( ) language construct that creates arrays. The following examples show how arrays of integers and strings can be constructed and assigned to variables for later use:

$numbers = array(5, 4, 3, 2, 1);
$words = array("Web", "Database", "Applications");

// Print the third element from the array 
// of integers: 3
echo $numbers[2];

// Print the first element from the array 
// of strings: "Web"
echo $words[0];

By default, the index for the first element in an array is 0. The values contained in an array can be retrieved and modified using the bracket [ ] syntax. The following code fragment illustrates the bracket syntax with an array of strings:

$newArray[0] = "Potatoes";
$newArray[1] = "Carrots";
$newArray[2] = "Spinach";

// Oops, replace the third element
$newArray[2] = "Tomatoes";

Numerically indexed arrays can be created to start at any index value. Often it's convenient to start an array at index 1, as shown in the following example:

$numbers = array(1=>"one", "two", "three", "four");

Arrays can also be sparsely populated, such as:

$oddNumbers = array(1=>"one", 3=>"three", 5=>"five");

An empty array can be created by assigning a variable with no parameters with

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

A string of characters—a string—is probably the most commonly used data type when developing scripts, and PHP provides a large library of string functions to help transform, manipulate, and otherwise manage strings. We introduced PHP strings earlier, in Section 2.1.1. Here, we examine string literals in more detail and describe some of the useful string functions PHP provides.

As already shown in previous examples, enclosing characters in single quotes or double quotes can create a string literal. Single-quoted strings are the simplest form of string literal; double-quoted strings are parsed to substitute variable names with the variable values and allow characters to be encoded using escape sequences. Single-quoted strings don't support all the escape sequences, only \' to include a single quote and \\ to include a backslash.

Tab, newline, and carriage-return characters can be included in a double-quoted string using the escape sequences \t, \n, and \r, respectively. To include a backslash, a dollar sign, or a double quote in a double-quoted string, use the escape sequences \\, \$, or \".

Other control characters and characters with the most significant bit set can be included using escaped octal or hexadecimal sequences. For example, to include the umlauted character ö, the octal sequence \366 or the hexadecimal sequence \xf6 are used:

//Print a string that includes a lowercase
//o with the umlaut mark
echo "See you at the G\xf6teborg Film Festival";

PHP uses eight-bit characters in string values, so the range of characters that can be represented is \000 to \377 in octal notation or \x00 to \xff in hexadecimal notation.

Unlike many other languages, PHP allows newline characters to be included directly in a string literal. The following example show the variable $var assigned with a string that contains a newline character:

// This is Ok. $var contains a newline character
$var = 'The quick brown fox
        jumps over the lazy dog';

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In this section we show how regular expressions can achieve more sophisticated pattern matching to find, extract, and even replace complex substrings within a string.

While regular expressions provide capabilities beyond those described in the last section, complex pattern matching isn't as efficient as simple string comparisons. The functions described in the last section are more efficient than those that use regular expressions and should be used if complex pattern searches aren't required.

This section starts with a brief description of the POSIX regular expression syntax. This isn't a complete description of all the capabilities, but we do provide enough details to create quite powerful regular expressions. The second half of the section describes the functions that use POSIX regular expressions. Examples of regular expressions can be found in this section and in Chapter 7.

A regular expression follows a strict syntax to describe patterns of characters. PHP has two sets of functions that use regular expressions: one set supports the Perl Compatible Regular Expression (PCRE) syntax, while the other supports the POSIX extended regular expression syntax. In this book we use the POSIX functions.

To demonstrate the syntax of regular expressions, we introduce the function ereg() :

boolean ereg(string pattern, string subject [, array var])

ereg( ) returns true if the regular expression pattern is found in the subject string. We discuss how the ereg( ) function can extract values into the optional array variable var later in this section.

The following trivial example shows how ereg() is called to find the literal pattern "cat" in the subject string "raining cats and dogs":

// prints "Found a cat"
if (ereg("cat", "raining cats and dogs"))
  echo "Found 'cat'";

The regular expression "cat" matches the subject string, and the fragment prints "

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

There are several PHP library functions that work with dates and times. Most either generate a Unix timestamp or format a Unix timestamp in a human-readable form.

Date and time is generally represented as a Unix timestamp: the number of seconds since 1 January 1970 00:00:00 Greenwich Mean Time. Most systems represent a timestamp using a signed 32-bit integer, allowing a range of dates from December 13, 1901 through January 19, 2038. While timestamps are convenient to work with in scripts, care must be taken when manipulating timestamps to avoid integer overflow errors. A common source of errors is to compare two timestamps in which the date range is greater than the largest positive integer—a range just over 68 years for a signed 32-bit integer.

PHP gives unexpected results when comparing two integers that differ by an amount greater than the largest positive integer, typically 231-1. A safer way to compare large integers is to cast them to floating-point numbers. The following example illustrates this point:

$var1 = -2106036000;  // 16/08/1902
$var2 = 502808400;    // 24/08/1984

// $result is assigned false
$result = $var1 < $var2; 

// $result is assigned true as expected
$result = (float) $var1 < (float) $var2;

Even floating-point numbers can overflow. To manipulate numbers of arbitrary precision, the BCMath library should be considered.

Section 2.8.1.1: Current time

PHP provides several functions that generate a Unix timestamp. The simplest:

integer time(  )

returns the timestamp for the current date and time, as shown in this fragment:

// prints the current timestamp: e.g., 1008553254
echo time(  );

Section 2.8.1.2: Creating timestamps with mktime( ) and gmmktime( )

To create a timestamp for a past or future date in the range December 13, 1901 through January 19, 2038, the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Apart from the basic operators +, -, /, *, and %, PHP provides the usual array of mathematical library functions. In this section, we present some of the library functions that are used with integer and float numbers.

The absolute value of an integer or a float can be found with the

abs(
)

function:

integer abs(integer number)
float abs(float number)

The following examples show the result of abs( ) on floats and integers:

echo abs(-1);       // prints 1
echo abs(1);        // prints 1
echo abs(-145.89);  // prints 145.89
echo abs(145.89);   // prints 145.89

The ceil( ) and

floor(
)

functions can return the integer value above and below a fractional value, respectively:

float ceil(float value)
float floor(float value)

The return type is a float because an integer may not be able to represent the result when a large value is passed as an argument. Consider the following examples:

echo ceil(27.3);   // prints 28
echo floor(27.3);  // prints 27

The round( ) function uses 4/5 rounding rules to round up or down a value to a given precision:

float round(float value [, integer precision])

Rounding by default is to zero decimal places, but the precision can be specified with the optional precision argument. The 4/5 rounding rules determine if a number is rounded up or down based on the digits that are lost due to the rounding precision. For example, 10.4 rounds down to 10, and 10.5 rounds up to 11. The following examples show rounding at various precisions:

echo round(10.4);           // prints 10
echo round(10.5);           // prints 11
echo round(2.40964, 3);     // prints 2.410
echo round(567234.56, -3);  // prints 567000
echo round(567234.56, -4);  // prints 570000

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Functions provide a way to group together related statements into a cohesive block. For reusable code, a function saves duplicating statements and makes maintenance of the code easier.

We've already presented many examples of function calls in this chapter. Once written, a user-defined function is called in exactly the same way. Consider an example of a simple user-developed function as shown in Example 2-6.

Example 2-6. A user-defined function to output bold text

<!DOCTYPE HTML PUBLIC 
  "-//W3C//DTD HTML 4.0 Transitional//EN"
  "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <title>Simple Function Call</title>
</head>
<body bgcolor="#ffffff">
<?php

function bold($string)
{
  echo "<b>" . $string . "</b>\n";
}

// First example function call (with a static string)
echo "this is not bold\n";
bold("this is bold");
echo "this is again not bold\n";

// Second example function call (with a variable)
$myString = "this is bold";
bold($myString);
?>
</body></html>

The script defines the function

bold(
)

, which takes one parameter, $string, and prints that string prefixed by a bold <b> tag and suffixed with a </b> tag. The bold( ) function, defined here, can be used with a string literal expression or a variable, as shown.

Functions can also return values. For example, consider the following code fragment that declares and uses a function

heading(
)

, which returns a string using the return statement:

function heading($text, $headingLevel)
{
  switch ($headingLevel)
  case 1:
    $result = "<h1>" . ucwords($text) . "</h1>";
    break;

  case 2:
    $result = "<h2>" . ucwords($text) . "</h2>";
    break;

  case 3:
    $result = "<h3>" . ucfirst($text) . "</h3>";
    break;

  default:
    $result = "<p><b>" . ucfirst($text) . "</b>";

  return($result);
}

$test = "user defined functions";
echo heading($test, 2);

The function takes two parameters: the text of a heading and a heading level. Based on the value of

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

PHP has limited support for object-oriented programming and allows programmers to define their own classes and create object instances of those classes. We make little use of objects in this book, and this section serves as an introduction to PHP's support of object-oriented features. The subject of object-oriented programming is extensive, and we don't provide a complete explanation of the subject here.

A class defines a compound data structure made up of member variables and a set of functions—known as methods or member functions—that operate with the specific structure. Example 2-7 shows how a class Counter is defined in PHP. The class Counter contains two member variables—the integers $count and $startPoint—and four functions that use these member variables. Collectively, the variables and the functions are members of the class Counter.

Example 2-7. A simple class definition of the user-defined class Counter

<?php
  // A class that defines a counter.
  class Counter
  {
    // Member Variables
    var $count = 0;
    var $startPoint = 0;

    // Methods
    function startCountAt($i)
    {
      $this->count = $i;
      $this->startPoint = $i;
    }    
  
    function increment(  )
    {
      $this->count++;
    }
    
    function reset(  )
    {
      $this->count = $this->startPoint;
    }

    function showvalue(  )
    {
      print $this->count;
    }
  }
?>

To use the data structures and functions defined in a class, an instance of the class—an object—needs to be created. Like other data types—integers, strings, arrays, and so on—objects are held by variables. However, unlike other types, objects are created using the new operator. An object of class Counter can be created and assigned to a variable as follows:

$aCounter = new Counter;

Once the variable $aCounter is created, the member variables and functions of the new object can be used. Members of the object, both variables and functions, are accessed using the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

When switching to PHP, there are several common mistakes even experienced programmers make. In this short section, we highlight some of these mistakes and the basics of how to rectify them.

One of the most common problems in debugging PHP scripts is seeing:

No page rendered by the web browser when much more is expected
A pop-up dialog box stating that the "Document Contains No Data"
A partial page when more is expected

Most of these problems are caused not by a bug in script-programming logic, but by a bug in the HTML produced by the script. For example, if the </table>, </form>, or </frame> closing tags are omitted, the page may not be rendered.

The HTML problem can usually be identified by viewing the HTML page source using the web browser. With Netscape, the complete output of the erroneous example is shown in the page-source view, and the HTML problem can hopefully be easily identified.

For compound or hard-to-identify HTML bugs, the W3C validator at http://validator.w3.org retrieves a page, analyzes the correctness of the HTML, and issues a report. It's an excellent assistant for debugging and last-minute compliance checks before delivery of an application.

If the problem still proves hard to find, consider adding calls to the

flush(
)

function after echo, print, or printf statements. flush( ) empties the output buffer maintained by the PHP engine, sending all currently buffered output to the web server. The function has no effect on buffering at the web server or the web browser, but it ensures that all data output by the script is available to the web server to be transmitted and rendered by a browser. Remember to remove the

flush(
)

function calls after debugging, because unnecessary flushing may prevent efficient buffering of output by the PHP scripting engine.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In this chapter, we introduce the MySQLdatabase management system (DBMS) and the SQL database query language for defining and manipulating databases. Using our case study, Hugh and Dave's Online Wines, as a guide, we illustrate examples of how to use SQL. The techniques that we discuss are used to interact with a DBMS after a database has been designed. An introduction to relational modeling and design can be found in Appendix C, and a more comprehensive introduction to MySQL and SQL can be found in many of the resources that are listed in Appendix E.

In this chapter, we cover the following topics:

A short introduction to relational databases and relational modeling
A quick start guide to the winestore database and its full entity-relationship model
The MySQL command interpreter and the basic features of MySQL
Using SQL to create and modify databases, tables, and indexes
Using SQL to insert, delete, and update data
The SQL SELECT statement for querying, with examples of simple and advanced queries
Functions and operators in SQL and MySQL
Advanced features, including managing indexes and keys, tuning the MySQL DBMS, security, and the limitations of MySQL

We assume that you have already installed MySQL. If not, the guide in Appendix A will help you. Chapter 6 covers other selected advanced database topics that arise when writing to databases, such as supporting multiple users, transactions, and locking in MySQL. Complete examples of SQL queries and MySQL in use in a web database application can be found in Chapter 10 to Chapter 13.

The field of databases has its own terminology. Terms such as database, table, attribute, row, primary key, and relational model have specific meanings and are used throughout this chapter. In this section, we present an example of a simple database to introduce the basic components of relational databases, and we list and define selected terms used in the chapter. More detail can be found in Appendix C.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The field of databases has its own terminology. Terms such as database, table, attribute, row, primary key, and relational model have specific meanings and are used throughout this chapter. In this section, we present an example of a simple database to introduce the basic components of relational databases, and we list and define selected terms used in the chapter. More detail can be found in Appendix C.

An example relational database is shown in Figure 3-1. This database stores data about wineries and the wine regions they are located in. A relational database manages data in tables, and there are two tables in this example: a winery table that manages wineries, and a region table that manages information about wine regions.

Figure 3-1: An example relational database containing two related tables

Databases are managed by a relational database management system (RDBMS). An RDBMS supports a database language to create and delete databases and to manage and search data. The database language used in almost all DBMSs is SQL, a set of statements that define and manipulate data. After creating a database, the most common SQL statements used are INSERT, UPDATE, DELETE, and SELECT, which add, change, remove, and search data in a database, respectively.

A database table may have multiple columns, or attributes, each of which has a name. For example, the winery table in Figure 3-1 has four attributes, winery ID, winery name, address, and region ID. A table contains the data as rows or records, and a row contains attribute values. The winery table has five rows, one for each winery managed by the database, and each row has a set of values. For example, the first winery has a winery ID value of 1, the winery name value Moss Brothers, and an address

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

This section is a quick start guide to loading the sample winestore database and understanding the design of the winestore database that is used in examples throughout this book.

A local copy of the winestore database is required to test the examples in this and later chapters. MySQL must be installed and configured before the winestore database can be loaded. MySQL installation instructions can be found in Appendix A.

The steps to load the winestore database are as follows:

Download a copy of the winestore database from this book's web site; look for winestore.database.tar.gz.
Uncompress the winestore database package in any directory by running:
```
gzip -d winestore.database.tar.gz
```
Untar the tape archive file by running:
```
tar xvf winestore.database.tar
```
A list of files extracted is output.
Check that MySQL is running using:
```
/usr/local/bin/mysqladmin -uroot -ppassword version
```
where password is the root user password. If MySQL isn't running, log in as the Linux root user, and start the MySQL server using:
```
/usr/local/bin/safe_mysqld --user=mysql &
```
Run the MySQL command-line interpreter using the username and password created when MySQL was installed, and load the winestore data. The login name is username, and the password is password:
```
/usr/local/bin/mysql -uusername -ppassword < winestore.database
```
After the loading is complete—it may take a few seconds—the database can be tested by running a query. Type the following command on one line:
```
/usr/local/bin/mysql -uusername -ppassword
```

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The MySQL command interpreter is commonly used to create databases and tables in web database applications and to test queries. Throughout the remainder of this chapter we discuss the SQL statements for managing a database. All these statements can be directly entered into the command interpreter and executed. The statements can also be included in server-side PHP scripts, as discussed in later chapters.

Once the MySQL DBMS server is running, the command interpreter can be used. The command interpreter can be run using the following command from the shell, assuming you've created a user hugh with a password shhh:

% /usr/local/bin/mysql -uhugh -pshhh

The shell prompt is represented here as a percentage character, %.

Running the command interpreter displays the output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36 to server version: 3.22.38

Type 'help' for help.

mysql>

The command interpreter displays a mysql> prompt and, after executing any command or statement, it redisplays the prompt. For example, you might issue the statement:

mysql> SELECT NOW(  );

This statement reports the time and date by producing the following output:

+---------------------+
| NOW(  )               |
+---------------------+
| 2002-01-01 13:48:07 |
+---------------------+
1 row in set (0.00 sec)

mysql>

After running a statement, the interpreter redisplays the mysql> prompt. We discuss the SELECT statement later in this chapter.

As with all other SQL statements, the SELECT statement ends in a semicolon. Almost all SQL command interpreters permit any amount of whitespace—spaces, tabs, or carriage returns—in SQL statements, and they check syntax and execute statements only after encountering a semicolon that is followed by a press of the Enter key. We have used uppercase for the SQL statements throughout this book. However, any mix of upper- and lowercase is equivalent.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The Data Definition Language (DDL) is the set of SQL statements used to manage a database. In this section, we use the MySQL command interpreter to create databases and tables using the online winestore as a case study. We also discuss the statements that delete, alter, and drop databases and tables, as well as statements for managing indexes.

The CREATE DATABASE statement can create a new, empty database without any tables or data. The following statement creates a database called winestore:

mysql> CREATE DATABASE winestore;

To work with a database, the command interpreter requires the user to be using a database before SQL statements can be issued. Different command interpreters have different methods for using a database and these aren't part of the SQL standard. In the MySQL interpreter, you can issue the command:

mysql> use winestore

For the rest of this chapter, we omit the mysql> prompt from the command examples.

After issuing the use winestore command, you then usually issue commands to create the tables in the database, as shown in Example 3-1. (You already created the tables in the winestore database in Section 3.2 of this chapter). Let's look at one of these tables, the customer table. The statement that created this table is shown in Example 3-2.

Example 3-2. Creating the customer table with SQL

CREATE TABLE customer (
  cust_id int(5) DEFAULT '0' NOT NULL auto_increment,
  surname varchar(50) NOT NULL,
  firstname varchar(50) NOT NULL,
  initial char(1),
  title varchar(10),
  addressline1 varchar(50) NOT NULL,
  addressline2 varchar(50),
  addressline3 varchar(50),
  city varchar(20) NOT NULL,
  state varchar(20),
  zipcode varchar(5),
  country varchar(20) DEFAULT 'Australia',
  phone varchar(15),
  fax varchar(15),
  email varchar(30) NOT NULL,
  salary int(7),
  birth_date date(  ),
  PRIMARY KEY (cust_id),
  KEY names (surname,firstname)
);

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The Data Manipulation Language (DML) encompasses all SQL statements used for manipulating data. There are four statements that form the DML statement set: SELECT, INSERT, DELETE, and UPDATE. We describe the last three statements in this section. While SELECT is also part of DML, we cover it in its own section, Section 3.6. Longer worked examples using all the statements can be found in the section Section 3.8.

Having created a database and the accompanying tables and indexes, the next step is to insert data. Inserting a row of data into a table can follow two different approaches. We illustrate both approaches by inserting the same data for a new customer, Dimitria Marzalla.

Consider an example of the first approach using the customer table:

INSERT INTO customer 
  VALUES (NULL,'Marzalla','Dimitria', 'F','Mrs',
          '171 Titshall Cl','','','St Albans','WA',
          '7608','Australia','(618)63576028','',
          'dimitria@lucaston.com','1969-11-08',35000);

In this approach a new row is created in the customer table, then the first value listed—in this case, a NULL—is inserted into the first attribute of customer. The first attribute of customer is cust_id and—because cust_id has the auto_increment modifier and this is the first row—a 1 is inserted as the cust_id. The value "Marzalla" is then inserted into the second attribute surname, "Dimitria" into firstname, and so on. The number of values inserted must be the same as the number of attributes in the table. To create an INSERT statement in this format, you need to understand the ordering of attributes in the table.

The number i

Table of Contents

Section 2.8.1.1: Current time

Section 2.8.1.2: Creating timestamps with mktime( ) and gmmktime( )