COMP284 Practical 5
PHP (2)

Introduction

• This worksheet contains further exercises that are intended to familiarise you with PHP Pro-
gramming. In particular, we will consider the use of the PHP Data Objects (PDO) extension
for accessing databases that is independent of the specific DBMS that is used. This is of-
ten preferable over DBMS specific approaches, like the MySQLi extension to access MySQL
databases, as it improves the portability of your code.

While you work through the exercises below compare your results with those of your fellow
students and ask for help and comments if required.

• This document can be found at

http://cgi.csc.liv.ac.uk/~ullrich/COMP284/notes/practical05.pdf

and you might proceed more quickly if you cut-and-paste code from that PDF file. Note that
a cut-and-paste operation may introduce extra spaces into your code. It is important that
those are removed and that your code exactly matches that shown in this worksheet.

• The exercises and instructions in this worksheet assume that you use the Department’s Linux
systems to experiment with PHP.

If you want to use the Department’s Windows systems instead, then you can do so.

• To keep things simple, we will just use a text editor, a terminal, and a web browser. You can
use whatever text editor and web browser you are most familiar or comfortable with.

• If you do not manage to get through all the exercises during this practical session, please
complete them in your own time before the next practical takes place.

Exercises

1. Let us start with a number of task relating to the use of MySQL. It is assumed that COMP102
Introduction to Databases (or equivalent) has already familiarised you with MySQL.

a. Open a terminal and connect to the MySQL server using the command

mysql -h mysql

where should be replaced by your departmental user name ( indicates
which database you want to use).
If successful, you should see the following output in the terminal, and you are now
interacting with our MySQL server using its command line interface:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2535214

Server version: 5.5.38 MySQL Community Server (GPL) by Remi

Oracle is a registered trademark of Oracle Corporation and/or its

http://cgi.csc.liv.ac.uk/~ullrich/COMP284/notes/practical05.pdf

affiliates. Other names may be trademarks of their respective owners.

Type ’help;’ or ’\h’ for help. Type ’\c’ to clear the current input statement.

mysql>

b. By default, your database is not password protected, meaning that anyone can access it.
To set a password for your database, use the following command at the MySQL prompt:

set password=password(’’);

where should be replaced by a password chosen by yourself. Note that
you must be able to remember that password. It should also not coincide with your
departmental or MWS passwords.
If successful, you will see the output

Query OK, 0 rows affected (0.00 sec)

c. We should now test whether your password is working. First, disconnect your connec-
tion to the MySQL server by using the command

quit;

You are now back at the shell command prompt and should reconnect to the MySQL
server using one of the following two commands:

mysql -h mysql -p

mysql -h mysql -u -p

where should be replaced by your departmental user name (the option -u
specifies the MySQL user id that you want to use when connecting to the MySQL server,
it defaults to your departmental user name).
You should now be asked to enter a password:

Enter password:

After entering the password that you have specified in Exercise 1b you should be back
at the MySQL prompt.

d. Let us create our first database table in MySQL using the following command at the
MySQL prompt:

create table meetings(slot INT NOT NULL, name VARCHAR(50),

email VARCHAR(50), primary key(slot));

e. If the command in Exercise 1d was successful, then

describe meetings;

should produce the following output

+——-+————-+——+—–+———+——-+

+——-+————-+——+—–+———+——-+

| slot | int (11) | NO | PRI | NULL | |

+——-+————-+——+—–+———+——-+

3 rows in set (0.00 sec)

f. We now want to fill the meetings table with some data

slot name email
1 Michael North M.North@student.liverpool.ac.uk
5 Jody Land J.Land@student.liverpool.ac.uk
7 Trish Shelby T.Shelby@student.liverpool.ac.uk
11 Wayne Ungood W.Ungood@student.liverpool.ac.uk

• Devise four INSERT-statements that put the data shown at the bottom of the previous
page into the meetings table and execute all four.
Hint: Refer to http://dev.mysql.com/doc/refman/5.5/en/insert.html if you have
forgotten the syntax of INSERT-statements.

• Check that the data has been inserted correctly by using

select * from meetings;

The output should be

+——+—————+———————————-+

| slot | name | email |

+——+—————+———————————-+

| 1 | Michael North | M.North@student.liverpool.ac.uk |

| 5 | Jody Land | J.Land@student.liverpool.ac.uk |

| 7 | Trish Shelby | T.Shelby@student.liverpool.ac.uk |

| 11 | Wayne Ungood | W.Ungood@student.liverpool.ac.uk |

+——+—————+———————————-+

4 rows in set (0.00 sec)

• Devise a SELECT-statement that just returns the values stored for the slot attribute
in the meetings table and execute it.
Hint: Refer to http://dev.mysql.com/doc/refman/5.5/en/select.html for infor-
mation on SELECT-statements.

