ENGINEERING, MATHEMATICS AND PHYSICAL SCIENCES

B.SC. INFORMATION TECHNOLOGYMANAGEMENT FOR BUSINESS

B.SC. MATHEMATICS AND COMPUTER SCIENCE

DATA DRIVEN WEB SITESUSING PHP AND MYSQL

OCTOBER 29, 2010

Contents

1 Introduction 31.1 Introduction to the Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

1.1.1 Testing That It Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31.1.2 Uploading & Displaying the Site . . . . . . . . . . . . . . . . . . . . . . . . . . . 31.1.3 Creating the Site . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41.1.4 Modifying the Site . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

1.2 Database Maintenance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41.2.1 Connecting to the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41.2.2 Changing your password . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41.2.3 Creating and Dumping Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51.2.4 Adding Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

2 Introduction to Dynamic Web Sites 72.1 Introduction to the Scenario . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

2.1.1 The Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72.1.2 Web Site Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

2.2 Creating a Simple Website . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72.2.1 Connecting to the database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72.2.2 PHP Superglobals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102.2.3 Retrieving & Displaying Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102.2.4 Formatting the Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

2.3 Adding Data to the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122.3.1 Inserting a New Person . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122.3.2 Header Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

3 Basic Security and Sessions 153.1 Deleting Data from the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153.2 Updating The Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163.3 Logging In . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

3.3.1 User Authentication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183.3.2 User Login . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183.3.3 Validating the User . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193.3.4 Passing Error Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

3.4 Using Sessions for Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193.4.1 What Are Sessions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193.4.2 Starting PHP Sessions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203.4.3 Controlling User Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203.4.4 Using Session Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203.4.5 Logging Out . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

4 Project Groups & Site Search 234.1 Setting Up a Project Group . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

4.1.1 Using Sessions to Set Up a Project Group . . . . . . . . . . . . . . . . . . . . . . 23

i

ii CONTENTS

4.1.2 Formatting the Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244.1.3 The Add Person to Group Link . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244.1.4 The Add Person to Group Script . . . . . . . . . . . . . . . . . . . . . . . . . . . 244.1.5 Avoiding Multiple Entries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254.1.6 The Group Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254.1.7 Removing a Person from the Session Variable . . . . . . . . . . . . . . . . . . . . 264.1.8 The Remove From Group Script . . . . . . . . . . . . . . . . . . . . . . . . . . . 264.1.9 Making the Group Script Reusable . . . . . . . . . . . . . . . . . . . . . . . . . . 27

4.2 Creating the Project Group and Storing in the Database . . . . . . . . . . . . . . . . . . . 274.2.1 The Create Group Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274.2.2 The View Groups Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294.2.3 Complete Code for View Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . 304.2.4 Creating a Search Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

5 Finishing Touches 335.1 Validation & Customising the Site . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33

5.1.1 Controlling Input . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335.1.2 Using the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335.1.3 Using JavaScript . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345.1.4 Modifying the Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345.1.5 Using Array Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355.1.6 Testing Numeric Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35

5.2 Navigation & Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365.2.1 Adding Navigation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365.2.2 Approaches to Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36

5.3 Graphics & Colour . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365.3.1 Types of Graphic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365.3.2 Using Graphics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365.3.3 Using Colour . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365.3.4 Using JavaScript . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365.3.5 Usability & Accessibility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375.3.6 Site Optimisation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37

A Setting Up the Technology at Home 41A.1 Obtaining the Software . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41A.2 Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41A.3 Using phpMyAdmin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42

B PHP Quick Start 45B.1 Variables in PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45B.2 Loops . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46B.3 Selection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46

B.3.1 if Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46B.3.2 Switch . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47

B.4 Strings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47B.4.1 Literal strings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47B.4.2 Concatenating with variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47B.4.3 String functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48

B.5 Dates & Times . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48B.6 Superglobal Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49

B.6.1 $GLOBALS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49B.6.2 $_SERVER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49B.6.3 $_GET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49B.6.4 $_POST . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49

CONTENTS iii

B.6.5 $_COOKIE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49B.6.6 $_FILES . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49B.6.7 $_ENV . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49B.6.8 $_REQUEST . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49

iv CONTENTS

Preface

Typographic Conventions

In this book the following typographic conventions are used:

• PHP code is shown in teletext typeface

• File names are show in bold teletext

• Commands to be typed at the DOS command prompt are also shown in slanted bold teletexttypeface

• Items that need to be replaced by you (e.g. filenames) are shown between angled 〈brackets〉. (Notethat the brackets should not be included in what you type.)

1

2 CONTENTS

1 Introduction

1.1 Introduction to the Environment

In order to make your site work, you will need to run it from empslocal. You cannot simply run yourfiles from your U: drive.

1.1.1 Testing That It Works

Create a test page using PHP.

Use your text editor, create the following page – call it test.php

1

Save the file into your ISD area on your U: drive.

1.1.2 Uploading & Displaying the Site

In order to do this you will need to open:

a) a web browser

b) the File Manager/Explorer or an MS-DOS command shell.

Copy your files into your web directory on the server. On a windows machine that would be\\secamsmb\people\ug\〈cohort〉\〈userid〉, while on the Linux systems the directory is/secamfs/web/people/ug/〈cohort〉/〈userid〉.

Where 〈userid〉 is your login id and 〈cohort〉 indicates your program of study and the year you enrolled.A two letter code is used to indicate the program of study: cs for Computing, se for ITMB and ma forMaths. Only the last two digits of the year of enrolment are used. Thus a student who enrolled on ITMBin 2009 would appear in the se09 cohort.

These pages are now available under the URL:

http://secamsql.ex.ac.uk/people/ug/〈cohort〉/〈userid〉/

To create your site, you will, of course need to use:

• Some way of editing your code: Any text editor should suffice (i.e. Emacs, TextPad, Kate, etc.).

• A MySQL administrative tool, so that you may create, update and maintain your MySQL databaseefficiently. See Section 1.2 – Database maintenance.

3

4 1. INTRODUCTION

1.1.3 Creating the Site

1. Create your pages with your editor of choice.

2. Save your pages to your U: drive.

3. Copy the files to your web directory.

4. View your pages in your web browser of choice.

5. Test your pages with all available browsers!!

1.1.4 Modifying the Site

1. Make the necessary changes to the file and save it to your U: drive.

2. Copy the new page to your web directory. Note that it never a good idea to edit pages directly in theweb directory. Edit and test them, as much as you can, on your U: before publishing them.

3. Refresh the page in your browser to see and check the amendments.

1.2 Database Maintenance

1.2.1 Connecting to the Database

