";
foreach ($products as $section => $items)
foreach ($items as $key => $value)
echo "$section:\t$key\t($value)
";
echo "

...

 The output from this code looks like the following:
paper:
paper:
paper:
paper:
pens:
pens:
pens:
misc:
misc:
misc:

copier
inkjet
laser
photo
ball
hilite
marker
tape
glue
clips

(Copier & Multipurpose)
(Inkjet Printer)
(Laser Printer)
(Photographic Paper)
(Ball Point)
(Highlighters)
(Markers)
(Sticky Tape)
(Adhesives)
(Paperclips)

You can directly access a particular element of the array using square brackets, like this:
echo $products['misc']['glue'];

which outputs the value “Adhesives”

...

 Example 6-11 creates the board for a
chess game with the pieces in their starting positions

...

 Creating a multidimensional numeric array
$chessboard = array(
array('r', 'n', 'b',
array('p', 'p', 'p',
array(' ', ' ', ' ',
array(' ', ' ', ' ',
array(' ', ' ', ' ',
array(' ', ' ', ' ',
array(' ', ' ', ' ',
array(' ', ' ', ' ',
array('P', 'P', 'P',
array('R', 'N', 'B',

'k',
'p',
' ',
' ',
' ',
' ',
' ',
' ',
'P',
'K',

echo "
";
foreach ($chessboard as $row)
{
foreach ($row as $piece)

122 | Chapter 6: PHP Arrays

'q',
'p',
' ',
' ',
' ',
' ',
' ',
' ',
'P',
'Q',

'b',
'p',
' ',
' ',
' ',
' ',
' ',
' ',
'P',
'B',

'n',
'p',
' ',
' ',
' ',
' ',
' ',
' ',
'P',
'N',

'r'),
'p'),
' '),
' '),
' '),
' '),
' '),
' '),
'P'),
'R'));

www

...

info
Download at Boykma

...


The key is r=rook, n=knight, b=bishop, k=king, q=queen, and p=pawn

...

as loops walk through the array and display its contents

...

 This loop has two statements within
it, so curly braces enclose them

...

 This loop has a single
statement, so curly braces are not required to enclose it

...



Using Array Functions
You’ve already seen the list and each functions, but PHP comes with numerous other
functions for handling arrays

...

net/manual/en/ref

...

php

...



is_array()
Arrays and variables share the same namespace

...

 If you’re in doubt and your
code needs to check whether a variable is an array, you can use the is_array function
like this:
echo (is_array($fred)) ? "Is an array" : "Is not an array";

Using Array Functions | 123

www

...

info
Download at Boykma

...



count()
Although the each function and foreach

...

 To count
all the elements in the top level of an array, use a command such as the following:
echo count($fred);

Should you wish to know how many elements there are altogether in a multidimensional array, you can use a statement such as:
echo count($fred, 1);

The second parameter is optional and sets the mode to use

...



sort()
Sorting is so common that PHP provides a built-in function

...

 Instead it returns TRUE on success and FALSE
on error and also supports a few flags, but the main two that you might wish to use
force sorting to be made either numerically or as strings, like this:
sort($fred, SORT_NUMERIC);
sort($fred, SORT_STRING);

You can also sort an array in reverse order using the rsort function, like this:
rsort($fred, SORT_NUMERIC);
rsort($fred, SORT_STRING);

shuffle()
There may be times when you need the elements of an array to be put in random order,
such as when creating a game of playing cards:
shuffle($cards);

Like sort, shuffle acts directly on the supplied array and returns TRUE on success or
FALSE on error

...

it-ebooks

...

Com

explode()
This is a very useful function with which you can take a string containing several items
separated by a single character (or string of characters) and then place each of these
items into an array

...


Example 6-12

...


Example 6-13 shows a slight variation

...

 Exploding a string delimited with *** into an array
$temp = explode('***', "A***sentence***with***asterisks");
print_r($temp);
?>

The code in Example 6-13 prints out the following:
Array
(
[0]
[1]
[2]
[3]
)

=>
=>
=>
=>

A
sentence
with
asterisks

extract()
Sometimes it can be convenient to turn the key/value pairs from an array into PHP
variables

...



Using Array Functions | 125

www

...

info
Download at Boykma

...

 If the variables were sent using the GET method, they
will be placed in an associative array called $_GET, and if they were sent using POST, they
will be placed in an associative array called $_POST

...

 However, sometimes you just want to store the values sent into variables for later use

...


Be careful with this approach, though, because if any extracted variables conflict with
ones that you have already defined, your existing values will be overwritten

...

 I strongly recommend that you use this
version of the function when handling the $_GET and $_POST arrays, or any other array
whose keys could be controlled by the user, because malicious users could submit keys
chosen deliberately to overwrite commonly used variable names and compromise your
website

...

 Example 6-14 shows how you might use this
function

...

 Using the compact function
$fname
$sname
$address
$city
$country

=
=
=
=
=

"Elizabeth";
"Windsor";
"Buckingham Palace";
"London";
"United Kingdom";

$contact = compact('fname', 'sname', 'address', 'city', 'country');
print_r($contact);
?>

The result of running Example 6-14 is:
Array
(

126 | Chapter 6: PHP Arrays

www

...

info
Download at Boykma

...

 This is because compact is looking for an array of
variable names

...


Example 6-15

...


If you copy and paste the print_r line of code, you only need to alter the variables
named there for a quick print out of a group of variables’ values

...

as construct or the each function walk through an array, they keep
an internal PHP pointer that makes a note of which element of the array they should
return next

...

 Examples of how to use this function
are:
reset($fred);
// Throw away return value
$item = reset($fred); // Keep first element of the array in $item

Using Array Functions | 127

www

...

info
Download at Boykma

...

 In the next chapter,
we’ll look at using PHP for common, practical tasks

...



128 | Chapter 6: PHP Arrays

www

...

info
Download at Boykma

...

 This chapter builds on
your new programming skills to teach you some common but important practical tasks

...

 You’ll also find out how to create and otherwise modify files,
including those uploaded by users

...

 Together these topics will extend your understanding of both practical PHP programming and developing international web standards

...

 But a much more powerful function, printf, controls the format of the output
by letting you put special formatting characters in a string

...

 For instance, the following example uses the %d conversion
specifier to display the value 3 in decimal:
printf("There are %d items in your basket", 3);

If you replace the %d with %b, the value 3 would be displayed in binary (11)

...



129

www

...

info
Download at Boykma

...

 The printf conversion specifiers
Specifier

Conversion action on argument arg

Example (for an arg of 123)

%

Display a % character (no arg is required)

%

b

Display arg as a binary integer

1111011

c

Display ASCII character for the arg

{

d

Display arg as a signed decimal integer

123

e

Display arg using scientific notation

1

...

000000

o

Display arg as an octal integer

173

s

Display arg as a string

123

u

Display arg as an unsigned decimal

123

x

Display arg in lowercase hexadecimal

7b

X

Display arg in uppercase hexadecimal

7B

You can have as many specifiers as you like in a printf function, as long as you pass a
matching number of arguments, and as long as each specifier is prefaced by a % symbol

...

 I’m 33 years
old, which is 21 in hexadecimal”:
printf("My name is %s

...


A more practical example of printf sets colors in HTML using decimal

...

 An easy solution is:
printf("Hello", 65, 127, 245);

Check the format of the color specification between the apostrophes ('') carefully

...

 Then come three %X
format specifiers, one for each of your numbers

...

 For instance, if you stored values for your colors in the three variables $r, $g,
and $b, you could create a darker color with:
printf("Hello", $r-20, $g-20, $b-20);

130 | Chapter 7: Practical PHP

www

...

info
Download at Boykma

...

 For example, amounts of currency are usually displayed with only two
digits of precision

...

42/12, which results in 10

...

 To ensure that such values are
correctly stored internally, but displayed with only two digits of precision, you can
insert the string “

...

2f", 123

...

29

But you actually have even more control than that, because you can also specify whether
to pad output with either zeros or spaces by prefacing the specifier with certain values

...


Example 7-1

...

42 / 12);
// Pad to 15 spaces, fill with zeros
printf("The result is $%015f\n", 123

...

2f\n", 123

...

2f\n", 123

...

2f\n", 123

...

285000
$00000010

...

29
$000000000010

...

29

The way it works is simple if you go from right to left (see Table 7-2)

...

 In this case, it is f for floating
point

...


Using printf | 131

www

...

info
Download at Boykma

...

 In the
previous example, this is 15 characters

...


• The leftmost parameter allowed before the % symbol is a 0, which is ignored unless
a padding value has been set, in which case the output is padded with zeros instead
of spaces

...


• On the left is the % symbol, which starts the conversion

...

 Conversion specifier components
Start conversion

Pad character

Number of pad
characters

%

Display
precision

15

Conversion
specifier

Examples

f

10

...

4

f

000000000010

...

2

f

########10

...

 Example 7-2 shows various examples

...

 String padding
echo "
"; // Enables viewing of the spaces
$h = 'House';
printf("[%s]\n",
printf("[%10s]\n",
printf("[%-10s]\n",
printf("[%010s]\n",
printf("[%'#10s]\n\n",

$h);
$h);
$h);
$h);
$h);

//
//
//
//
//

Standard string output
Right justify with spaces
Left justify with spaces
Zero padding
Use the custom padding character '#'

$d = 'Doctor House';
printf("[%10

...

6s]\n",
$d); // Left justify, cutoff of 6 characters
printf("[%-'@10

...

 The output from this example is as follows:

132 | Chapter 7: Practical PHP

www

...

info
Download at Boykma

...


Table 7-3 shows a breakdown of the components available to string conversion
specifiers

...

 String conversion specifier components
Start
conversion

Left or right
justify

Padding
character

Number of pad
characters

Cutoff

%

Conversion
specifier

Examples

s

%

-

%

'#

8



...

 This is where the sprintf function comes in

...


You might use it simply to make a conversion, as in the following example, which
returns the hexadecimal string value for the RGB color group 65, 127, 245 in
$hexstring:
$hexstring = sprintf("%X%X%X", 65, 127, 245);

Or you may wish to store output ready to display later on:
$out = sprintf("The result is: $%

...

42 / 12);
echo $out;

Date and Time Functions
To keep track of the date and time, PHP uses standard Unix timestamps, which are
simply the number of seconds since the start of January 1, 1970

...

it-ebooks

...

Com

Because the value is stored as seconds, to obtain the timestamp for this time next week,
you would use the following, which adds 7 days × 24 hours × 60 minutes × 60 seconds
to the returned value:
echo time() + 7 * 24 * 60 * 60;

If you wish to create a timestamp for a given date, you can use the mktime function

...

1

...

 Well,
it’s because the original developers of Unix chose the start of the year
1970 as the base date that no programmer should need to go before!
Luckily, because as of version 5

...

0, PHP supports systems using a
signed 32-bit integer for the timestamp, dates 1901 to 2038 are allowed
on them

...

 We have to
hope it will all be solved well before we get too close to that date

...

 The format is as
follows:
date($format, $timestamp);

The parameter $format should be a string containing formatting specifiers as detailed
in Table 7-4 and $timestamp should be a Unix timestamp

...

net/manual/en/function

...

php

...

it-ebooks

...

Com

Table 7-4

...

it-ebooks

...

Com

Date Constants
There are a number of useful constants that you can use with the date command to
return the date in specific formats

...

 Some of the more commonly used
constants are:
DATE_ATOM

This is the format for Atom feeds

...


DATE_COOKIE

This is the format for cookies set from a web server or JavaScript

...


DATE_RSS

This is the format for RSS feeds

...


DATE_W3C

This is the format for “World Wide Web Consortium

...


The complete list can be found at http://php

...

datetime

...



Using checkdate
You’ve seen how to display a valid date in a variety of formats

...


For example, if February 30 of any year is input, it will always be an invalid date

...

 As it stands, it will find the given
date invalid

...

 Checking for the validity of a date
$month = 9;
$day
= 31;
$year = 2012;

// September (only has 30 days)
// 31st
// 2012

if (checkdate($month, $day, $year)) echo "Date is valid";
else echo "Date is invalid";
?>

136 | Chapter 7: Practical PHP

www

...

info
Download at Boykma

...

 Sometimes it can be quicker and more convenient to directly access
files on the hard disk

...


First, though, a note about file naming

...

 For example, Windows and Mac OS X filenames are not case-sensitive, but
Linux and Unix ones are

...



Checking Whether a File Exists
To determine whether a file already exists, you can use the file_exists function, which
returns either TRUE or FALSE, and is used like this:
if (file_exists("testfile

...

txt doesn’t exist, so let’s create it and write a few lines to it

...

php

...

 Creating a simple text file

...

txt", 'w') or die("Failed to create file");
$text = <<<_END
Line 1
Line 2
Line 3
_END;
fwrite($fh, $text) or die("Could not write to file");
fclose($fh);
echo "File 'testfile

...

txt’ written successfully”

...

 Otherwise, the file testfile

...

php program

...

it-ebooks

...

Com

Line 1
Line 2
Line 3

This simple example shows the sequence that all file handling takes:
1

...

 This is done through a call to fopen

...

 Then you can call other functions; here we write to the file (fwrite), but you can
also read from an existing file (fread or fgets) and do other things

...

 Finish by closing the file (fclose)

...


Every open file requires a file resource so that PHP can access and manage it

...

 Thereafter, each file handling function that accesses the opened file, such as fwrite or fclose, must be passed $fh as a parameter to
identify the file being accessed

...


Upon failure, FALSE will be returned by fopen

...

 A web application would never abort in this crude
way (you would create a web page with an error message instead), but this is fine for
our testing purposes

...

 It is simply the character w, which tells
the function to open the file for writing

...

 Be careful when playing around with these functions: if the file already exists, the
w mode parameter causes the fopen call to delete the old contents (even if you don’t
write anything new!)

...


Table 7-5

...

 Return
FALSE if the file doesn’t already exist

...

 Return
FALSE if the file doesn’t already exist

...

 If the file doesn’t exist, attempt to create it

...

 If the file doesn’t exist, attempt to create it

...

it-ebooks

...

Com

Mode

Action

Description

'a'

Append to file end

Open for writing only; place the file pointer at the end of the file

...



'a+'

Append to file end and allow reading

Open for reading and writing; place the file pointer at the end of the file

...



Reading from Files
The easiest way to read from a text file is to grab a whole line through fgets (think of
the final s as standing for “string”), as in Example 7-5

...

 Reading a file with fgets
$fh = fopen("testfile

...


Example 7-6

...

txt", 'r') or
die("File does not exist or you lack permission to open it");
$text = fread($fh, 3);
fclose($fh);
echo $text;
?>

I’ve requested three characters in the fread call, so the program displays the following:
Lin

The fread function is commonly used with binary data

...



Copying Files
Let’s try out the PHP copy function to create a clone of testfile

...

 Type in Example 7-7 and save it as copyfile

...



File Handling | 139

www

...

info
Download at Boykma

...

 Copying a file

...

txt', 'testfile2

...

txt'";
?>

If you check your folder again, you’ll see that you now have the new file testfile2

...

 By the way, if you don’t want your programs to exit on a failed copy attempt, you
could try the alternate syntax in Example 7-8

...

 Alternate syntax for copying a file

...

txt', 'testfile2

...

txt'";
?>

Moving a File
To move a file, rename it with the rename function, as in Example 7-9

...

 Moving a file

...

txt', 'testfile2

...

new'";
?>

You can use the rename function on directories, too

...



Deleting a File
Deleting a file is just a matter of using the unlink function to remove it from the file
system, as in Example 7-10

...

 Deleting a file

...

new')) echo "Could not delete file";
else echo "File 'testfile2

...

 For example, if you are deleting a file based on user input, you
must make absolutely certain it is a file that can be safely deleted and
that the user is allowed to delete it

...

it-ebooks

...

Com

As with moving a file, a warning message will be displayed if the file doesn’t exist, which
you can avoid by using file_exists to first check for its existence before calling unlink

...


You can use one of the append write modes (see Table 7-5), or you can simply open a
file for reading and writing with one of the other modes that supports writing, and
move the file pointer to the correct place within the file that you wish to write to or
read from

...

 It is not the same as the file handle (as stored in the
variable $fh in Example 7-4), which contains details about the file being accessed

...

php

...


Example 7-11

...

php
$fh = fopen("testfile

...

txt' successfully updated";
?>

What this program does is open testfile

...

 It then uses the fgets
function to read in a single line from the file (up to the first line feed)

...

 The resulting file now looks like this:
Line
Line
Line
Line

1
2
3
1

The first line has successfully been copied and then appended to the file’s end

...

 The SEEK_END tells the function to move the file pointer to
the end of the file and the 0 parameter tells it how many positions it should then be
moved backward from that point

...



File Handling | 141

www

...

info
Download at Boykma

...

 The SEEK_SET option tells the function to set the file pointer to the exact
position given by the preceding parameter

...



Therefore, if the file pointer is currently at position 18, the following call will move it
to position 23:
fseek($fh, 5, SEEK_CUR);

Although this is not recommended unless you have very specific reasons for it, it is even
possible to use text files such as this (but with fixed line lengths) as simple flat-file
databases

...

 Records can also be deleted by overwriting
them with zero characters, and so on

...

 If more than one person
tries to write to a file simultaneously, it can become corrupted

...

 To handle simultaneous users, it’s necessary to use the file locking
flock function

...

 So, whenever your programs use write access on files that
may be accessed concurrently by multiple users, you should also add file locking to
them, as in Example 7-12, which is an updated version of Example 7-11

...

 Updating a file with file locking
$fh = fopen("testfile

...

txt' successfully updated";
?>

There is a trick to file locking to preserve the best possible response time for your
website visitors: perform it directly before you make a change to a file, and then unlock
it immediately afterward

...

 This is why the calls to flock in Example 7-12 are
directly before and after the fwrite call

...

it-ebooks

...

Com

The first call to flock sets an exclusive file lock on the file referred to by $fh using the
LOCK_EX parameter:
flock($fh, LOCK_EX);

From this point onward, no other processes can write to (or even read from) the file
until the lock is released by using the LOCK_UN parameter, like this:
flock($fh, LOCK_UN);

As soon as the lock is released, other processes are allowed access again to the file

...


However, did you notice that the call to request an exclusive lock is nested as part of
an if statement? This is because flock is not supported on all systems and therefore it
is wise to check whether you successfully secured a lock, just in case one could not be
obtained

...


This means that it locks out only other processes that call the function

...


By the way, implementing file locking and then accidentally leaving it out in one section
of code can lead to an extremely hard-to-locate bug

...

 Also, when using a multithreaded server like
ISAPI, you may not be able to rely on flock to protect files against other
PHP scripts running in parallel threads of the same server instance

...



Reading an Entire File
A handy function for reading in an entire file without having to use file handles is
file_get_contents

...


Example 7-13

...

txt");
echo "
"; // Terminates pre tag
?>

File Handling | 143

www

...

info
Download at Boykma

...

 The O’Reilly home page grabbed with file_get_contents

But the function is actually a lot more useful than that, because you can also use it to
fetch a file from a server across the Internet, as in Example 7-14, which requests the
HTML from the O’Reilly home page, and then displays it as if the page itself had been
surfed to

...


Example 7-14

...

com");
?>

Uploading Files
Uploading files to a web server is a subject area that seems daunting to many people,
but it actually couldn’t be much easier

...

 To see how this works, type in the program in Example 7-15 and save
it as upload

...

 When you run it, you’ll see a form in your browser that lets you upload
a file of your choice

...

it-ebooks

...

Com

Example 7-15

...

php

...

php' enctype='multipart/form-data'>
Select File: 


_END;
if ($_FILES)
{
$name = $_FILES['filename']['name'];
move_uploaded_file($_FILES['filename']['tmp_name'], $name);
echo "Uploaded image '$name'
";
}
echo "";
?>

Let’s examine this program a section at a time

...


Next we come to the form that selects the POST method of form submission, sets the
target for posted data to the program upload

...


With the form set up, the next lines display the prompt “Select File:” and then request
two inputs

...


The second requested input is just a Submit button that is given the label “Upload”
(replacing the default button text of “Submit Query”)

...


This short program shows a common technique in web programming in which a single
program is called twice: once when the user first visits a page, and again when the user
presses the Submit button

...

 Therefore a quick check to see
whether $_FILES has anything in it is sufficient to determine whether the user has uploaded a file

...


The first time the user visits the page, before uploading a file, $_FILES is empty, so the
program skips this block of code

...



File Handling | 145

www

...

info
Download at Boykma

...

 Uploading an image as form data

Once the program realizes that a file was uploaded, the actual name, as read from the
uploading computer, is retrieved and placed into the variable $name

...

 This is done using the move_uploaded_file
function, passing it the original name of the file, with which it is saved to the current
directory

...


If you run this program and receive warning messages such as “Permission denied” for the move_uploaded_file function call, then you may not
have the correct permissions set for the folder in which the program is
running

...


Table 7-6

...

g

...

jpg)

$_FILES['file']['type']

The content type of the file (e

...

, image/jpeg)

146 | Chapter 7: Practical PHP

www

...

info
Download at Boykma

...

 Table 7-7 shows some of the more frequently used
types that turn up in $_FILES['file']['type']

...

 Some common Internet media content types
application/pdf

image/gif

multipart/form-data

text/xml

application/zip

image/jpeg

text/css

video/mpeg

audio/mpeg

image/png

text/html

video/mp4

audio/x-wav

image/tiff

text/plain

video/quicktime

Validation
Hopefully it now goes without saying (although I’ll do so anyway) that form-data validation is of the utmost importance, due to the possibility of users attempting to hack
into your server

...


Taking all these things into account, Example 7-16, upload2

...

php

...

 A more secure version of upload

...

php
echo <<<_END

default:
$ext = '';
break;

}
if ($ext)
{
$n = "image

...


As with the previous version, this if line checks whether any data was actually posted,
but there is now a matching else near the bottom of the program that echoes a message
to screen when nothing has been uploaded

...

 Instead a switch statement is used to check the
uploaded content type against the four types of image this program supports

...

 Should
no match be found, the file uploaded was not of an accepted type and the variable
$ext is set to the empty string ""

...

 This means that the program is in full control over the name of the file
to be created, as it can be only one of image

...

gif, image

...

tif

...

 It moves the uploaded temporary
image to its new location and then displays it, while also displaying the old and new
image names

...



After the if statement there is a matching else, which is executed only if an unsupported image type was uploaded, in which case it displays an appropriate error message

...

it-ebooks

...

Com

When you write your own file uploading routines, I strongly advise you to use a similar
approach and have prechosen names and locations for uploaded files

...

 If this means that more than one user could end up having a file uploaded
with the same name, you could prefix such files with their usernames, or save them to
individually created folders for each user

...

]", "", $name);

This leaves only the characters A–Z, a–z, 0–9 and periods in the string $name, and strips
out everything else

...

]", "", $name));

Sometimes you may encounter the media type of image/pjpeg, which
indicates a progressive jpeg, but you can safely add this to your code as
an alias of image/jpeg, like this:
case 'image/pjpeg':
case 'image/jpeg': $ext = 'jpg'; break;

System Calls
Sometimes PHP will not have the function you need to perform a certain action, but
the operating system it is running on may

...


For example, to quickly view the contents of the current directory, you can use a program such as Example 7-17

...

 On Linux, Unix, or Mac OS X, comment out or remove the
first line and uncomment the second to use the ls system command

...

php and call it up in your browser

...

 Executing a system command

...

it-ebooks

...

Com

{
}
?>

echo "
";
foreach($output as $line) echo "$line\n";

Depending on the system you are using, the result of running this program will look
something like this (from a Windows dir command):
Volume in drive C is HP
Volume Serial Number is E67F-EE11
Directory of C:\web
20/01/2011

10:34


...


19/01/2011 16:26
236 maketest

...

php
20/01/2011 08:04
13,741 smiley

...

php
19/01/2011 16:59
35 testfile

...

php
6 File(s)
15,150 bytes
2 Dir(s) 382,907,748,352 bytes free

exec takes three arguments:

1

...

 An array in which the system will put the output from the command (in the previous case, $output)
3

...


You should also note the use of the escapeshellcmd function

...


The system calling functions are typically disabled on shared web hosts
as they pose a security risk

...

 Also, going to the system is relatively slow and you
need to code two implementations if your application is expected to run
on both Windows and Linux/Unix systems

...

it-ebooks

...

Com

XHTML
I’ve used some elements of XHTML (eXtensible Hypertext Markup Language) already
in this book, although you may not have realized it

...

 But what’s the difference
between the two markup languages?
Well, not a lot at first glance, but XHTML improves on HTML by clearing up a lot of
little inconsistencies that make it hard to process

...



The Benefits of XHTML
XHTML documents can be quickly processed by any program that can handle XML
files

...

 The tighter syntax required by XHTML is a big factor
in helping this cross-platform compatibility

...



XHTML Versions
The XHTML standard is constantly evolving, and there are a few versions in use:
XHTML 1

...

01 standard but requires the use
of XML syntax

...

1
This version has not been widely adopted, although it is largely compatible with
XHTML 1

...

 A major feature of this version is that CSS is used to
control browser presentation

...

2
This version is only in the proposal stage and is not currently implemented

...

0
This version of XHTML makes a totally clean break from previous versions and
also from HTML 4

...



XHTML | 151

www

...

info
Download at Boykma

...

0 is the main version that you need to understand

...

0 specifications, because
XHTML 1

...



What’s Different?
The following XHTML rules differentiate it from HTML:
• All tags must be closed by another tag

...

 So, for example, a tag such as  needs to be changed into


...

 And no, you can’t replace them with 


...

 Therefore the string My first name is
Robin is not allowed, because the opening  has been closed before
the 

...


• All tag attributes must be enclosed in quotation marks

...

php> you should instead use


Waiting for the next line of a string started with a single quote

">

Waiting for the next line of a string started with a double quote

`>

Waiting for the next line of a string started with a back tick

/*>

Waiting for the next line of a comment started with /*

Canceling a command
If you are partway through entering a command and decide you don’t wish to execute
it after all, whatever you do don’t press Ctrl-C! That will close the program

...

 Example 8-1 shows how to use it

...

 Canceling a line of input
meaningless gibberish to mysql \c

Accessing MySQL via the Command Line | 163

www

...

info
Download at Boykma

...

 Without the \c, it would have displayed an error message

...

 Example 8-2 shows the right way to do this

...

 Canceling input from inside a string
this is "meaningless gibberish to mysql" \c

Also note that using \c after a semicolon will not work, as it is then a new statement

...

 The commands you’ll use most often are listed in Table 8-3

...

 A selection of common MySQL commands (and/or shorthand forms where available)
Command

Parameter(s)

Meaning

ALTER

DATABASE, TABLE

Alter DATABASE or TABLE

BACKUP

TABLE

Back up TABLE
Cancel input

\c
CREATE

DATABASE, TABLE,

Create DATABASE or TABLE

DELETE

(expression with TABLE & ROW)

Delete ROW from TABLE

DESCRIBE

TABLE

Describe the TABLE'S columns

DROP

DATABASE,TABLE

Delete DATABASE or TABLE
Exit

EXIT (CTRL-C)
GRANT

(user details)

Change user privileges

HELP (\h, \?)

item

Display help on item

INSERT

(expression with data)

Insert data

LOCK

TABLE(s)

Lock TABLE(s)
Same as EXIT

QUIT (\q)
RENAME

TABLE

Rename TABLE

SHOW

(too many items to list)

List item’s details

SOURCE

filename

Execute commands from filename
Display current status

STATUS (\s)
TRUNCATE

TABLE

Empty TABLE

UNLOCK

table(s)

Unlock TABLE(s)

UPDATE

(expression with data)

Update an existing record

USE

database

Use database

164 | Chapter 8: Introduction to MySQL

www

...

info
Download at Boykma

...

 CREATE, create, and CrEaTe all
mean the same thing

...


• Table names are case-sensitive on Linux and Mac OS X, but case-insensitive on
Windows

...

 The recommended style is to use lowercase for tables

...

 Otherwise, get the ball rolling by issuing the following command to
create a new database called publications:
CREATE DATABASE publications;

A successful command will return a message that doesn’t mean much yet—“Query
OK, 1 row affected (0

...

 Now that you’ve created
the database, you want to work with it, so issue:
USE publications;

You should now see the message Database changed and will then be set to proceed with
the following examples

...


To create a user, issue the GRANT command, which takes the following form (don’t type
this in—it’s not an actual working command):
GRANT PRIVILEGES ON database

...

object part

...


Table 8-4

...

*

All databases and all their objects

database

...

object

Only the database called database and its object called object
Accessing MySQL via the Command Line | 165

www

...

info
Download at Boykma

...

* TO 'jim' IDENTIFIED BY 'mypasswd';

What this does is allow the user jim@localhost (the localhost is implied by omitting
it) full access to the publications database using the password mypasswd

...

 See Table 8-5 for the correct command for your
operating system

...


Table 8-5

...

0\mysql\bin\mysql" -u jim -p

Mac OS X

/Applications/MAMP/Library/bin/mysql -u jim -p

Linux

mysql -u jim -p

All you now have to do is enter your password when prompted and you will be logged
in

...

 But this is considered a poor practice, because if other people are logged in to your system, there may
be ways for them to look at the command you entered and find out your password

...

 There are a whole range of
privileges you can choose to grant if you are not granting all privileges

...

mysql

...

0/en/grant

...


You also need to be aware that if you create a new user but do not specify
an IDENTIFIED BY clause, the user will have no password, a situation that
is very insecure and should be avoided

...

 So make sure that database is in use by typing the following
(replacing publications with the name of your database if it is different):
USE publications;

166 | Chapter 8: Introduction to MySQL

www

...

info
Download at Boykma

...

 Creating a table called classics
CREATE TABLE classics (
author VARCHAR(128),
title VARCHAR(128),
type VARCHAR(16),
year CHAR(4)) ENGINE MyISAM;

You could also issue this command on a single line like this:
CREATE TABLE classics (author VARCHAR(128), title
VARCHAR(128), type VARCHAR(16), year CHAR(4)) ENGINE MyISAM;

but MySQL commands can be long and complicated, so I recommend
a single line at a time until you are comfortable with longer ones

...

 If you see an error message instead, check your
syntax carefully

...

 In case you are wondering, the ENGINE MyISAM tells MySQL the type of database
engine to use for this table

...


Example 8-4

...

03 sec)
mysql> DESCRIBE classics;
+--------+--------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| author | varchar(128) | YES |
| NULL
|
|
| title | varchar(128) | YES |
| NULL
|
|
| type
| varchar(16) | YES |
| NULL
|
|
| year
| char(4)
| YES |
| NULL
|
|
+--------+--------------+------+-----+---------+-------+
4 rows in set (0

...

it-ebooks

...

Com

The DESCRIBE command is an invaluable debugging aid when you need to ensure that
you have correctly created a MySQL table

...

 Let’s look at each of
the headings in detail:
Field

The name of each field or column within a table

...


Null

Whether a field is allowed to contain a value of NULL

...

 The Key heading shows what type of key (if any) has been applied

...


Extra

Additional information, such as whether a field is set to auto-increment

...

 The term VARCHAR stands for
VARiable length CHARacter string and the command takes a numeric value that tells
MySQL the maximum length allowed to a string stored in this field

...

 The downside is that if you ever attempt to assign a
string value longer than the length allowed, it will be truncated to the maximum length
declared in the table definition

...

 The parameter of 4 allows for four bytes of data,
supporting all years from −999 to 9999

...



168 | Chapter 8: Introduction to MySQL

www

...

info
Download at Boykma

...

 This is because
MySQL stores the year in a single byte for reasons of efficiency, but it
also means that only 256 years are available, and the publication years
of the titles in the classics table are well before this

...


The difference is that every string in a CHAR field has the specified size

...

 A VARCHAR field does not pad the text; it lets the
size of the field vary to fit the text that is inserted

...

 So CHAR is slightly more efficient if
the sizes are similar in all records, whereas VARCHAR is more efficient if sizes can vary a
lot and get large

...



The CHAR data type
Table 8-6 lists the CHAR data types

...

 As the table shows, each type
also has a built-in maximum

...

 MySQL’s CHAR data types
Data type

Bytes used

Examples

CHAR(n)

Exactly n (<= 255)

CHAR(5) “Hello” uses 5 bytes
CHAR(57) “New York” uses 57 bytes

VARCHAR(n)

Up to n (<= 65535)

VARCHAR(100) “Greetings” uses 9 bytes
VARCHAR(7) “Morning” uses 7 bytes

The BINARY data type
The BINARY data type is used for storing strings of full bytes that do not have an associated character set

...


Table 8-7

...

it-ebooks

...

Com

The TEXT and VARCHAR data types
The differences between TEXT and VARCHAR are small:
• Prior to version 5

...

3, MySQL would remove leading and trailing spaces from
VARCHAR fields

...


• MySQL indexes only the first n characters of a TEXT column (you specify n when
you create the index)

...

 If you will never search more than a certain number
of leading characters in a field, you should probably use a TEXT data type (see Table 8-8)

...

 MySQL’s TEXT data types
Data type

Bytes used

Attributes

TINYTEXT(n)

Up to n (<= 255)

Treated as a string with a character set

TEXT(n)

Up to n (<= 65535)

Treated as a string with a character set

MEDIUMTEXT(n)

Up to n (<= 16777215)

Treated as a string with a character set

LONGTEXT(n)

Up to n (<= 4294967295)

Treated as a string with a character set

The BLOB data type
The term BLOB stands for Binary Large OBject and therefore, as you would think, the
BLOB data type is most useful for binary data in excess of 65,536 bytes in size

...


Table 8-9

...

 Although the most memory that a numeric field can use up is
eight bytes, you are well advised to choose the smallest data type that will adequately
handle the largest value you expect

...


Table 8-10 lists the numeric data types supported by MySQL and the ranges of values
they can contain

...

it-ebooks

...

Com

one with a possible range from a negative value, through zero, to a positive one, and
an unsigned one has a value ranging from zero to a positive one

...

 Note that floating-point
values (of any precision) may only be signed

...

 MySQL’s numeric data types
Data type

Bytes used

Minimum value (Signed/Unsigned)

Maximum value (Signed/Unsigned)

TINYINT

1

−128

127

0

255

SMALLINT

2

−32768

32767

0

65535

−8388608

8388607

0

16777215

−2147483648

2147483647

0

4294967295

−9223372036854775808

9223372036854775807

0

18446744073709551615

−3

...

402823466E+38

(no unsigned)

(no unsigned)

−1

...

7976931348623157E+308

(no unsigned)

(no unsigned)

MEDIUMINT

3

INT or INTEGER

4

BIGINT

8

FLOAT
DOUBLE or REAL

4

8

To specify whether a data type is signed or unsigned, use the UNSIGNED qualifier

...

 It may seem counterintuitive, but
what the number actually represents is the display width of the data in the field when
it is retrieved

...

it-ebooks

...

Com

characters long

...



DATE and TIME
The main remaining data types supported by MySQL relate to the date and time and
can be seen in Table 8-11

...

 MySQL’s DATE and TIME data types
Data type

Time/date format

DATETIME

'0000-00-00 00:00:00'

DATE

'0000-00-00'

TIMESTAMP

'0000-00-00 00:00:00'

TIME

'00:00:00'

YEAR

0000 (Only years 0000 and 1901–2155)

The DATETIME and TIMESTAMP data types display the same way

...


TIMESTAMP is useful, however, because you can let MySQL set the value for you

...


You can also have MySQL update a TIMESTAMP column each time you change a row

...

 You could do this in your program by carefully checking the data you enter
and making sure that there is at least one value that differs in any two rows, but this
approach is error-prone and works only in certain circumstances

...

 Likewise, the year of publication
will also be frequently duplicated, and so on

...


The general solution is to use an extra row just for this purpose

...


As its name implies, a column given this data type will set the value of its contents to
that of the column entry in the previously inserted row, plus 1

...

 Adding the auto-incrementing column id
ALTER TABLE classics ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY;

172 | Chapter 8: Introduction to MySQL

www

...

info
Download at Boykma

...


ALTER operates on an existing table, and can add, change, or delete columns

...


NOT NULL

Ensures that every column has a value

...

 But that would allow duplicates,
which would violate the whole reason for this column’s existence

...


AUTO_INCREMENT

Causes MySQL to set a unique value for this column in every row, as described
earlier

...


KEY

An auto-increment column is useful as a key, because you will tend to search
for rows based on this column

...


Each entry in the column id will now have a unique number, with the first starting at
1 and the others counting upward from there

...


Rather than applying the column retroactively, you could have included it by issuing
the CREATE command in slightly different format

...

 Check the final line in particular

...

 Adding the auto-incrementing id column at table creation
CREATE TABLE classics (
author VARCHAR(128),
title VARCHAR(128),
type VARCHAR(16),
year CHAR(4),
id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY) ENGINE MyISAM;

If you wish to check whether the column has been added, use the following command
to view the table’s columns and data types:
DESCRIBE classics;

Now that we’ve finished with it, the id column is no longer needed, so if you created it
using Example 8-5, you should now remove the column using the command in Example 8-7

...

it-ebooks

...

Com

Figure 8-4

...

 Removing id column
ALTER TABLE classics DROP id;

Adding data to a table
To add data to a table, use the INSERT command

...


Example 8-8

...

 Once all lines have
been entered, type the following command, which will display the table’s contents

...

 Suffice it to say that as typed, it
will display all the data you just entered

...

it-ebooks

...

Com

Let’s go back and look at how we used the INSERT command

...

 Then, within parentheses,
the four column names are listed—author, title, type, and year—all separated by commas

...


The second line of each INSERT command contains the keyword VALUES followed by
four strings within parentheses, and separated by commas

...

 (As always,
my choice of where to break the lines was arbitrary

...

 If you accidentally listed the columns in a different order from the
data, the data would go into the wrong columns

...



Renaming a table
Renaming a table, like any other change to the structure or meta-information about a
table, is achieved via the ALTER command

...

 So to change the data type of column year from
CHAR(4) to SMALLINT (which requires only two bytes of storage and so will save disk
space), enter the following:
ALTER TABLE classics MODIFY year SMALLINT;

When you do this, if the conversion of data type makes sense to MySQL, it will automatically change the data while keeping the meaning

...



Adding a new column
Let’s suppose that you have created a table and populated it with plenty of data, only
to discover you need an additional column

...

 Here’s how to add the new
column pages, which will be used to store the number of pages in a publication:
ALTER TABLE classics ADD pages SMALLINT UNSIGNED;

Accessing MySQL via the Command Line | 175

www

...

info
Download at Boykma

...

 Adding the new pages column and viewing the table

This adds the new column with the name pages using the UNSIGNED SMALLINT data type,
sufficient to hold a value of up to 65,535—hopefully that’s more than enough for any
book ever published!
And, if you ask MySQL to describe the updated table using the DESCRIBE command, as
follows, you will see the change has been made (see Figure 8-5):
DESCRIBE classics;

Renaming a column
Looking again at Figure 8-5, you may decide that having a column named type can be
confusing, because that is the name used by MySQL to identify data types

...

 That’s because the
CHANGE keyword requires the data type to be specified, even if you don’t intend to change
it, and VARCHAR(16) was the data type specified when that column was initially created
as type

...

it-ebooks

...

Com

Figure 8-6

...

 But, because I don’t want you to have to reenter
all the data for the classics table, let’s quickly create a new table, verify its existence,
and then delete it by typing in the commands in Example 8-9

...


Example 8-9

...

 At that point,
database accesses will get slower and slower with every new row added, because
MySQL has to search through every row whenever a query is issued

...


Of course, you don’t have to search libraries that way, because they have either a card
index system or, most likely, a database of their own

...

it-ebooks

...

Com

because at the expense of a slight overhead in memory and disk space, you can create
a “card index” for a table that MySQL will use to conduct lightning-fast searches

...

 But the decision is not so simple

...

 Also you must decide which
columns require an index, a judgment that requires you to predict whether you will be
searching any of the data in that column

...

 And even when you’ve decided that,
you still have the option of reducing index size by limiting the amount of each column
to be indexed

...

 However, if the pages column created
in the earlier section (“Adding a new column” on page 175) had not been deleted, it
would probably not have needed an index, as most people would be unlikely to search
for books by the number of pages they have

...


Example 8-10

...

 For instance, when MySQL indexes the following title:
The Adventures of Tom Sawyer

It will actually store in the index only the first 20 characters:
The Adventures of To

This is done to minimize the size of the index, and to optimize database access speed

...

 If MySQL finds two indexes with the same contents, it will have to
waste time going to the table itself and checking the column that was indexed to find
out which rows really matched

...

 You can also reindex this column later, when you have a more complete set

178 | Chapter 8: Introduction to MySQL

www

...

info
Download at Boykma

...

 Adding indexes to the classics table

of categories

...


The results of issuing these commands (and a DESCRIBE command to confirm that they
worked) can be seen in Figure 8-7, which shows the key MUL for each column

...



Using CREATE INDEX
An alternative to using ALTER TABLE to add an index is to use the CREATE INDEX command

...

 The format of this command can be seen
in the second line of Example 8-11

...

 These two commands are equivalent
ALTER TABLE classics ADD INDEX(author(20));
CREATE INDEX author ON classics (author(20));

Adding indexes when creating tables
You don’t have to wait until after creating a table to add indexes

...


Therefore, let’s look at a command that creates the table classics with indexes already
in place

...

it-ebooks

...

Com

Example 8-12 is a reworking of Example 8-3 in which the indexes are created at the
same time as the table

...

 If you want to try it out without first deleting
your current classics table, change the word classics in line one to something else like
classics1, then drop classics1 after you have finished with it

...

 Creating the table classics with indexes
CREATE TABLE classics (
author VARCHAR(128),
title VARCHAR(128),
category VARCHAR(16),
year SMALLINT,
INDEX(author(20)),
INDEX(title(20)),
INDEX(category(4)),
INDEX(year)) ENGINE MyISAM;

Primary keys
So far you’ve created the table classics and ensured that MySQL can search it quickly
by adding indexes, but there’s still something missing

...

 The importance of having a key with a unique value for each row
will come up when we start to combine data from different tables

...

 However, I wanted to reserve that
task for a more appropriate column: the internationally recognized ISBN number

...

 Now, bearing in mind that ISBN
numbers are 13 characters long, you might think that the following command would
do the job:
ALTER TABLE classics ADD isbn CHAR(13) PRIMARY KEY;

But it doesn’t

...

 The reason
is that the table is already populated with some data and this command is trying to add
a column with the value NULL to each row, which is not allowed, as all columns using
a primary key index must be unique

...


In our current situation, we have to be a bit sneaky and create the new column without
an index, populate it with data, and then add the index retrospectively using the commands in Example 8-13

...

 Note that this example

180 | Chapter 8: Introduction to MySQL

www

...

info
Download at Boykma

...

 Retrospectively adding a primary key to the classics table

uses the UPDATE and WHERE keywords, which are explained in more detail in the upcoming
section “Querying a MySQL Database” on page 183

...

 Populating the isbn column with data and using a primary key
ALTER TABLE classics ADD isbn CHAR(13);
UPDATE classics SET isbn='9781598184891' WHERE
UPDATE classics SET isbn='9780582506206' WHERE
UPDATE classics SET isbn='9780517123201' WHERE
UPDATE classics SET isbn='9780099533474' WHERE
UPDATE classics SET isbn='9780192814968' WHERE
ALTER TABLE classics ADD PRIMARY KEY(isbn);
DESCRIBE classics;

year='1876';
year='1811';
year='1856';
year='1841';
year='1594';

Once you have typed in these commands, the results should look like the screenshot
in Figure 8-8

...


To have created a primary key when the table classics was created, you could have used
the commands in Example 8-14

...


Example 8-14

...

it-ebooks

...

Com

INDEX(year),
PRIMARY KEY (isbn)) ENGINE MyISAM;

Creating a FULLTEXT index
Unlike a regular index, MySQL’s FULLTEXT allows super-fast searches of entire columns
of text

...


Actually, it’s not strictly true that MySQL stores all the words in a
FULLTEXT index, because it has a built-in list of more than 500 words that
it chooses to ignore because they are so common that they aren’t very
helpful when searching anyway

...

 The list helps MySQL run much more quickly when
performing a FULLTEXT search and keeps database sizes down

...



Here are some things that you should know about FULLTEXT indexes:
• FULLTEXT indexes can be used only with MyISAM tables, the type used by MySQL’s
default storage engine (MySQL supports at least 10 different storage engines)

...


• FULLTEXT indexes can be created for CHAR, VARCHAR, and TEXT columns only

...


• For large data sets, it is much faster to load your data into a table that has no
FULLTEXT index and then create the index than to load data into a table that has an
existing FULLTEXT index

...


Example 8-15

...

 This feature could
really come into its own if you could now add the entire text of these publications to
the database (particularly as they’re out of copyright protection) and they would be
fully searchable

...

AGAINST” on page 188 for a description
of searches using FULLTEXT

...

it-ebooks

...

Com

If you find that MySQL is running slower than you think it should be
when accessing your database, the problem is usually related to your
indexes

...

 Tweaking a table’s indexes will often solve such a problem

...



Querying a MySQL Database
So far we’ve created a MySQL database and tables, populated them with data, and
added indexes to make them fast to search

...



SELECT
As you saw in Figure 8-4, the SELECT command is used to extract data from a table

...

 Let’s now examine SELECT in more detail

...

 For instance, Example 8-16 shows
how to select just the author and title and just the title and isbn

...


Example 8-16

...

 In Example 8-17, it displays the number of rows in the table by passing * as a
parameter, which means “all rows

...


Example 8-17

...

it-ebooks

...

Com

Figure 8-9

...

 For instance, suppose that you want a list of all
authors in the table

...

 But by adding the DISTINCT keyword, you can show each author
just once

...


Example 8-18

...

 Example 8-19 and Figure 8-10
show that the simple SELECT lists Dickens twice, and the command with the DISTINCT
qualifier shows him only once

...

 With and without the DISTINCT qualifier
SELECT author FROM classics;
SELECT DISTINCT author FROM classics;

DELETE
When you need to remove a row from a table, use the DELETE command

...


184 | Chapter 8: Introduction to MySQL

www

...

info
Download at Boykma

...

 Selecting data with and without DISTINCT

Now that you’ve seen the effects of the DISTINCT qualifier, if you typed in Example 8-18, you should remove Little Dorrit by entering the commands in Example 8-20

...

 Removing the new entry
DELETE FROM classics WHERE title='Little Dorrit';

This example issues a DELETE command for all rows whose title column contains the
string ‘Little Dorrit’

...

 So now we’ll spend some time on that clause, which is the heart and
soul of SQL

...

 Example 8-20 returns only the rows where the column exactly matches the string ‘Little Dorrit’, using the equality operator =

...


Example 8-21

...


But we could easily add more books by Mark Twain, in which case the first line would
display all titles he wrote and the second line would continue (because we know the
Indexes | 185

www

...

info
Download at Boykma

...

 Using WHERE with the LIKE qualifier

ISBN is unique) to display The Adventures of Tom Sawyer

...


You can also do pattern matching for your searches using the LIKE qualifier, which
allows searches on parts of strings

...

 When placed before a keyword, % means “anything before”
and after a keyword it means “anything after

...


You can see the results of these commands in Figure 8-11

...

 Using the LIKE qualifier
SELECT author,title FROM classics WHERE author LIKE "Charles%";
SELECT author,title FROM classics WHERE title LIKE "%Species";
SELECT author,title FROM classics WHERE title LIKE "%and%";

The first command outputs the publications by both Charles Darwin and Charles
Dickens because the LIKE qualifier was set to return anything matching the string
Charles followed by any other text

...

 Lastly both Pride and
Prejudice and Romeo and Juliet are returned, because they both matched the string
and anywhere in the column

...



186 | Chapter 8: Introduction to MySQL

www

...

info
Download at Boykma

...

 Restricting the rows returned with LIMIT

LIMIT
The LIMIT qualifier enables you to choose how many rows to return in a query, and
where in the table to start returning them

...

 If you pass it two parameters, the first indicates the offset from the
start of the results where MySQL should start the display, and the second indicates
how many to return

...

”
Example 8-23 includes three commands

...

 The second returns two rows starting at position 1 (skipping the first row)

...


Figure 8-12 shows the results of issuing these three commands

...

 Limiting the number of results returned
SELECT author,title FROM classics LIMIT 3;
SELECT author,title FROM classics LIMIT 1,2;
SELECT author,title FROM classics LIMIT 3,1;

Be careful with the LIMIT keyword, because offsets start at zero, but the
number of rows to return starts at 1

...



Indexes | 187

www

...

info
Download at Boykma

...

 Using MATCH

...

AGAINST
The MATCH

...


With it, you can make natural language searches as you would in an Internet search
engine

...

= or WHERE

...

AGAINST lets you enter
multiple words in a search query and checks them against all words in the FULLTEXT
columns

...


Assuming that you have added a FULLTEXT index to the author and title columns, enter
the three queries shown in Example 8-24

...

 Because and is a stopword, MySQL will ignore it
and the query will always produce an empty set—no matter what is stored in the columns

...

 And the last query applies the
same kind of search for the words tom and sawyer

...


Example 8-24

...

 AGAINST on FULLTEXT indexes
SELECT
WHERE
SELECT
WHERE
SELECT
WHERE

author,title FROM classics
MATCH(author,title) AGAINST('and');
author,title FROM classics
MATCH(author,title) AGAINST('old shop');
author,title FROM classics
MATCH(author,title) AGAINST('tom sawyer');

188 | Chapter 8: Introduction to MySQL

www

...

info
Download at Boykma

...

 Using MATCH

...

IN BOOLEAN MODE

MATCH

...

IN BOOLEAN MODE
If you wish to give your MATCH

...


This changes the effect of the standard FULLTEXT query so that it searches for any combination of search words, instead of requiring all search words to be in the text

...


Boolean mode also allows you to preface search words with a + or - sign to indicate
whether they must be included or excluded

...

” A minus sign means, “This word must not be present; its presence
disqualifies the row from being returned

...

 The first asks for all rows
containing the word charles and not the word species to be returned

...

 Figure 8-14 shows the results of these queries

...


There is something of interest to note in the second query: the stopword
of is part of the search string, but is still used by the search because the
double quotation marks override stopwords

...

it-ebooks

...

Com

Figure 8-15

...

 Using MATCH

...

IN BOOLEAN MODE
SELECT author,title FROM classics
WHERE MATCH(author,title)
AGAINST('+charles -species' IN BOOLEAN MODE);
SELECT author,title FROM classics
WHERE MATCH(author,title)
AGAINST('"origin of"' IN BOOLEAN MODE);

UPDATE

...

 If you wish to change the
contents of one or more fields, you need to first narrow in on just the field or fields to
be changed, in much the same way you use the SELECT command

...

SET in two different ways

...


Example 8-26

...

SET
UPDATE
WHERE
UPDATE
WHERE

classics SET author='Mark Twain (Samuel Langhorne Clemens)'
author='Mark Twain';
classics SET category='Classic Fiction'
category='Fiction';

In the first query Mark Twain’s real name of Samuel Langhorne Clemens was appended
to his pen name in brackets, which affected only one row

...


When performing an update you can also make use of the qualifiers you have already
seen, such as LIMIT, and the following ORDER BY and GROUP BY keywords

...

it-ebooks

...

Com

Figure 8-16

...

 Example 8-27 shows two such queries, the results of which can be seen in
Figure 8-16

...

 Using ORDER BY
SELECT author,title FROM classics ORDER BY author;
SELECT author,title FROM classics ORDER BY title DESC;

As you can see, the first query returns the publications by author in ascending alphabetical order (the default), and the second returns them by title in descending order

...


The DESC keyword applies only to the preceding column, year

...

 You could also have
explicitly specified ascending order for that column, with the same results:
SELECT author,title,year FROM classics ORDER BY author ASC,year DESC;

GROUP BY
In a similar fashion to ORDER BY, you can group results returned from queries using
GROUP BY, which is good for retrieving information about a group of data

...

it-ebooks

...

Com

SELECT category,COUNT(author) FROM classics GROUP BY category;

which returns the following output:
+-----------------+---------------+
| category
| COUNT(author) |
+-----------------+---------------+
| Classic Fiction |
3 |
| Non-Fiction
|
1 |
| Play
|
1 |
+-----------------+---------------+
3 rows in set (0

...

 For example, consider the case of a customers table that needs to
be able to be cross-referenced with publications purchased from the classics table

...

 Figure 8-17 shows the result

...

 Creating and populating the customers table
CREATE TABLE customers (
name VARCHAR(128),
isbn VARCHAR(128),
PRIMARY KEY (isbn)) ENGINE MyISAM;
INSERT INTO customers(name,isbn)
VALUES('Joe Bloggs','9780099533474');
INSERT INTO customers(name,isbn)
VALUES('Mary Smith','9780582506206');
INSERT INTO customers(name,isbn)
VALUES('Jack Wilson','9780517123201');
SELECT * FROM customers;

There’s also a shortcut for inserting multiple rows of data, as in Example 8-28, in which you can replace the three separate INSERT INTO queries
with a single one listing the data to be inserted, separated by commas,
like this:
INSERT INTO customers(name,isbn) VALUES
('Joe Bloggs','9780099533474'),
('Mary Smith','9780582506206'),
('Jack Wilson','9780517123201');

Of course, in a proper table containing customers’ details there would also be addresses,
phone numbers, email addresses, and so on, but they aren’t necessary for this explanation

...

 Because it has the same meaning

192 | Chapter 8: Introduction to MySQL

www

...

info
Download at Boykma

...

 Creating the table customers

in both tables (an ISBN refers to a book, and always the same book), we can use this
column to tie the two tables together into a single query, as in Example 8-29

...

 Joining two tables into a single SELECT
SELECT name,author,title from customers,classics
WHERE customers

...

isbn;

The result of this operation is the following:
+-------------+-----------------+------------------------+
| name
| author
| title
|
+-------------+-----------------+------------------------+
| Joe Bloggs | Charles Dickens | The Old Curiosity Shop |
| Mary Smith | Jane Austen
| Pride and Prejudice
|
| Jack Wilson | Charles Darwin | The Origin of Species |
+-------------+-----------------+------------------------+
3 rows in set (0

...


This kind of join takes two tables and automatically joins columns that have the same
name

...

it-ebooks

...

Com

JOIN

...

ON construct, as follows, to achieve results identical to those of Example 8-29:
SELECT name,author,title FROM customers
JOIN classics ON customers

...

isbn;

Using AS
You can also save yourself some typing and improve query readability by creating aliases
using the AS keyword

...

 The following
code, therefore, is also identical in action to Example 8-29

...


SELECT name,author,title from
customers AS cust, classics AS class WHERE cust

...

isbn;

Using Logical Operators
You can also use the logical operators AND, OR, and NOT in your MySQL WHERE queries to
further narrow down your selections

...


Example 8-30

...

 Thus, the query returns publications as long as
the author column starts with Charles and ends with Darwin

...

 The third query returns publications written by authors
with the first name Charles but not the surname Darwin

...

 The answer is very simple: the
MySQL functions work on the data right there in the database

...



194 | Chapter 8: Introduction to MySQL

www

...

info
Download at Boykma

...

 If you wish to learn more about the
available functions, you can visit the following URLs:
• String functions: http://dev

...

com/doc/refman/5

...

html
• Date and time: http://dev

...

com/doc/refman/5

...

html
However, to get you started, Appendix D describes a subset containing the most useful
of these functions

...


The following explanation assumes you have worked through the previous examples
in this chapter and have created the tables classics and customers in the database publications

...



Windows Users
Ensure that you have EasyPHP up and running so that the MySQL database is ready,
then type the following into the address bar of your browser:
http://localhost/home/mysql/

Your browser should now look like Figure 8-18, and you are now ready to proceed to
the section “Using phpMyAdmin” on page 197

...

 You will now be ready to proceed to the section “Using phpMyAdmin” on page 197

...

it-ebooks

...

Com

Figure 8-18

...

 The Mac OS X phpMyAdmin main screen

196 | Chapter 8: Introduction to MySQL

www

...

info
Download at Boykma

...

 The Linux XAMPP main screen

Your browser should now look like Figure 8-20

...



Using phpMyAdmin
In the lefthand pane of the main phpMyAdmin screen, which should now be in your
browser, click on the drop-down menu that says “Databases” and select the database
publications, which will open the database and display its two tables just below

...


From here you can perform all the main operations for your databases, such as creating
databases, adding tables, creating indexes, and much more

...

phpmyadmin

...


If you worked with me through the examples in this chapter, congratulations—it’s been
quite a long journey

...



Accessing MySQL via phpMyAdmin | 197

www

...

info
Download at Boykma

...

 The classics table as viewed in phpMyAdmin

In the next chapter, we’ll start looking at how to approach efficient database design,
advanced SQL techniques, and MySQL functions and transactions

...

it-ebooks

...

Com

Question 8-6
What benefit does a FULLTEXT index provide?
Question 8-7
What is a stopword?
Question 8-8
Both SELECT DISTINCT and GROUP BY cause the display to show only one output row
for each value in a column, even if multiple rows contain that value

...

WHERE construct, how would you return only rows containing
the word Langhorne somewhere in the author column of the classics table used in
this chapter?
Question 8-10
What needs to be defined in two tables to make it possible for you to join them
together?
Question 8-11
Observant readers may have noticed that three book publication dates are incorrect
in this chapter

...

 How could you correct these entries?
See the section “Chapter 8 Answers” on page 441 in Appendix A for the answers to
these questions

...

it-ebooks

...

Com

www

...

info
Download at Boykma

...

 You’ve learned about creating databases and
the tables that comprise them, as well as inserting, looking up, changing, and deleting
data

...

 For example, how do you decide what data to place
in which table? Well, over the years, a number of guidelines have been developed that—
if you follow them—ensure that your databases will be efficient and capable of growing
as you feed them more and more data

...


Sitting down with a sheet of paper and a pencil and writing down a selection of the
queries that you think you and your users are likely to ask is an excellent starting point

...

it-ebooks

...

Com

•
•
•
•

What is the best-selling book?
Which books have not sold this year?
Which books did a certain customer buy?
Which books have been purchased along with the same other books?

Of course, there are many more queries that could be made on such a database, but
even this small sample will begin to give you insights into how to lay out your tables

...

 In contrast, books and
customers should be in separate tables, because their connection is very loose

...


When you plan to do a lot of searches on something, it can often benefit by having its
own table

...


Taking into account those simple rules of thumb, we can guess we’ll need at least three
tables to accommodate all these queries:
Authors
There will be lots of searches for authors, many of whom have collaborated on
titles, and many of whom will be featured in collections

...


Books
Many books appear in different editions

...

 So the links between
books and authors are complicated enough to call for a separate table

...



Primary Keys: The Keys to Relational Databases
Using the power of relational databases, we can define information for each author,
book, and customer in just one place

...

 I’ll show you the basic principles, and then it just takes practice for it to feel natural

...

 Do the same for every book
and for every customer

...

 For a book, it makes sense to use the ISBN, although you then have to
deal with multiple editions that have different ISBNs

...

it-ebooks

...

Com

can just assign arbitrary keys, which the AUTO_INCREMENT feature that you saw in the last
chapter makes easy

...

 Don’t choose a key that could possibly have the same value for different
objects

...

 Most of the time, you’ll create an arbitrary
key for this purpose, using AUTO_INCREMENT

...

 Its main goal is to make sure each piece of information appears in the
database only once

...


But, more importantly, the presence of duplicates creates a strong risk that you’ll update
only one row of duplicated data, creating inconsistencies in a database and potentially
causing serious errors

...

 It’s better
to keep the title in one place and use the ISBN in other places

...


Luckily, E

...

 Codd, the inventor of the relational model, analyzed the concept of normalization and split it into three separate schemas called First, Second, and Third Normal Form

...


To see how the normalization process works, let’s start with the rather monstrous
database in Table 9-1, which shows a single table containing all of the author names,
book titles, and (fictional) customer details

...


Obviously, this is inefficient design, because data is duplicated all over the place (duplications are highlighted), but it represents a starting point

...

it-ebooks

...

Com

Table 9-1

...

S

...

 name

Cust

...

 date

David
Sklar

Adam
Trachtenberg

PHP
Cookbook

0596101015

44

...

99

Darren
Ryder

4758 Emily
Drive, Richmond, VA
23219

Dec 19
2008

Danny
Goodman

Hugh E

...

95

Earl B

...

99

Darren
Ryder

4758 Emily
Drive, Richmond, VA
23219

Dec 19
2008

Rasmus
Lerdorf

Kevin Tatroe
& Peter
MacIntyre

Programming
PHP

0596006815

39

...



First Normal Form
For a database to satisfy the First Normal Form, it must fulfill three requirements:
1

...


2

...


3

...


Looking at these requirements in order, you should notice straight away that the Author
1 and Author 2 columns constitute repeating data types

...


Second, there are three authors listed for the final book, Programming PHP

...

 Yet another reason to transfer the Author details to a separate
table

...



204 | Chapter 9: Mastering MySQL

www

...

info
Download at Boykma

...

 Already
it looks a lot less cluttered, although there remain duplications that are highlighted

...

 The result of stripping the Authors column from Table 9-1
Title

ISBN

Price

Cust

...

 address

Purch

...

99

Emma Brown

1565 Rainbow Road, Los Angeles, CA
90014

Mar 03 2009

Dynamic HTML

0596527403

59

...

95

Earl B

...

99

Darren Ryder

4758 Emily Drive, Richmond, VA
23219

Dec 19 2008

Programming
PHP

0596006815

39

...

 It just lists the ISBN of
a title along with an author

...

 At first you may feel ill at ease with this table, because you can’t tell
which author wrote which book

...

 All you
have to do is tell it which book you want information for, and MySQL will use its ISBN
to search the Authors table in a matter of milliseconds

...

 The new Authors table
ISBN

Author

0596101015

David Sklar

0596101015

Adam Trachtenberg

0596527403

Danny Goodman

0596005436

Hugh E Williams

0596005436

David Lane

0596006815

Rasmus Lerdorf

0596006815

Kevin Tatroe

0596006815

Peter MacIntyre

As I mentioned earlier, the ISBN will be the primary key for the Books table, when we
get around to creating that table

...

 In the real world, the
Authors table would deserve a primary key, too, so that each author would have a key
to uniquely identify him or her

...

 In fact, it

Normalization | 205

www

...

info
Download at Boykma

...


Because we’ll use it to link authors to books in another table, this column is called a
foreign key

...

 The fundamental reason for defining a key is to make searches faster

...

 But a key can also be useful to uniquely identify an item

...



Second Normal Form
The First Normal Form deals with duplicate data (or redundancy) across multiple columns

...

 In order
to achieve Second Normal Form, your tables must already be in First Normal Form

...


So let’s look again at Table 9-2

...

 This tells us that the Customer columns need to be
pulled into their own tables

...


Table 9-4

...

99

0596527403

Dynamic HTML

59

...

95

0596006815

Programming PHP

39

...

 Along the way,
we’ve managed to reduce the information to data closely related to book titles

...

 The only rule is that we can’t put in any
column that could have multiple values for a single book, because then we’d have to
list the same book in multiple rows and would thus violate Second Normal Form

...



206 | Chapter 9: Mastering MySQL

www

...

info
Download at Boykma

...

 And it could also be argued that First Normal Form Rule 2 (all columns
should contain a single value) has not been properly complied with, because the
addresses really need to be broken into separate columns for Address, City, State, and
Zip code

...

 The Customer details from Table 9-2
ISBN

Cust

...

 address

Purch

...

 Thurston

862 Gregory Lane, Frankfort, KY 40601

Jun 22 2009

0596101015

Darren Ryder

4758 Emily Drive, Richmond, VA 23219

Dec 19 2008

0596006815

David Miller

3647 Cedar Lane, Waltham, MA 02154

Jan 16 2009

What we have to do is split this table further to ensure that each customer’s details are
entered only once

...


Table 9-6 is the result of normalizing the Customers table into both First and Second
Normal Forms

...

 All the parts of their addresses have also been separated into distinct
columns to make them easily searchable and updateable

...

 The new Customers table
CustNo

Name

Address

City

State

Zip

1

Emma Brown

1565 Rainbow Road

Los Angeles

CA

90014

2

Darren Ryder

4758 Emily Drive

Richmond

VA

23219

3

Earl B

...

 Instead, the purchase data is now
placed in a new table called Purchases (see Table 9-7)

...

 The new Purchases table
CustNo

ISBN

Date

1

0596101015

Mar 03 2009

2

0596527403

Dec 19 2008

Normalization | 207

www

...

info
Download at Boykma

...

 Because the ISBN column is also repeated here, this
table can be linked with either of the Authors or the Titles tables, too

...

 A single customer can buy multiple books (and even multiple copies of one book),
so the CustNo column is not a primary key

...

 That’s all right, because we don’t expect to need to keep track of unique purchases

...

 For easy searching, we can define both CustNo
and ISBN as keys—just not as primary keys

...

 We arrived at this decision through the normalization processes, by methodically following the First and Second
Normal Form rules, which made it plain that a fourth table called Purchases would also be required

...


For example, to see which books Darren Ryder has purchased, you can look him up in
Table 9-6, the Customers table, where you will see his CustNo is 2

...

 This looks like a lot of trouble for a human, but it’s not so hard for MySQL

...

 Should
you wish to know the authors of these books, you could also use the ISBN numbers
you just looked up on Table 9-3, the Authors table, and you would see that ISBN
0596527403, Dynamic HTML, was written by Danny Goodman, and that ISBN
0596101015, PHP Cookbook, was written by David Sklar and Adam Trachtenberg

...

 However, if
208 | Chapter 9: Mastering MySQL

www

...

info
Download at Boykma

...


For example, in Table 9-6, the Customers table, it could be argued that the State, City,
and Zip code keys are not directly related to each customer, because many other people
will have the same details in their addresses, too

...


Therefore, to satisfy Third Normal Form for Table 9-6, you would need to split it into
Tables 9-8, 9-9, 9-10, and 9-11

...

 Third Normal Form Customers table
CustNo

Name

Address

Zip

1

Emma Brown

1565 Rainbow Road

90014

2

Darren Ryder

4758 Emily Drive

23219

3

Earl B

...

 Third Normal Form Zip codes table
Zip

CityID

90014

1234

23219

5678

40601

4321

02154

8765

Table 9-10

...

 Third Normal Form States table
StateID

Name

Abbreviation

5

California

CA

46

Virginia

VA

17

Kentucky

KY

21

Massachusetts

MA
Normalization | 209

www

...

info
Download at Boykma

...


Given this information, you could then look up the city Name in Table 9-10 and then
also find the StateID, which you could use in Table 9-11 to look up the State’s Name

...

 For example, take a look at Table 9-11, where it has been possible to include both a state’s name and its two-letter abbreviation

...


Table 9-10 could also contain even more localized demographics that
could be useful to you and/or your customers

...



Deciding whether to use the Third Normal Form can be tricky

...

 If you are absolutely
certain that the name and address of a customer is all that you will ever require, you
probably will want to leave out this final normalization stage

...

S

...

 What would you do if a city were to be renamed? With a table
such as Table 9-6, you would need to perform a global search and replace on every
instance of that city

...


Therefore, I suggest that you ask yourself two questions to help you decide whether to
perform a Third Normal Form normalization on any table:
1

...

 Could any of this table’s fields require a global update at any point?
If either of the answers is yes, you should probably consider performing this final stage
of normalization

...

 That’s right—you should
never fully normalize your tables on sites that will cause MySQL to thrash

...

 On a very popular site, if you have normalized
tables, your database access will slow down considerably once you get above a few
dozen concurrent users, because they will be creating hundreds of database accesses

210 | Chapter 9: Mastering MySQL

www

...

info
Download at Boykma

...

 In fact I would go so far as to say you should denormalize any commonly
looked-up data as much as you can

...

 This means that you can simply add an extra column
to a query and that field will be available for all matching results

...


Multiple updates can be computerized, though

...


(Triggers are, however, beyond the scope of this book

...


The program reads changes from a “master” table and updates all the others

...

)
However, until you are very experienced with MySQL, I recommend you fully normalize all your tables, as this will instill the habit and put you in good stead

...



Relationships
MySQL is called a relational database management system because its tables store
not only data but the relationships among the data

...



One-to-One
A one-to-one relationship is like a (traditional) marriage: each item has a relationship
to only one item of the other type

...

 For instance, an author can
write multiple books, a book can have multiple authors, and even an address can be
associated with multiple customers

...


However, for the sake of argument, let’s assume that there can ever be only one customer at any address

...



Relationships | 211

www

...

info
Download at Boykma

...

 There are two reasons for splitting them into separate tables:
• You want to be prepared in case the relationship changes later

...



Figure 9-1

...



One-to-Many
One-to-many (or many-to-one) relationships occur when one row in one table is linked
to many rows in another table

...


So, looking at Table 9-8a within Figure 9-1, you can see that it shares a one-to-many
relationship with Table 9-7 because there is only one of each customer in Table 9-8a

...

 Therefore one customer has a relationship with many purchases

...

 This one-to-many relationship is also
the preferred scheme to use when describing a many-to-one relationship, in which case
you would normally swap the left and right tables to view them as a one-to-many
relationship

...

 To create this relationship, add a third table containing the same key
212 | Chapter 9: Mastering MySQL

www

...

info
Download at Boykma

...

 Illustrating the relationship between two tables

column from each of the other tables

...


Table 9-12 is just such a table

...

 What it now contains is a copy of the ISBN
number of every title sold, along with the customer number of the purchaser

...

 An intermediary table
Customer

ISBN

1

0596101015

2

0596527403

2

0596101015

3

0596005436

4

0596006815

With this intermediary table in place, you can traverse all the information in the database through a series of relations

...


For example, let’s suppose that you want to find out about purchases in the 23219 zip
code

...

 At this point, you can
use Table 9-8a within Figure 9-1 to find out his or her name, or use the new intermediary
Table 9-12 to see the book(s) purchased

...


If it seems to you that this is really combining multiple one-to-many relationships, then
you are absolutely correct

...



Relationships | 213

www

...

info
Download at Boykma

...

 Creating a many-to-many relationship via a third table

Follow any zip code in the left table to associated customer IDs

...

 Now all you have to do is follow an ISBN over to the right table to
see which book it relates to

...

 The Titles table can tell you the ISBN, which you can use in the middle
table to find ID numbers of customers who bought the books, and finally, the Customers table matches the customer ID numbers to the customers’ zip codes

...

 Note that we went from customers’ zip codes to customers’ purchases, and back
again, in the previous example, without finding out the name of a customer

...



Transactions
In some applications, it is vitally important that a sequence of queries runs in the correct
order and that every single query successfully completes

...

 You would not want either of the following events to occur:
• You add the funds to the second account, but when you try to subtract them from
the first account the update fails, and now both accounts have the funds

...



214 | Chapter 9: Mastering MySQL

www

...

info
Download at Boykma

...

 But how can
you ensure this happens, because surely after a query has occurred, it cannot be undone? Do you have to keep track of all parts of a transaction and then undo them all
one at a time if any one fails? The answer is absolutely not, because MySQL comes with
powerful transaction handling features to cover just these types of eventualities

...



Transaction Storage Engines
In order to be able to use MySQL’s transaction facility, you have to be using MySQL’s
InnoDB storage engine

...

 So go ahead and create a table of bank accounts by typing
in the commands in Example 9-1

...

)
Example 9-1

...

 The output from it should look like this:
+---------+---------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| number | int(11) | NO
| PRI | 0
|
|
| balance | float
| YES |
| NULL
|
|
+---------+---------+------+-----+---------+-------+
2 rows in set (0

...


Type in the commands in Example 9-2

...

 Populating the accounts table
INSERT INTO accounts(number, balance) VALUES(12345, 1025

...

00);
SELECT * FROM accounts;

The third line displays the contents of the table to confirm that the rows were correctly
inserted

...

it-ebooks

...

Com

+--------+---------+
| number | balance |
+--------+---------+
| 12345 | 1025

...

00 sec)

With this table created and prepopulated, you are now ready to start using transactions

...


Type in the commands in Example 9-3 to send a transaction to MySQL

...

 A MySQL transaction
BEGIN;
UPDATE accounts SET balance=balance+25

...

61 |
| 67890 |
140 |
+--------+---------+
2 rows in set (0

...

11 and is
now 1050

...

 You may also have noticed the COMMIT command in Example 9-3, which
is explained next

...

 Until a
COMMIT is received, all the changes you make are considered to be merely temporary by
MySQL

...



Using ROLLBACK
Using the ROLLBACK command, you can tell MySQL to forget all the queries made since
the start of a transaction and to end the transaction

...



216 | Chapter 9: Mastering MySQL

www

...

info
Download at Boykma

...

 A funds transfer transaction
BEGIN;
UPDATE accounts SET balance=balance-250 WHERE number=12345;
UPDATE accounts SET balance=balance+250 WHERE number=67890;
SELECT * FROM accounts;

Once you have entered these lines, you should see the following result:
+--------+---------+
| number | balance |
+--------+---------+
| 12345 | 800

...

00 sec)

The first bank account now has a value that is 250 less than before, and the second has
been incremented by 250—you have transferred a value of 250 between them

...

 All you have
to do is issue the commands in Example 9-5

...

 Cancelling a transaction using ROLLBACK
ROLLBACK;
SELECT * FROM accounts;

You should now see the following output, showing that the two accounts have had
their previous balances restored, due to the entire transaction being cancelled using the
ROLLBACK command:
+--------+---------+
| number | balance |
+--------+---------+
| 12345 | 1050

...

00 sec)

Using EXPLAIN
MySQL comes with a powerful tool for investigating how the queries you issue to it
are interpreted

...

 Example 9-6 shows how to use it
with the accounts table you created earlier

...

 Using the EXPLAIN command
EXPLAIN SELECT * FROM accounts WHERE number='12345';

The results of this EXPLAIN command should look like the following:

Using EXPLAIN | 217

www

...

info
Download at Boykma

...

00 sec)

The information that MySQL is giving you here is as follows:
select_type

The selection type is SIMPLE

...


table

The current table being queried is accounts

...

 From worst to best, the possible values can be: ALL, index,
range, ref, eq_ref, const, system, and NULL

...


key

The key actually used is PRIMARY

...


key_len

The key length is 4

...


ref

The ref column displays which columns or constants are used with the key

...


rows

The number of rows that need to be searched by this query is 1

...


Whenever you have a query that seems to be taking longer than you think it should to
execute, try using EXPLAIN to see where you can optimize it

...


When you have finished experimenting with the temporary accounts
table, you may wish to remove it by entering the following command:
DROP TABLE accounts;

Backing Up and Restoring
Whatever kind of data you are storing in your database it must have some value to you,
even if it’s only the cost of the time required for reentering it should the hard disk fail

...

 Also there

218 | Chapter 9: Mastering MySQL

www

...

info
Download at Boykma

...

 It is also important that you test your backups
from time to time to ensure that they are valid and will work if they need to be used

...



Using mysqldump
With mysqldump, you can dump a database or collection of databases into one or more
files containing all the instructions necessary to recreate all your tables and repopulate
them with your data

...

 Its main drawback is that you
must make sure that no one writes to a table while you’re backing it up

...


Or you can lock the tables you are backing up before running mysqldump

...

 READ

Then, to release the lock(s), enter:
UNLOCK TABLES;

By default, the output from mysqldump is simply printed out, but you can capture it in
a file through the > redirect symbol

...


Table 9-13 shows the likely locations of the program for the different installations and
operating systems covered in Chapter 2

...


Table 9-13

...

0\mysql\bin\

Mac MAMP

/Applications/MAMP/Library/bin/

Linux LAMP

/usr/local/bin/

Backing Up and Restoring | 219

www

...

info
Download at Boykma

...

 Dumping the publications database to screen

So, to dump the contents of the publications database that you created in Chapter 8 to
the screen, enter mysqldump (or the full path if necessary) and the command in Example 9-7

...

 Dumping the publications database to screen
mysqldump -u user -ppassword publications

Make sure that you replace user and password with the correct details for your installation of MySQL

...

 The result of issuing this
command will look something like the screenshot in Figure 9-4

...


Assuming that you wish to call the backup file publications

...


Example 9-8

...

sql

220 | Chapter 9: Mastering MySQL

www

...

info
Download at Boykma

...

 If you need it to be saved elsewhere, you should insert a file
path before the filename

...



If you echo the backup file to screen or load it into a text editor, you will see that it
comprises sequences of SQL commands such as the following:
DROP TABLE IF EXISTS `classics`;
CREATE TABLE `classics` (
`author` varchar(128) default NULL,
`title` varchar(128) default NULL,
`category` varchar(16) default NULL,
`year` smallint(6) default NULL,
`isbn` char(13) NOT NULL default '',
PRIMARY KEY (`isbn`),
KEY `author` (`author`(20)),
KEY `title` (`title`(20)),
KEY `category` (`category`(4)),
KEY `year` (`year`),
FULLTEXT KEY `author_2` (`author`,`title`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

This is smart code that can be used to restore a database from a backup, even if it
currently exists, because it will first drop any tables that need to be recreated, thus
avoiding potential MySQL errors

...


I’ve assumed here that you haven’t shut down the MySQL server

...


Example 9-9

...

sql
$ mysql -u user -ppassword
mysql> UNLOCK TABLES
mysql> QUIT

Or, if you want to back up all your MySQL databases at once (including the system
databases such as mysql), you can use a command such as the one in Example 9-10,
which would make it possible to restore an entire MySQL database installation—
remembering to use locking where required

...

it-ebooks

...

Com

Example 9-10

...

sql

Of course, there’s a lot more than just a few lines of SQL code in backedup database files

...



Restoring from a Backup File
To perform a restore from a file, call the mysql executable, passing it the file to restore
from using the < symbol

...


Example 9-11

...

sql

To restore a single database, use the -D option followed by the name of the database,
as in Example 9-12, where the publications database is being restored from the backup
made in Example 9-8

...

 Restoring the publications database
mysql -u user -ppassword -D publications < publications

...


Example 9-13

...

sql

Dumping Data in CSV Format
As previously mentioned, the mysqldump program is very flexible and supports various
types of output, such as the CSV format

...

txt and customers

...

 By default, on an EasyPHP 3

...

 On OS X or Linux systems,
you should modify the destination path to an existing folder

...

 Dumping data to CSV format files
mysqldump -u user -ppassword --no-create-info --tab=c:/web
--fields-terminated-by=',' publications

222 | Chapter 9: Mastering MySQL

www

...

info
Download at Boykma

...

 The
result is the following:
Mark Twain (Samuel Langhorne Clemens)','The Adventures
of Tom Sawyer','Classic Fiction','1876','9781598184891
Jane Austen','Pride and Prejudice','Classic Fiction','1811','9780582506206
Charles Darwin','The Origin of Species','Non-Fiction','1856','9780517123201
Charles Dickens','The Old Curiosity Shop','Classic Fiction','1841','9780099533474
William Shakespeare','Romeo and Juliet','Play','1594','9780192814968
Mary Smith','9780582506206
Jack Wilson','9780517123201

Planning Your Backups
The golden rule to backing up is to do so as often as you find practical

...

 If your database gets updated at least once a day, you should really back it up
on a daily basis

...


You should also consider making multiple backups and storing them in
different locations

...

 You would also be well advised to
make physical backups of removable hard disks, thumb drives, CDs or
DVDs, and so on, and to keep these in separate locations—preferably
somewhere like a fireproof safe

...



Test Your Knowledge: Questions
Question 9-1
What does the word relationship mean in reference to a relational database?
Question 9-2
What is the term for the process of removing duplicate data and optimizing tables?
Question 9-3
What are the three rules of First Normal Form?
Question 9-4
How can you make a table satisfy Second Normal Form?
Test Your Knowledge: Questions | 223

www

...

info
Download at Boykma

...

sql?
See the section “Chapter 9 Answers” on page 442 in Appendix A for the answers to
these questions

...

it-ebooks

...

Com

CHAPTER 10

Accessing MySQL Using PHP

If you worked through the previous chapters, you’re proficient in using both MySQL
and PHP

...



Querying a MySQL Database with PHP
The reason for using PHP as an interface to MySQL is to format the results of SQL
queries in a form visible in a web page

...

 However,
instead of using MySQL’s command line to enter instructions and view output, you
will create query strings that are passed to MySQL

...

 Further PHP commands can retrieve the
data and format it for the web page

...


2

...


4

...


6

...



Connect to MySQL

...


Build a query string

...


Retrieve the results and output it to a web page

...


Disconnect from MySQL

...


225

www

...

info
Download at Boykma

...

 Therefore,
it’s sensible to create a single file to store these and then include that file wherever it’s
needed

...

php

...

 We’ll be
making use of the file shortly

...


Example 10-1

...

php file

...

php file in Example 10-1, because they mean that the lines between can be interpreted only as PHP
code

...

 But, with the tags in place,
all they will see is a blank page

...


The $db_hostname variable will tell PHP which computer to use when connecting to a
database

...

 However, the examples in this chapter will be working on the local server

...

myserver

...

0

...

1) will correctly refer to it

...

php accordingly)

...


Another benefit of keeping these login details in a single place is that
you can change your password as frequently as you like and there will
be only one file to update when you do, no matter how many PHP files
access MySQL

...

it-ebooks

...

Com

Connecting to MySQL
Now that you have the login

...

 This has been chosen
in preference to an include statement, as it will generate a fatal error if the file is not
found

...


Also, using require_once instead of require means that the file will be read in only when
it has not previously been included, which prevents wasteful duplicate disk accesses

...


Example 10-2

...

php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " 

...

 Upon success it returns an
identifier to the server; otherwise, FALSE is returned

...


The die message explains that it was not possible to connect to the MySQL database,
and—to help identify why this happened—includes a call to the mysql_error function

...


The database server pointer $db_server will be used in some of the following examples
to identify the MySQL server to be queried

...


The die function is great for when you are developing PHP code, but of
course you will want more user-friendly error messages on a production
server

...

 The error message we got was:
$msg: $msg2

Please click the back button on your browser
and try again

...

it-ebooks

...

Com

please 
...

 Pass it the name of the database you want and the server to which you connected

...

 Therefore the PHP or statement was used, which means “if the previous command failed, do the following

...



Building and executing a query
Sending a query to MySQL from PHP is as simple as issuing it using the mysql_query
function

...


Example 10-4

...

 mysql_error());
?>

First, the variable $query is set to the query to be made

...

 Note that, unlike using MySQL’s command line, no
semicolon is required at the tail of the query, because the mysql_query function is used
to issue a complete query, and cannot be used to query by sending multiple parts, one
at a time

...


This function returns a result that we place in the variable $result

...

it-ebooks

...

Com

so on

...

 Instead, upon
success, $result will contain a resource that can be used to extract the results of the
query

...

 Upon failure, $result
contains FALSE

...

 If it’s FALSE, it means that
there was an error and the die command is executed

...

 The simplest way to do this is to fetch the cells you want,
one at a time, using the mysql_result function

...

 I suggest that you save it in the same folder as login

...

php

...

 Fetching results one cell at a time

...

php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " 

...

 mysql_error());
$query = "SELECT * FROM classics";
$result = mysql_query($query);
if (!$result) die ("Database access failed: " 

...

 mysql_result($result,$j,'author')
echo 'Title: '


...

 mysql_result($result,$j,'category')
echo 'Year: '


...

 mysql_result($result,$j,'isbn')
}
?>



...




...




...

 They start by setting
the variable $rows to the value returned by a call to mysql_num_rows

...


Armed with the row count, we enter a for loop that extracts each cell of data from each
row using the mysql_result function

...


Querying a MySQL Database with PHP | 229

www

...

info
Download at Boykma

...

 The output from the query

...

 Figure 10-1
shows the result of running this program

...

php

...

 Luckily, there is a
much better way of retrieving the data, which is getting a single row at a time using the
mysql_fetch_row function

...

 That’s because we created this table before encountering normalization

...



230 | Chapter 10: Accessing MySQL Using PHP

www

...

info
Download at Boykma

...

 So, replace the for loop of query

...


Example 10-6

...


$row[0] 

...


$row[1] 

...


$row[2] 

...


$row[3] 

...


$row[4] 

...

 This returns a single row of data in an array, which is then
assigned to the variable $row

...

 Therefore $row[0] contains the Author data, $row[1] the Title, and
so on, because each column is placed in the array in the order in which it appears in
the MySQL table

...



Closing a connection
When you have finished using a database, you should close the connection

...


Example 10-7

...



Querying a MySQL Database with PHP | 231

www

...

info
Download at Boykma

...


But in longer programs, where you may continually open and close database connections, you are strongly advised to close each one as soon
as accessing it is complete

...

 I recommend that you type in Example 10-8 and save it to your web
development directory using the filename sqltest

...

 You can see an example of the
program’s output in Figure 10-2

...

 The output from Example 10-8, sqltest

...

 The following chapter
explains forms in detail, but in this chapter I take form handling for
granted and just deal with database interaction

...

it-ebooks

...

Com

Example 10-8

...

php

...

php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " 

...

 mysql_error());
if (isset($_POST['author']) &&
isset($_POST['title']) &&
isset($_POST['category']) &&
isset($_POST['year']) &&
isset($_POST['isbn']))
{
$author
= get_post('author');
$title
= get_post('title');
$category = get_post('category');
$year
= get_post('year');
$isbn
= get_post('isbn');
if (isset($_POST['delete']) && $isbn != "")
{
$query = "DELETE FROM classics WHERE isbn='$isbn'";
if (!mysql_query($query, $db_server))
echo "DELETE failed: $query
" 

...

 "

";

}
else
{

$query = "INSERT INTO classics VALUES" 

...


mysql_error() 

...

php" method="post">

Author 
Title 
Category 
Year 
ISBN 


_END;
$query = "SELECT * FROM classics";
$result = mysql_query($query);

A Practical Example | 233

www

...

info
Download at Boykma

...

 mysql_error());
$rows = mysql_num_rows($result);
for ($j = 0 ; $j < $rows ; ++$j)
{
$row = mysql_fetch_row($result);
echo <<<_END

Author $row[0]
Title $row[1]
Category $row[2]
Year $row[3]
ISBN $row[4]


...

 mysql_error());
$query = "CREATE TABLE cats (
id SMALLINT NOT NULL AUTO_INCREMENT,

238 | Chapter 10: Accessing MySQL Using PHP

www

...

info
Download at Boykma

...

 mysql_error());
?>

As you can see, the MySQL query looks pretty similar to how you would type it in
directly to the command line, except that there is no trailing semicolon, as none is
needed when accessing MySQL from PHP

...


It simply issues the query DESCRIBE cats and then outputs an HTML table with four
headings: Column, Type, Null, and Key, underneath which all columns within the table
are shown

...


Example 10-10

...

php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " 

...

 mysql_error());
$query = "DESCRIBE cats";
$result = mysql_query($query);
if (!$result) die ("Database access failed: " 

...

it-ebooks

...

Com

The output from the program should look like this:
Column
id
family
name
age

Type
smallint(6)
varchar(32)
varchar(32)
tinyint(4)

Null Key
NO
PRI
NO
NO
NO

Dropping a Table
Dropping a table is very easy to do and is therefore very dangerous, so be careful

...

 However, I don’t recommend that you
try it until you have been through the other examples, as it will drop the table cats and
you’ll have to recreate it using Example 10-9

...

 Dropping the table cats
require_once 'login

...

 mysql_error());
mysql_select_db($db_database)
or die("Unable to select database: " 

...

 mysql_error());
?>

Adding Data
Let’s add some data to the table using the code in Example 10-12

...

 Adding data to table cats
require_once 'login

...

 mysql_error());
mysql_select_db($db_database)
or die("Unable to select database: " 

...

 mysql_error());
?>

You may wish to add a couple more items of data by modifying $query as follows and
calling the program up in your browser again:

240 | Chapter 10: Accessing MySQL Using PHP

www

...

info
Download at Boykma

...


Of course, the most efficient way to populate MySQL with data is to create an array
and insert the data with a single query

...


Example 10-13

...

php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " 

...

 mysql_error());
$query = "SELECT * FROM cats";
$result = mysql_query($query);
if (!$result) die ("Database access failed: " 

...

 Its output is as follows:
Id
1
2
3

Family
Lion
Cougar
Cheetah

Name
Leo
Growler
Charly

Age
4
2
3

Here you can see that the id column has correctly auto-incremented

...

it-ebooks

...

Com

Updating Data
Changing data that you have already inserted is also quite simple

...


Example 10-14

...

php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " 

...

 mysql_error());
$query = "UPDATE cats SET name='Charlie' WHERE name='Charly'";
$result = mysql_query($query);
if (!$result) die ("Database access failed: " 

...


Example 10-15

...

php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " 

...

 mysql_error());
$query = "DELETE FROM cats WHERE name='Growler'";
$result = mysql_query($query);
if (!$result) die ("Database access failed: " 

...

it-ebooks

...

Com

Using AUTO_INCREMENT
When using AUTO_INCREMENT, you cannot know what value has been given to a column
before a row is inserted

...

 This need is common: for instance, when you
process a purchase, you might insert a new customer into a Customers table and then
refer to the newly created CustId when inserting a purchase into the purchase table

...


Example 10-16

...

php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " 

...

 mysql_error());
$query = "INSERT INTO cats VALUES(NULL, 'Lynx', 'Stumpy', 5)";
$result = mysql_query($query);
echo "The Insert ID was: " 

...

 mysql_error());
?>

The contents of the table should now look like the following (note how the previous
id value of 2 is not reused, as this could cause complications in some instances):
Id
1
3
4

Family
Lion
Cheetah
Lynx

Name
Leo
Charlie
Stumpy

Age
4
3
5

Using insert IDs
It’s very common to insert data in multiple tables: a book followed by its author, or a
customer followed by their purchase, and so on

...


For example, let’s assume that these cats can be “adopted” by the public as a means of
raising funds, and that when a new cat is stored in the cats table, we also want to create
a key to tie it to the animal’s adoptive owner

...

it-ebooks

...

Com

Now the cat is connected to its “owner” through the cat’s unique ID, which was created
automatically by AUTO_INCREMENT

...

 Suppose that two
people visit the website at the same time and submit new information, causing the web
server to run your program twice at the same time

...

) The second visitor might insert a
new cat just before the first visitor’s program issues mysql_insert_id

...


So a completely safe procedure for linking tables through the insert ID is to use locks
(or transactions, as described in Chapter 9)

...


The sequence is:
1

...


3

...


5

...

g

...


Insert data into the first table

...


Unlock the first table

...



The lock can safely be released before inserting data into the second table, because the
insert ID has been retrieved and is stored in a program variable

...



Performing Additional Queries
OK: that’s enough feline fun

...

 There will be two customers in the customers table; the classics table holds
the details of a few books

...


For example, to display each of the customers along with the titles and authors of the
books they have bought, you can use the code in Example 10-17

...

 Performing a secondary query
require_once 'login

...

 mysql_error());
mysql_select_db($db_database)
or die("Unable to select database: " 

...

it-ebooks

...

Com

$result = mysql_query($query);
if (!$result) die ("Database access failed: " 

...

 mysql_error());
$subrow = mysql_fetch_row($subresult);
echo " '$subrow[1]' by $subrow[0]
";

This program uses an initial query to the customers table to look up all the customers
and then, given the ISBN number of the book each customer purchased, makes a new
query to the classics table to find out the title and author for each

...

 For example, suppose you have a simple piece of code to verify a user, and it
looks like this:
$user = $_POST['user'];
$pass = $_POST['pass'];
$query = "SELECT * FROM users WHERE user='$user' AND pass='$pass'";

At first glance, you might think this code is perfectly fine

...

it-ebooks

...

Com

This is all well and good, but what if someone enters the following for $user (and doesn’t
even enter anything for $pass)?
admin' #

Let’s look at the string that would be sent to MySQL:
SELECT * FROM users WHERE user='admin' #' AND pass=''

Do you see the problem there? In MySQL, the # symbol represents the start of a comment

...

 In the following, the part of the query that will be
executed is shown in bold—the rest will be ignored

...

 At
least you might still be able to go into your application and undo any changes the user
makes as admin

...

 Why? Because this feature can be turned off; many programmers do so in
order to put their own security code in place

...

 In fact, the feature was deprecated as of
PHP 5

...

0 and has been removed in PHP 6

...

0

...

 Example 10-18 is a function you can use that will remove any magic quotes
added to a user-inputted string and then properly sanitize it for you

...

 How to properly sanitize user input for MySQL
function mysql_fix_string($string)
{
if (get_magic_quotes_gpc()) $string = stripslashes($string);
return mysql_real_escape_string($string);

246 | Chapter 10: Accessing MySQL Using PHP

www

...

info
Download at Boykma

...

 In that case,
any slashes that have been added to a string have to be removed or the function
mysql_real_eascape_string could end up double-escaping some characters, creating
corrupted strings

...


Example 10-19

...



Using placeholders
Another way—this one virtually bulletproof—to prevent SQL injections is to use a
feature called placeholders

...

 Then, instead of calling a MySQL query directly, you call the
predefined one, passing the data to it

...

 In other words, SQL injections become impossible

...


Example 10-20

...

it-ebooks

...

Com

The first command prepares a statement called statement for inserting data into the
classics table

...

 These are the placeholders

...


Then the predefined statement is executed, passing these variables as parameters

...


In PHP, the code for this procedure looks like Example 10-21 (assuming that you have
created login

...


Example 10-21

...

php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " 

...

 mysql_error());
$query = 'PREPARE statement FROM "INSERT INTO classics
VALUES(?,?,?,?,?)"';
mysql_query($query);
$query = 'SET @author = "Emily Brontë",' 

...


'@category = "Classic Fiction",' 

...


'@isbn = "9780553212587"';
mysql_query($query);
$query = 'EXECUTE statement USING @author,@title,@category,@year,@isbn';
mysql_query($query);
$query = 'DEALLOCATE PREPARE statement';
mysql_query($query);
?>

Once you have prepared a statement, until you deallocate it, you can use it as often as
you wish

...

 This approach is more efficient than creating the entire statement from scratch
on each pass through the loop

...

 That’s Cross Site
Scripting, also referred to as XSS

...

it-ebooks

...

Com

This occurs when you allow HTML, or more often JavaScript code, to be input by a
user and then displayed back by your website

...

 What most often happens is that a malicious user will try to write code that
steals cookies from your site’s users, allowing him or her to discover username and
password pairs or other information

...


But preventing this is as simple as calling the htmlentities function, which strips out
all HTML markup codes and replaces them with a form that displays the characters,
but does not allow a browser to act on them

...

com/hack

...

 But if
it is first passed through htmlentities, it will be turned into the following, totally
harmless string:
<script src='http://x

...

js'>
</script><script>hack();</script>

Therefore, if you are ever going to display anything that your users enter, either immediately or after first storing it in database, you need to first sanitize it with
htmlentities

...


Example 10-22

...

 Example 10-23 shows your new “ultimate protection” version of Example 10-19

...

 How to safely access MySQL and prevent XSS attacks
$user = mysql_entities_fix_string($_POST['user']);
$pass = mysql_entities_fix_string($_POST['pass']);
$query = "SELECT * FROM users WHERE user='$user' AND pass='$pass'";
function mysql_entities_fix_string($string)

Practical MySQL | 249

www

...

info
Download at Boykma

...



Test Your Knowledge: Questions
Question 10-1
What is the standard PHP function for connecting to a MySQL database?
Question 10-2
When is the mysql_result function not optimal?
Question 10-3
Give one reason why using the POST form method is usually better than GET

...



250 | Chapter 10: Accessing MySQL Using PHP

www

...

info
Download at Boykma

...

 These were introduced very early on in the development of the World
Wide Web in 1993—even before the advent of e-commerce—and have remained a
mainstay ever since, due to their simplicity and ease of use

...



Building Forms
Handling forms is a multipart process

...

 This data is then sent to the web server, where it is interpreted,
often with some error checking

...

 When the code is satisfied with the accuracy of the input, it takes some action that usually involves the
database, such as entering details about a purchase

...

 Type it in and save it as
formtest

...



251

www

...

info
Download at Boykma

...

 formtest

...

php
echo <<<_END





echo <<<_END





Your name is: $name



We’ve already covered the name and value parameters, but two more are introduced
here: size and maxlength

...


The only required parameters are type, which tells the web browser what type of input
is to be expected, and name, for providing a name to the input that is then used to
process the field upon receipt of the submitted form

...

 This
is similar to a text box but, because it allows multiple lines, it has some different parameters

...

 It therefore requires a closing  to end input

...




To control the width and height, use the cols and rows parameters

...

 If you omit these
values, a default input box will be created that will vary in dimensions depending on
the browser used, so you should always define them to be certain about how your form
will appear

...

it-ebooks

...

Com

Lastly, you can control how the text entered into the box will wrap (and how any such
wrapping will be sent to the server) using the wrap parameter

...

 If you leave out the wrap parameter, soft wrapping is used

...

 The wrap types available in a textarea input
Type

Action

off

Text does not wrap and lines appear exactly as the user types them

...



hard

Text wraps and is sent to the server in wrapped format with soft returns and line feeds

...

 The format to use is:


If you include the checked parameter, the box is already checked when the browser is
displayed (the string you assign to the parameter doesn’t matter; the parameter just has
to be present)

...

 Here
is an example of an unchecked box:
I Agree 

If the user doesn’t check the box, no value will be submitted

...

 If you prefer to have your own value
submitted instead of the word “on” (such as the number 1), you could use the following
syntax:
I Agree 

On the other hand, if you wish to offer a newsletter to your readers when submitting
a form, you might want to have the checkbox already checked as the default value:
Subscribe? 

If you want to allow groups of items to be selected at one time, assign them all the same
name

...

 For example, Example 11-4 allows the user to select his favorite ice creams
(see Figure 11-4 for how it displays in a browser)

...

 Offering multiple checkbox choices
Vanilla 
Chocolate 
Strawberry 

If only one of the checkboxes is selected, such as the second one, only that item will be
submitted (the field named ice would be assigned the value “Chocolate”)

...

it-ebooks

...

Com

Figure 11-4

...


If you want exclusive behavior—so that only one item can be submitted—then you
should use radio buttons (see the next section), but to allow multiple submissions, you
have to slightly alter the HTML, as in Example 11-5 (note the addition of the square
brackets, [], following the values of ice):
Example 11-5

...

 In each case, you can extract
either the single submitted value, or the array of values, to a variable like this:
$ice = $_POST['ice'];

If the field ice has been posted as a single value, $ice will be a single string, such as
“Strawberry”

...


Table 11-2 shows the seven possible sets of values that could be submitted by this
HTML for one, two, or all three selections

...


Table 11-2

...

it-ebooks

...

Com

One value submitted

Two values submitted

Three values submitted

$ice[0] => Chocolate
$ice[1] => Strawberry

If $ice is an array, the PHP code to display its contents is quite simple and might look
like this:
foreach($ice as $item) echo "$item
";

This uses the standard PHP foreach construct to iterate through the array $ice and pass
each element’s value into the variable $item, which is then displayed using the echo
command

...


By default, checkboxes are square

...

 They
are used when you want only a single value to be returned from a selection of two or
more options

...


For example, if your website offers a choice of delivery times for items purchased from
your store, you might use HTML like that in Example 11-6 (see Figure 11-5 to see how
it displays)

...

 Selecting a single value with radio buttons

Example 11-6

...

it-ebooks

...

Com

Here the second option of Noon-4pm has been selected by default

...

 Had one of the items not been already
checked, the user might forget to select an option and no value would be submitted at
all for the delivery time

...



Hidden Fields
Sometimes it is convenient to have hidden form fields so that you can keep track of the
state of form entry

...

 You can achieve this by adding some HTML in your PHP code, such
as the following:
echo ''

This is a simple PHP echo statement that adds an input field to the HTML form

...

 The first
time the PHP program receives the input, this line of code has not run, so there will be
no field named submitted

...


So when the visitor resubmits the form, the PHP program receives it with the submitted field set to “yes”

...



Hidden fields can also be useful for storing other details, such as a session ID string
that you might create to identify a user, and so on

...

 The HTML
containing them can easily be viewed using a browser’s View Source
feature

...

 It conforms to the following syntax:


The parameter size is the number of lines to display

...

 If you use the multiple parameter, multiple
options can be selected from the list by pressing the Ctrl key when clicking

...



260 | Chapter 11: Form Handling

www

...

info
Download at Boykma

...

 Creating a drop-down list with select
Example 11-7

...

 Figure 11-6 shows the output where the list has been clicked on to drop it
down, and the option Carrots has been highlighted

...


Example 11-8

...

 But, as you can see from Figure 11-7, it is now possible to
select more than one option by using the Ctrl key when clicking

...

 I also recommend against multiple select boxes smaller than
two rows in height—some browsers may not correctly display the scroll bars needed
to access it

...

it-ebooks

...

Com

Figure 11-7

...



Labels
You can provide an even better user experience by utilizing the label tag

...


For instance, going back to the example of choosing a delivery time, you could allow
the user to click on the radio button itself and the associated text, like this:
8am-Noon

The text will not be underlined like a hyperlink when you do this, but as the mouse
passes over, it will change to an arrow instead of a text cursor, indicating that the whole
item is clickable

...

gif" />

Sanitizing Input
Now we return to PHP programming

...

it-ebooks

...

Com

with utmost caution from the word go

...


The first thing to remember is that regardless of what constraints you have placed in
an HTML form to limit the types and sizes of inputs, it is a trivial matter for a hacker
to use their browser’s View Source feature to extract the form and modify it to provide
malicious input to your website

...

 If you don’t, users may try to inject JavaScript
into the data to interfere with your site’s operation, or even attempt to add MySQL
commands to compromise your database

...

 For example, to prevent
escape characters being injected into a string that will be presented to MySQL, you
should use the following (remembering that this function takes into account the current
character set of a MySQL connection, so it can be used only with an open connection):
$variable = mysql_real_escape_string($variable);

To get rid of unwanted slashes, use:
$variable = stripslashes($variable);

And to remove any HTML from a string, use the following:
$variable = htmlentities($variable);

For example, this would change a string of interpretable HTML code like hi
into <b>hi</b>, which displays as text, and won’t be interpreted as HTML
tags

...


Example 11-9

...

it-ebooks

...

Com

function sanitizeMySQL($var)
{
$var = mysql_real_escape_string($var);
$var = sanitizeString($var);
return $var;
}
?>

Add this code to the end of your PHP programs and you can then call it for each user
input to sanitize, like this:
$variable = sanitizeString($_POST['user_input']);

Or, when you have an open MySQL connection:
$variable = sanitizeMySQL($_POST['user_input']);

An Example Program
So let’s look at how a real life PHP program integrates with an HTML form by creating
the program convert

...

 Type it in as shown and try it for
yourself

...

 A program to convert values between Fahrenheit and Celsius

...

php">
Fahrenheit 
Celsius 



264 | Chapter 11: Form Handling

www

...

info
Download at Boykma

...

php in a browser, the result should look something like the
screenshot in Figure 11-8

...

 The temperature conversion program in action

To break the program down, the first line initializes the variables $c and $f in case they
do not get posted to the program

...

 If the user inputs
both, the Celsius is simply ignored and the Fahrenheit value is converted

...


So, having either submitted values or empty strings in both $f and $c, the next portion
of code constitutes an if

...

else structure that first tests whether $f has a
value

...


If $f is found to have a value, the variable $c is assigned a simple mathematical expression that converts the value of $f from Fahrenheit to Celsius

...

 The variable $out is then set to a message string
explaining the conversion

...

it-ebooks

...

Com

On the other hand, if $c is found to have a value, a complementary operation is performed to convert the value of $c from Celsius to Fahrenheit and assign the result to
$f

...

 As with the previous section,
the string $out is then set to contain a message about the conversion

...

 It’s not necessary, but looks better

...

 If no temperature conversion was made, $out will have a value of NULL
and nothing will be displayed, which is exactly what we want when the form hasn’t yet
been submitted

...


After this, we come to the form, which is set to submit using the POST method to the
file convert

...

 Within the form, there are two inputs for either a
Fahrenheit or Celsius value to be entered

...


After outputting the HTML to close the document, we come finally to the function
sanitizeString from Example 11-9

...

 I recommend this, as the neatest and most secure method

...

 Reasons to do this might include making the result of a search bookmarkable or directly linkable from another page

...



Test Your Knowledge: Questions
Question 11-1
Form data can be submitted using either the POST or the GET method

...

it-ebooks

...

Com

Question 11-4
If a form has to offer three choices to a user, each of which is mutually exclusive,
so that only one of the three can be selected, which input type would you use for
this, given a choice between checkboxes and radio buttons?
Question 11-5
How can you submit a group of selections from a web form using a single field
name?
Question 11-6
How can you submit a form field without displaying it in the browser?
Question 11-7
Which HTML tag is used to encapsulate a form element and support text or
graphics, making the entire unit selectable with a mouse-click?
Question 11-8
Which PHP function converts HTML into a format that can be displayed but will
not be interpreted as HTML by a browser?
See the section “Chapter 11 Answers” on page 444 in Appendix A for the answers to
these questions

...

it-ebooks

...

Com

www

...

info
Download at Boykma

...


Initially PHP itself was developed as a sort of templating system with a few elements
of programming and flow control

...

 Some developers still treat it a little like a templating system, though, as in the case of the WordPress blogging platform, which uses
a set of template PHP files for each theme

...

 Additionally, using a separate templating system frees up designers to modify templates to their hearts’ content, safe in
the knowledge that nothing they do can break your program code; it leads to much
greater flexibility

...

 Without a
templating system, you’d very likely have to search through many files of PHP code to
make the necessary modifications

...

 If you’re one of
them, I still recommend that you read this chapter, as you’ll learn all
about templating, in case you’re suddenly required to work on any
projects that use it

...

it-ebooks

...

Com

Why Smarty?
The Smarty templating system is probably the best known and most used on the Internet

...

 They can modify the templates all they
want, but the code stays intact

...


• Errors in the templates are confined to Smarty’s error-handling routines, making
them simple and intuitive to deal with

...


• Programmers can go about maintaining the application code, changing the way
content is acquired, and so on, without disturbing the presentation layer

...


• Smarty has many security features built in so that designers won’t breach security
and you won’t open your server to the execution of arbitrary PHP code

...



Installation
To install Smarty, visit http://www

...

net/download

...

 Once it’s downloaded, you need to perform the following steps:
1

...


2

...

 Create a new folder called Smarty in this document root

...

 Open the extracted folder, navigate into the libs directory, and copy the entire
contents (including subfolders) into the Smarty directory you just created

...

)
plugins
(various files

...

class

...

tpl

270 | Chapter 12: Templating with Smarty

www

...

info
Download at Boykma

...

class

...

class

...

 So create the new application directory temp just under the
same document root where you just installed Smarty

...


Inside the temp directory, create another one called smarty to house the folders containing the template files

...

 Your directory structure is
now:
temp

smarty
cache
config
templates
templates_c

Creating Scripts
You are now ready to create some Smarty scripts

...

php

...

 The smarty

...

php
$path = $_SERVER['DOCUMENT_ROOT'];
require "$path/Smarty/Smarty

...

php";
$smarty = new Smarty();
$smarty->template_dir =
$smarty->compile_dir =
$smarty->cache_dir
=
$smarty->config_dir
=

"$path/temp/smarty/templates";
"$path/temp/smarty/templates_c";
"$path/temp/smarty/cache";
"$path/temp/smarty/configs";

$smarty->assign('title', 'Test Web Page');
$smarty->display("$path/temp/index

...

class

...

 Because we will be using both 

...

tpl template files
in this chapter, I have included everything you need in each file

...

 It
then uses $path as a prefix for fetching the Smarty class files and the template files from
the temp folder

...



Creating Scripts | 271

www

...

info
Download at Boykma

...

 This creates a Smarty variable called
title and assigns it the string value “Test Web Page”

...


Once you have typed the program in, save it using the filename smarty

...



Creating Templates
Now you need to write a simple Smarty template file to test whether everything is
working, so type in Example 12-2 and save it in a file named index

...


Example 12-2

...

tpl template file





This is a Smarty Test



As you can see, this is simply an HTML file with a 

...

 But note the use
of the Smarty variable {$title} on the third line

...

 Smarty will substitute the value of the variable instead of the
text in Example 12-2, because of the surrounding curly braces {} (see Figure 12-1)

...

 The output from index

...

php from Example 10-8 in Chapter 10 and rewrite it to
use Smarty

...

 Example 12-3 is the revised program

...

php

...

it-ebooks

...

Com

Example 12-3

...

php program rewritten for Smarty as smartytest

...

php
$path = $_SERVER['DOCUMENT_ROOT'];
require "$path/Smarty/Smarty

...

php";
$smarty = new Smarty();
$smarty->template_dir =
$smarty->compile_dir =
$smarty->cache_dir
=
$smarty->config_dir
=

"$path/temp/smarty/templates";
"$path/temp/smarty/templates_c";
"$path/temp/smarty/cache";
"$path/temp/smarty/configs";

require_once("$path/temp/login

...

 mysql_error());
mysql_select_db($db_database)
or die("Unable to select database: " 

...


mysql_error() 

...


"('$author', '$title', '$category', '$year', '$isbn')";

}

}

if (!mysql_query($query))
{
echo "INSERT failed: $query
" 

...

 "
";
}

$query = "SELECT * FROM classics";

A Practical Example | 273

www

...

info
Download at Boykma

...

 mysql_error());
$rows = mysql_num_rows($result);
for ($j = 0 ; $j < $rows ; ++$j)
{
$results[] = mysql_fetch_array($result);
}
mysql_close($db_server);
$smarty->assign('results', $results);
$smarty->display("$path/temp/smartytest

...


• You must have copied the file login

...


The program starts off by loading in both the Smarty class and login

...

 Then it is followed by the program code from the old sqltest

...

 In place of the HTML, we’ll use the
presentation layer template that I’ll show next

...

 The reason for this is to fetch an entire row as an associative array

...

 The
$results object is an array of arrays

...


This makes it easy to pass a lot of data directly to the Smarty template

...



274 | Chapter 12: Templating with Smarty

www

...

info
Download at Boykma

...

 So now let’s look at the template, which is in Example 12-4

...

tpl in the temp/smarty/templates folder you created earlier

...

 The smartytest

...

php" method="post">

Author 
Title 
Category 
Year 
ISBN 


{section name=row loop=$results}


Author
{$results[row]

...

title}
Category {$results[row]

...

year}
ISBN
{$results[row]

...

php program

...

 A {section} tag is used for looping over arrays of data

...


In the same {section} tag, the loop keyword indicates the array that must be processed

...

 Given these parameters, it is possible to pull any data wanted from the result
rows that were returned by MySQL

...

 For example, to output
the current row’s year field, use {$results[row]

...

year tells Smarty which
column to reference

...

it-ebooks

...

Com

Figure 12-2

...

php and smartytest

...

php (now you see why the program was changed from using
mysql_fetch_row to mysql_fetch_array)

...

 Otherwise, the HTML below it is displayed

...

 They can place any items of data in any positions and in
any order

...


And as for you the programmer? If you’re handing over the task of layout
to a web designer, you’ll never have to worry what the output is going
to look like

...

 It’s up to them to then knock it all together
into an award-winning design

...



276 | Chapter 12: Templating with Smarty

www

...

info
Download at Boykma

...

 If you think templating will be useful in your projects, you
can learn what else it can do for you at http://www

...

net/crashcourse

...

smarty

...


In the next chapter, we’ll look at a range of practical PHP functions and techniques
that you’ll need to create efficient programs

...


Question 12-2
How does a PHP program pass a variable to a Smarty template?
Question 12-3
How does a Smarty template access a variable that has been passed to it?
Question 12-4
What Smarty programming tag is used to iterate through an array?
Question 12-5
How do you enable Smarty templating in a PHP program?
See the section “Chapter 12 Answers” on page 445 in Appendix A for the answers to
these questions

...

it-ebooks

...

Com

www

...

info
Download at Boykma

...

 Even if you aren’t offering logins and passwords, you
will still often find a need to store details about a user’s current session and possibly
also recognize them when they return to your site

...

 Between them, they offer the
opportunity for you to configure your site to your users’ preferences and ensure a
smooth and enjoyable transition through it

...

 It can contain almost any alphanumeric information (as long as it’s under
4 KB) and can be retrieved from your computer and returned to the server

...


Because of their privacy implications, cookies can be read only from the issuing domain

...

com, it can be retrieved only
by a web server using that domain

...


Due to the way the Internet works, multiple elements on a web page can be embedded
from multiple domains, each of which can issue its own cookies

...

 Most commonly, these are created by advertising companies in order to track users across multiple websites

...

 Fortunately, most people who disable
cookies do so only for third-party websites

...

it-ebooks

...

Com

Figure 13-1

...

 Therefore careful planning of cookie usage is important

...


This exchange shows a browser receiving two pages:
1

...

html, at the website
http://www

...

com

...


2

...

com receives this pair of headers, it returns some
of its own

...

 Only
then are the contents of the web page transferred

...

 Once the browser has received the cookie, it will then return it with every future
request made to the issuing server until the cookie expires or is deleted

...

html, it also returns the cookie name with
the value value

...

 Because the cookie has already been set, when the server receives the request to
send /news

...



280 | Chapter 13: Cookies, Sessions, and Authentication

www

...

info
Download at Boykma

...

 As long as no HTML has yet been transferred,
you can call the setcookie function, which has the following syntax (see Table 13-1):
setcookie(name, value, expire, path, domain, secure, httponly);

Table 13-1

...

 This is the name that your server will use to access
the cookie on subsequent browser requests

...

 This can contain up to 4
KB of alphanumeric text

...

 Generally, you will
probably use time() plus a number of seconds

...



time() + 2592000

path

(optional) The path of the cookie on the server

...

webserver

...

 If it is a subdirectory, the cookie is available only within that
subdirectory

...



/

domain

(optional) The Internet domain of the cookie

...

com, the
cookie is available to all of webserver

...

webserver

...

webserver

...

 If it is images

...

com, the cookie is available only to images

...

com and its
subdomains such as sub

...

webserver

...

webserver

...





...

com

secure

(optional) Whether the cookie must use a secure connection (https://)

...

 The default is FALSE

...

2

...

 If this value is TRUE, scripting languages such as
JavaScript cannot access the cookie

...

 The
default is FALSE

...

 For
example, if you wish to see whether the current browser has the cookie called username already stored and, if so, to read its value, use the following:
Using Cookies in PHP | 281

www

...

info
Download at Boykma

...

 This
means that when you issue a cookie, you cannot read it in again until the browser
reloads the page (or another with access to the cookie) from your website and passes
the cookie back to the server in the process

...

 It is important for
all parameters in your new setcookie call except the timestamp to be identical to the
parameters when the cookie was first issued; otherwise, the deletion will fail

...

 However, I have
used a time of 2592000 seconds (one month) in the past in case the client computer’s
date and time are not correctly set

...

 It’s adequate for most applications that ask users to log in, although some
applications have specialized needs or more stringent security requirements that call
for other techniques

...

 The server must have this feature turned on in order
for it to work, but because it’s so common, your server is very likely to offer the feature

...

 So attempting to run
these examples may generate an error telling you that the feature is not
enabled, in which case you must install the module, change the configuration file to load the module, or ask your system administrator to do
these fixes

...


The code to make this happen looks like Example 13-1

...

it-ebooks

...

Com

Figure 13-2

...

 PHP authentication
if (isset($_SERVER['PHP_AUTH_USER']) &&
isset($_SERVER['PHP_AUTH_PW']))
{
echo "Welcome User: " 

...


" Password: " 

...

0 401 Unauthorized');
die("Please enter your username and password");
}
?>

The first thing the program does is look for two particular values:
$_SERVER['PHP_AUTH_USER'] and $_SERVER['PHP_AUTH_PW']

...


If either of the values do not exist, the user has not yet been authenticated and the
prompt in Figure 13-2 is displayed by issuing the following header, where “Basic realm”
is the name of the section that is protected and appears as part of the pop-up prompt:
WWW-Authenticate: Basic realm="Restricted Area"

If the user fills out the fields, the PHP program runs again from the top

...

0 401 Unauthorized

The die statement causes the text “Please enter your username and password” to be
displayed (see Figure 13-3)

...

it-ebooks

...

Com

Figure 13-3

...

 You may need to close and reopen your
browser a few times as you work through this section and try different
things out

...

 The code in Example 13-1 doesn’t
require much change to add this check, other than modifying the previous welcome
message code into a test for a correct username and password, followed by issuing a
welcome message

...


Example 13-2

...

0 401 Unauthorized');
die ("Please enter your username and password");
}
?>

284 | Chapter 13: Cookies, Sessions, and Authentication

www

...

info
Download at Boykma

...

” It doesn’t say whether the username or the password or both were
wrong—the less information you can give to a potential hacker, the better

...

 Also, the password appears in clear text within the PHP file, and if someone
managed to hack into your server, they would instantly know it

...



Storing Usernames and Passwords
Obviously MySQL is the natural way to store usernames and passwords

...

 Instead, we’ll use a neat trick called a
one-way function

...

 Due to their one-way nature, such functions are virtually impossible to reverse,
so their output can be safely stored in a database—and anyone who steals it will be
none the wiser as to the passwords used

...

 You pass it a string to hash and it returns
a 32-character hexadecimal number

...



Salting
Unfortunately, md5 on its own is not enough to protect a database of passwords, because
it could still be susceptible to a brute force attack that uses another database of known
32-character hexadecimal md5 tokens

...


Thankfully, though, we can put a spanner in the works of any such attempts by salting all the passwords before they are sent to md5

...

 Of course,
the more obscure you can make the salt, the better

...

it-ebooks

...

Com

Here some random characters have been placed both before and after the password

...


All you have to do when verifying someone’s login password is to add these same
random strings back in before and after it, and then check the resulting token from an
md5 call against the one stored in the database for that user

...

 So
type in and save the program in Example 13-3 as setupusers

...


Example 13-3

...

php
require_once 'login

...

 mysql_error());
mysql_select_db($db_database)
or die("Unable to select database: " 

...

 mysql_error());
$salt1 = "qm&h*";
$salt2 = "pg!@";
$forename = 'Bill';
$surname = 'Smith';
$username = 'bsmith';
$password = 'mysecret';
$token
= md5("$salt1$password$salt2");
add_user($forename, $surname, $username, $token);
$forename = 'Pauline';
$surname = 'Jones';
$username = 'pjones';
$password = 'acrobat';
$token
= md5("$salt1$password$salt2");
add_user($forename, $surname, $username, $token);
function add_user($fn, $sn, $un, $pw)
{
$query = "INSERT INTO users VALUES('$fn', '$sn', '$un', '$pw')";
$result = mysql_query($query);
if (!$result) die ("Database access failed: " 

...

it-ebooks

...

Com

}
?>

This program will create the table users within your publications database (or whichever database you set up for the login

...

 In this table, it will create
two users: Bill Smith and Pauline Jones

...


Using the data in this table, we can now modify Example 13-2 to properly authenticate
users, and Example 13-4 shows the code needed to do this

...

php, and call it up in your browser

...

 PHP authentication using MySQL

...

php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " 

...

 mysql_error());
if (isset($_SERVER['PHP_AUTH_USER']) &&
isset($_SERVER['PHP_AUTH_PW']))
{
$un_temp = mysql_entities_fix_string($_SERVER['PHP_AUTH_USER']);
$pw_temp = mysql_entities_fix_string($_SERVER['PHP_AUTH_PW']);
$query = "SELECT * FROM users WHERE username='$un_temp'";
$result = mysql_query($query);
if (!$result) die("Database access failed: " 

...

0 401 Unauthorized');
die ("Please enter your username and password");

function mysql_entities_fix_string($string)
{
return htmlentities(mysql_fix_string($string));
}

HTTP Authentication | 287

www

...

info
Download at Boykma

...

 But don’t be put off

...

 They are there to sanitize the user input—very important

...

 Next, a query is issued to MySQL to look up the user
$un_temp and, if a result is returned, to assign the first row to $row

...

) Then the two salts are created in $salt1 and
$salt2, which are then added before and after the submitted password $pw_temp

...


Now all that’s necessary is to check $token against the value stored in the database,
which happens to be in the fourth column—which is column 3 when starting from 0

...

 If the two
match, a friendly welcome string is output, calling the user by his or her first name (see
Figure 13-4)

...

 As mentioned before, the error
message is the same regardless of whether such a username exists, as this provides
minimal information to potential hackers or password guessers

...

 Bill Smith has now been authenticated

You can try this out for yourself by calling up the program in your browser and entering
a username of “bsmith” and password of “mysecret” (or “pjones” and “acrobat”), the
values that were saved in the database by Example 13-3

...

it-ebooks

...

Com

Using Sessions
Because your program can’t tell what variables were set in other programs—or even
what values the same program set the previous time it ran—you’ll sometimes want to
track what your users are doing from one web page to another

...

 These are groups of variables that are stored on the
server but relate only to the current user

...


This cookie has meaning only to the web server and cannot be used to ascertain any
information about a user

...

 Well, that’s not a problem since PHP 4

...

0, because it will identify when
this is the case and place a cookie token in the GET portion of each URL request instead

...



Starting a Session
Starting a session requires calling the PHP function session_start before any HTML
has been output, similarly to how cookies are sent during header exchanges

...

 So let’s further modify authenticate

...


Example 13-5 shows the changes needed

...

 Type this program in (or modify Example 13-4) and save it as
authenticate2

...

 But don’t run it in your browser yet, as you will also need to create
a second program in a moment

...

 Setting a session after successful authentication

...

php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die("Unable to connect to MySQL: " 

...

 mysql_error());

Using Sessions | 289

www

...

info
Download at Boykma

...

 mysql_error());
elseif (mysql_num_rows($result))
{
$row = mysql_fetch_row($result);
$salt1 = "qm&h*";
$salt2 = "pg!@";
$token = md5("$salt1$pw_temp$salt2");
if ($token == $row[3])
{
session_start();
$_SESSION['username'] = $un_temp;
$_SESSION['password'] = $pw_temp;
$_SESSION['forename'] = $row[0];
$_SESSION['surname'] = $row[1];
echo "$row[0] $row[1] : Hi $row[0],
you are now logged in as '$row[2]'";
die ("

...

0 401 Unauthorized');
die ("Please enter your username and password");

function mysql_entities_fix_string($string)
{
return htmlentities(mysql_fix_string($string));
}
function mysql_fix_string($string)
{
if (get_magic_quotes_gpc()) $string = stripslashes($string);
return mysql_real_escape_string($string);
}
?>

One other addition to the program is the “Click here to continue” link with a destination URL of continue

...

 This will be used to illustrate how the session will transfer
to another program or PHP web page

...

php by typing in the program
in Example 13-6 and saving it

...

it-ebooks

...

Com

Example 13-6

...

php
session_start();
if (isset($_SESSION['username']))
{
$username = $_SESSION['username'];
$password = $_SESSION['password'];
$forename = $_SESSION['forename'];
$surname = $_SESSION['surname'];
echo "Welcome back $forename

...



Your username is '$username'
and your password is '$password'

...

php>click here to log in

...

php into your browser, enter a username of
“bsmith” and password of “mysecret”, (or “pjones” and “acrobat”) when prompted,
and click on the link to load in continue

...

 When your browser calls it up, the result
should be something like Figure 13-5

...

 Maintaining user data with sessions

Sessions neatly confine to a single program the extensive code required to authenticate
and log in a user

...

 You need only to call up
session_start and look up any variables to which you need access from $_SESSION

...


If $_SESSION['username'] has not been assigned a value, no session is active, so the last
line of code in Example 13-6 directs users to the login page at authenticate2

...


Using Sessions | 291

www

...

info
Download at Boykma

...

php program prints back the value of the user’s password
to show you how session variables work

...



Ending a Session
When the time comes to end a session, usually when a user requests to log out from
your site, you can use the session_destroy function in association with the unset function, as in Example 13-7

...


Example 13-7

...

php as in Example 13-8

...

 Retrieving session variables, then destroying the session
session_start();
if (isset($_SESSION['username']))
{
$username = $_SESSION['username'];
$password = $_SESSION['password'];
$forename = $_SESSION['forename'];
$surname = $_SESSION['surname'];
echo "Welcome back $forename

...



Your username is '$username'
and your password is '$password'

...

php>click here to log in

...

it-ebooks

...

Com

}
?>

session_destroy();

The first time you surf from authenticate2

...

php, it will display all the
session variables

...



Setting a timeout
There are other times when you might wish to close a user’s session yourself, such as
when the user has forgotten or neglected to log out, and you wish the program to do
it for them for their own security

...


To do this, use the ini_set function as follows

...

gc_maxlifetime', 60 * 60 * 24);

If you wish to know what the current timeout period is, you can display it using the
following:
echo ini_get('session

...

 The reason is that it’s possible to use packet sniffing (sampling of data) to discover
session IDs passing across a network

...

 The only truly secure way
of preventing these from being discovered is to implement a Secure Socket Layer (SSL)
and run HTTPS instead of HTTP web pages

...

apache-ssl

...



Preventing session hijacking
When SSL is not a possibility, you can further authenticate users by storing their IP
address along with their other details by adding a line such as the following when you
store their session:
$_SESSION['ip'] = $_SERVER['REMOTE_ADDR'];

Then, as an extra check, whenever any page loads and a session is available, perform
the following check

...

it-ebooks

...

Com

if ($_SESSION['ip'] != $_SERVER['REMOTE_ADDR']) different_user();

What code you place in your different_user function is up to you

...

 Don’t say any more than that or you’re giving away potentially useful
information

...

 Again,
if this is a problem for you, use SSL

...

 Use the following to store the user agent:
$_SESSION['ua'] = $_SERVER['HTTP_USER_AGENT'];

And use this to compare the current agent string with the saved one:
if ($_SESSION['ua'] != $_SERVER['HTTP_USER_AGENT']) different_user();

Or, better still, combine the two checks like this and save the combination as an md5
hexadecimal string:
$_SESSION['check'] = md5($_SERVER['REMOTE_ADDR'] 

...


$_SERVER['HTTP_USER_AGENT'])) different_user();

Preventing session fixation
Session fixation happens when a malicious user tries to present a session ID to the server
rather than letting the server create one

...

com/authenticate

...


Now, consider Example 13-9, which is susceptible to session fixation

...

php

...

 A session susceptible to session fixation

...

it-ebooks

...

Com

Once saved, call it up in your browser using the following URL (prefacing it with the
correct pathname, such as http://localhost/web/):
sessiontest

...

 Now try browsing to:
sessiontest

...

 Leave
the counter on a different number than the first URL and then go back to the first URL
and see how the number changes back

...


The reason this approach is so dangerous is that a malicious attacker could try to distribute these types of URLs to unsuspecting users, and if any of them followed these
links, the attacker would be able to come back and take over any sessions that had not
been deleted or expired!
To prevent this, add a simple additional check to change the session ID using
session_regenerate_id

...

 To do this, you can
check for a special session variable’s existence

...


Example 13-10 shows what the code might look like using the session variable
initiated

...

 Session regeneration
session_start();
if (!isset($_SESSION['initiated']))
{
session_regenerate_id();
$_SESSION['initiated'] = 1;
}
if (!isset($_SESSION['count'])) $_SESSION['count'] = 0;
else ++$_SESSION['count'];
echo $_SESSION['count'];
?>

This way, an attacker can come back to your site using any of the session IDs that he
generated, but none of them will call up another user’s session, as they will all have
been replaced with regenerated IDs

...



Using Sessions | 295

www

...

info
Download at Boykma

...

use_only_cookies', 1);

With that setting, the ?PHPSESSID= trick will be completely ignored

...



Using a shared server
On a server shared with other accounts, you will not want to have all your session data
saved into the same directory as theirs

...

save_path', '/home/user/myaccount/sessions');

The configuration option will keep this new value only during the program’s execution,
and the original configuration will be restored at the program’s ending

...

 The more it’s used, the less time you
will want to keep a session stored

...

 There are automated bots running riot around the Internet trying to find sites vulnerable to exploits

...



At this point, you should now have a very good grasp of both PHP and MySQL, so in
the next chapter it’s time to introduce the third major technology covered by this book,
JavaScript

...

it-ebooks

...

Com

Question 13-4
Where are the username and password stored in a PHP program when using HTTP
authentication?
Question 13-5
Why is the md5 function a powerful security measure?
Question 13-6
What is meant by “salting” a string?
Question 13-7
What is a PHP session?
Question 13-8
How do you initiate a PHP session?
Question 13-9
What is session hijacking?
Question 13-10
What is session fixation?
See the section “Chapter 13 Answers” on page 445 in Appendix A for the answers to
these questions

...

it-ebooks

...

Com

www

...

info
Download at Boykma

...

 Every time you see something pop up when you mouse over an item in the browser, or see new text, colors, or
images appear on the page in front of your eyes, or grab an object on the page and drag
it to a new location—all those things are done through JavaScript

...


JavaScript first appeared in the Netscape Navigator browser in 1995, coinciding with
the addition of support for Java technology in the browser

...

 However, the naming was just a marketing ploy
to help the new scripting language benefit from the popularity of the Java programming
language

...


DOM makes it relatively easy to add a new paragraph or focus on a piece of text and
change it

...

 They are
both fairly high-level languages, too; for instance, they are weakly typed, so it’s easy to
change a variable to a new type just by using it in a new context

...

 And you’ll be
glad you did, because it’s at the heart of the Web 2

...



JavaScript and HTML Text
JavaScript is a client-side scripting language that runs entirely inside the web browser

...


299

www

...

info
Download at Boykma

...

01 “Hello World” document using JavaScript might look like Example 14-1

...

 “Hello World” displayed using JavaScript





...

 It is there so that browsers that do support JavaScript will ignore the
following -->, but non-JavaScript browsers will ignore the preceding //, and act on the
--> by closing the HTML comment

...

)
// -->

However, the use of these comments is unnecessary for any browser released over the
past several years

...


These include Microsoft’s VBScript, which is based on the Visual Basic
programming language, and Tcl, a rapid prototyping language

...

 VBScript works only in Internet Explorer; use of it in other browsers requires a plug-in

...

 So both should be considered nonstandard and neither is
covered in this book

...

 The
syntax for this is:
 tags

...

 First there’s the single line comment, like this:
// This is a comment

This style uses a pair of forward slash characters (//) to inform JavaScript that everything following is to be ignored

...

 Just
remember that you cannot nest multiline comments, so make sure that you don’t comment out large sections of code that already contain multiline comments

...

 Therefore the following is valid:
x += 10

Semicolons | 305

www

...

info
Download at Boykma

...


There are exceptions to the semicolon rule

...


So, if in doubt, use a semicolon

...


Instead, variables use the following naming rules:
• A variable may include only the letters a-z, A-Z, 0-9, the $ symbol, and the
underscore (_)

...


• The first character of a variable name can be only a-z, A-Z, $, or _ (no numbers)

...

 Count, count, and COUNT are all different variables

...


And yes, you’re right, that is the $ sign there in that list

...

 Although I don’t recommend
keeping the $ signs, it means that you can port a lot of PHP code more quickly to
JavaScript that way

...

 But a quote of the same type must be escaped using the backslash
character, like this:
greeting = "\"Hello there\" is a greeting"
warning = '\'Be careful\' is a warning'

To read from a string variable, you can assign it to another one, like this:
newstring = oldstring

306 | Chapter 14: Exploring JavaScript

www

...

info
Download at Boykma

...

write(status)

Numeric Variables
Creating a numeric variable is as simple as assigning a value, like these examples:
count
= 42
temperature = 98

...



Arrays
JavaScript arrays are also very similar to those in PHP, in that an array can contain
string or numeric data, as well as other arrays

...

 So, to create
a two-dimensional array containing the colors of a single face of a scrambled Rubik’s
Cube (where the colors red, green, orange, yellow, blue, and white are represented by
their capitalized initial letters), you could use the following code

...

write(face[1][2])

This statement will output the letter O for orange

...

it-ebooks

...

Com

JavaScript arrays are powerful storage structures, so Chapter 16 discusses them in much greater depth

...

 JavaScript mathematical operators
look a lot like plain arithmetic; for instance, the following statement outputs 16:
document

...



Arithmetic Operators
Arithmetic operators are used to perform mathematics

...


Table 14-2

...

14

%

Modulus (division remainder)

j%6

++

Increment

++j

--

Decrement

--j

Assignment Operators
The assignment operators are used to assign values to variables

...

 The operator += adds the value on the
right side to the variable on the left, instead of totally replacing the value on the left

...


308 | Chapter 14: Exploring JavaScript

www

...

info
Download at Boykma

...

 Assignment operators
Operator

Example

Equivalent to

=

j = 99

j = 99

+=

j += 2

j = j + 2

+=

j += 'string'

j = j +
'string'

-=

j -= 12

j = j - 12

*=

j *= 2

j = j * 2

/=

j /= 6

j = j / 6

%=

j %= 7

j = j % 7

Comparison Operators
Comparison operators are generally used inside a construct such as an if statement
where you need to compare two items

...


Table 14-4

...


Table 14-5

...

it-ebooks

...

Com

Variable Incrementing and Decrementing
The following forms of post- and preincrementing and decrementing you learned to
use in PHP are also supported by JavaScript:
++x
--y
x += 22
y -= 3

String Concatenation
JavaScript handles string concatenation slightly differently from PHP

...

 (period) operator, it uses the plus sign (+), like this:
document

...

")

Assuming that the variable messages is set to the value 3, the output from this line of
code will be:
You have 3 messages

...

 Here
is an example using tabs to lay out a heading; it is included here merely to illustrate
escapes, because in web pages, there are better ways to do layout:
heading = "Name\tAge\tLocation"

Table 14-6 details the escape characters available

...

 JavaScript’s escape characters
Character

Meaning

\b

Backspace

\f

Form feed

\n

New line

\r

Carriage return

\t

Tab

\'

Single quote (or apostrophe)

\"

Double quote

310 | Chapter 14: Exploring JavaScript

www

...

info
Download at Boykma

...

 Usually, you don’t have to worry about the type; JavaScript figures out what you
want and just does it

...

 The variable n is assigned the string value “838102050”, the next line prints out its
value, and the typeof operator is used to look up the type

...

 n is given the value returned when the numbers 12345 and 67890 are multiplied
together

...

 The type
of variable is then looked up and displayed

...

 Some text is appended to the number n and the result is displayed

...

 Setting a variable’s type by assignment


This function takes the two parameters passed, multiplies them together, and returns
the product

...

 They can be defined in the following ways:
a = 123
// Global scope
var b = 456
// Global scope
if (a == 123) var c = 789 // Global scope

Regardless of whether you are using the var keyword, as long as a variable is defined
outside of a function, it is global in scope

...



Local Variables
Parameters passed to a function automatically have local scope

...

 However, there is one exception

...


To define a local variable that has scope only within the current function, and has not
been passed as a parameter, use the var keyword

...



312 | Chapter 14: Exploring JavaScript

www

...

info
Download at Boykma

...

 A function creating variables with global and local scope


To test whether scope setting has worked in PHP, we can use the isset function

...


Example 14-11

...

 So let’s use it to test our isset function in Example 14-12

...

 Checking the scope of the variables defined in function test
 tags

...

com

The Document Object Model | 315

www

...

info
Download at Boykma

...

write method

...


There is also a short form that works equally well, which starts with the value in the
id attribute: mylink

...

 So you can replace this:
url = document

...

mylink

...

href

Browser Incompatibilities
If you tried Example 14-13 in Safari, Firefox, Opera, or Chrome, it will have worked
just great

...

 Welcome to the world of advanced web development!
So what can we do about this? Well, in this case, instead of using the links child object
of the parent document object, which Internet Explorer balks at when used this way,
you have to replace it with a method to fetch the element by its id

...

links

...

href

can be replaced with this one:
url = document

...

href

And now the script will work in all major browsers

...

href

Another use for the $ sign
As mentioned earlier, the $ symbol is allowed in JavaScript variable and function names

...

href

Some enterprising programmers have decided that the getElementById function is so
prevalent in JavaScript that they have written a function to replace it called $, shown
in Example 14-14

...

 A replacement function for the getElementById method


Therefore, as long as you have included the $ function in your code, syntax such as:
$('mylink')

...

getElementById('mylink')

...

links[0]

...

links

...

links

...

write(document

...

href + '
')

The length of something is a property of every array, and many objects as well

...

write(history

...

 But you can replace the current page with one from the history, if you know
what position it has within the history

...

 For example, to send the browser back three pages, issue the following
command:
history

...

back()
history

...

location

...

com'

The Document Object Model | 317

www

...

info
Download at Boykma

...

 As
you progress through the following chapters on JavaScript, you’ll become quite familiar
with the DOM and how to access it

...


Question 14-9
Which two JavaScript commands will make the browser load the previous page in
its history array?
Question 14-10
What JavaScript command would you use to replace the current document with
the main page at the oreilly

...



318 | Chapter 14: Exploring JavaScript

www

...

info
Download at Boykma

...

 Now it’s time to
look at how to construct complex expressions in JavaScript and how to control the
program flow of your scripts using conditional statements

...

 As you learned in Chapter 4,
an expression is a combination of values, variables, operators, and functions that results
in a value; the result can be a number, a string, or a Boolean value (which evaluates to
either true or false)

...

 For each line, it prints out a letter between a and d, followed by a colon and the result of the expressions (the 
 tag is
there to create a line break and separate the output into four lines)

...

 Four simple Boolean expressions


"
"
"
"

+
+
+
+

(42 > 3)
(91 < 4)
(8 == 2)
(4 < 17)

+
+
+
+

""""
/>")
/>")
/>")
/>")

The output from this code is as follows:
a:
b:
c:
d:

true
false
false
true

Notice that both expressions a: and d: evaluate to true

...

 Unlike PHP (which would print the number 1 and nothing, respectively), actual
strings of “true” and “false” are displayed

...

it-ebooks

...

Com

In JavaScript, when checking whether a value is true or false, all values evaluate to
true with the exception of the following, which evaluate to false: the string false itself,
0, −0, the empty string, null, undefined, and NaN (Not a Number, a computer engineering concept for an illegal floating-point operation such as division by zero)

...

 This is because, unlike in PHP,
these values must be in lowercase in JavaScript

...

write('true') // True
if (1 == TRUE) document

...



Literals and Variables
The simplest form of an expression is a literal, which means something that evaluates
to itself, such as the number 22 or the string “Press Enter”

...

 They are both
types of expressions, because they return a value

...


Example 15-2

...

write("a:
document

...

write("c:
document

...

write("e:


"
"
"
"
"

+
+
+
+
+

42
"Hi"
true
myname
myage

+
+
+
+
+

"""""
/>")
/>")
/>")
/>")
/>")

//
//
//
//
//

Numeric literal
String literal
Constant literal
Variable string literal
Variable numeric literal

And, as you’d expect, you see a return value from all of these in the following output:
a:
b:
c:
d:
e:

42
Hi
true
Peter
24

Operators let you create more complex expressions that evaluate to useful results

...



320 | Chapter 15: Expressions and Control Flow in JavaScript

www

...

info
Download at Boykma

...

 The first assigns the result of the expression 366 day_number to the variable days_to_new_year, and the second outputs a friendly message
only if the expression days_to_new_year < 30 evaluates to true

...

 Two simple PHP statements


is
is
is
is

greater than b
")
less than b
")
greater than or equal to b
")
less than or equal to b
")

In this example, where a is 7 and b is 11, the following is output:
7 is less than 11
7 is less than or equal to 11

Logical operators
Logical operators produce true-or-false results, and are also known as Boolean operators

...


Table 15-4

...


Example 15-7

...

write((a && b) + "
")
document

...

write(( !b ) + "
")


The && statement requires both operands to be true if it is going to return a value of
true, the || statement will be true if either value is true, and the third statement performs a NOT on the value of b, turning it from 0 into a value of true

...

it-ebooks

...

Com

The || operator can cause unintentional problems, because the second operand will
not be evaluated if the first is evaluated as true

...


Example 15-8

...


Example 15-9

...

or statement modified to ensure calling of getnext


In this case, the code in function getnext will be executed and its return value stored
in gn before the if statement

...

 You should
also note that !true equals false and !false equals true

...

 All possible logical expressions
Inputs

Operators & results

a

b

&&

||

true

true

true

true

true

false

false

true

false

true

false

true

false

false

false

false

The with Statement
The with statement is not one that you’ve seen in earlier chapters on PHP, because it’s
exclusive to JavaScript

...


References to properties and methods within the with block are assumed to apply to
that object

...

write function
never references the variable string by name

...

it-ebooks

...

Com

Example 15-10

...

write("The string is " + length + " characters
")
document

...

write, this code still manages to output the following:
The string is 43 characters
In upper case it's: THE QUICK BROWN FOX JUMPS OVER THE LAZY DOG

This is how the code works: the JavaScript interpreter recognizes that the length property and toUpperCase() method have to be applied to some object

...



Using onError
Here are more constructs not available in PHP

...


Events are actions that can be detected by JavaScript

...

 For example, the onClick event of
a button element can be set to call a function and make it run whenever a user clicks
on the button

...


Example 15-11

...

writ("Welcome to this website") // Deliberate error
function errorHandler(message, url, line)
{
out = "Sorry, an error was encountered

...

\n\n";
alert(out);
return true;
}


326 | Chapter 15: Expressions and Control Flow in JavaScript

www

...

info
Download at Boykma

...

 Using the onError event with an alert method pop up

The first line of this script tells the error event to use the new errorHandler function
from now on

...


Then, to test the new function, a syntax error is deliberately placed in the code with a
call to document

...

write (the final e is missing)

...

 Using onError this way can also be
quite useful during the debugging process

...



Using try

...

 These keywords let you trap errors for a
selected section of code, rather than all scripts in a document

...


The try

...


For example, in Chapter 18 we’ll be exploring Ajax techniques that make use of the
XMLHttpRequest object

...

catch | 327

www

...

info
Download at Boykma

...

 Therefore, we can use try and
catch to trap this case and do something else if the function is not available

...


Example 15-12

...

 There’s also another keyword associated with try
and catch called finally that is always executed, regardless of whether an error occurs
in the try clause

...

 They enable you to ask questions about certain things
and respond to the answers you get in different ways

...



The if Statement
Several examples in this chapter have already made use of if statements

...


Multiline if statements require curly braces around them, but as in PHP, you can omit
the braces for single statements

...

write("a is greater than 100")
}
if (b == 10) document

...

it-ebooks

...

Com

The else statement
When a condition has not been met, you can execute an alternative using an else
statement, like this:
if (a > 100)
{
document

...

write("a is less than or equal to 100")
}

Unlike, PHP there is no elseif statement, but that’s not a problem, because you can
use an else followed by another if to form the equivalent of an elseif statement, like
this:
if (a > 100)
{
document

...

write("a is less than 100")
}
else
{
document

...

 Although I have shown braces on the
statements, because each is a single line the whole previous example could be written
as follows:
if (a > 100) document

...

write("a is less than 100")
else document

...


For example, the following code takes the PHP menu system we put together in Chapter 4 and converts it to JavaScript

...

 Let’s say the options are Home, About, News,
Login, and Links, and we set the variable page to one of these according to the user’s
input

...

else if

...



Conditionals | 329

www

...

info
Download at Boykma

...

 A multiline if

...

 statement


==
==
==
==
==

"Home")
"About")
"News")
"Login")
"Links")

document

...

write("You
document

...

write("You
document

...


Example 15-14

...

write("You selected Home")
document

...

write("You selected News")
document

...

write("You selected Links")

The variable page is mentioned only once at the start of the switch statement

...

 When one occurs, the matching conditional
statement is executed

...



Breaking out
As you can see in the Example 15-14, just as with PHP, the break command allows your
code to break out of the switch statement once a condition has been satisfied

...



Default action
When no condition is satisfied, you can specify a default action for a switch statement
using the default keyword

...


Example 15-15

...

write("Unrecognized selection")
break

330 | Chapter 15: Expressions and Control Flow in JavaScript

www

...

info
Download at Boykma

...

else tests

...

 After that, place a : and
the code to execute if the expression evaluates to false

...


Example 15-16

...

write(
a <= 5 ?
"a is less than or equal to 5" :
"a is greater than 5"
)


The statement has been broken up into several lines for clarity, but you would be more
likely to use such a statement on a single line, in this manner:
size = a <= 5 ? "short" : "long"

Looping
Again, you will find many close similarities between JavaScript and PHP when it comes
to looping

...

while, and for loops

...

 If it is false, execution skips
over to the next JavaScript statement (if any)

...

 Example 15-17 shows such a loop

...

 A while loop


Looping | 333

www

...

info
Download at Boykma

...

 In such cases, you can use the
continue command

...


Example 15-21

...

write("
- Found at location " + j + "
")
continue
}
document

...

write call does not have to be enclosed in an else
statement (which it did before), because the continue command will skip it if a match
has been found

...

 Instead,
when you need a value to be of a certain type, use one of JavaScript’s built-in functions,
shown in Table 15-6

...

it-ebooks

...

Com

Table 15-6

...

1415927
i = parseInt(n)
document

...

write(parseInt(3

...

 The next chapter focuses on the use of functions, objects, and arrays in JavaScript

...


Question 15-8
How do if and while statements interpret conditional expressions of different data
types?

Test Your Knowledge: Questions | 335

www

...

info
Download at Boykma

...



336 | Chapter 15: Expressions and Control Flow in JavaScript

www

...

info
Download at Boykma

...

 In fact, JavaScript is
actually based on objects, because—as you’ve seen—it has to access the DOM, which
makes every element of an HTML document available to manipulate as an object

...



JavaScript Functions
In addition to having access to dozens of built-in functions (or methods) such as
write, which you have already seen being used in document

...

 Whenever you have a more complex piece of code that is likely to
be reused, you have a candidate for a function

...

]])
{
statements
}

The first line of the syntax indicates that:
• A definition starts with the word function

...



337

www

...

info
Download at Boykma

...


• One or more parameters, separated by commas, are optional (indicated by the
square brackets, which are not part of the function syntax)

...


In JavaScript there is a general naming convention for functions: the first letter of each
word in a name is capitalized except for the very first letter, which is lowercase

...

 The convention is commonly referred to as bumpyCaps

...

 These statements may include one or more
return statements, which force the function to cease execution and return to the calling
code

...



The arguments array
The arguments array is a member of every function

...

 Take the example of a
function called displayItems

...


Example 16-1

...

write(v1 + "
")
document

...

write(v3 + "
")
document

...

write(v5 + "
")
}


When you call this script in your browser, it will display the following:
Dog
Cat
Pony
Hamster
Tortoise

All of this is fine, but what if you wanted to pass more than five items to the function?
Also, reusing the document

...

 Luckily, the arguments array gives you the flexibility to handle
a variable number of arguments

...


338 | Chapter 16: JavaScript Functions, Objects, and Arrays

www

...

info
Download at Boykma

...

 Modifying the function to use the arguments array


Note the use of the length property, which you already encountered in the previous
chapter, and also how the array displayItems

...

 I also chose to keep the function short and sweet by not surrounding
the contents of the for loop in curly braces, as it contains only a single statement

...



Returning a Value
Functions are not used just to display things

...

 The function fixNames in
Example 16-3 uses the arguments array (discussed in the previous section) to take a
series of strings passed to it and return them as a single string

...


Example 16-3

...

write(fixNames("the", "DALLAS", "CowBoys"))
function fixNames()
{
var s = ""
for (j = 0 ; j < fixNames

...

length ; ++j)
s += fixNames

...

charAt(0)

...

arguments[j]

...

toLowerCase() + " "
return s

...

length-1)
}


When called with the parameters “the”, “DALLAS”, and “CowBoys”, for example, the
function returns the string “The Dallas Cowboys”

...


The function first initializes the temporary (and local) variable s to the empty string

...

it-ebooks

...

Com

toUpperCase method

...


Then the substr method is used to fetch the rest of each string, which is converted to
lowercase using the toLowerCase method

...

length) - 1 )

In other words, this substr method says, “Start with the character at position 1 (the
second character) and return the rest of the string (the length minus one)

...


After the whole argument is converted to our desired case, a space character is added
to the end and the result is appended to the temporary variable s

...

 This is removed by using substr to return the
string up to, but not including, the final character

...

 For example:
fixNames

...

substr(1)

...


JavaScript evaluates these elements of the statement from left to right as follows:
1

...


2

...


3

...

 This passes all but
the first character to the next section of the expression

...

 Apply the method toLowerCase to the string that has been passed this far

...

 So, for example, if the string
“mixedCASE” is passed to the example expression, it will go through the following
transformations:
mixedCase
ixedCase
ixedcase

One final reminder: the s variable created inside the function is local, and therefore
cannot be accessed outside the function

...


But s itself disappears at the end of the function

...



340 | Chapter 16: JavaScript Functions, Objects, and Arrays

www

...

info
Download at Boykma

...


Example 16-4

...

length ; ++j)
document

...

arguments

...

arguments[j]

...

toUpperCase() +
fixNames

...

substr(1)

...

 Then a for loop iterates through the
array and displays each member

...


This function enables the extraction of individual parameters from its returned values,
like the following (the output from which is simply “The Cowboys”):
words = fixNames("the", "DALLAS", "CowBoys")
document

...

 An object groups
data together with the functions needed to manipulate it

...

 Each new object based on this class is called an instance (or occurrence)

JavaScript Objects | 341

www

...

info
Download at Boykma

...

 As you’ve already seen, the data associated with an object are called its
properties, while the functions it uses are called methods

...

 To create the class, just write a function named after the class

...

 The function is called a constructor

...

 The class also defines the method showUser

...

 Declaring the User class and its method


The function is different from other functions we’ve seen so far in two ways:
• It refers to an object named this

...

 The same function can be called over and over with different arguments, and will create a new
User each time with different values for the properties forename, and so on

...

 The syntax shown
here is new and rather complicated, but its purpose is to tie showUser to the User
class

...


The naming convention I have used is to keep all properties in lowercase and to use at
least one uppercase character in method names, following the bumpyCaps convention
mentioned earlier in the chapter

...

 However, you can also refer to functions
defined outside the constructor, as in Example 16-6

...

 Separately defining a class and method


This works because all functions have a prototype property, designed to hold properties
and methods that are not replicated in any objects created from a class

...


This means that you can add a prototype property or method at any time and all objects
(even those already created) will inherit it, as the following statements illustrate:
User

...

greeting = "Hello"
document

...

greeting)

The first statement adds the prototype property of greeting with a value of “Hello” to
the class User

...


344 | Chapter 16: JavaScript Functions, Objects, and Arrays

www

...

info
Download at Boykma

...

prototype

...

write("Name " +
this

...

username + " Pass " + this

...

showUser()

You might add these lines to your script in a conditional statement (such as if), so they
run if user activities cause you to decide you need a different showUser method

...

showUser will run the new function

...



Static methods and properties
When reading about PHP objects, you learned that classes can have static properties
and methods as well as properties and methods associated with a particular instance
of a class

...

 Thus, the following statements
set and read a static string from User:
User

...

greeting = "Hello"
document

...

prototype

...

 For example,
suppose that you would like to add the ability to replace all spaces in a string with
nonbreaking spaces in order to prevent it from wrapping around

...

prototype

...

replace(/ /g, ' ') }

Here the replace method is used with a regular expression (see Chapter 17) to find and
replace all single spaces with the string “ ”

...

write("The quick brown fox"

...

 Or here’s a
method you can add that will trim leading and trailing spaces from a string (once again
using a regular expression):
String

...

trim =
function() { return this

...


document

...

trim())

JavaScript Objects | 345

www

...

info
Download at Boykma

...

 Nevertheless, given all you have already learned about arrays, this section should
be relatively straightforward for you

...

push("Element 1")
arrayname

...

 When you need to know how many elements are in an array, you can
use the length property, like this:
document

...

length)

Alternatively, if you wish to keep track of the element locations yourself and place them
in specific locations, you can use syntax such as this:
arrayname[0] = "Element 1"
arrayname[1] = "Element 2"

Example 16-8 shows a simple script that creates an array, loads it with some values,
and then displays them

...

 Creating, building, and printing an array


In this example, the lowercase letters represent black pieces and the uppercase white

...


The outer loop contains two statements, so curly braces enclose them

...

 This loop contains a single statement, so
curly braces are not required to enclose it

...

it-ebooks

...

Com

You can also directly access any element within this array using square brackets, as
follows:
document

...



Using Array Methods
Due to the power of arrays, JavaScript comes ready-made with a number of methods
for manipulating them and their data

...



concat
The concat method concatenates two arrays, or a series of values with an array

...

write(fruit

...


Here’s another way to use concat, This time plain values are concatenated with the
array pets, which outputs “Cat,Dog,Fish,Rabbit,Hamster”:
pets
= ["Cat", "Dog", "Fish"]
more_pets = pets

...

write(more_pets)

forEach: For non-IE browsers
The forEach method in JavaScript is another way of achieving functionality similar to
the PHP foreach keyword, but only for browsers other than Internet Explorer

...

 Example 16-11 shows how

...

 Using the forEach method


JavaScript Arrays | 349

www

...

info
Download at Boykma

...

 It takes three parameters:
the element, its index, and the array

...


In this example, just the element and index values are displayed using the function
document

...


Once an array has been populated, the method is called up like this:
pets

...

 Therefore until
IE does support it, and to ensure cross-browser compatibility, you should use a statement such as the following instead of pets

...

length ; ++j) output(pets[j], j)

join
With the join method, you can convert all the values in an array to strings and then
join them together into one large string, placing an optional separator between them

...


Example 16-12

...

write(pets

...

write(pets

...

write(pets

...

 The output of Example 16-12 looks
like this:
Cat,Dog,Rabbit,Hamster
Cat Dog Rabbit Hamster
Cat : Dog : Rabbit : Hamster

push and pop
You already saw how the push method can be used to insert a value into an array

...

 It deletes the most recently inserted element from an array and
returns it

...


350 | Chapter 16: JavaScript Functions, Objects, and Arrays

www

...

info
Download at Boykma

...

 Using the push and pop methods


/>")
/>")
/>")
/>")

The three main statements of this script are shown in bold type

...

 After that it pops that element back off

...

write

...


Example 16-14

...

push(j);
document

...

write("
")
document

...

pop() + "
")
document

...

pop() + "
")
document

...

pop() + "
")


The output from this example is:
Pushed 0
Pushed 1
Pushed 2
Popped 2
Popped 1
Popped 0

JavaScript Arrays | 351

www

...

info
Download at Boykma

...

 Example 16-15 shows this in action

...

 Using the reverse method


The original array is modified and the output from this script is:
Hockey,Baseball,Tennis,Football

sort
With the sort method, you can place all the elements of an array in alphabetical or
other order, depending upon the parameters used

...


Example 16-16

...

sort()
document

...

sort()

...

write(sports + "
")
// Ascending numerical sort
numbers = [7, 23, 6, 74]
numbers

...

write(numbers + "
")
// Descending numerical sort
numbers = [7, 23, 6, 74]
numbers

...

write(numbers + "
")


The first of the four example sections is the default sort method, alphabetical sort, while
the second uses the default sort and then applies the reverse method to get a reverse
alphabetical sort

...

it-ebooks

...

Com

The third and fourth sections are a little more complicated by using a function to compare the relationships between a and b

...

 You have already seen the function named function to create an
anonymous function; we used it to define a method in a class (the showUser method)

...


If the function returns a value greater than zero, the sort assumes that a comes before
b

...

 The sort runs this function across all the values in the array to determine their order

...


And, believe it or not, this represents the end of your introduction to JavaScript

...

 The next chapter will look at some advanced techniques used across these
technologies, such as pattern matching and input validation

...


Question 16-4
When defining a class, what keyword is used to refer to the current object?
Question 16-5
Do all the methods of a class have to be defined within the class definition?
Question 16-6
What keyword is used to create an object?
Question 16-7
How can a property or method be made available to all objects in a class without
replicating the property or method within the object?
Question 16-8
How can you create a multidimensional array?

Test Your Knowledge: Questions | 353

www

...

info
Download at Boykma

...


See the section “Chapter 16 Answers” on page 448 in Appendix A for the answers to
these questions

...

it-ebooks

...

Com

CHAPTER 17

JavaScript and PHP Validation and
Error Handling

With your solid foundation in both PHP and JavaScript, it’s time to bring these technologies together to create web forms that are as user-friendly as possible

...


Final validation of the input will then be made by the PHP, which will, if necessary,
present the form again to the user for further modification

...



Validating User Input with JavaScript
JavaScript validation should be considered an assistance more to your users than to
your websites because, as I have already stressed many times, you cannot trust any data
submitted to your server, even if it has supposedly been validated with JavaScript

...


Another reason you cannot rely on JavaScript to perform all your input validation is
that some users disable JavaScript, or use browsers that don’t support it

...



355

www

...

info
Download at Boykma

...

html Document (Part One)
So let’s take a general sign-up form, common on most sites that offer memberships or
registered users

...

 Example 17-1 provides a good template for such a
form

...

 A form with JavaScript validation (part one)



_END;
// Finally, here are the PHP functions
function validate_forename($field) {
if ($field == "") return "No Forename was entered
";
return "";
}
function validate_surname($field) {
if ($field == "") return "No Surname was entered
";
return "";
}
function validate_username($field) {
if ($field == "") return "No Username was entered
";
else if (strlen($field) < 5)
return "Usernames must be at least 5 characters
";
else if (preg_match("/[^a-zA-Z0-9_-]/", $field))
return "Only letters, numbers, - and _ in usernames
";
return "";
}
function validate_password($field) {
if ($field == "") return "No Password was entered
";
else if (strlen($field) < 6)
return "Passwords must be at least 6 characters
";
else if ( !preg_match("/[a-z]/", $field) ||
!preg_match("/[A-Z]/", $field) ||
!preg_match("/[0-9]/", $field))
return "Passwords require 1 each of a-z, A-Z and 0-9
";
return "";

Redisplaying a Form After PHP Validation | 373

www

...

info
Download at Boykma

...

") > 0) &&
(strpos($field, "@") > 0)) ||
preg_match("/[^a-zA-Z0-9

...



Figure 17-5

...

it-ebooks

...

Com

I have put the PHP section of this code (and changes to the HTML section) in a bold
typeface so that you can more clearly see the difference between this and Examples
17-1 and 17-2

...

net website), you’ll have seen that the PHP code is almost a clone of the
JavaScript code; the same regular expressions are used to validate each field in very
similar functions

...

 First, the fix_string function (right at the end)
is used to sanitize each field and prevent any attempts at code injection from succeeding

...

 _END; structure, displaying the form with the values that the
visitor entered the previous time

...

> tag (such as value="$forename")

...


In the real world, you probably wouldn’t start with an HTML form such
as the one in Example 17-1

...

 And, of course, you’d also need to make a minor tweak
for the case when it’s the first time the program is called up, to prevent
it displaying errors when all the fields are empty

...

js file for separate inclusion

...



Test Your Knowledge: Questions
Question 17-1
What JavaScript method can you use to send a form for validation prior to submitting it?
Question 17-2
What JavaScript method is used to match a string against a regular expression?
Question 17-3
Write a regular expression to match any characters that are not in a word, as defined
by regular expression syntax

...

it-ebooks

...

Com

Question 17-4
Write a regular expression to match either of the words fox or fix

...


Question 17-6
Using regular expressions, write a JavaScript function to test whether the word
fox exists in the string “The quick brown fox”

...


Question 17-8
What HTML keyword is used to precomplete form fields with a value?
See the section “Chapter 17 Answers” on page 449 in Appendix A for the answers to
these questions

...

it-ebooks

...

Com

CHAPTER 18

Using Ajax

The term “Ajax” was first coined in 2005

...

 An excellent example
of this technology is Google Maps (see Figure 18-1), in which new sections of a map
are downloaded from the server when needed, without requiring a page refresh

...

 Google Maps is an excellent example of Ajax in action

377

www

...

info
Download at Boykma

...

 The results are a much improved user interface and
better responsiveness

...

 ActiveX is Microsoft’s
technology for signing plug-ins that add additional software to your computer

...


However, even before then, an early form of Ajax had already surfaced that used hidden
frames on a page that interacted with the server in the background

...


Nowadays, though, XMLHttpRequest is the way to go, and there have been numerous
frameworks written to simplify its use

...

 But first, let’s see how to implement
Ajax with raw JavaScript

...



Using XMLHttpRequest
Due to the differences between browser implementations of XMLHttpRequest, it’s necessary to create a special function in order to ensure that your code will work on all
major browsers

...

XMLHTTP")
• IE 6+: request = new ActiveXObject("Msxml2

...

 Therefore, the code in Example 18-1 will work for all the following
browsers and newer versions:
•
•
•
•

Windows Internet Explorer 5

...

0
Netscape 7

...

2

378 | Chapter 18: Using Ajax

www

...

info
Download at Boykma

...

0
Nokia S60
Google Chrome 1

...

0

Example 18-1

...

XMLHTTP")
}
catch(e2)
{
try // IE 5?
{
request = new ActiveXObject("Microsoft

...

catch construct

...


Otherwise, a catch traps the error and the subsequent command is executed

...

 If that attempt fails, then the browser doesn’t support Ajax and the request
object is set to false; otherwise, the object is returned

...


OK, so now you have a means of creating an XMLHttpRequest object, but what can you
do with these objects? Well, each one comes with a set of properties (variables) and
methods (functions), which are detailed in Tables 18-1 and 18-2

...

it-ebooks

...

Com

Table 18-1

...



readyState

An integer property that reports on the status of a request

...



responseText

The data returned by the server in text format

...



status

The HTTP status code returned by the server

...



Table 18-2

...



getAllResponseHeaders()

Returns all headers as a string

...



open('method', 'url', 'asynch')

Specifies the HTTP method to use (GET or POST), the target URL, and
whether the request should be handled asynchronously (true or
false)

...



setRequestHeader('param', 'value')

Sets a header with a parameter/value pair

...

 For example, you
can choose whether to request plain text (which could include HTML and other tags)
or data in XML format

...


Let’s look at the POST method first by creating a very simple pair of documents: a combination of HTML and JavaScript, and a PHP program to interact via Ajax with the
first

...

0
and Ajax are all about

...



Your First Ajax Program
Type in and save the code in Example 18-2 as urlpost

...



380 | Chapter 18: Using Ajax

www

...

info
Download at Boykma

...

 urlpost

...

com"
request = new ajaxRequest()
request

...

php", true)
request

...

setRequestHeader("Content-length", params

...

setRequestHeader("Connection", "close")
request

...

readyState == 4)
{
if (this

...

responseText != null)
{
document

...

innerHTML =
this

...

statusText)
}
}
request

...

XMLHTTP")

new ActiveXObject("Microsoft

...

it-ebooks

...

Com

}

}

}
return request

}


Let’s go through this document and look at what it does, starting with the first three
lines, which simply set up an HTML document and display a heading

...

 Later on, the text returned from the Ajax call will be inserted
here

...

 The first sets
the variable params to a parameter=value pair, which is what we’ll send to the server

...

 After this, the open method is called to set the
object to make a POST request to geturl

...

 The last three lines
in this group set up headers that are required for the receiving server to know that a
POST request is coming

...

 The “asynchronous” aspect of Ajax allows the browser to keep accepting user
input and changing the screen, while our program sets the onreadystatechange property
to call a function of our choice each time readyState changes

...

 This type of function is known as a callback function, as it is called back each time
readyState changes

...

onreadystatechange = function()
{
if (this

...

onreadystatechange = ajaxCallback
function ajaxCallback()
{
if (this

...

it-ebooks

...

Com

Looking at Table 18-1, you’ll see that readyState can have five different values

...

 Therefore, each time the new function gets called, it returns without doing anything until
readyState has a value of 4

...


If it’s not 200, an alert pop up is displayed containing the error message contained in
statusText

...

readyState, this

...

readyState or request

...

 This is
so that you can easily copy and paste the code and it will work with any
object name, because the this keyword always refers to the current
object

...

 If not, an error message is displayed
in an alert box

...

getElementById('info')

...

responseText

What happens in this line is that the element “info” is referenced using the
getElementByID method and then its innerHTML property is assigned the value that was
returned by the Ajax call

...

send(params)

After that, all the preceding code is activated each time readyState changes

...



The server half of the Ajax process
Now we get to the PHP half of the equation, which you can see in Example 18-3

...

php

...

 urlpost

...

php
if (isset($_POST['url'])) {
echo file_get_contents("http://"

...

it-ebooks

...

Com

}
?>

$var = strip_tags($var);
$var = htmlentities($var);
return stripslashes($var);

As you can see, this is short and sweet, and also makes use of the ever-important
SanitizeString function, as should always be done with all posted data

...

 The
file_get_contents function is versatile, in that it loads in the entire contents of a file
or web page from either a local or a remote server—it even takes into account moved
pages and other redirects

...

html into your
web browser and, after a few seconds, you should see the contents of the oreilly

...

 It won’t be as fast as
directly loading the web page, because it is transferred twice: once to the server and
again from the server to your browser

...



Figure 18-2

...

com front page has been loaded into a DIV

384 | Chapter 18: Using Ajax

www

...

info
Download at Boykma

...

 Incidentally, if we had tried to find a way to fetch the
oreilly

...

 So this little example also illustrates a handy solution to a very practical
problem

...

 However, there
is a downside: some browsers may cache GET requests, whereas POST requests will never
be cached

...

 The solution to this
is to use a workaround that adds a random parameter to each request, ensuring that
each URL requested is unique

...


Example 18-4

...

html


Loading a web page into a DIV

This sentence will be replaced



XMLHttpRequest()

ActiveXObject("Msxml2

...

XMLHTTP")

false

The differences to note between the two documents are highlighted in bold, and are as
follows:
• It is not necessary to send headers for a GET request

...

com

...

 This is
used to ensure that each URL requested is different, and therefore that no requests
will be cached

...

 Note that leaving the parameter out is not an option, as
it would result in an error

...

php

...

 urlget

...

sanitizeString($_GET['url']));
}

386 | Chapter 18: Using Ajax

www

...

info
Download at Boykma

...

 The end result of calling up urlget

...

html

...

 This is where the Ajax term is a bit of a misnomer,
because the technology actually allows you to request any type of textual data, only
one of which is XML

...


So let’s modify the previous example document and PHP program to fetch some XML
data

...

php, shown in Example 18-6

...

 xmlget

...

sanitizeString($_GET['url']));
}
function sanitizeString($var) {
$var = strip_tags($var);
$var = htmlentities($var);
return stripslashes($var);
}
?>

This program has been very slightly modified (shown in bold highlighting) to first output the correct XML header before returning a fetched document

...


Now on to the HTML document, xmlget

...


Example 18-7

...

html


Loading XML content into a DIV

This sentence will be replaced


Using XMLHttpRequest | 387

www

...

info
Download at Boykma

...

random() * 1000000
url = "rss

...

yahoo

...

open("GET", "xmlget

...

onreadystatechange = function()
{
if (this

...

status == 200)
{
if (this

...

responseXML

...

length ; ++j)
{
out += titles[j]

...

nodeValue + '
'
}
document

...

innerHTML = out

}
else alert("Ajax error: No data received")

}

}

}
else alert( "Ajax error: " + this

...

send(null)
function ajaxRequest() {
try
{
var request = new
}
catch(e1)
{
try
{
request = new
}
catch(e2)
{
try
{
request =
}
catch(e3)
{
request =
}
}
}

388 | Chapter 18: Using Ajax

XMLHttpRequest()

ActiveXObject("Msxml2

...

XMLHTTP")

false

www

...

info
Download at Boykma

...

news

...

com/rss/topstories, contains an XML document, the Yahoo! News Top
Stories feed

...

 Whenever a server returns XML data, responseText will return
a null value, and responseXML will contain the XML returned instead

...

 This means it is accessible, for example, by the JavaScript
getElementsByTagName method

...

 However, the beauty of XML is that this type of structure can be stored internally in a DOM tree (see Figure 18-3) to make it quickly searchable

...

 The DOM tree of Example 18-8
Example 18-8

...

0" encoding="UTF-8"?>


Or you could link directly to Yahoo’s copy, like this:



396 | Chapter 19: Using YUI for Ajax and More

www

...

info
Download at Boykma

...

yahooapis

...

7

...

js">


The YUI asyncRequest method
Now all you need to do is call asyncRequest, Yahoo’s version of the ajaxRequest function
we created in the last chapter

...

util

...

asyncRequest('method', 'url', callback [, 'parameters

...


If you’re using the GET method, the name of the script should be followed by a ? and
the parameter/value pairs you wish to send

...


So an Ajax GET request might look like this:
YAHOO

...

Connect

...

php', callback)

The callback object should be created like this:
callback = { success:successHandler, failure:failureHandler }

The successHandler and failureHandler functions should contain instructions for your
program to execute according to the intention of your project

...

html)

...

 yuiurlget

...

js">





This document is fairly similar to the previous one, in that the same YUI framework
scripts are included, but right away, you’ll notice that the url is different

...

weather

...

com/forecastrss?p=20500")

This encoding has the effect of turning any special characters into a form that will not
confuse the PHP program into thinking that additional parameters have been passed

...

php to
xmlget

...

 The xmlget

...


Using YUI | 399

www

...

info
Download at Boykma

...

 The result of calling up yuixmlget

...

 So the main remaining
change is in successHandler

...

 The result of calling up yuixmlget

...



Other Uses for YUI
The YUI framework offers support in a wide range of areas, including animation, buttons, calendars, charts, colors, cookies, drag and drop, fonts, imaging, menus, styles,
uploading, and a great deal more as well

...

html page in the downloaded distribution or at http://developer

...

com/yui
and click on ones that interest you

...

it-ebooks

...

Com

A Simple YUI Calendar
For example, clicking through to the calendar link reveals how you can make your own
calendars, a common feature needed by many websites

...

yahoo

...

html
To do so, locate all the following files in the build folder of the downloaded YUI distribution on your hard disk, and copy them to your web folder (bearing in mind that
assets is a folder, not a file):
•
•
•
•

fonts/fonts-min

...

js
calendar/calendar-min

...

html, which, when you
call it up in your browser, will look like Figure 19-5

...

 A YUI calendar

Other Uses for YUI | 401

www

...

info
Download at Boykma

...

 calendar

...

css" />
type="text/css" href="assets/skins/sam/calendar

...

js">


All the other YUI features are just as easy to use, and require you only
to carefully read the accompanying documentation before copying and
pasting the supplied code

...



I hope you have a lot of fun using the resources supplied by YUI (and any of the other
frameworks you try)

...



402 | Chapter 19: Using YUI for Ajax and More

www

...

info
Download at Boykma

...



Test Your Knowledge: Questions
Question 19-1
What is YUI’s method of implementing an Ajax connection?
Question 19-2
Write a callback object for YUI called callback to reference a success handler called
succeeded and a failure handler called failed

...

php and a
callback object

...

com/message?m=123, which contains the ?
symbol, so that if sent as a GET request it will be treated just as a string and not
interpreted?
See the section “Chapter 19 Answers” on page 451 in Appendix A for the answers to
these questions

...

it-ebooks

...

Com

www

...

info
Download at Boykma

...

 In fact, it’s 10 examples, because I’ve put together a simple
social networking project comprising all the main features you’d expect from such a
site

...


Each example file is complete and self-contained, yet works with all the others to build
a fully working social networking site

...

 This means that, as
it stands, the end product is particularly usable on mobile platforms such as the iPhone,
where reducing the file size and dimensions of web documents is important

...

 Perhaps you may even wish to build on these files to create
a social networking site of your own

...

 These included:
•
•
•
•
•
•

A sign-up process
A login form
A logout facility
Session control
User profiles with uploaded thumbnails
A member directory
405

www

...

info
Download at Boykma

...

html page and a separate include file for the main functions, 10
PHP program files were required

...

 Also, all the filenames (except
index

...

 If you change these names, make sure you also change all references
across all the files

...

 But if you plan on extending the code,
I strongly recommend you consider them, as PEAR can make the programming process
simpler

...


However, where I have implemented an Ajax call, I have also included an alternative
YUI version, as you can use it without installing any software on your server

...

net, where the code syntax is color-highlighted, making it easier to follow

...

 This will download an archive file called examples

...


Of particular interest to this chapter, within the ZIP file there’s a folder called
robinsnest, in which all the following examples have been saved using the correct filenames required by this sample application

...



rnfunctions

...

php, the
include file of main functions

...

it-ebooks

...

Com

separate file

...


It doesn’t matter what you call the database, as long as it already exists (see Chapter 8 for how to create a new database)

...

 With correct values, the subsequent
two lines will open a connection to MySQL and select the database

...

 If you want to change the name, here’s the
place to do so

...


tableExists

Returns a value of 1 if a table already exists, otherwise 0

...


destroySession

Destroys a PHP session and clears its data to log users out

...


showProfile

Displays a user’s image and “about me” if they have one

...

jpg (where user is the username of the current user), and if found, displays it

...


I have ensured that error handling is in place for all the functions that need it, so that
they can catch any typographical or other errors you may introduce

...


So type this file in and save it as rnfunctions

...


Example 20-1

...

php
$dbhost
$dbname
$dbuser

rnfunctions

...


= 'username';
// 

...

php | 407

www

...

info
Download at Boykma

...

to your installation
$appname = "Robin's Nest"; // 

...

jpg"))
echo "session_start();
if (isset($_SESSION['user']))
{
$user = $_SESSION['user'];
$loggedin = TRUE;
}
else $loggedin = FALSE;
echo "";
echo "
$appname
";
if ($loggedin)
{
echo "$user:
Members |
Messages |
Log out";
}
else
{
echo "Sign up |

...

php
This file is a trivial file but necessary nonetheless to give the project a home page

...

 In a finished application, this would be
where you sell the virtues of your site to encourage signups

...

php, into your browser to get your first
peek at the new application

...



Figure 20-1

...

php | 411

www

...

info
Download at Boykma

...

 index

...

php
include_once 'rnheader

...

";
?>

rnsignup

...

php

...


Let’s start by looking at the end block of HTML

...

 But note the use of the empty span given the
id of 'info'

...

 See Chapter 18 for a complete description of
how this works

...

 This is called by the JavaScript onBlur event when focus is
removed from the username field of the form

...


Next a request is made to the program rnchecker

...

 The returned result of the Ajax call, a friendly message, is then
placed in the 'info' span

...

 This section also uses the sanitizeString function to remove potentially malicious characters before looking up the username in the
database and, if it’s not already taken, inserting the new username $user and password
$pass

...

 A more fluid response
at this point might be to automatically log in a newly created user but, as I don’t want
to overly complicate the code, I have kept the sign-up and login modules separate from
each other

...

php, shown later)
this program will look like Figure 20-2, where you can see that the Ajax call has identified that the username Robin is available

...

it-ebooks

...

Com

Figure 20-2

...

 rnsignup

...

php
include_once 'rnheader

...

value == '')
{
document

...

innerHTML = ''
return
}
params = "user=" + user

...

open("POST", "rncheckuser

...

setRequestHeader("Content-type",
"application/x-www-form-urlencoded")
request

...

length)
request

...

onreadystatechange = function()
{
if (this

...

status == 200)
{
if (this

...

php | 413

www

...

info
Download at Boykma

...

getElementById('info')

...

responseText

}
else alert("Ajax error: No data received")

}
else alert( "Ajax error: " + this

...

send(params)

function ajaxRequest()
{
try
{
var request = new
}
catch(e1)
{
try
{
request = new
}
catch(e2)
{
try
{
request =
}
catch(e3)
{
request =
}
}
}
return request
}

Sign up Form

_END;

XMLHttpRequest()

ActiveXObject("Msxml2

...

XMLHTTP")

false

$error = $user = $pass = "";
if (isset($_SESSION['user'])) destroySession();
if (isset($_POST['user']))
{
$user = sanitizeString($_POST['user']);
$pass = sanitizeString($_POST['pass']);
if ($user == "" || $pass == "")
{
$error = "Not all fields were entered

";
}
else

414 | Chapter 20: Bringing It All Together

www

...

info
Download at Boykma

...

");

echo <<<_END

...

js">

 tags

...

 If the head it will output to the head; if the body then the body

...

js'> tag

...

write function (or method)

...


Question 14-6
The JavaScript string concatenation operator is the + symbol

...

it-ebooks

...

Com

Question 14-7
Within a JavaScript function, you can define a variable that has local scope by
preceding it with the var keyword upon first assignment

...

write(document

...

href)
document

...

href)

Question 14-9
The commands to change to the previous page in the browser’s history array are:
history

...

go(-1)

Question 14-10
To replace the current document with the main page at the oreilly

...

location

...

com'

Chapter 15 Answers
Question 15-1
The most noticeable difference between Boolean values in PHP and JavaScript is
that PHP recognizes the keywords TRUE, true, FALSE, and false, whereas only
true and false are supported in JavaScript

...


Question 15-2
Unlike PHP, no character is used (such as $) to define a JavaScript variable name

...


Question 15-3
The difference between unary, binary, and ternary operators is the number of operands each requires (one, two, and three, respectively)

...


Question 15-5
You use the identity operator when you wish to bypass JavaScript’s automatic
operand type changing

...

it-ebooks

...

Com

Question 15-6
The simplest forms of expressions are literals (such as numbers and strings) and
variables, which simply evaluate to themselves

...


Question 15-8
Most conditional expressions in if and while statements are literal or Boolean and
therefore trigger execution when they evaluate to TRUE

...

 String expressions trigger execution when they evaluate to a nonempty string

...


Question 15-9
Loops using for statements are more powerful than while loops, because they
support two additional parameters to control loop handling

...

 Using it, you specify an object
once, then for each statement within the with block, that object is assumed

...

 The variables Count,
count, and COUNT are all different

...


Question 16-3
One way to return multiple values from a function is to place them all inside an
array and return the array

...


Question 16-5
The methods of a class do not have to be defined within a class definition

...


Question 16-6
New objects are created using the new keyword

...

it-ebooks

...

Com

to create a single instance, which is then passed by reference to all the objects in a
class

...


Question 16-9
The syntax you would use to create an associative array is key : value, within curly
braces, as in the following:
assocarray = {"forename" : "Paul", "surname" : "McCartney",
"group" : "Beatles"}

Question 16-10
A statement to sort an array of numbers into descending numerical order would
look like this:
numbers

...

> tag

...


Question 17-2
To match a string against a regular expression in JavaScript, use the test method

...


Question 17-4
A regular expression to match either of the words fox or fix could be /f[oi]x/

...


Question 17-6
A JavaScript function using regular expressions to test whether the word fox exists
in the string “The quick brown fox” could be:
document

...

test("The quick brown fox"))

Question 17-7
A PHP function using a regular expression to replace all occurrences of the word
the in “The cow jumps over the moon” with the word my could be:
$s=preg_replace("/the/i", "my", "The cow jumps over the moon");

Chapter 17 Answers | 449

www

...

info
Download at Boykma

...

> tag and takes the form
value="value"

...

 By writing a function to test the browser in use, you
can ensure that code will work on all major browsers

...

catch construct is to set an error trap for the code inside
the try statement

...


Question 18-3
An XMLHTTPRequest object has six properties and six methods (see Tables 18-1 and
18-2)

...


Question 18-5
When an Ajax call successfully completes, the object’s status will have a value of
200

...


Question 18-7
The responseXML property of an XMLHTTPRequest object contains a DOM tree created
from the XML returned by a successful Ajax call

...

 You can also use an
unnamed, inline function

...



450 | Appendix A: Solutions to the Chapter Questions

www

...

info
Download at Boykma

...



Chapter 19 Answers
Question 19-1
To implement an Ajax connection, YUI uses a method called asyncRequest, which
is referenced as YAHOO

...

Connect

...


Question 19-2
A callback object called callback for YUI, referring to a success handler called
succeeded and a failure handler called failed, would be written like this:
callback = { success:succeeded, failure:failed }

Question 19-3
A GET call to asyncRequest that refers to the program getdata

...

util

...

asyncRequest('GET', 'getdata

...

com/message?m=123, which contains the ? symbol, so
that if sent as a GET request, it will be treated just as a string and not interpreted,
use the encodeURI method, like this:
url = encodeURI("mysite

...

it-ebooks

...

Com

www

...

info
Download at Boykma

...



PHP Resource Sites
•
•
•
•
•
•
•
•
•
•
•
•
•
•

http://codewalkers

...

yahoo

...

devshed

...

net
http://hotscripts

...

com/beyond/php/
http://php

...

resourceindex

...

com
http://phpbuilder

...

com
http://phpunit

...

com/php/
http://zend

...

it-ebooks

...

Com

MySQL Resource Sites
•
•
•
•
•
•
•
•

http://code

...

com/edu/tools101/mysql

...

net/mysql/
http://mysql

...

net/mysql
http://planetmysql

...

com/software/products/mysql/
http://sun

...

jsp
http://w3schools

...

asp

JavaScript Resource Sites
•
•
•
•
•
•
•
•

http://developer

...

org/en/JavaScript
http://dynamicdrive

...

about

...

internet

...

com
http://javascriptkit

...

com/JS/
http://www

...

com/js/

Ajax Resource Sites
•
•
•
•
•
•
•
•
•
•

http://ajax

...

net
http://ajaxian

...

com
http://developer

...

org/en/AJAX
http://dojotoolkit

...

com
http://mochikit

...

net
http://openjs

...

org

454 | Appendix B: Online Resources

www

...

info
Download at Boykma

...

net/projects/clean-ajax
• http://w3schools

...

org
http://easyphp

...

org
http://editra

...

mozdev

...

net/projects/glossword/
http://mamp

...

php

...

org
http://putty

...

net
http://wampserver

...

com
http://onlamp

...

com/onlamp/general/mysql

...

com/ajax/
http://oreilly

...

com/mysql/
http://oreilly

...

com/javascript/

O’Reilly Resource Sites | 455

www

...

info
Download at Boykma

...

it-ebooks

...

Com

APPENDIX C

MySQL’s FULLTEXT Stopwords

This appendix contains the more than 500 stopwords referred to in the section “Using
a FULLTEXT Index” in Chapter 7

...

 Theoretically,
ignoring these words makes little difference to the results of most FULLTEXT searches,
but makes MySQL databases considerably smaller and more efficient

...

it-ebooks

...

Com

F
far, few, fifth, first, five, followed, following, follows, for, former, formerly, forth,
four, from, further, furthermore
G
get, gets, getting, given, gives, go, goes, going, gone, got, gotten, greetings
H
had, hadn’t, happens, hardly, has, hasn’t, have, haven’t, having, he, he’s, hello, help,
hence, her, here, here’s, hereafter, hereby, herein, hereupon, hers, herself, hi, him,
himself, his, hither, hopefully, how, howbeit, however
I
i’d, i’ll, i’m, i’ve, ie, if, ignored, immediate, in, inasmuch, inc, indeed, indicate, indicated, indicates, inner, insofar, instead, into, inward, is, isn’t, it, it’d, it’ll, it’s, its, itself
J
just
K
keep, keeps, kept, know, knows, known
L
last, lately, later, latter, latterly, least, less, lest, let, let’s, like, liked, likely, little, look,
looking, looks, ltd
M
mainly, many, may, maybe, me, mean, meanwhile, merely, might, more, moreover, most, mostly, much, must, my, myself
N
name, namely, nd, near, nearly, necessary, need, needs, neither, never, nevertheless, new, next, nine, no, nobody, non, none, noone, nor, normally, not, nothing,
novel, now, nowhere
O
obviously, of, off, often, oh, ok, okay, old, on, once, one, ones, only, onto, or, other,
others, otherwise, ought, our, ours, ourselves, out, outside, over, overall, own
P
particular, particularly, per, perhaps, placed, please, plus, possible, presumably,
probably, provides
Q
que, quite, qv
R
rather, rd, re, really, reasonably, regarding, regardless, regards, relatively, respectively, right

458 | Appendix C: MySQL’s FULLTEXT Stopwords

www

...

info
Download at Boykma

...

it-ebooks

...

Com

www

...

info
Download at Boykma

...

 If you wish to learn more about the available functions you can visit the following URLs:
• String functions: http://dev

...

com/doc/refman/5

...

html
• Date and time: http://dev

...

com/doc/refman/5

...

html
But, for easy reference, here are some of the most commonly used MySQL functions

...

)

Returns the result of concatenating str1, str2, and any other parameters (or NULL if any argument is NULL)

...

 The code returns the string “MySQL”:
SELECT CONCAT('My', 'S', 'QL');

CONCAT_WS()
CONCAT_WS(separator, str1, str2, 

...

 If the separator is NULL the result will be NULL, but NULL values can be used as
other arguments, which will then be skipped

...

it-ebooks

...

Com

LEFT()
LEFT(str, len)

Returns the leftmost len characters from the string str (or NULL if any argument is NULL)

...

 This
code returns the string “Columbus”:
SELECT RIGHT('Christopher Columbus', '8');

MID()
MID(str, pos, len)

Returns up to len characters from the string str starting at position pos

...

 You may use a negative value for pos,
in which case it represents the character pos places from the end of the string

...

 This code returns the string “stop”:
SELECT MID('Christopher Columbus', '6', '4');

LENGTH()
LENGTH(str)

Returns the length in bytes of the string str

...

 If you need to know the actual number of characters in a string use the CHAR_LENGTH
function

...

 If str is longer than len then the string returned will be truncated to len
characters

...

it-ebooks

...

Com

March
April
May

Notice how all the strings have been padded to be eight characters long

...

 This code returns the string “Hi!!!”:
SELECT RPAD('Hi', '5', '!');

LOCATE()
LOCATE(substr, str, pos)

Returns the position of the first occurrence of substr in the string str

...

 If substr is not found in str, a value of zero is returned

...

 Returns the string str with all the characters changed to lowercase

...

 It returns the string str with all the characters changed to uppercase

...

it-ebooks

...

Com

SELECT UPPER('I can't help shouting');

QUOTE()
QUOTE(str)

Returns a quoted string that can be used as a properly escaped value in a SQL statement

...

 If the argument str is NULL,
the return value is the word NULL without enclosing quotes

...


SELECT QUOTE("I'm hungry");

REPEAT()
REPEAT(str, count)

Returns a string comprising count copies of the string str

...

 If either parameter is NULL then NULL is returned

...

 The
search and replace is case-sensitive when searching for from

...

 The specifier can be one
of BOTH, LEADING, or TRAILING

...

 The remove
string is optional and, if omitted, spaces are removed

...

it-ebooks

...

Com

LTRIM() and RTRIM()
LTRIM(str)
RTRIM(str)

The function RTRIM returns the string str with any leading spaces removed, while the function
RTRIM performs the same action on the string’s tail

...

 Whenever financial transactions take
place, the date has to be recorded, expiry dates of credit cards need to be noted for
repeat billing purposes, and so on

...



CURDATE()
CURDATE()

Returns the current date in YYYY-MM-DD or YYYMMDD format, depending on whether
the function is used in a numeric or string context

...

 This code returns the value
“1961-05-02”:
SELECT DATE('1961-05-02 14:56:23');

DATE_ADD()
DATE_ADD(date, INTERVAL expr unit)

Returns the result of adding the expression expr using units unit to the date

...


Table D-1 shows the interval types supported and the expected expr values

...

it-ebooks

...

Com

in this table that show where it is necessary to surround the expr value with quotes for MySQL
to correctly interpret them

...


Table D-1

...

MICROSECONDS'

11

...

MICROSECONDS'

11

...

MICROSECONDS'

11

...

MICROSECONDS'

11

...

 However it’s not actually
necessary for you to use the DATE_ADD or DATE_SUB functions, as you can use date arithmetic
directly in MySQL

...

it-ebooks

...

Com

2011-01-01 00:00:00
1999-12-31 23:59:59

Notice how the last two commands use direct date arithmetic without recourse to functions

...

 Table D-2 shows the
specifiers that can be used in the format string

...

 This code returns the given date and time as “Thursday May 4th
2006 03:02 AM”:
SELECT DATE_FORMAT('2006-05-04 03:02:01', '%W %M %D %Y %h:%i %p');

Table D-2

...

)

%d

Day of the month, numeric (00–31)

%e

Day of the month, numeric (0–31)

%f

Microseconds (000000–999999)

%H

Hour (00–23)

%h

Hour (01–12)

%I

Hour (01–12)

%i

Minutes, numeric (00–59)

%j

Day of year (001–366)

%k

Hour (0–23)

%l

Hour (1–12)

%M

Month name (January–December)

%m

Month, numeric (00–12)

%p

AM or PM

%r

Time, 12-hour (hh:mm:ss followed by AM or PM)

%S

Seconds (00–59)

%s

Seconds (00–59)

%T

Time, 24-hour (hh:mm:ss)

DATE_FORMAT() | 467

www

...

info
Download at Boykma

...

 You can also use the function DAYOFMONTH to return
the same value

...

 This code returns the string “Saturday”:
SELECT DAYNAME('2001-02-03');

DAYOFWEEK()
DAYOFWEEK(date)

Returns the weekday index for date between 1 for Sunday through 7 for Saturday

...

it-ebooks

...

Com

Returns the day of the year for date in the range 1 to 366

...

 If the argument is invalid
it returns NULL

...



MAKEDATE()
MAKEDATE(year, dayofyear)

Returns a date given year and dayofyear values

...

 This
code returns the date “2011-10-01”:
SELECT MAKEDATE(2011,274);

MONTH()
MONTH(date)

Returns the month for date in the range 1 through 12 for January through December

...

 This code
returns the value 7:
SELECT MONTH('2012-07-11');

MONTHNAME()
MONTHNAME(date)

Returns the full name of the month for date

...

it-ebooks

...

Com

SYSDATE()
SYSDATE()

Returns the current date and time as a value in either YYYY-MM-DD HH:MM:SS or
YYYYMMDDHHMMSS format, depending on whether the function is used in a string or
numeric context

...

 On December 19, 2011, this code returns
the values 2011-12-19 19:11:13 and 20111219191113:
SELECT SYSDATE();
SELECT SYSDATE() + 0;

YEAR()
YEAR(date)

Returns the year for date in the range 1000 to 9999, or 0 for the zero date

...

 If passed the optional mode parameter, the week number
returned will be modified according to Table D-3

...

 This code returns
the week number 14:
SELECT WEEK('2006-04-04', 1);

Table D-3

...



0

Sunday

0–53

with a Sunday in this year

1

Monday

0–53

with more than 3 days this year

2

Sunday

1–53

with a Sunday in this year

3

Monday

1–53

with more than 3 days this year

4

Sunday

0–53

with more than 3 days this year

5

Monday

0–53

with a Monday in this year

6

Sunday

1–53

with more than 3 days this year

7

Monday

1–53

with a Monday in this year

470 | Appendix D: MySQL Functions

www

...

info
Download at Boykma

...

 This code returns
the value 1:
SELECT WEEKDAY('2006-04-04');

Time Functions
Sometimes you need to work with the time, rather than the date, and MySQL provides
plenty of functions for you to do so

...

uuuuuu, depending on whether the function is used in a string or numeric context

...

 When the current time is 11:56:23, this code returns the values
11:56:23 and 11:56:23

...

 This code returns the value 11:
SELECT HOUR('11:56:23');

MINUTE()
MINUTE(time)

Returns the minute for time

...

 This code returns the value 23:
SELECT SECOND('11:56:23');

SECOND() | 471

www

...

info
Download at Boykma

...

 This code
returns the time 11:56:23:
SELECT MAKETIME(11, 56, 23);

TIMEDIFF()
TIMEDIFF(expr1, expr2)

Returns the difference between expr1 and expr2 (expr1 – expr2) as a time value

...

 This code returns the value
01:37:38:
SELECT TIMEDIFF('2000-01-01 01:02:03', '1999-12-31 23:24:25');

UNIX_TIMESTAMP()
UNIX_TIMESTAMP([date])

If called without the optional date argument, this function returns the number of seconds
since 1970-01-01 00:00:00 UTC as an unsigned integer

...

 This
code will return the value 946684800 (the number of seconds up to the start of the new
millennium) followed by a TIMESTAMP representing the current Unix time at the moment you
run it:
SELECT UNIX_TIMESTAMP('2000-01-01');
SELECT UNIX_TIMESTAMP();

FROM_UNIXTIME()
FROM_UNIXTIME(unix_timestamp [, format])

Returns the unix_timestamp parameter as either a string in YYYY-MM-DD HH:MM:SS or
YYYYMMDDHHMMSS

...

 If the optional format parameter is provided, the result is formatted
according to the specifiers in Table 8-17

...

it-ebooks

...

Com

APPENDIX E

Using PEAR and PHPUnit

If you’re going to use PHP as a web development language, why not make use of the
wealth of packages that have already been written for it? The community has turned
out in force to write an enormous amount of add-ons, a whole host of which have been
combined in PEAR (the PHP Extension and Application Repository)

...

 Table E-1 lists some of the PEAR packages

...

 Categories of PEAR packages (number in each category)
Authentication (8)

Filesystem (5)

Math (19)

Streams (2)

Caching (2)

Gtk Components (4)

Networking (55)

Structures (30)

Console (7)

Gtk2 Components (7)

Numbers (2)

System (8)

Database (31)

HTML (40)

Payment (4)

Text (19)

Date and Time (22)

HTTP (14)

PEAR (18)

Tools & Utilities (9)

Encryption (13)

Images (19)

PEAR Website (5)

Validate (29)

Event (2)

Internationalization (6)

PHP (20)

Web Services (40)

File Formats (33)

Mail (8)

Semantic Web (5)

XML (32)

Installation
Installation of PEAR will vary according to which operating system you are using

...

 But
Windows and Mac OS X require a little more work

...


You must have administrative privileges to install PEAR

...

it-ebooks

...

Com

Now navigate to C:\Program Files\EasyPHP 3

...

bat by typing the following (and then pressing Return):
go-pear

During installation, accept the defaults by pressing Return whenever you’re asked to
do something

...



Figure E-1

...

 Installing the PEAR MDB2 package

474 | Appendix E: Using PEAR and PHPUnit

www

...

info
Download at Boykma

...

 To do this, type in the following (see Figure E-3):
pear install -f MDB2_Driver_mysql

Figure E-3

...


Finally, as there appears to be a problem with file and path locations in EasyPHP 3

...

* includes

Mac OS
Most Macs come supplied with a version of PEAR, but often it’s out of date

...

php

...

php in your MAMP htdocs folder

...

php

Now all you have to do is click on the Next >> button to see the main installation screen
(see Figure E-4)

...

it-ebooks

...

Com

Figure E-4

...

 You can now sit
back and watch the installation as it progresses

...

 To do this, open up the file /Applications/MAMP/conf/php5/
php

...

:/Applications/MAMP/bin/PHP5/lip/php"

Now change the string after include_path = to read:
"

...

 If prompted, you may also have to enter your Mac password, too

...

 To do this, call up the Terminal and type the following

...


/Applications/MAMP/bin/pear install MDB2_Driver_mysql

476 | Appendix E: Using PEAR and PHPUnit

www

...

info
Download at Boykma

...

 Installing the PEAR MySQL MDB2 driver

Linux/Unix
If you installed the XAMPP package in Chapter 2, you already have PEAR installed

...

 To do this, you should need to issue only the following two commands:
pear install MDB2
pear install MDB2_Driver_mysql

Creating a Connect Instance
With all of PEAR, the MDB2 package, and the MySQL driver installed, you can start
to take advantage of these new additions

...


In other words, MDB2 knows everything about accessing any major brand of database
program you may have installed

...

 This means you can migrate to another SQL database
such as PostgreSQL and will only have to install the new MDB2 driver and change a
single line of code in your PHP file to be up and running again

...

php file:

Creating a Connect Instance | 477

www

...

info
Download at Boykma

...

php';
$dsn = "$db_username:$db_password@$db_hostname/$db_database";
$mdb2 = MDB2::connect("mysql://$dsn");

The require_once line loads MDB2

...

 It comprises
username:password@hostname/database

...

 Recall that as mentioned in Chapter 5, the double colon (::) token indicates a class to be used on the left and a method
to call from that class to the right

...

 If, for example, you were using a PostgreSQL database you
would replace the head with pgsql://

...


To check whether the program successfully connected to the database, you can issue
a call to the PEAR isError method, like this:
if (PEAR::isError($mdb2))
die("Unable to connect to MySQL: " 

...

 In that case the die function is called, and an error message is issued before calling
the getMessage method from within the $mdb2 object to output the last message, describing the error encountered

...

 Instead
of calling the mysql_query function, call the query method of the $mdb2 object as follows
(assuming that the variable $query has already been assigned a query string):
$result = $mdb2->query($query);

Fetching a Row
The variable $result, returned by the query method, is another object

...

it-ebooks

...

Com

Closing a Connection
To close an MDB2 database connection, call the disconnect method of the $mdb2 object:
$mdb2->disconnect();

Rewriting Example 10-8 to Use PEAR
Hopefully you now have the hang of this new object-oriented approach to accessing
MySQL

...

php program in Example 10-8 can be rewritten
using PEAR’s MDB2 package (see Example E-1, sqltest_mdb2

...


Example E-1

...

php

...

php';
require_once 'MDB2

...

 $mdb2->getMessage());
if (isset($_POST['author']) &&
isset($_POST['title']) &&
isset($_POST['category']) &&
isset($_POST['year']) &&
isset($_POST['isbn']))
{
$author
= get_post('author');
$title
= get_post('title');
$category = get_post('category');
$year
= get_post('year');
$isbn
= get_post('isbn');
if (isset($_POST['delete']) && $isbn != "")
{
$query = "DELETE FROM classics WHERE isbn='$isbn'";
if (!$mdb2->query($query))
echo "DELETE failed: $query
" 

...

 "

";

}
else
{

$query = "INSERT INTO classics VALUES" 

...


$mdb2->getMessage() 

...

it-ebooks

...

Com

}
echo <<<_END

...

 The differences have been highlighted in bold

...

php for yourself to use MDB2, as in the
example, and then save it as sqltest_mdb2

...



480 | Appendix E: Using PEAR and PHPUnit

www

...

info
Download at Boykma

...



Adding Other PEAR Packages
With PEAR properly installed on your system, you are able to install additional packages from the command line by using one of the commands in Table E-2, where
package is the name of a PEAR package, as listed at the web page http://pear

...

net/
packages

...


Table E-2

...

0\php\pear" install package

Mac OS X

/Applications/MAMP/bin/pear install package

Linux Unix

pear install package

To determine which packages are installed, replace install package in Table E-2 with
the word list and the output should be similar to the following:
INSTALLED PACKAGES, CHANNEL PEAR

...

NET:
=========================================
PACKAGE
VERSION STATE
Archive_Tar
1

...

2
stable
Console_Getopt
1

...

3
stable
MDB2
2

...

1
stable
MDB2_Driver_mysql 1

...

1
stable
PEAR
1

...

2
stable
Structures_Graph
1

...

2
stable

Unit Testing with PHPUnit
Now that you are familiar with object-oriented programming, it’s a good idea to get a
taste of unit testing

...


Unit testing provides the following benefits:
• It allows for automation of the testing process

...


• Testing is often enhanced because attention is given to each unit

...

 To do this, go to a Command or Terminal
prompt, ensure that you have Administrator or Superuser privileges, and issue the two

Unit Testing with PHPUnit | 481

www

...

info
Download at Boykma

...

 (On Windows systems,
ignore and close any pop-up “Invalid configuration directive” alerts that may appear

...

 Commands for Installing PHPUnit on different systems
System

Instructions to type from an Administrator Command prompt or a Terminal window

XP/Vista

cd \Program Files\EasyPHP 3

...

phpunit

...

* includes

Mac OS X

cd /Applications/MAMP/bin

(3 instructions)

pear channel-discover pear

...

de
pear install phpunit/PHPUnit

Linux/Unix

pear channel-discover pear

...

de

(2 instructions)

pear install phpunit/PHPUnit

The two main commands register the PEAR channel on your system, download
PHPUnit, and install it

...

0

...


You are then ready to take on some powerful bug testing

...

 So, for example, consider the case of
testing PHP’s in-built array and the function sizeof and its alias count

...


Example E-2

...

 "
";
$names[] = 'Bob';
echo count($names) 

...



482 | Appendix E: Using PEAR and PHPUnit

www

...

info
Download at Boykma

...

 Modified Example E-2 to output OK/Not OK
$names = array();
echo sizeof($names) == 0 ? "OK
" : "Not OK
";
$names[] = 'Bob';
echo sizeof($names) == 1 ? "OK
" : "Not OK
";

Helpful as this code is, there’s an even better way to handle errors, which is to display
a message only when a value is incorrect

...


Example E-4

...

');
}

Now we’ve arrived at a fully automated test, let’s look at how we would rewrite it using
PHPUnit (see Example E-5)

...

 PHPUnit testing in action
require_once 'PHPUnit/Framework

...

it-ebooks

...

Com

The first thing to notice is that PHPUnit/Framework

...

 After that, the program defines a
new class that extends the PHPUnit_Framework_TestCase class

...


The rules for writing PHPUnit tests are:
•
•
•
•

The tests for a class called Class go into a class with the name ClassTest

...


The tests are public methods that are named testSomethingDescriptive

...



And there you have it

...

 All
being well, this program will display nothing, so to see the output from PHPUnit, try
changing the 0 or 1 parameters in the assertEquals calls to other values

...

phpunit

...

 Click “Read the documentation” to view it in either HTML or PDF format

...

it-ebooks

...

Com

Index

Symbols
! (exclamation mark)
!= (not equal) operator, 43, 65, 68, 309,
321
!== (not identical) operator, 65, 68, 309,
321
logical not operator, 44, 309, 321, 324
precedence in PHP, 65
NOT operator, 69
" " (quotation marks, double)
escaping in JavaScript strings, 310
in multiline PHP strings, 47
in MySQL search strings, 189
in PHP strings, 38, 46
in JavaScript strings, 306
$ (dollar sign)
$ function in JavaScript, 316
end-of-line matching in regular expressions,
366, 367
preceding PHP variable names, 37
omitting when using -> operator, 105
% (percent sign)
%= (modulus assignment) operator, 43, 65,
308, 321
modulus operator, 42, 65, 308, 321
& (ampersand)
&& (logical and) operator, 44, 309, 321,
324
precedence of, 321
&& (logical and) operator/precedence in
PHP, 65
&= (bitwise and assignment) operator, 65,
321
bitwise and operator, 65

variables passed by reference, 94
' ' (quotation marks, single)
enclosing PHP array items, 40
escaping in JavaScript strings, 310
in PHP strings, 46
in JavaScript strings, 306
( ) (parentheses)
forcing operator precedence, 65
function call in JavaScript, 321
function call in PHP, 90
grouping in regular expressions, 363, 367
implied, indicating operator precedence,
65
precedence in PHP, 65
* (asterisk)
*= (multiplication assignment) operator, 43,
65, 308, 321
multiplication operator, 42, 65, 308, 321
regular expression metacharacter, 361, 367
wildcard character, use with SELECT
command, 183
+ (plus sign)
++ (increment) operator, 42, 45, 308, 310,
321
precedence in PHP, 65
using in while loop, 80
+= (addition assignment) operator, 43, 45,
65, 308, 321
addition and string concatenation operator
in JavaScript, 321
addition operator, 42, 65, 308
Boolean mode in MySQL searches, 189
regular expression metacharacter, 362, 367
string concatenation operator in JavaScript,
310, 321

We’d like to hear your suggestions for improving our indexes

...

com

...

it-ebooks

...

Com

, (comma)
comma operator in JavaScript, 321
separating statements within parameter
section of loop, 82
- (hyphen)
indicating ranges in regular expressions,
364, 367
- (minus sign)
-= (subtraction assignment) operator, 43
-> operator (PHP), 105


...

= (string concatenation and assignment)
operator, 43, 46, 65
member operator in JavaScript, 321
in regular expressions, 362, 367
separating objects, properties, and methods
in JavaScript, 314
string concatenation operator, 46, 65
/ (slash)
/* and */ in JavaScript multiline comments,
305
/* and */ in PHP comments*, 36
// in JavaScript single-line comments, 305
// in PHP comments, 36
/= (division assignment) operator, 43, 65,
308, 321
division operator, 42, 65, 308, 321
enclosing regular expressions, 361, 367
/i (case-insensitive matching) in regular
expressions, 369
: (colon)
:: (scope resolution) operator in PHP, 106
using with self keyword, 107
replacing first curly brace in PHP switch
statement, 77
; (semicolon)
ending JavaScript statements, 305
ending MySQL commands, 163
ending PHP statements, 37
separating parameters in for loop, 82
< (less than) operator, 43, 65, 68, 309, 321,
324
<< (bitwise left shift) operator, 65, 321
<<< (heredoc) operator, 48
<<= (bitwise left shift and assignment)
operator, 65, 321
<= (less than or equal to) operator, 43, 65, 68,
309, 321, 324
<> (not equal) operator, 65

486 | Index

 tags in PHP code, 5
 tags, 34
importance in login file, 226
omitting closing tag, 35
= (equals sign)
== (equal to) operator, 43, 65, 67, 309, 321,
323
=== (identity) operator, 65, 68, 309, 321,
323
=> assigning value to array index, 118
assignment operator, 43, 65, 308, 321
> (greater than) operator, 43, 65, 68, 309, 321,
324
>= (greater than or equal to) operator, 43, 65,
68, 309, 321, 324
>> (bitwise right shift) operator, 65, 321
>>= (bitwise right shift and assignment)
operator, 65, 321
>>> (bitwise unsigned right shift) operator,
321
>>>= (unsigned right shift and assignment)
operator, 321
? (question mark)
? : (ternary) operator, 65, 77, 321, 331
encoding in URL for GET request, 399
regular expression metacharacter, 367
[ ] (square brackets)
accessing array elements in JavaScript, 348,
349
array element, accessing, 122
character classes in regular expressions, 364,
367
member operator in JavaScript, 321
\ (backslash)
escaping characters in JavaScript, 310
escaping characters in PHP strings, 47
escaping regular expression metacharacters,
363
^ (caret)
beginning-of-line matching in regular
expressions, 366, 367
bitwise xor operator, 65
negating character class in regular
expressions, 364, 367
^= (bitwise xor with assignment) operator,
65, 321
_ (underscore)
double underscore (__), beginning method
names in PHP, 105

www

...

info
Download at Boykma

...

 while loops, 81
in if 

...

 elseif 

...

html), 380–
385
GET method, using in example program,
385–387
properties and methods, 379
sending XML requests, 387–391
using YUI, 394–400
Ajax XML example, 399–400
asyncRequest method, 397
GET request (example), 397

including framework files, 396
alphabetical sort (JavaScript sort method), 352
ALTER command, 173
adding auto-incrementing column, 172
creating table index, 178
removing a column, 173
renaming a table, 175
and operator
&&, 44, 309
low-precedence and, 44
precedence in PHP, 65
AND operator, 69
AND operator (in MySQL), 194
anonymous functions, 382
Apache web server, 8
arguments array (JavaScript functions), 338
arithmetic operators
JavaScript, 308
PHP, 42
array function (PHP), testing, 482
array keyword (JavaScript), 347
array keyword (PHP), 118
arrays
fetching row from MySQL database as
associative array, 274
JavaScript, 307, 346–353
associative arrays, 347
concat method, 349
forEach method, 349
join method, 350
multidimensional arrays, 348
numeric arrays, 346
push and pop methods, 350
reverse method, 352
sort method, 352
PHP, 39, 115–128
assignment using array keyword, 118
associative arrays, 117
foreach 

...

it-ebooks

...

Com

combining with expressions in PHP, 63
to PHP arrays, using array keyword, 118
variable type, setting in JavaScript, 311
assignment operators
JavaScript, 308
PHP, 43
associative arrays
in JavaScript, 347
in PHP, 117
$_FILES array, 145
multidimensional, 121
walking through, using foreach 

...

 AGAINST
queries, 189
break command
using in JavaScript loops, 333
using in JavaScript switch statement, 330
using in PHP for loop, 83
using in PHP switch statement, 76
browser/server request/response dialog with
cookies, 279
browsers, 1
basic request/response procedure, 2
catching JavaScript errors with try 

...

it-ebooks

...

Com

CGI (Common Gateway Interface), server-side
scripting, 5
CHANGE keyword, specifying data type, 176
CHAR data type, 168
listing of CHAR types, 169
character classes in regular expressions, 363
negating, 364
check user program (social networking site
project), 417
checkboxes in forms, 257
submitting multiple values with an array,
258
checkdate function (PHP), 136
classes
declaring in JavaScript, 342
defined, 89
PHP, 99
declaring, 100
inheritance and extension, 109–113
static properties and methods, 108
Classic FTP, 28
clone operator (PHP), 103
cloning objects in PHP, 102
Codd, E

...

, 203
code examples from this book, xvi
viewing and downloads, 35
website, 406
columns
adding auto-incrementing column, 173
adding new column and viewing its data,
175
changing data type, 175
defined, 158
removing, 173, 176
renaming, 176
comma (,)
comma operator in JavaScript, 321
separating statements within parameter
section of loop, 82
Comma-Separated Values format (see CSV
format)
command prompts, MySQL, 163
command-line interface, accessing MySQL,
158–177
Linux users, 161
Mac OS X users, 160
MySQL commands, 164–168
MySQL data types, 168–177
using MySQL on, 163

Windows users, 159
commands, MySQL, 164–168
canceling, 163
comment tags in HTML , 301
comments
JavaScript, 305
PHP, 36
COMMIT command, 216
compact function, using with PHP arrays, 126
comparison operators
JavaScript, 309, 324
PHP, 43, 68
concat method (JavaScript), 349
condition expression (for loop), 81
conditionals
in JavaScript, 328–331
? operator, 331
if statement, 328
switch statement, 329
in PHP, 70–78
? operator, 77
else statement, 72
elseif statement, 73
if statement, 71
switch statement, 74–77
connection-min

...

it-ebooks

...

Com

browser/server request/response dialog
with, 279
destroying in PHP, 282
forcing cookie-only sessions, 296
setting in PHP, 281
third-party, 279
copy function (PHP), 139
count function (PHP), counting array elements,
124
COUNT qualifier, SELECT command, 183
CREATE command
adding auto-incrementing id column, 173
adding indexes when creating tables, 179
CREATE INDEX, 179
creating a table in MySQL, 167
Cross-Site Scripting (XSS) injections, 248
CSS (Cascading Style Sheets), manipulation
with JavaScript, 5
CSV (Comma-Separated Values) format, 219
dumping data in, using mysqldump, 222

D
\d (digit character) in regular expressions, 368
\d (digit character) in regular expressions, 364
\D (nondigit character) in regular expressions,
368
data types
casting, in PHP, 84
JavaScript type-changing functions, 334
MySQL, 168–177
AUTO_INCREMENT type, 172
BINARY types, 169
BLOB, 170
changing for column, 175
CHAR types, 169
DATE and TIME types, 172
numeric, 170
TEXT and VARCHAR types, 170
variable typing in JavaScript, 311
databases, 6
creating in MySQL, 165
defined, 157
design in MySQL
highly inefficient table design, 203
requirements of First Normal Form,
205
designing in MySQL, 201
privacy and, 214

490 | Index

selecting database for PHP query in MySQL,
228
triggers, 211
DATE and TIME data types, 172
date and time functions (PHP), 133–137
checkdate, 136
date constants, 136
date function (PHP), 53, 134
date functions (MySQL), 465–471
DATETIME data type, 172
debugging JavaScript errors, 303
decrement operator (--), 42, 45
in JavaScript, 308
variable decrementing in JavaScript, 310
decrementing variables in JavaScript, 310
default action for switch statement
in JavaScript, 330
in PHP, 76
default values in forms, 255
DELETE command, 184
issuing DELETE FROM query using PHP,
242
dependencies in table data, 209
DESC keyword (MySQL), 191
DESCRIBE command, 168
issuing from PHP, 239
destroy_session_and_data function (PHP),
292
destructor methods (PHP5), 104
development server, setting up, 13–32
installing LAMP on Linux, 25–26
installing MAMP on Mac OS X, 19–25
installing WAMP on Windows, 14–18
using a program editor, 29
using an IDE, 30
WAMP, MAMP, or LAMP, 13
working remotely, 26–28
die function (PHP), 227
different_user function (PHP), 294
digits (\d) in regular expressions, 364
DISTINCT qualifier, SELECT command, 184
DNS (Domain Name Service), 3
do 

...

01 document types, 153
XHTML 1

...

it-ebooks

...

Com

example XML document, DOM tree of,
389
DOUBLE data type, 171
DROP keyword, 176
DROP TABLE command, issuing from PHP,
240
duplication of data
across multiple database columns, 204
across multiple database rows, 206
dynamic linking (PHP), 85
use by WordPress blogging platform, 86
dynamic web content, 1–11
Apache web server, 8
benefits of PHP, MySQL, and JavaScript, 5–
8
combination of PHP, MySQL, and
JavaScript, 9
HTTP and HTML, 2

E
each function, using with list function to walk
through associative array, 120
EasyPHP, 14–18
downloading and installing, 14
overcoming installation problems, 14
testing installation, 16
echo <<< construct, 252
echo command (PHP), print command versus,
51
Editra program editor, 29
else statements
in JavaScript, 329
in PHP, 72
closing if 

...

 elseif 

...

 catch, 327
debugging, 303
trapping in for loop using break statement,
83
trapping in for loop using continue
statement, 84
escape characters
backslash (\) in regular expressions, 363
in JavaScript, 310
in PHP, 47
preventing in strings for submission to
MySQL, 263
escapeshellcmd function (PHP), 150
European Laboratory for Particle Physics
(CERN), 1
event-min

...

it-ebooks

...

Com

creating a file, 137
deleting a file, 140
locking files, 142
moving a file, 140
reading entire file, 143
reading from files, 139
updating files, 141
uploading files, 144–149
form data validation, 147
using $_FILES array, 146
file pointer, 141
$_FILES array, 145
contents of, 146
files, including and requiring in PHP, 96
include statement, 96
include_once, 97
require and require_once, 97
FileZilla, 28
file_exists function, 137
file_get_contents function (PHP), 143
final methods (PHP), 112
finally clause (try 

...

 as loops, 119–120

492 | Index

printing out values in multidimensional
associative array, 122
walking through multidimensional numeric
array, 123
forEach method (JavaScript), 349
cross-browser solution, 350
foreign keys, 206
form feed (\f) in JavaScript strings, 310
 tag, onSubmit attribute, 357
forms, 251–267
building using PHP, 251
creating form to add records to MySQL
database, using Smarty, 272
data validation, 147
example PHP program converting between
Fahrenheit and Celsius, 264–266
inserting and deleting data in MySQL using
PHP (example), 232–236
processing submitted data using PHP, 253–
264
checkboxes, 257
default values, 255
hidden fields, 260
input types, 256
labels, 262
radio buttons, 259
sanitizing input, 263
select tags, 260
text areas, 256
text boxes, 256
redisplaying after PHP validation, 370–375
uploading files from, 144–149
validating user input with JavaScript, 355–
361
form field validation, 358–361
frameworks for JavaScript, 393, 394
(see also YUI)
fread function (PHP), 138
reading a file, 139
friends on social networking site
adding and dropping, 424
module showing user’s friends and
followers, 427–430
fseek function (PHP), 141
FTP, transferring files to and from web server,
27
FULLTEXT indexes, 182
stopwords, 457–459
using MATCH 

...

it-ebooks

...

Com

functions
defined, 89
JavaScript, 312, 337–341
defining, 337
prototype property, 344
returning a value, 339
returning an array, 341
MySQL, 194
commonly used, reference listing, 461–
472
PHP, 52, 90–96
array functions, 123–128
defining, 91
passing by reference, 94
returning a value, 92
returning an array, 93
version compatibility, 98
social networking site project, 407–409
fwrite function (PHP), 138

G
/g (global matching) in regular expressions,
369
$_GET and $_POST arrays, sanitizing user
input, 263
GET method, 380, 397
Ajax GET example using YUI, 397
using instead of Ajax POST, 385–387
getElementById function (JavaScript), 316
get_post function (PHP), 234
global matching in regular expressions (/g),
369
global variables
JavaScript, 312
PHP, 55
returning from function calls, 95
Glossword WAMP, 18
Gmail, use of Ajax to check for username
availability, 10
Google Chrome
accessing JavaScript error messages, 303
Error Console message for JavaScript error,
304
Google Maps, 377
GRANT command, example parameters for,
165
GROUP BY command, 191

H
head section of HTML document, using
JavaScript within, 301
heredoc (<<<) operator, 48
hexadecimals, escaping in JavaScript strings,
310
hidden fields in forms, 260
history object (JavaScript), 317
HTML, 2, 151
(see also XHTML)
basic knowledge of, xiii
DOM (Document Object Model) in
JavaScript, 314–318
incorporating PHP within, 33
JavaScript and, 300
comment tags for older and nonstandard
browsers, 301
debugging JavaScript errors, 303
including JavaScript files, 302
using scripts within document head,
301
left arrow (← entity), 417
manipulation with JavaScript, 7
multiline output, creating in PHP, 252
program editors for, 29
sanitizing in form input, 263
HTML 4

...

it-ebooks

...

Com

PHP IDEs, listed, 32
if 

...

php file (social networking site project),
411
indexes, 206
arrays in PHP, 40
associative arrays, 117
numerically indexed, 115
MySQL tables, 177–183
adding when creating tables, 179
creating using ALTER TABLE, 178
creating using CREATE INDEX, 179
FULLTEXT index, 182
performance and, 182
PRIMARY KEY, 180
types of indexes, 178
using MATCH 

...

it-ebooks

...

Com

using scripts within document head,
301
HTML text and, 300
injection via malicious form input, 263
local variables, 312
looping, 331–334
objects, 341–346
onError event, 326
operators, 308–311, 321–325
regular expressions, 361–369
using, 369
semicolon (;) ending statements, 305
try 

...

 ON construct in MySQL, 194
join method (JavaScript), 350
joining tables in MySQL, 192–194
AS keyword, 194
JOIN 

...

php file, creating, 226
looping
in JavaScript, 331–334
breaking out of loops, 333
continue statement, 334
do 

...

 while loops, 81
for loops, 81
foreach 

...

3, 25
installing other PEAR packages, 481
installing PEAR, 475
installing PHPUnit, 482
likely location for mysqldump, 219
Index | 495

www

...

info
Download at Boykma

...

 AGAINST queries, 188
using Boolean mode, 189
md5 function (PHP), 285
md5 hexadecimal strings, 294
MDB2 package (PEAR), 473
creating connect instance, 477
installing on Linux or Unix, 477
installing on Mac OS, 475
installing on Windows, 474
querying database, 478
using to rewrite sqltest

...

it-ebooks

...

Com

normalization, 203
PEAR MDB2 package (see MDB2 package)
performing secondary query using PHP,
244
PHP authentication using, 287
preventing escape character injection into
string presented to MySQL, 263
preventing HTML and XSS injections, 248
preventing SQL injection, 245–248
querying a database, 183–192
querying database with PHP, 225–231
connecting to MySQL, 227–231
creating login file, 226
steps in process, 225
relationships among data, 211–214
sanitizing in form input, 263
social networking site project
tables setup file, 410
transactions, 214–217
updating data using PHP, 242
using, 6
mysqldump utility, 219
dumping backups to file, 220
dumping data into CSV format files, 222
locations on various installations and
operating systems, 219
mysql_close function (PHP), 231
mysql_connect function (PHP), 227
mysql_entities_fix_string function (PHP), 249
mysql_error function (PHP), 227
mysql_fetch_array function (PHP), 274
mysql_fetch_row function (PHP), 231
mysql_insert_id function (PHP), 244
mysql_result function (PHP), 229

N
\n (newline character), 367
in JavaScript strings, 310
in PHP strings, 47
{n} matches exactly n times in regular
expressions, 368
{n, } matches n times or more in regular
expressions, 368
naming convention for JavaScript functions,
338, 342
NATURAL JOIN in MySQL, 193
new operator in JavaScript, 322
normalization, 203–211
First Normal Form, 204

Second Normal Form, 206–208
Third Normal Form, 209–210
when not to use, 210
 tags, 300
not operator (!), 44, 309
NOT operator (!), 69
NOT operator (in MySQL), 194
NULL values, representing FALSE, 69
numbers
converting PHP strings to and from, 49
converting strings to and from in JavaScript,
311
numeric arrays
in PHP, 115
multidimensional, 122
walking through, using foreach 

...

it-ebooks

...

Com

octals, escaping in JavaScript strings, 310
one-to-many relationships, 212
one-to-one relationships in data, 211
one-way functions, 285
onError event (JavaScript), 326
open source, 9
Opera browsers
accessing JavaScript error messages, 303
Error Console message for JavaScript, 304
Error Console message for JavaScript error,
304
operating systems, 13
(see also individual operating system names)
system calls in PHP, 149
operators
JavaScript, 308–311, 321–325
arithmetic, 308
assignment, 308
associativity, 322
comparison, 309
escaping characters, 310
logical, 309
precedence of, 321
relational, 323
string concatenation, 310
types of, 321
PHP, 42, 63–70
arithmetic, 42
assignment, 43
associativity, 66
comparison, 43
logical, 44
operator precedence, 64
precedence of, 64
relational, 67–70
types of, 63
or operator
low-precedence or, 44
precedence in PHP, 65
||, 44, 309
OR operator, 69
causing problems in if statements, 70
OR operator (in MySQL), 194
ORDER BY command, 191

P
page design and layout file (social networking
project), 409
parent operator (PHP), 110
498 | Index

parsers
calling PHP parser, 34
passing by reference, 94
pattern matching, 361
(see also regular expressions)
doing with LIKE qualifier in MySQL, 186
PEAR (PHP Extension and Application
Repository), 473–481
creating connect instance, 477
installation, 473–477
installing other PEAR packages, 481
querying database, 478
rewriting sqltest

...

it-ebooks

...

Com

calling PHP parser, 34
inserting and deleting data in MySQL
(example), 232–238
$_POST array, 234
deleting a record, 235
displaying form, 236
querying database, 236
running program, 237
inserting and deleting data in MySQL table,
deleting a record, 235
multiple-line commands, 47
operators, 42–45, 63–70
arithmetic, 42
assignment, 43
comparison, 43
logical, 44
performing secondary query on MySQL
table, 244
phpDesigner IDE, 30
preventing HTML and XSS injections in
MySQL, 248
preventing SQL injection in MySQL, 245–
248
using placeholders, 248
program editors for, 29
querying MySQL database, 225–231
building and executing query, 228
closing connection, 231
connecting to MySQL, 227
creating login file, 226
fetching result, 229
selecting a database, 228
steps in process, 225
regular expressions in, 369
retrieving data from MySQL table, 241
semicolon (;) ending PHP statements, 37
support by Apache web server, 9
updating data in MySQL table, 242
using, 5
using AUTO_INCREMENT in MySQL
table, 243
using cookies, 279–282
accessing a cookie, 281
destroying a cookie, 282
setting a cookie, 281
using for Ajax
program for Ajax GET request, 397
urlget

...

php program (example), 386
urlpost

...

php program (example), 387
variable assignment, 45–47
escaping characters in strings, 47
incrementing and decrementing variable,
45
string concatenation, 46
string types, 46
variable scope, 53–58
variable typing, 49
variables, 38–42
arrays, 39
naming rules, 42
numeric, 39
syntax, 37
two-dimensional arrays, 40
version compatibility, checking for
functions, 98
PHP 5
constructor methods, creating, 104
destructor methods, creating, 104
property and method scope, 107
static methods, 105
PHP Extension and Application Repository (see
PEAR)


...

it-ebooks

...

Com

using to prevent SQL injection in MySQL,
247
pop method (JavaScript arrays), 350
ports, configuring for MAMP on Mac OS X,
24
$_POST array, 234
POST method, 145
Ajax program, urlpost

...

 AGAINST construct, 188
SELECT command, 183
SELECT COUNT command, 183
SELECT DISTINCT command, 184
sorting results with ORDER BY, 191
UPDATE 

...

it-ebooks

...

Com

regeneration, session, 295
register_globals function (PHP), 254
regular expressions, 361–370
breakdown of typical regular expression,
365
breakdown of validateUsername regular
expression (example), 366
character classes, 363
examples of, 368
general modifiers, 369
grouping through parentheses, 363
metacharacters, 361
summary of, 367
negation of character class, 364
ranges in, 364
using in JavaScript, 369
using in PHP, 369
using to validate username in a form, 360
using with ereg_replace function in PHP,
149
relational databases, 158
(see also MySQL)
normalization, 203
relational operators
in JavaScript, 323–325
in PHP, 67–70
relationships in database data, 211–214
many-to-many, 213
one-to-many, 212
one-to-one, 211
privacy and, 214
remote server, accessing MySQL on, 162
RENAME command, 175
rename function (PHP), 140
replace method (JavaScript), 369
request/response process, 2
for dynamic web pages, 3
require statements (PHP), 97
require_once (PHP), 97, 227
reset function, using with PHP arrays, 127
results from PHP query of MySQL database,
229
fetching a row, 231
return statements, 92
reverse method (JavaScript), 352
Robin’s Nest project (see social networking site,
creating)
ROLLBACK command, 216
rows

defined, 158
deleting from table, 184
preventing duplicates, using
AUTO_INCREMENT type, 172

S
\S (nonwhitespace character) in regular
expressions, 367
\s (whitespace character) in regular characters,
367
Safari, accessing JavaScript error messages,
303
salting passwords, 285
sanitizeString and sanitizeMySQL functions
(PHP), 263
sanitizing user input, PHP authentication using
MySQL, 288
scope of variables
global and local variables in JavaScript, 312
PHP, 53–58, 96
scope resolution operator (::), 106
 tags, 300
scripting languages, VBScript and Tcl, 302
security
register_globals function in PHP, 254
sessions, 293–296
superglobal variables in PHP, 57
SELECT command, 7, 183
grouping results with GROUP BY, 191
issuing SELECT * FROM statement using
PHP, 241
joining two tables in single SELECT, 193
SELECT COUNT, 183
SELECT DISTINCT, 184
sorting results with ORDER BY, 191
using LIKE qualifier, 186
using LIMIT qualifier, 187
WHERE keyword, 185
select tags in forms, 260
using with multiple parameter, 261
self keyword (PHP), referencing constants,
107
servers, 1
Apache web server, 8
basic request/response procedure, 2
dynamic request/response procedure, 3
shared, session security and, 296
sessions, 289–296
ending, 292
Index | 501

www

...

info
Download at Boykma

...

php program to use Smarty
(example), 272–277
social networking site, creating, 405–433
checking username for availability and
notifying user, 417
code examples on website, 406
designing the site, 405
friends module, 427–430
home page, index

...

it-ebooks

...

Com

security and, 57
switch statements
in JavaScript, 329
break command, 330
default action, 330
in PHP, 74–77
alternative syntax, 77
breaking out, 76
default action, 76
system calls in PHP, 149

T
\t (tab character)
in JavaScript strings, 310
in PHP strings, 47
in regular expressions, 367
use with echo statement to print out array
data, 122
tables
adding new column, 175
checking whether new table has been
created, 167
creating for MySQL database, 166
creating in MySQL using PHP, 238
creating, viewing, and deleting, 177
defined, 158
describing in MySQL using PHP, 239
dropping in MySQL using PHP, 240
indexing, 177–183
intermediary table for many-to-many
relationships, 213
joining, 192–194
linking through insert ID, 244
populating using INSERT command, 174
relationships among, 212
renaming, 175
Tcl scripting language, 302
Telnet, using for remote access, 27
templating, 269
ternary operator (?), 77
ternary operators, 64
test function (JavaScript), 360
test method (JavaScript), 360, 369
text areas in forms, 256
controlling text wrapping, 257
text boxes in forms, 256
TEXT data type
listing of TEXT types, 170
VARCHAR versus, 170

  tags, 256
this keyword (JavaScript), 342
$this variable (PHP), 105
TIME data type, 172
time function (PHP), 53, 133
time functions (MySQL), 471
timeout, setting for sessions, 293
TIMESTAMP data type, 172
TINYINT data type, 171
transactions, 214–217
beginning with BEGIN or START
TRANSACTION, 216
canceling using ROLLBACK, 216
committing using COMMIT command,
216
storage engines for, 215
triggers, 211
try 

...

 SET queries, 190
updates, database
triggers for, 211
URLs
encoding question mark (?) in URL for GET
request, 399
links object in JavaScript, 317
user agent string (browsers), 294
user profiles (see profiles, social networking site
project)
usernames and passwords
checking validity in PHP authentication,
284

Index | 503

www

...

info
Download at Boykma

...

0, 1
Web 2

...

it-ebooks

...

Com

word (\w) character in regular expressions,
368
WordPress blogging platform, 86
World Wide Web, 1

sign-up page for social networking site
project, 415–417
simple calendar, 400–403
using for Ajax, 396–400
Ajax GET example using YUI, 397
Ajax XML example using YUI, 399–400
asyncRequest method, 397
including framework files, 396

X
XAMPP, 18
downloading for Mac OS X, 25
installing on Linux, 25
XHTML, 151
benefits of, 151
document types in XHTML 1

...

01 document types, 153
rules differentiating it from HTML, 152
validation, 154
versions, 151
XML
Ajax XML example using YUI, 399–400
example XHTML 1

...

html), 380–385
readyState property, 382
server half of Ajax process, 383
example program using GET, 385–387
properties and methods, 379
sending XML requests, 387–391
xor (exclusive or) operator, 44
precedence in PHP, 65
XOR operator, 69
XSS injections, 248

Z
Zend Server CE, 25
ZEROFILL qualifier, using with INT type, 171

Y
Yahoo! User Interface (see YUI)
yahoo-min

...

it-ebooks

...

Com

www

...

info
Download at Boykma

...

 During
this time he has written in excess of 500 articles for many of the UK’s top computer
magazines

...


Robin started his computing career in the Cheshire homes for disabled people, where
he was responsible for setting up computer rooms in a number of residential homes,
and for evaluating and tailoring hardware and software so that disabled people could
use the new technology—sometimes by means of only a single switch operated by
mouth or finger

...


With the dawn of the Internet in the 1990s, Robin branched out into developing websites

...

 In order to enable people to continue to surf while listening, Robin
also developed the first known pop-up windows

...



Colophon
The animals on the cover of Learning PHP, MySQL, and JavaScript are sugar gliders
(Petaurus breviceps)

...

 Their tails, which are distinguished by a black
tip, are usually as long as their bodies

...


Sugar gliders are native to Australia and Tasmania

...


Though sugar gliders reside in groups and defend their territory together, they don’t
always live in harmony

...

 This ensures that members of the group
will know when an outsider approaches; group members will fight off any sugar glider
not bearing their scent

...



www

...

info
Download at Boykma

...

 But there are disadvantages to keeping sugar gliders as
pets: as they are exotic animals, sugar gliders need specialized, complicated diets consisting of items such as crickets, a variety of fruits and vegetables, and mealworms;
healthy housing requires a cage or space no less than the size of an aviary; their distinctive scents can be bothersome to humans; as they are nocturnal creatures, they will
bark, hiss, run, and glide all night long; it’s not uncommon for them to extricate their
bowels while playing or eating; and in some states and countries, it is illegal to own
sugar gliders as household pets

...

 The cover font is Adobe ITC Garamond

...




Title: Learning PHP JAVA SCRIPT And SQL
Description: Step By Step Guide To Creating A Dynamic Websites with PHP Language.PHP Function and objects. Introduction about SQL and how to use SQL Database.How to Explore the javascript and how to use html Text in Java Script.Programig in PHP Language.
Buy These NotesPreview