• Devise a DELETE-statement that removes the database entry for slot 11 and execute
it.
Hint: DELETE-statements are described at
http://dev.mysql.com/doc/refman/5.5/en/delete.html.

• Check that the deletion in the previous step has been successful.

g. Disconnect from the MySQL server by using the command

quit;

2. Let us try to connect to our MySQL database using PHP.

a. Open a text editor and enter the following HTML markup and PHP code:

Practical 5: PHP and Databases

“;
$db_username = ““;
$db_password = ““;
$db_charset = “utf8mb4”;

$dsn = “mysql:host=$db_hostname;dbname=$db_database;charset=$db_charset”;
$opt = array(

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,

PDO::ATTR_EMULATE_PREPARES => false

);

try {

$pdo = new PDO($dsn,$db_username,$db_password,$opt);

// Code for 4c here

// Code for 4d here

// Code for 5a here

echo “

Data in meeting table (While loop)

\n”;

$stmt = $pdo->query(“select * from meetings”);
echo “Rows retrieved: “.$stmt->rowcount().”

\n”;
while ($row = $stmt->fetch()) {

echo “Slot: “,$row[“slot”], “
\n”;
echo “Name: “,$row[“name”], “
\n”;
echo “Email: “,$row[“email”],”

\n”;

}

echo “

Data in meeting table (Foreach loop)

\n”;

$stmt = $pdo->query(“select * from meetings”);
foreach($stmt as $row) {

echo “Slot: “,$row[“slot”], “
\n”;
echo “Name: “,$row[“name”], “
\n”;
echo “Email: “,$row[“email”],”

\n”;

}

$pdo = NULL;
} catch (PDOException $e) {

exit(“PDO Error: “.$e->getMessage().”
“);
}

Replace both occurrences of with your departmental user name and replace with the password you have chosen for your database.

b. Use the documentation at http://php.net/manual/en/pdo.connections.php to un-
derstand what the various PDO-functions in the code do.

c. Save the code to a file name php05A.php in $HOME/public_html/. Make sure that
nobody but you has read access for the file by using

chmod og-rwx ∼/public_html/php05A.php

You should only have to do so once. File permissions should not change while you
continue to edit the file.

http://php.net/manual/en/pdo.connections.php

d. Execute the PHP script in the terminal using the command

php ∼/public_html/php05A.php

Check that there are no syntax error and that the script produces the output

Practical 19

Practical 19A

Data in meeting table (While loop)

Rows retrieved: 3

Slot: 1

Name: Michael North

Email: M.North@student.liverpool.ac.uk

Slot: 5

Name: Jody Land

Email: J.Land@student.liverpool.ac.uk

Slot: 7

Name: Trish Shelby

Email: T.Shelby@student.liverpool.ac.uk

Data in meeting table (Foreach loop)

Rows retrieved: 3

Slot: 1

Name: Michael North

Email: M.North@student.liverpool.ac.uk

Slot: 5

Name: Jody Land

Email: J.Land@student.liverpool.ac.uk

Slot: 7

Name: Trish Shelby

Email: T.Shelby@student.liverpool.ac.uk

e. Open a web browser and access the URL

http://cgi.csc.liv.ac.uk/∼/php05A.php

where should be replaced by your departmental user name.
Make sure that the web page you are shown corresponds to the HTML code you have
seen in Exercise 2d.

f. It would be nice if the database data would be presented in the form of a HTML table
with three columns called ‘Slot’, ‘Name’ and ‘Email’. Change the code of your PHP script
so that two such tables are produced. Also, modify the query so that entries in the tables
will be ordered by slot number.

3. We now want to add some interactivity to our web application.

a. Add the following code to php05A.php just before the statement $pdo = NULL.

echo<<

Name:

http://php.net/manual/en/pdostatement.fetch.php
http://php.net/manual/en/pdostatement.fetch.php

Email:

FORM2;

b. Save the modified file, check that your code is syntactically correct by executing the
script in a terminal, then refresh the URL for the script. You should now see a form that
allows you to enter a slot number, name and e-mail address.

c. Add code to your PHP script after the comment “Code for 4c here” and before the
comment “Code for 4d here” that does the following: If a user supplies a non-empty
slot number, name and e-mail address using the form introduced in Exercise 4a and
clicks on the ‘Insert into DB’ button, then your code should insert those values into
the database. If the insertion is successful, a success message should be shown. If the
insertion fails (which will be the case if the slot number already exists in the database),
then a failure message should be shown that includes the error message you get from
MySQL.
Hints:

• First check whether the user has clicked the ‘Insert into DB’ button. If so, check
whether the user has provided a slot number, name and e-mail address. If one of the
pieces of information is missing, generate an error message and do not proceed to
insert the incomplete information that was provided by the user.