Using a web browser, login to the MySQL administration tool (phpMyAdmin) using the urlhttp://secamsql.ex.ac.uk/phpmyadmin. It will ask you for a username and password. Thisis not your university login, but your MySQL username and password. Both the username and passwordshould be your university login id. Once you have logged into the database administration tool you shouldsee something along the lines of figure 1.1.

Notice the select (drop down) box in the left hand navigation panel. This contains a list of all the databasesyou currently have access to. There should be two, ‘information_schema’ which is the MySQL’s internaldata structure and 〈userid〉 your own database.

If you are unable to login to the system, or do not have a database, please ensure the supervisor is madeaware of the difficulty.

1.2.2 Changing your password

The system has been configured with your username as the password. As this is obviously less than secureyou are advised to change the password to something only you know. Unfortunately, the phpMyAdmin toolis unable to change the MySQL password, this must be done from with the MySQL command line.

To access the MySQL database from the command line it is necessary to use the mysql program. If youare on a windows machine, use PuTTY to connect to one of the SECaM Linux machines, i.e., one of thebluen machines rather than hashbang. From the Linux command line:

1.2. DATABASE MAINTENANCE 5

Figure 1.1: phpMyAdmin

$ mysql -h secamsql.ex.ac.uk -u 〈userid〉 -pEnter password: 〈password〉Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 182Server version: 5.0.27 Source distribution

Type ’help;’ or ’\h’ for help. Type ’\c’ to clear the buffer.

mysql> set password=password(’〈new password〉’);Query OK, 0 rows affected (0.00 sec)

mysql> quitBye

If you wish to access your database directly from the MySQL command line, you must first select adatabase:

mysql> use 〈database name〉Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A

Database changedmysql>

You may now use the SQL commands you should be familiar with from the Information Systems module.

1.2.3 Creating and Dumping Tables

We expect students to create their tables and populate them in the way that they have been taught in theInformation Systems module, by writing a text file of the necessary SQL and uploading and executing it.

6 1. INTRODUCTION

So, for example, write the following text file and save it with a .sql file extension:

1 drop table if exists person;2

3 create table person (4 pid int not null auto_increment primary key,5 name varchar(50),6 job varchar(50),7 password varchar(20)8 );

Now you need to upload the .sql file that you have created into the phpMyAdmin tool. In a web browser,go to http://secamsql.ex.ac.uk/phpmyadmin and select the ‘Import’ tab. Use the ‘Choose. . . ’button to select your .sql file and then select the ‘Go’ button at the bottom of the page. If the SQL file iscorrect, your will see the two queries in the file both run successfully. You should also see the new table‘person’ appear in the left hand navigation panel. If there are any SQL errors fix them and re-import yourfile.

It is also possible, using phpMyAdmin, to dump to screen all of the SQL necessary to recreate any or alltables in the database. Selecting the ‘Export’ tab will bring up a page with a list of each table withinthe database. Select the tables you wish to export, the format in which you wish to export it (SQL), the‘Structure’ and ‘Data’ checkboxes and then click on the ‘Go’ button at the bottom of the page. It willreturn something like this:

