revised 20 February 2019
| CS 199 | Willamette University | Spring, 2019 |
|
Programming in PHP, Databases with MySQL, and Web Applications |
||
This lab is the second in a series that gradually provides experience with PHP with MySQL.
The previous lab explored establishing a MySQL connection within PHP and working with SELECT queries.
This lab provides experience with INSERT, UPDATE, and DELETE statements.
Two more labs consider the inclusion of such MySQL/PHP interactions within the context of Web pages.
The previous lab described the following PHP code for establishing a PDO connection to a database:
<?php
// identify details and credentials for accessing the database
$server = 'local host';
$database = ... ;
$username = ... ;
$password = ... ;
// Establish an environment to report trouble, if a problem occurs
try {
$pdo = new PDO("mysql:host=local host;dbname=$database", $username, $password);
// set the PDO error mode to throw exception when run-time error encountered
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// provide as much protection as possible to counteract possible injection attacks
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}
catch (Exception $e) {
echo "error connecting to $database \n";
echo "full error message " . $e->getMessage() . "\n";
echo "exiting\n";
exit;
}
echo "connected to database '$database'\n";
Within this context, the lab described two common approaches for executing SQL queries:
$pdo->query, and
$pdo->prepare and $pdo->execute
Those same approaches apply to INSERT and UPDATE queries, as well. For example, the following code inserts a new record into the faculty table, as described previously.
$pdo->query approach:
$sqlQuery = 'INSERT INTO faculty ';
$sqlQuery .= '(first, last, dept, email, phone, office) ';
$sqlQuery .= 'VALUES ("H", "MacKay", "Far Out", ';
$sqlQuery .= '"walker@grinnell.edu", "n/a", "199")';
echo "query: $sqlQuery \n";
$result = $pdo->query($sqlQuery);
$pdo->prepare and $pdo->execute approach:
try {
$sqlQuery = 'INSERT INTO faculty ';
$sqlQuery .= 'set first = "Alter", ';
$sqlQuery .= 'last = "Ego", ';
$sqlQuery .= 'dept = "Consultant", ';
$sqlQuery .= 'email = "n/a", ';
$sqlQuery .= 'phone = "n/a", ';
$sqlQuery .= 'office = 3141592';
echo "query: $sqlQuery \n";
$preparedQuery = $pdo->prepare($sqlQuery);
$preparedQuery->execute();
}
catch (Exception $e) {
echo "error on query: $sqlQuery \n";
echo "error message: $e->getMessage \n";
echo "exiting \n";
exit;
}
In reviewing these statements, several observations may be helpful.
INSERT (and UPDATE and DELETE) statements often are reasonably detailed and long. This has several consequences:
The example shows two different versions of the INSERT statement, as discussed in the lab on Changing Tables Either version of the INSERT query string can be used with either approach for executing a query, as shown above.
When writing an INSERT or other SQL statement, it is easy to miss an element or misspell something. Echoing the query, when it is formed, allows careful checking that the query is what you intend.
Since an INSERT statement will change the database, caution is needed before actually executing the query.
// $result = $pdo->query($sqlQuery);
A complete program with these insert statements is available as a text file.
The approaches just described for implementing INSERT statements also apply to UPDATE and DELETE statements.
UPDATE and DELETE statements can be dangerous if a WHERE clause is omitted!
Double check UPDATE and DELETE statements before executing them in a PHP program!
The lab on More Practice with MySQL suggested that you create a table of second-floor rooms in Ford Hall, using these main steps:
CREATE a table involving room number, type of room, number of computer workstations, number of chairs, and number of projection devices.
INSERT rooms numbered 201 through 224, with 1 workstation, 3 chairs, and 0 projection devices.
DELETE rooms 203, 205, and 222.
Modify classrooms 201, 202, 203, and 224 (see earlier lab for details).
Modify rooms 209, 218, and 220 to meet identified conditions.
Although you manually typed individual SQL statements for these steps in the earlier labs, your experience with PHP can help streamline some of these steps. Translating the earlier lab to a PHP program is the basis for this lab.
In what follows, develop your program in small stages, so you can focus on each step before going onto the next step!
For the first 3 steps below, use the $pdo->query approach for executing queries.
Write a CREATE statement within a PHP program to establish the room table, simply placing the earlier CREATE statement in a PHP program that defines and executes the table.
To insert records for rooms 201 through 224, a program could define and execute one INSERT statement and then place the statement in a loop to cycle through all identified rooms.
To delete the rooms 203, 205, and 222, create an array containing these three numbers. Then develop a DELETE statement to remove a single record, and place it in a loop that will cycle through the room numbers in the array.
For the remaining steps, use the $pdo->prepare and $pdo->execute approach, as described above.
Write UPDATE queries to change the information stored for the classrooms 201, 202, 203, and 224. (You may decide to do this with 4 separate UPDATE statements, or you may discover a pattern that will simplify the overall work.)
Write additional UPDATE statements to modify the data stored for Rooms 209, 218, and 220.
|
created 11 February 2019 revised 20 February 2019 |
|