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 <user>
where <user> should be replaced by your departmental user name (<user> 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('
<password>
');
where <password> 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 <user> mysql -h mysql -u <user> -p <user>
where <user> should be replaced by your departmental user name (the option -u <user> 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 |
| |
| |
|
|
| |
|
| |
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 |
|
|
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 |
|
| |
|
|
+ – |
– – – – – |
+ – |
– – – – – |
– – – – – – – – – |
+ – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – |
+ |
|
| |
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:
<!DOCTYPE html> <html>
<head>
<title>Practical 5: PHP and Databases</title> </head>
<body>
<h1>Practical 5: PHP and Databases</h1>
<?php
$db_hostname = "mysql";
$db_database = "<user>";
$db_username = "<user>";
$db_password = "<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 "<h2>Data in meeting table (While loop)</h2>\n"; $stmt = $pdo->query("select * from meetings");
echo "Rows retrieved: ".$stmt->rowcount()."<br><br>\n"; while ($row = $stmt->fetch()) {
echo "Slot: ",$row["slot"], "<br>\n";
echo "Name: ",$row["name"], "<br>\n";
echo "Email: ",$row["email"],"<br><br>\n";
}
echo "<h2>Data in meeting table (Foreach loop)</h2>\n"; $stmt = $pdo->query("select * from meetings"); foreach($stmt as $row) {
echo "Slot: ",$row["slot"], "<br>\n";
echo "Name: ",$row["name"], "<br>\n";
echo "Email: ",$row["email"],"<br><br>\n";
}
$pdo = NULL;
} catch (PDOException $e) {
exit("PDO Error: ".$e->getMessage()."<br>");
}
?>
</body>
</html>
Replace both occurrences of <user> with your departmental user name and replace <password> 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 the command
php /public_html/php05A.php
Check that there are no syntax error and that the script produces the output
<!DOCTYPE html> <html> <head> <title>Practical 19</title> </head> <body> <h1>Practical 19A</h1> <h2>Data in meeting table (While loop)</h2> Rows retrieved: 3<br><br> Slot: 1<br> Name: Michael North<br> Email: M.North@student.liverpool.ac.uk<br><br> Slot: 5<br> Name: Jody Land<br> Email: J.Land@student.liverpool.ac.uk<br><br> Slot: 7<br> Name: Trish Shelby<br> Email: T.Shelby@student.liverpool.ac.uk<br><br> <h2>Data in meeting table (Foreach loop)</h2> Rows retrieved: 3<br><br> Slot: 1<br> Name: Michael North<br> Email: M.North@student.liverpool.ac.uk<br><br> Slot: 5<br> Name: Jody Land<br> Email: J.Land@student.liverpool.ac.uk<br><br> Slot: 7<br> Name: Trish Shelby<br> Email: T.Shelby@student.liverpool.ac.uk<br><br> </body> </html>
e. Open a web browser and access the URL
http://cgi.csc.liv.ac.uk/ <user>/php05A.php
where <user> 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<<<FORMSTART <form name="form1" method="post"> <select name="select" onChange="document.form1.submit()"> <option value="None">Select a name</option> FORMSTART; // Add further options here echo<<<FORMEND </select> </form> FORMEND; foreach ($_REQUEST as $key => $value) echo "$key => $value<br>\n";
b. Save the modified file, check that your code is syntactically correct by executing the script in a terminal, then refresh the URL
http://cgi.csc.liv.ac.uk/ <user>/php05A.php
in your web browser. You should now see a rudimentary drop-down menu at the bottom of the page.
c. At the point indicated by the comment “Add further options here” in Exercise 3a, add PHP code that generates additional options for the drop-down menu, one for each entry in the meetings database table. The value attribute for each option should be the email address stored in the database while the label should be the name, for example:
<option value='T.Shelby@student.liverpool.ac.uk'>Trish Shelby</option>
As in Exercise 2f, the required data should be retrieved from the database.
d. Once you have successfully completed Exercise 3c, observe what happens if you select one of the options in the pop-up menu. You should see additional text at the bottom of the web page, for example
select => M.North@student.liverpool.ac.uk
if you have selected the name Michael North among the options.
Make sure that you understand where this text comes from and how it comes about.
e. Modify your script so that the output you see in Exercise 3c is no longer produced by the script, but instead the script adds
You can contact Michael North via the e-mail address M.North@student.liverpool.ac.uk
at the bottom of the page. Make sure that on the first visit of the URL no text is shown. Hint: The PDO function fetch described at http://php.net/manual/en/pdostatement. fetch.php will be useful, as you only retrieve one row from the database.
4. The next task is to add a facility that allows us to insert new data into the database via our web page.
a. Add the following code to phpDB1.php just before the statement $pdo = NULL.
echo<<<FORM2 <form name="form2" method="post"> Slot: <input type="number" name="slot" min="1" max="100"><br> Name: <input type="text" name="name" size="100"><br Email: <input type="text" name="email" size="100"><br> <input type="submit" name="insert" value="Insert into DB"> <input type="submit" name="delete" value="Delete from DB"> <input type="submit" name="query" value="Query DB"> </form> 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
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.
• 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.
代写CS&Finance|建模|代码|系统|报告|考试
编程类：C++,JAVA ,数据库,WEB,Linux,Nodejs,JSP,Html,Prolog,Python,Haskell,hadoop算法,系统 机器学习
金融类：统计，计量，风险投资，金融工程，R语言，Python语言，Matlab，建立模型，数据分析，数据处理
服务类：Lab/Assignment/Project/Course/Qzui/Midterm/Final/Exam/Test帮助代写代考辅导
E-mail:850190831@qq.com 微信：BadGeniuscs 工作时间：无休息工作日-早上8点到凌晨3点
如果您用的手机请先保存二维码到手机里面，识别图中二维码。如果用电脑，直接掏出手机果断扫描。