1 CREATE TABLE ‘person‘ (2 ‘pid‘ int(11) NOT NULL auto_increment,3 ‘name‘ varchar(50) default NULL,4 ‘job‘ varchar(50) default NULL,5 ‘password‘ varchar(20) default NULL,6 PRIMARY KEY (‘pid‘)7 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Note the differences between the SQL that was used to create the table and the SQL that the databasereturns.

1.2.4 Adding Data

Amend your .sql file by appending to the end of it a query that will insert a record into the database.Your .sql file should now look something like this:

1 drop table if exists person;2

3 create table person (4 pid int not null auto_increment primary key,5 name varchar(50),6 job varchar(50),7 password varchar(20)8 );9

10 insert into person values (0, ’Fred Bloggs’, ’Graphic Designer’, ’password’);

Again ‘Import’ the file. You should now be able to use the phpMyAdmin administration tool to view therecord in the table.

Extend your .sql file so that it inserts several records into the table, and again view the records in thetable to ensure that the updates worked.

http://secamsql.ex.ac.uk/phpmyadmin

2 Introduction to Dynamic Web Sites

2.1 Introduction to the Scenario

This book will take you through the building of a simple dynamic web site, which will allow users to enter,delete, modify and view details about a person. It will also allow the creation of project groups from theavailable list of people. People can be added to, or removed from groups, and when the people have beenadded the group can be given a name, and stored with the current date. It will also be possible to view allgroups and their members.

2.1.1 The Database

The database can be started with just two tables – person and project. The ERD is given in figure 2.1. Oneperson can be in many projects and each project may have many people working on it; this gives the manyto many relationship shown.

Normalising the database will get rid of the many-to-many relationship, resulting in a new relation whichrecords weather a person is a memeber of a project. The revised design is given in figure 2.2.

2.1.2 Web Site Structure

Let us map out what the site will need to contain and how the pages are linked together. We initially needa log-in page to validate users. Once logged in we will need pages to display all the people, add people,create groups, update existing people records, delete a person and log out. We will also create a file calleddbconn.php which will contain the code to connect to the database — many of the other pages need todo this, and it is far easier to keep this in one place, as well as being good programming practice. Figure2.3 shows the logical structure of the files in the site.

2.2 Creating a Simple Website

The first dynamic page that we will build is a simple page to list all the people that exist in the ‘people’table of the database.

2.2.1 Connecting to the database

As previously mentioned, it is good programming practice to have a separate small file that controls thedatabase connection. Many of the files in the site will need to do this, and by creating this as a separate filemeans that you only need to write or amend the code once for it to take effect site-wide. For instance, youmay be running a test environment on one server and a production environment on another. It would notbe feasible to have both servers given the same name — not even possible if they are on the same network.

7

8 2. INTRODUCTION TO DYNAMIC WEB SITES

Figure 2.1: Initial ERD

Figure 2.2: Revised ERD

pid The person identification number number, primary keyname Name of person varcharjob Current job of the person varcharpassword The password the user needs to access the system varchar

Table 2.1: The person relation

projid The project identification number number, primary keytitle Name of project varcharprojdate The date the project was set up date

Table 2.2: The project relation

pid The person identification number number, foreign key, composite keyprojid The project identification number number, foreign key, composite keystatus The job the person has within the project varchar

Table 2.3: The member relation

2.2. CREATING A SIMPLE WEBSITE 9

loginform.php �

Invalid login?

logincheck.php

?people.php

addpersonform.php-

addperson.php-

logout.php-

creategroup.php-

group.php-

removefromgroup.php-

viewgroups.php-

addtogroup.php-

updatepersonform.php-

updateperson.php-

deleteperson.php-

group.php-

removefromgroup.php-

Figure 2.3: Map of the logical structure of the web site

10 2. INTRODUCTION TO DYNAMIC WEB SITES

By doing it this way, you can simply have both server names in the connection file and simply commentout the one that you are not using. That way, the switch from your testing environment to your productionenvironment is achieved by simply swapping the comment over.

Here is the script that connects to the database – dbconn.php

1

Notes about this script

The script declares and sets the value of a number of variables – $db_password, $db_username,$db_host, $db_name and $connection. Note that all PHP variables are prepended by the $ sign.There is more on this in Appendix B – Programming with PHP.

Remember to replace the items within [] with your own details.

Note also the use of the mysql_connect function. There is also a mysql_pconnect function in PHP– you should study the differences between these two functions. Finally note the use of the . (dot) operatorin the error messages. This operator is the string concatenation operator in PHP. The mysql_error()function will return much more meaningful error messages than simply hard-coding in a text error message.Also note the familiar semicolon terminating each PHP statement.

2.2.2 PHP Superglobals

Read the section in B.6 on PHP superglobals — you will be using several of them.

2.2.3 Retrieving & Displaying Data

Now we can connect to the database, we can create a simple script that will return a web page showing allthe people in the ‘people’ table of the database. Call this people.php.

1 7 9

10 11 12 List All People13 14 15 List All People16

2.2. CREATING A SIMPLE WEBSITE 11

17 18 Name: 19 Job:
20 21 22

Notes about this script

The script starts with the 18 Name: 19 Job:
20

The data in $row is stored as an array. We can access it either by using array subscripts, or by using thecolumn name from the table. So, in our person table $row[1] or $row[’name’] will both access thesame piece of data — the person’s name stored on that particular row. Note that the field names are casesensitive. There is also a performance trade-off to be made here. A lookup using the array subscript is(very slightly) faster than a lookup by column name, but using column names is far more understandableand maintainable.

Notice also in this piece of script how the PHP is switched on and off, the “Name: ” and “Job: ” parts arejust ordinary XHTML text and have been placed outside the PHP. The part isa shorthand way of retrieving the actual data stored in the variable.

It is possible to achieve the same result using just PHP, so:

echo "Name: " . $row[’name’] . " Job: " . $row[’job’] . "
";

The sections of the line are concatenated using the dot . operator.

There is much discussion ‘in the trade’ about which is the ‘correct’ or ‘better’ way to do it. The disadvantageof doing it this way is that in design view in authoring tools you cannot see any of the content when it is alloutput using a PHP echo statement.

12 2. INTRODUCTION TO DYNAMIC WEB SITES

2.2.4 Formatting the Output

We can now improve the way the page looks. The arrangement of Name/Job would be a prime candidatefor tabular data.

Sample output from the script

Amend the PHP code by creating a table in XHTML outsidethe loop, and using and tags to start the tablelayout. Create the subsequent rows holding the actual datainside the loop. Finally write the table closing tag after theloop has terminated.

The amended code:

17 18 19 Name20 Job21 22 25 26 27 28 29 30

2.3 Adding Data to the Database

Now we can create a page that allows us to add a new person into the database. Before we do that we canadd a link to our existing people.php page. Add the following code below the end of table tag:

31 Add new person

2.3.1 Inserting a New Person

Creating a user input form

We need a page like the screenshot for the user to input data.The data will be redirected to the addperson.php scriptusing the HTTP post method.

The form uses normal XHTML input fields and contains noPHP at all, but we will still save it as a PHP file, first forconsistency and second because we may later need to addsome PHP to it.

2.3. ADDING DATA TO THE DATABASE 13

The Code for addpersonform.php

1 3 4 5 6 Add Person7 8 9 Add Person

10 New Person Details11 12

Name:
13

14

Job:
15

16

Password:
17

18

19 20 21

This is a straightforward XHTML form with a single ‘Submit’ button. When the button is clicked, thePOSTDATA will get forwarded to the page referenced in ‘action’.

The Add Person script

This script does the work of adding the details to the database. It has no output, and it uses the PHPfunction of header, which redirects the output to some other page. In this instance it redirects the outputto the people.php page, so that you may see that the person has been added to the database. Theaddperson.php script is:

1

Once again this script needs the dbconn.php file to create a database connection.

Next we create three new variables to hold the values of the POSTDATA sent by the addpersonform.phpfile. We use the PHP superglobal $_POST variable to retrieve the posted values and store them (lines 3–6):

We could use the superglobal $_POST variables directly in the SQL, but it soon becomes untidy doingit that way – it is already very easy to make mistakes with the positioning of the " and ’ quotes withoutincluding even more. Note that this implementation uses 0 for the pid field. Since this is an auto-incrementfield in the database, by doing it this way MySQL will control the auto-incrementing of the field.

Note the way that the quotes are arranged with the SQL statement is concatenated together. We have toensure that those fields that require quotes in SQL are given them.

14 2. INTRODUCTION TO DYNAMIC WEB SITES

Debugging hint: if you have problems with SQL errors and/or your updates don’t work, comment out lines8 to 11 (the lines starting with $result = ... to the exit() line) and insert a line echo $sql;.By doing that the page will display your actual SQL statement and you should be able to identify any errorsin it.

Once the script successfully runs, notice that it redirects your browser back to people.php. This is doneby the header("Location: people.php"); line. The exit(); function causes this script tostop running once the redirection has taken place.

2.3.2 Header Errors

Many pages like this one give no output, but just do database manipulation. There should be no output atall in any page that uses the header() function. If you get a warning such as:

Warning: Cannot add header information – headers already sent . . .

then check that the PHP tag starts at the first character of the first line. If there is any whitespace (even asingle space or blank line) here it will cause this error.

3 Basic Security and Sessions

3.1 Deleting Data from the Database

We will change our original people.php page to allow us to delete a person we choose. We will do thisby including a ‘Delete’ link for each person on the people.php page. This link will contain a HTTPGET query containing the ‘pid’ of the selected person. To do this we need to include the following line ofcode to create an extra column in the table, so:

28

16 3. BASIC SECURITY AND SESSIONS

1

3.2 Updating The Database

The next task of this session is to allow the user to edit (most of) the details of a person.

We do this by creating a link on the person’s name to an updatepersonform.php file:

26 27 28

Note that although the link is on the name, it actually passes back the ‘pid’ to the update form.

Figure 3.2: Revised Update Person Form

Now we need to create the form that the user needs to update a person’s details:

1

3.2. UPDATING THE DATABASE 17

7 if ( mysql_num_rows($result) != 1 ) {8 die("Database did not return one result!");9 } else {

10 $row = mysql_fetch_array($result);11 }12 ?>13 15 16 17 18 Edit Person Details19 20 21 Edit Person Details22 23

PID:
24

18 3. BASIC SECURITY AND SESSIONS

11 or die("MySQL Error: " . mysql_error());12 header("Location: people.php");13 exit();14 ?>

3.3 Logging In

3.3.1 User Authentication

Many web applications, particularly those using encrypted delivery (HTTPS), require users to log on to theapplication. For the purposes of this exercise, we will not be using any security; we are storing passwordsin plain text in our database. This would be a bad idea in a production environment.

You could set up your own password hashing using the built in PHP function crypt(), but achievinggood results is difficult. There is a good discussion of this at the Openwall Project1, together with free,non-copyrighted software that achieves the best results with PHP and isolates you, as a developer, fromthe complexities of hashing algorithms. Note that the topic is password hashing, not password encryption.Encryption is a two-way process allowing you to get back the plain text of what is encrypted. Hashingis deliberately a one-way process; you cannot (or should not be able to) get back to the plaintext that ishashed.

3.3.2 User Login

The first task is to create a user login page loginform.php. It is a simple form with fields for Usernameand Password and a submit button:

1 3 4 5 6 Log In7 8 9 Log In

10 11

Name:
12

13

Password:
14

15

16 17 18

The Login Form

This shows the login form page with one text input, one password inputand a Login button (submit).

Notice again that we have used the HTTP POST method. Also note thatthe form action is to run the logincheck.php script.

1http://www.openwall.com/phpass/

http://www.openwall.com/phpass/

3.4. USING SESSIONS FOR SECURITY 19

3.3.3 Validating the User

Once again, the contents of the logincheck.php script are similar to the ones we have used before:

1

The SQL query is built up as before. The result of a query should return a record — if it fails to do so weknow that there is no matching username and password in the database, and the script is redirected back tothe loginform.php script (lines 11 and 15).

Note that as our database stands at the moment there may be multiple entries for a username/passwordcombination. If you added a UNIQUE constraint to the username field, there would only ever be one recordmatching that combination, and the PHP function mysql_num_rows() function could be used insteadof the existing test to check that a single row is returned. This would, however, preclude us from usingnames in the way we have — it is quite possible to imagine that a large organisation could have two JohnSmiths working for it.

3.3.4 Passing Error Messages

It is good practice to provide user feedback, so in the event of an invalid login redirecting the user to theloginform.php script, some sort of error message such as “Invalid login: please try again” could bedisplayed, possibly in a different font and colour. We leave this for the student to complete as an exercise.

3.4 Using Sessions for Security

We will now introduce another PHP superglobal — the $_SESSION variable. As the site stands now,anyone simply typing in people.php will go directly to that page, without having to log in to the site.We want to prevent this, and there are several mechanisms that we could use. We will use a session variableto control access to our pages.

3.4.1 What Are Sessions

In normal web browsing, each page is a separate entity, the web does not maintain any sort of state frompage to page. We already know that we can pass information from page to page using the HTTP GET andPOST methods. This method could be used, but it is very onerous on the programmer. XHTML formshave to be created on all the pages that pass information, probably using hidden fields. It is all too easy

20 3. BASIC SECURITY AND SESSIONS

to lose track of what information is being passed where. It also increases page sizes, making downloadsslightly slower. We could also use ‘cookies’ – small text files stored in a special area on the client machine,to which the web browser has access. There are security issues connected with cookies, and many usershave cookies turned off. For these reasons we will be using sessions. Values are stored on the server andare associated with a particular client browser.

“A common solution is to exchange a token between a web browser and a web server that uniquelyidentifies the user and the session. Each time a browser requests a resource, it presents the token, andeach time the web responds, it returns the token to the web browser. There are two ways to achieve this:variables that hold the state can be stored in the browser (using cookies) and included with each requestor variables can be stored on the server (using sessions)."

(Williams & Lane 2002)

3.4.2 Starting PHP Sessions

To start a PHP session we must use the session_start() function. This will create a session if noneexists, or maintain a session if one has already been started. To do this the function must be included inall the files. It must be the very first line after the opening

3.4. USING SESSIONS FOR SECURITY 21

23

logged in

24

somewhere suitable in the page layout, this will retrieve the username from the session variable and displayit on the page.

3.4.5 Logging Out

Session variables normally have a finite lifespan — this is determined by the configuration of the webserver. It can vary enormously; typical figures can be between five minutes and two days!

We need a way of allowing the user to explicitly log out, so amend people.php again to include a buttonor link to a logout script. Here is the code for a button:

25

27

The actual logout.php script would be:

1

22 3. BASIC SECURITY AND SESSIONS

4 Project Groups & Site Search

4.1 Setting Up a Project Group

4.1.1 Using Sessions to Set Up a Project Group

We need to be able to create project groups, which will consist of various numbers of the people from our‘person’ table. To do this we need to be able to add them to a group, create and save the group, and give ita name.

First alter the layout of the people.php page so that it looks like figure 4.1.

Figure 4.1: List All People with Project Groups

The page layout has been changed so that it now consists of four s – a top one for the header andthe ‘logged in’ text, and three columnar s below that. The navigation links have been moved intothe left column, our existing tabular data (with its new column of links) is in the centre, and the ProjectGroups will occupy the right hand column.

This is all achieved properly, by the inclusion of some CSS style into the head of the page:

16 17 18 List All People19 20 #top { clear: left; }

23

24 4. PROJECT GROUPS & SITE SEARCH

21 #left { max-width: 20%; float: left; padding: 5px 5px 5px 5px; }22 #mid { max-width: 50%; float: left; padding: 5px 5px 5px 5px; }23 #right { max-width: 30%; float: left; padding: 5px 5px 5px 5px; }24 25

4.1.2 Formatting the Page

In order to make these changes we need to do the following:

1. Put the plus sign into the tableThis allows us to click on the [+] and the person will be added into the project group.

2. Create the addtogroup.php scriptThis is the script called by the [+] link. It will add a person array into a session variable, so it willremain stored for as long as the session is alive. We can then save the group to the database once wehave chosen all the people in the group.

3. Create the creategroup.php scriptThis script is used to display the contents of the session variable – i.e., it displays the currentlyselected group of people. It allows us to save the group into the database in the project and memberstables.

4. Create the removefromgroup.php scriptThe allows us to remove a person from the group.

4.1.3 The Add Person to Group Link

We do this by adding another column to our table, so we will need to add another set of . . .tags within the ‘while’ loop of people.php. Then we need to create the link in a similar fashion to thedelete link. In this case we will pass ‘pid’, ‘name’ and ‘job’ to the script, as we are going to store these inan array in the session variable.

The code to be added looks like this:

4.1. SETTING UP A PROJECT GROUP 25

7 $person[’name’] = $_GET[’name’];8 $person[’job’] = $_GET[’job’];9

10 $_SESSION[’group’] [] = $person;11 header("Location: people.php");12 exit();13 ?>

As with all the other scripts, we maintain the session on line 2. Next we create an array on line 5, thisallows us to store the ‘person’ details in one variable. Lines 6–8 add the data passed to the script as partof the URI, so we can use the $_GET superglobal on it. For consistency we will store the data with thesame names. Once we have created the person array and added the data we store the array into a sessionvariable (which is itself an array of $person), we call this session variable ‘group’ (line 10). Finally, line11, redirects the script to the people.php page.

4.1.5 Avoiding Multiple Entries

You may have noticed that it is possible to add a person multiple times to a group. We need to prevent thisfrom happening. Modify addtogroup.php so that it tests to see if the person is already in the sessionvariable. Only add them if they are not already there:

10 $found = false;11

12 if ( isset($_SESSION[’group’]) ) {13 foreach ( $_SESSION[’group’] as $key => $another ) {14 if ( $_SESSION[’group’][$key][’pid’] == $_GET[’pid’] ) {15 $found = true;16 break;17 }18 }19 }20

21 if ( $found == false ) {22 $_SESSION[’group’] [] = $person;23 }

First we create a variable $found which we initialise to ‘false’, therefore using it as a Boolean value. Thiswill control whether the person has been found in the session variable or not.

Next we need to loop through the whole array using a ‘foreach’ loop (line 13) that will look at every elementof the array. Note that we use a different variable name $another, as we already have a $personvariable in the script.

Inside the loop, line 14 checks each person already stored to see if they match the ‘pid’ from the GETrequest. If there is a match, we set $found to true and since we do not need to check further, break out ofthe ‘foreach’ loop.

If we get to line 21 and $found is still false, then we add them to the session variable as before.

4.1.6 The Group Script

We will create a separate script for displaying the current members of the session variable. We do this aswe will wish to reuse this script later on. It is based loosely on the addtogroup.php script, but alsocontains the necessary XHTML to display the group members in tabular form. We then require the scriptin the right hand of the people.php script, headed “Project Members”.

26 4. PROJECT GROUPS & SITE SEARCH

26 36 37 38 Name39 Job40  41 42 45 46 47 48

4.2. CREATING THE PROJECT GROUP AND STORING IN THE DATABASE 27

10 unset($_SESSION[’group’][$key]);11 break;12 }13 }14 header("Location: people.php");15 exit();16 ?>

