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
 
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its

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 -pmysql -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

+ –	– – – – –	-+ –	– – – – – – – – – – – –	+ –	– – – – –	+ –	– – – –	+ –	– – – – – – – –	+ – – – – – – –	+
|	Field	|	Type	|	Null	|	Key	|	Default	|  Extra	|
+ –	– – – – –	-+ –	– – – – – – – – – – – –	+ –	– – – – –	+ –	– – – –	+ –	– – – – – – – –	+ – – – – – – –	+
|	slot	|	int (11)	|	NO	|	PRI	|	NULL	|	|
|	name	|	varchar (50)	|	YES	|		|	NULL	|	|
|	email	|	varchar (50)	|	YES	|		|	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	[email protected]
5	Jody Land	[email protected]
7	Trish Shelby	[email protected]
11	Wayne Ungood	[email protected]

• 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 . N o r t h @ s t u d e n t . li v er po o l . ac . uk	|
|	5	|	Jody	Land	|  J . L a n d @ s t u d e n t . l iv e rp oo l . ac . uk	|
|	7	|	Trish	Shelby	|  T . S h e l b y @ s t u d e n t . l iv e rp oo l . ac . uk	|
|	11	|	Wayne	Ungood	|  W . U n g o o d @ s t u d e n t . li ve r po ol . 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.

f. 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

	Practical 5: PHP and Databases

 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.

d. Execute the PHP script in the terminal using th