• A naive solution will retrieve the three values entered by the user, construct an SQL
query as a string containing those values and execute that query, using code like

$query = “insert into meetings (slot,name,email) values(
{$_REQUEST[’slot’]},\”{$_REQUEST[’name’]}\”,\”{$_REQUEST[’email’]}\”)”;
$success = pdo->query($query);

This solution is vulnerable to SQL injection and should therefore by avoided. See
http://php.net/manual/en/security.database.sql-injection.php for additional
information.

• A better solution involves the use of a prepared statement and parameter binding.
Prepared statements are a kind of compiled template for SQL statements that includes
parameters/placeholders that will later be filled by values. Prepared statements offer
two major benefits: (i) the SQL statements only need to be parsed (or prepared)
once, but can be executed multiple times with the same or different values for the
parameters, (ii) neither the parameters nor the values that are bound to them need
to be quoted; this is handled automatically and in such a way that no SQL injection
will occur.
Using a prepared statement, the insertion of slot number, name and email address
into our database may look as follows:

$stmt = $pdo->prepare(
“insert into meetings (slot,name,email) values(?,?,?)”);

$success = $stmt->execute(
array($_REQUEST[’slot’],$_REQUEST[’name’],$_REQUEST[’email’]));

Here, using $pdo->prepare(), we first create a prepared statement with three place-
holders, indicated by ?, that we will later bind to values. The binding of placeholders
is done using $pdo->execute() that then also executes the query. The function takes

http://php.net/manual/en/security.database.sql-injection.php

as arguments an array with the values that should be bound to the placeholders.
The function will return a boolean value indicating whether the execution has been
successful.
Instead of ? it is possible to use named placeholders. The names of the placehold-
ers must then all appear as keys in the array that is used to provide values for the
placeholders:

$stmt = $pdo->prepare(
“insert into meetings (slot,name,email) values(:slot,:name,:email)”);

$success = $stmt->execute(
array(“name” => $_REQUEST[’name’],”slot” => $_REQUEST[’slot’],

“email” => $_REQUEST[’email’]));

The advantage of name placeholders is obviously that fewer errors with the order of
values are made.
The manual pages for the relevant functions are
– http://php.net/manual/en/pdo.prepare.php,
– http://php.net/manual/en/pdostatement.bindparam.php, and
– http://php.net/manual/en/pdostatement.execute.php

d. Add code to your PHP script after the comment “Code for 4d here” and before the
comment “Code for 5a here” that does the following: If a user supplies a non-empty
slot number using the form introduced in Exercise 4a and clicks on the ‘Delete from DB’
button, then your code should attempt to delete any entry with a matching slot number
from the database. If the deletion successful removes an entry from the database, then
a success message should be shown. If the deletion fails to remove anything from
the database, then a failure message should be shown. If the database operation fails
for any other reason, then a failure message should be shown that includes the error
message you get from MySQL.
Hint: For database operations like ‘update’ and ‘delete’ $pdo->execute() will return
TRUE even if no database entry was update or deleted. To determine whether the op-
eration was truely successful, one has to check whether the number of affected rows is
greater than zero (or equal to the expected number of affected rows). $stmt->rowcount()
returns the number of affected rows.

5. Finally, we add a facility that allows us to query the database using regular expressions.

a. Add code to your PHP script after the comment “Code for 5a here” that does the fol-
lowing: If a user supplies a regular expression in the name field of the form introduced
in Exercise 4a and clicks the ‘Query DB’ button, then your code should retrieve and
display all entries in the meetings table where the value in the name field matches that
regular expression.
Hints:

• First check whether the user has clicked the Query DB button. If so, check whether
the user has entered something into the name field. If not, generate an error message
and do not proceed to query the database.

• For information on regular expression matching in MySQL see
http://dev.mysql.com/doc/refman/5.5/en/pattern-matching.html

• Just as in Exercise 4c, a prepared statement is the safest way to query the database.
To prepare a statement for the query, to bind variables to the placeholder in the
statement and to execute the statement, proceed as in Exercise 4c.

http://php.net/manual/en/pdo.prepare.php
http://php.net/manual/en/pdostatement.bindparam.php
http://php.net/manual/en/pdostatement.execute.php
http://dev.mysql.com/doc/refman/5.5/en/pattern-matching.html

• Once the prepared statement has been executed, we can use a foreach-loop to access
and print out each row that was retrieved as in Exercise 2a.

• It would be nice if the information retrieved from the database would be presented
as HTML table, just as in Exercise 2f.

Practical 5: PHP and Databases

Data in meeting table (While loop)

Data in meeting table (Foreach loop)

Practical 19A

Data in meeting table (While loop)

Data in meeting table (Foreach loop)

Related Posts