We loop through the session variable array, and if we find that one of the entries in the array matches the‘pid’ we use the unset() PHP function to remove the whole person from the array.

4.1.9 Making the Group Script Reusable

We want to use the group.php script on several pages rather than rewriting the code to display the groupon every page, however this leaves us with a problem:

The group page calls the removefromgroup script when [-] is clicked on. At the momentthis redirects the user to the people page. The problem is – if we reuse the script in anotherpage, how does it know where the user should be redirected to?

Fortunately, there is another PHP superglobal that comes to our aid, the $_SERVER variable.$_SERVER[’PHP_SELF’] holds the name of the current script that is running. We can use this tomodify the group.php script to pass over the location of the current page, as well as the ‘pid’ to bedeleted. Make the line look like this:

44

28 4. PROJECT GROUPS & SITE SEARCH

Figure 4.2: Create Group Form

1

Like all the other scripts we start the session, check that the user is logged in and use the dbconn.phpfile to connect to the database.

We then retrieve the two values from the form sent by the HTTP GET method in lines 8 and 9. Using thisdata lines 11-12 creates an SQL query, and using the 0 which is ignored in the auto-increment ‘projid’ fieldline 13 creates a record in the ‘project’ table.

Now we need to put a row into the ‘member’ table for each person selected to be on the project. We havethe ‘pid’s of these people as they are stored in the ‘group’ session variable, but what is the ‘projid’ that weneed to use for this? We used a zero in the query because ‘projid’ is an auto-incrementing field, but whatdid MySQL actually put into the field? Fortunately there is a MySQL function that will supply us with thisinformation:

$projid = mysql_insert_id($connection);

4.2. CREATING THE PROJECT GROUP AND STORING IN THE DATABASE 29

Figure 4.3: View Groups

This function returns the integer used my MySQL for our specific connection ($connection), whichis why we pass it as a parameter to the function. If we did not do this (i.e., we left the brackets empty)MySQL will return the last integer used by any connection — which could well be someone else!

Using the projid, lines 17–23 loops through all the people on the project and insert them into the ‘member’table. Finally, on line 24, we empty the session variable — we have written it to the database, so we nolonger need to keep it. We use the unset() function to delete the session variable, while line 25 redirectsthe user to a viewgroups page which will display all groups created so far.

4.2.2 The View Groups Script

We want to be able to view all project groups created so far, and the last link on the people.php alsolinks to this page. We are simply creating a page that selects all the projects from the database and displaystheir title and the details of the people assigned to the project (figure 4.3).

The page is quite similar to the final people.php page, but the tricky part is that it must contain nested‘while’ loops. The outer loop gets the title of the project, and the inner loop populates the other cell ofthe table with the details of the people on that project. This means that we have to use two record setssimultaneously.

The first query selects the projects:

8 $sql = "SELECT * FROM project ORDER BY title";9 $result = mysql_query($sql, $connection)

10 or die("MySQL Error: " . mysql_error())

The outer loop iterates through this record set:

29

The second query selects only those people who are working on that project:

34 $sql2 = "SELECT * FROM person, member"35 . " WHERE person.pid = member.pid"36 . " AND projid=" . $row[’projid’];37 $result2 = mysql_query($sql2, $connection)38 or die("MySQL Error: " . mysql_error()

30 4. PROJECT GROUPS & SITE SEARCH

The the inner loop iterates through the second result set listing the member’s details:

40 while ( $row2 = mysql_fetch_array($result2) ) {

Note also the containing ‘if-else’ statement that checks whether there actually are any projects.

4.2.3 Complete Code for View Groups

The complete code for the viewgroups.php script:

1 12 14 15 16 17 Existing Project Details18 19 20 Existing Project Details21 22 No projects exist at this time23 24 25 26 Project Group Title27 Group Members28 29 30 31 32 33 42 43 44 45 46 47 48

4.2. CREATING THE PROJECT GROUP AND STORING IN THE DATABASE 31

49 50 51 52 53

4.2.4 Creating a Search Page

As an exercise, create a search page that will have a single input for user text, and a drop down box of thefield(s) to be searched — name, job, project title. When the user clicks the ‘Search’ button the page shouldsearch the database for relevant matching entries. Have the script call itself, so that it returns to the samepage and lists any matches found.

Hints:

1. Use the SQL LIKE ’%...%’ statement.

2. Explore the use of case changing functions in PHP.

3. Explore the SQL case functions.

32 4. PROJECT GROUPS & SITE SEARCH

5 Finishing Touches

5.1 Validation & Customising the Site

5.1.1 Controlling Input

Whenever possible the site should either validate user input or offer only a set range of inputs for the userto choose from. A good example of the latter is the use of drop-down lists for user choice. In our examplewe could limit the choice of job and eliminate the possibility of e.g., spelling mistakes by this method. Editthe addpersonform.php script and change the ‘job’ text field to a ‘’. Leave its name asjob, so that nothing else needs to be altered:

Job:

[Please Select]AnalystProgrammerWeb DeveloperGraphic DesignerDatabase Developer

The first option will be the default unless you use the “selected” attribute of .

5.1.2 Using the Database

This method certainly works, but what if we need to display this set of options on a number of pages? If weneed to add a job, we will need to make that change on all those pages, with the increased risk of missingone, using different spellings, etc. Instead of hard coding in this way, create a new table in the databasecalled ‘job’.

There are some issues to consider here also. We could store the job titles with an identifying id (‘jobid’),and store the ‘jobid’ against the people in the ‘person’ table. This would mean writing an extra query,and using it wherever we want to display a person’s job. Alternatively, we can just choose to have asingle column ‘jobtitle’ in the table. This is a balancing act of storage requirements against applicationcomplexity. If storage size is an issue, doing it the first way will reduce the data stored for each personfrom a varchar(50) to an int.

For our purposes we will go with the guiding light of KISS (keep it simple, stupid) and use the secondsuggestion – a single column of data, so our table will just have one varchar(50). Populate the tablewith some suitable data, and modify addpersonform.php so that it queries that table:

1

34 5. FINISHING TOUCHES

4 $result = mysql_query($sql, $connection)5 or die("MySQL Error: " . mysql_error());6 ?>

and build a loop that creates the s for the list box:

14

Job:
15 16 [Please Select]17 18 19 20 21

This will create the start of the list as usual, and then the ‘while’ loop in PHP will populate the rest of thelist with the entries from the database table.

Consider also what happens in the updatepersonform.php script. This allows the user to change thejob of an existing person, so we will need to display the existing job of the person in the list, not the defaultoption. Change the updatepersonform.php script like this:

12 $sql2 = "SELECT * FROM job ORDER BY jobtitle";13 $result2 = mysql_query($sql2, $connection)14 or die("MySQL Error: " . mysql_error());

Notice that this is $sql2 and $result2, as we already have a record set on this page. Now build the listas before, but checking against the person’s existing job:

27

Job:
28 29 [Please Select]30 37 38

This creates the list as before, but the person’s existing job will be displayed in the list box.

5.1.3 Using JavaScript

Use JavaScript to validate the rest of the inputs on the forms for this site. Note that the Firefox and Operaweb browsers come with rather good JavaScript consoles that will display any errors or warnings. Accessthem via the Tools menu.

5.1.4 Modifying the Forms

Validate loginform.php, addpersonform.php, updatepersonform.php andcreategroupform.php in this way. It is necessary to check updatepersonform.php becausethe user could easily have cleared the existing information in a text field and left it blank when attemptingto do the update.

5.1. VALIDATION & CUSTOMISING THE SITE 35

5.1.5 Using Array Indices

In the document object model the elements of a form may be accessed as an array, so:

document.addperson.elements[0]

will refer to the ‘name’ input field on the addperson form.

Similarly, the s of a field can be referenced by array subscript, so in our exampleabove [Please Select] can be referenced as:

document.addperson.job.selectedIndex[0]

This gives us a very simple method of checking whether a job has been selected, or whether the default[Please Select] is still selected. It is possible to add a ‘value’ attribute to theoptions, and to retrieve this by using (e.g.):

document.addperson.job.selectedIndex[document.addperson.job.selectedIndex].value

This is rather messy, and so long as the default is the option at array index zero, it can be tested for inJavaScript like this:

if ( document.addperson.job.selectedIndex == 0 ) {errmsg = errmsg + "Please select a job";

}

5.1.6 Testing Numeric Data

Another example of validation may be to use the isNaN (is not a number) function of JavaScript. Thisis useful where the input is a text field but we are expecting numeric input from the user. This functionreturns true if the user has not entered a valid number and false if they have entered a valid number. Forexample:

if ( isNaN(document.formname.age.value) ) {alert("Age must be a valid number.");

}

Other examples of where we may wish to validate user entry are dates or credit card numbers. With datesit is possible to constrain users by using separate lists for day, month and year – this also has the advantagethat you can be certain to store a valid date into your MySQL database. Read the section on dates in themySQL manual, as MySQL is rather forgiving of invalidly formatted dates and may give rise to unexpectedresults if incorrectly handled. An example of date handling could be:

$date = $_GET[’year’] . "-" . $_GET[’month’] . "-" . $_GET[’day’];$sql = "UPDATE project SET date = ’" . $date . "’ WHERE projid = " . $_GET[’projid’];

This works regardless of how the form is laid out with respect to locale and the differences of how UK andAmerican sites represent dates.

Credit cards could be input either as a single string of sixteen numeric characters, or in four fields each fourdigits long, separated by hyphens as on the actual card.

36 5. FINISHING TOUCHES

5.2 Navigation & Layout

5.2.1 Adding Navigation

At the moment the site contains little consistent navigation. We leave it as an exercise for the student todecide upon how the navigation of the site should work. Is the user expected to navigate the site in a setorder, or will navigation be positioned on every page? Are you going to put the links down the side oracross the top? Will you provide a breadcrumb trail?

5.2.2 Approaches to Layout

Recall the four main methods of page layout:

1. Use nested tables, although still used, it is regarded as a ‘bad thing’.

2. Use frames – again, frames are rather out of favour at the moment.

3. Use CSS and tags. Generally regarded as the ‘right’ way to do it, but hampered by browsersfailure to properly support CSS.

4. Just bung it on the page and hope the browser makes a realistic job of rendering the page. If youcan position your elements using just XHTML this is likely to achieve the greatest cross-browsercompatibility.

5.3 Graphics & Colour

5.3.1 Types of Graphic

The three main ‘web-safe’ image formats are .jpg, .gif and .png. Each type has its advantages anddisadvantages with which you should be familiar.

5.3.2 Using Graphics

Always remember that excessive use of graphics will slow down the rendering of your pages. Do thegraphics really enhance the user’s experience. Do not forget that consistent studies have shown that userswill be put off your site if the pages take any longer than three seconds to display the main content. Alsobear in mind the copyright issues with images – do you really have permission to use the graphic on yoursite?

5.3.3 Using Colour

The web developer may find themselves constrained in the use of colour on their pages. It may well be thatthe site has to fit in with a corporate identity, but always remember that the busiest and most well-used siteson the Internet almost invariably use the system default colours, normally black text on a white background,for textual content.

5.3.4 Using JavaScript

Avoid superfluous use of JavaScript – is that clock or news-ticker really necessary? JavaScript is a veryuseful tool when used for, e.g., validation or controlling rollover images that give user feedback.

5.3. GRAPHICS & COLOUR 37

5.3.5 Usability & Accessibility

Web page usability can be a difficult issue – have a look at

http://www.webpagesthatsuck.com

for some guidelines as to what not to do. You should also be aware, as a professional, of current andupcoming requirements in the field of accessibility.

5.3.6 Site Optimisation

SEO (Search Engine Optimisation) is a very thorny subject. It is a constant battle between webmasters andthose who write the search engine algorithms for the major search engines. In particular Google is veryunforgiving of sites that either knowingly or unknowingly break their ever changing rules. If Google detectyou breaking the rules, your site will disappear from a decent page ranking for ever, even if you correct thedefects that caused it to break the rules in the first place. It is worth having a read up on the subject at

http://searchenginewatch.com/webmasters/

http://www.webpagesthatsuck.comhttp://searchenginewatch.com/webmasters/

38 5. FINISHING TOUCHES

Appendices

39

A Setting Up the Technology at Home

A.1 Obtaining the Software

Visit http://www.appservnetwork.com/ and download the latest version of AppServe (at thetime of writing v2.5.10). Choose the Kent, UK download mirror — it is the nearest. This software provides:

• Apache v2.2.8

• MySQL 5.0.51b

• PHP 5.2.6

• phpMyAdmin-2.10.3

A.2 Installation

Run the installer software, after the initial welcome screen you should be prompted where you want thesoftware installed.

• Leave the default Destination Directory as C:\AppServ and click Next.

• Leave the Typical option checked and click Next.

• As we are only using this server software to host our development website we can leave all thesesetting at their default.

• You need to set the username and password with which you will connect to the MySQL database. Itwould make sense to use the same username and password that you have been using in the workshops.

• Once the software has finished installing have a look in the AppServ directory on your C: drive.The www directory is where your website should live. This directory is what is published via http://localhost/

Create a new file test.php (or use the one from Uni that you prepared in the workshops):

and save this file into the ‘www’ directory.

Open a browser and go to location http://localhost/test.php

You should see a page similar the one shown in figure A.1 (this is from an earlier version of php):

41

http://www.appservnetwork.com/http://localhost/http://localhost/http://localhost/test.php

42 A. SETTING UP THE TECHNOLOGY AT HOME

Figure A.1: PHP Test Page

A.3 Using phpMyAdmin

The AppServe setup will have installed the phpMyAdmin software. Make sure you have the MySQL Serversoftware running. You can start it from Start Menu ⇒ Programs ⇒ MySql ⇒ MySql Administrator, andyou can also start up a system tray icon from Start Menu ⇒ Programs ⇒ MySql ⇒ MySql System TrayMonitor. This icon will show a small green light while ever you have a connection to the database running.

Once you have MySQL up and running, launch a browser and enter the URL http://localhost/phpmyadmin. This will launch the database administration software show in figure A.2.

Note: There is a master database account with a username of root and no password which is setup bydefault.

Once you are connected you will see there are three databases already setup. One called MySQL, one calledTest and one called information_schema. You can see from the screenshot that I have alreadycreated another database, called “steve”.

To create your own database enter its name in the Create New Database text field. (Note: Don’t use specialcharacters or spaces in your name) It is recommended that you name your database the same as the oneyou have been allocated in your workshops.

Once you have created your database, select it, and from the create new table page (figure A.3. You cansee from the figure that table ’test’ is about to be created with two fields.

http://localhost/phpmyadminhttp://localhost/phpmyadmin

A.3. USING PHPMYADMIN 43

Figure A.2: PHP My Admin

Figure A.3: PHP My Admin: New Table Page

44 A. SETTING UP THE TECHNOLOGY AT HOME

B PHP Quick Start

B.1 Variables in PHP

All PHP variables start with a dollar sign ($) followed immediately by the variable name. PHP is untyped— that means that any variable can hold any data type at any time, simply by assigning a value of a differenttype to it. Variable names are cAsE SeNSitiVe!!

PHP has four simple data types — called scalars: boolean, integer, float and string.

PHP has two compound data types: array and object.

The assignment operator is = just as in Java. So:

$myvar = "Steve" $myvar is now a string containing the value “Steve”$myvar = 365 $myvar is now an integer containing the value 365$myvar = 0.175 $myvar is now a floating point number containing the value 0.175$myvar = true $myvar is now a boolean containing the value true

Variables in general behave as you would expect them to behave, e.g., numeric variables can be used toperform arithmetical operations, etc.

$number = $number + 10;$grossprice = $netprice + $vatamount;$number++; // increments by one

The syntax of creating an array is only slightly different from that in Java — ordinary brackets ( ) areused for predefining arrays rather than braces { } e.g:

$numarray = ( 1, 2, 3, 4, 5 );$namesarray = ( "fred", "bill", "jim" );

To debug use the print_r() and var_dump() functions which print the type and value of a variable orexpression in human readable format, e.g:

$num = 15;var_dump($num);

will output “int(15)”

During the running of a PHP script a variable may be undefined (variables may be defined at any point), orit may be unset. PHP provides isset() and empty() functions to test the state of a variable:

$var = "test";if ( isset($var) ) echo "Variable is set";if ( empty($var) ) echo "Variable is empty";

A variable can be explicitly destroyed using unset()

unset($var);

45

46 B. PHP QUICK START

B.2 Loops

The construction and syntax of all the PHP loops will be very familiar from Java.

For Loopfor ($count = 0; $count < 10; $count++) {

// loop body}

While Loop$count = 0;while ($count < 10) {

// loop body$count++;

}

Do-While Loop$count = 0;do {

// loop body$count++;

} while ($count < 10);

Foreach LoopThis is the only looping construct which uses a different syntax to Java. It iterates though an array,executing the loop body for each value in the array.

foreach ( $array as $item ) {// loop body// $item is each item of the array in turn

}

The foreach loop can also step though an associate array:

foreach ( $array as $key => $value ) {// loop body// $key and $value are the next value pair from the associate array

}

B.3 Selection

B.3.1 if Statements

The if statement tests whether a condition is true, and if so, executes one or more statements:

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

}

The if statement can have an else statement:

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

} else {echo "The variable is less than or equal to 5";

}

B.4. STRINGS 47

There can also be multiple ifs:

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

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

} else {echo "The variable is less than or equal to 5";

}

You can check equality (equal to): if ( $var == 1 )You can check inequality (not equal to): if ( $var != 1 )You can check multiple conditions using OR: if ( ($var == 3) || ($var == 7) )using AND: if ( ($var > 0) && ($var < 10) )

B.3.2 Switch

The switch statement can be used as an alternative to an if to select from a list of choices:

switch ( $menu ) {case 1:

echo "You picked one";break;

case 2:echo "You picked two";break;

case 3:echo "You picked three";break;

default:echo "You didnt pick one, two or three";

}

This example can be implemented with if and else if statements, but the switch method is usually morecompact, readable and efficient to type. The break statements stops execution passing to the next case andso stops checking the $menu variable.

B.4 Strings

B.4.1 Literal strings

Enclosing characters in double quotes can create a string literal. e.g.,

$name = "Fred Smith";

You can put escape sequence inside a literal for tasks such as: tab ( \t ), new line ( \n ), a backslash ( \\ ),or a dollar sign ( \$ ).

echo "\t Hello \n";

B.4.2 Concatenating with variables

You can concatenate literal strings with variables using the dot ( . ) e.g.

echo "Here is the contents of the " . $name . " name variable";

48 B. PHP QUICK START

B.4.3 String functions

printf() Sometimes more complex output is required than can be produced with echo. For example, ifyou want to format a floating-point number such as 3.14159 to 3.14 you can use the printf()function.

printf("Result: %0.2f \n", $variable);

The % begins the conversion specification. The f tells it to output a floating point number, whilethe 0.2 indicates the format of the number. In this instance, the 0 indicates to output a leadingzero, and the .2 means display only two places after the decimal point.

Once the format string is given, the variables to be formatted are listed. In this case we onlyhave one, but there must be the same number of variables as formats in the format string.

strtoupper()To change all the letters to upper case:

$var = "Hello";echo strtoupper($var);

will output “HELLO”.

strtolower()To change all letters to lower case, thus:

echo strtolower($var);

would output “hello”.

strlen() To find out the length of a string:

echo "The string \"$var\" is " . strlen($var) . " characters long";

substr() Returns a substring from a source string, starting position is 0:

$var = "abcdefgh";echo substr($var, 2); // cdefgh from the third letterecho substr($var, 2, 3); // cde from the third letter for three lettersecho substr($var, -1); // h counting from the end of the string

explode() and implode()Converts strings to an array, breaking the string on a specified separator:

$guestlist = "Sam Meg Sarah Ben Jess May Adam";$guestArray = explode (" " , $guestlist);sort($guestArray);$guestlist2 = implode("," , $guestArray);

So we can take a string, a list of names separated by a space, turn it into an array using theexplode function. We can then use the sort function to sort the names and then we can put itback into a single string, with a comma as a delimiter, using the implode function.

B.5 Dates & Times

PHP has a number of functions that work with dates and times. Use the on-line manual at http://www.php.net to obtain the information.

http://www.php.nethttp://www.php.net

B.6. SUPERGLOBAL VARIABLES 49

B.6 Superglobal Variables

B.6.1 $GLOBALS

Contains a reference to every variable which is currently available within the global scope of the script.The keys of this array are the names of the global variables. Note the lack of the underscore at the start ofthe variable name.

B.6.2 $_SERVER

Variables set by the web server or otherwise directly related to the execution environment of the currentscript.

B.6.3 $_GET

Variables provided to the script via URL query string.

B.6.4 $_POST

Variables provided to the script via HTTP POST.

B.6.5 $_COOKIE

Variables provided to the script via HTTP cookies.

B.6.6 $_FILES

Variables provided to the script via HTTP post file uploads. See POST method uploads for more information.

B.6.7 $_ENV

Variables provided to the script via operating system environment.

B.6.8 $_REQUEST

Variables provided to the script via the GET, POST, and COOKIE input mechanisms, and which thereforecannot be trusted. The presence and order of variable inclusion in this array is controlled by a configurationvariable. As such the exact nature of the variables in this array can not be trusted. We include it here asyou may see reference to it in other texts, but we advice to keep away from this array.

IntroductionIntroduction to the EnvironmentTesting That It WorksUploading & Displaying the SiteCreating the SiteModifying the Site

Database MaintenanceConnecting to the DatabaseChanging your passwordCreating and Dumping TablesAdding Data

Introduction to Dynamic Web SitesIntroduction to the ScenarioThe DatabaseWeb Site Structure

Creating a Simple WebsiteConnecting to the databasePHP SuperglobalsRetrieving & Displaying DataFormatting the Output

Adding Data to the DatabaseInserting a New PersonHeader Errors

Basic Security and SessionsDeleting Data from the DatabaseUpdating The DatabaseLogging InUser AuthenticationUser LoginValidating the UserPassing Error Messages

Using Sessions for SecurityWhat Are SessionsStarting PHP SessionsControlling User AccessUsing Session VariablesLogging Out

Project Groups & Site SearchSetting Up a Project GroupUsing Sessions to Set Up a Project GroupFormatting the PageThe Add Person to Group LinkThe Add Person to Group ScriptAvoiding Multiple EntriesThe Group ScriptRemoving a Person from the Session VariableThe Remove From Group ScriptMaking the Group Script Reusable

Creating the Project Group and Storing in the DatabaseThe Create Group ScriptThe View Groups ScriptComplete Code for View GroupsCreating a Search Page

Finishing TouchesValidation & Customising the SiteControlling InputUsing the DatabaseUsing JavaScriptModifying the FormsUsing Array IndicesTesting Numeric Data

Navigation & LayoutAdding NavigationApproaches to Layout

Graphics & ColourTypes of GraphicUsing GraphicsUsing ColourUsing JavaScriptUsability & AccessibilitySite Optimisation

Setting Up the Technology at HomeObtaining the SoftwareInstallationUsing phpMyAdmin

PHP Quick StartVariables in PHPLoopsSelectionif StatementsSwitch

StringsLiteral stringsConcatenating with variablesString functions

Dates & TimesSuperglobal Variables$GLOBALS$_SERVER$_GET$_POST$_COOKIE$_FILES$_ENV$_REQUEST