2 Establishing a Connection and First Queries

2.1 MySQLi vs. PDO

PHP programs can access relational databases through various interfaces. For MySQL and MariaDB, two PHP extensions are primarily used:

• MySQLi
• PDO

Both extensions allow a program to establish a database connection, execute SQL statements, and process the results. Additionally, both support prepared statements and transactions.

MySQLi is specifically designed for MySQL and the largely compatible MariaDB database. PDO, on the other hand, provides a unified programming interface for various database systems.

The following table summarizes the basic features of both approaches.

PDO

PDO provides a unified interface for accessing different database systems. Methods such as prepare(), execute(), and fetch() are used in a similar way regardless of the database system being used.

However, PDO does not standardize the different SQL dialects. Therefore, if the database system is changed, SQL statements may need to be adjusted accordingly.

A connection to the Invoices database can be established as follows:

$dsn = "mysql:host=localhost;dbname=Invoices;charset=utf8mb4";

$pdo = new PDO($dsn, "root", "",

    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

The variable $dsn contains the Data Source Name (DSN), a string that specifies the database driver, server, database, and character set to be used.

• Thanks to new PDO(), $pdo refers to an object of the PDO class.
• mysql: means that the SQL driver should be used within PDO.
• host=localhost indicates that the database server is running locally.
• dbname=Invoices specifies the database to be used.
• charset=utf8mb4 specifies the character set.
• root specifies the MySQL database user.
• "" specifies an empty password
• [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION] sets the error mode so that PDO throws exceptions when problems occur. Without this option, PDO would often only return error codes that would need to be checked manually. See the appendix fo further details.

MySQLi

MySQLi is a special extension designed exclusively for MySQL databases. A connection is established using the following commands (using the Invoices database as an example):

$con = new mysqli("localhost", "root", "", "Invoices");

if ($con->connect_error) {

    die("Connection error: " . $con->connect_error);

}

$con is a PHP variable. It refers to an object of the mysqli class that represents the connection between the PHP program and the database server. The $con object provides properties such as $con->connect_error and methods such as $con->query(), $con->execute_query(), $con->close(), and $con->prepare(). The name $con is short for "connection." However, any valid variable name could be used.

The individual parts of the connection statement have the following meanings:

• mysqli is the PHP class used to establish a MySQL connection.
• new mysqli() creates an object of the mysqli class and attempts to establish the connection.
• "localhost" specifies the MySQL server. During local development, localhost means that the database server is running on the same computer as the PHP program. Alternatively, a host name or IP address can be specified.
• "root" specifies the MySQL user account.
• "" specifies an empty password.
• "Invoices" specifies the database to be used.

The following if statement handles a failed connection attempt:

if ($con->connect_error) {

  die("Connection error: " . $con->connect_error);

}

• $con->connect_error contains information about an error that occurred while establishing the connection. If a connection error occurred, die() terminates the script and outputs the specified error message.
• The functions die() and exit() can both be used to terminate a PHP script immediately.

MySQLi supports prepared statements, stored procedures, and multiple statements. In addition to the object-oriented programming style shown above, MySQLi can also be used procedurally:

$con = mysqli_connect("localhost", "root", "", "Invoices");

This is possible because the MySQLi extension provides both an object-oriented and a procedural interface.

2.2 CustomerQueriesMySQLi_1.php

2.2.1 Program Features

• Launched via browser command
• Simple query
• Direct access to the database.
• Class used for establishing a connection: mysqli
• HTML output of the result

The first program executes a simple, fixed SQL query. Since the SQL statement contains no user input or other variable values, a prepared statement is not required here. In the following example, a search value is added to the query, so it is executed as a prepared statement.

2.2.2 The program

<!doctype html>

<html lang="en">

<head>

  <meta charset="utf-8">

  <title>Customer Relation</title>

  <link rel="stylesheet" type="text/css" href="mySQL_1.css">

</head>

<body>

  <p class="text-red">

    Customer Relation - with column headers:

  </p>

  <table border="1">

    <caption class="text-bold">Customer Relation</caption>

    <thead>

      <tr>

      <th>Customer No.</th>

      <th>Last Name</th>

      <th>First Name</th>

      </tr>

    </thead>

    <tbody>

<?php

/*

Establish a connection to the database.

mysqli stands for MySQL Improved. The statement "new mysqli()"

creates an object of the mysqli class. The variable $con refers to

this object and allows access to its properties and methods.

*/

$con = new mysqli("localhost", "root", "", "Invoices");

/*

Check whether an error occurred while establishing the database

connection. If an error occurs, die() displays the error message

and immediately terminates the program.

*/

    if ($con->connect_error) {

      die("Database connection failed: "

      . $con->connect_error);

    }

// Set the character set for the database connection.

// utf8mb4 supports Unicode characters.

$con->set_charset("utf8mb4");

// Define the SQL statement. Using AS gives the selected columns

// new names. These names are later used as keys for the

// associative array.

$sql = "SELECT CustomerNo AS CustNo, LastName, FirstName

  FROM Customers ORDER BY CustNo";

/*

Execute the SQL statement.

execute_query() is a method of the mysqli class. When used with a

SELECT statement, it returns an object of the mysqli_result class.

The variable $res refers to this result object.

*/

$res = $con->execute_query($sql);

// num_rows is a property of the result object. It contains

// the number of rows returned by the query.

if ($res->num_rows === 0) {

  echo "

    <tr>

      <td colspan=\"3\">No results</td>

    </tr>

    ";

}

/*

fetch_assoc() reads one row at a time from the result object

and returns it as an associative array.

The column names - or the names assigned using AS - form the keys

of the array.

The loop continues as long as another row

remains.

*/

while ($row = $res->fetch_assoc()) {

  echo "<tr>";

  echo "<td>" . htmlspecialchars((string)$row["CustNo"],

    ENT_QUOTES,"UTF-8") . "</td>";

  echo "<td>" . htmlspecialchars($row["LastName"],

    ENT_QUOTES, "UTF-8") . "</td>";

  echo "<td>" . htmlspecialchars($row["FirstName"],

    ENT_QUOTES, "UTF-8") . "</td>";

  echo "</tr>";

}

// Close the result object and release the resources used for it

$res->close();

// Close the database connection and associated resources.

$con->close();

?>

  </tbody>

  </table>

</body>

</html>

Browser Output

2.2.3 The stylesheets

File mySQL_1.css

body {margin: 10px; color: black; font-family: Arial, sans-serif;}

h1 {margin: 5px 0 10px; color: blue; font-family: Calibri, Arial,

  sans-serif; font-size: 16pt; font-weight: bold; text-align: left;}

p {margin: 10px 0;}

table {border-collapse: collapse;font-family: inherit;}

thead {font-size: 10pt; font-weight: bold;}

td, th {padding: 3px; border: 1px solid black; text-align: left;

  vertical-align: middle;}

.text-red {color: red; font-size: 14pt;}

.text-bold {font-size: 14pt; font-weight: bold;}

2.2.4 Notes

The following classes are used in this program:

• mysqli: This class is used to establish a connection to a MySQL database. An object created from this class - for example, the object referred to by $con - provides properties and methods for working with the database. One method of mysqli is execute_query(). It executes an SQL statement and, in the case of a SELECT statement, returns a result object of the mysqli_result class.
• mysqli_result: An object of the mysqli_result class contains the result of a query. One of its properties is num_rows. One of its methods is fetch_assoc(). This method reads one row at a time from the result and returns it as an associative array.

Integration

The program illustrates how procedural and object-oriented programming elements can be combined in PHP. With the statement

$con = new mysqli("localhost", "root", "", "Invoices");

an object of the mysqli class is created. The PHP variable $con refers to this object. Its properties and methods can be accessed via $con.

For example:

$con->connect_error // property

$con->query($sql) // Method

Regarding the Programming

• fetch_assoc() does not output the rows itself. The method reads one row at a time and returns it as an associative array. The actual output can then be generated using echo, for example.
• The values read from the database are processed using htmlspecialchars() before being inserted into the HTML output (see Appendix). This converts characters that have a special meaning in HTML so that they are not interpreted as part of the HTML markup.
• The execute_query() method has been available since PHP 8.2. If the SQL statement does not contain any placeholders, $con->query($sql) can be used instead in older PHP versions. If it contains placeholders, the query must be executed as a prepared statement using prepare(), bind_param(), and execute().

2.3 CustomerQueriesMySQLi_2.php

2.3.1 Program Features

• Launched via a browser
• Query using a prepared statement
• Fixed search value in the program
• Direct access to the database
• Classes used: mysqli, mysqli_stmt, and mysqli_result
• Preformatted output in the browser

The second example again demonstrates the basic structure of a PHP program that is launched via a browser and queries a database using the mysqli class. However, the query is now executed as a prepared statement () rather than a simple SQL query, which is highly recommended for security reasons.

Prepared Statement

2.3.2 The program

<?php

/*

Customer number to search for.

In this example, the customer number is defined directly in the

program. In a web application, it would normally come from an input

form.

*/

$id = 1002;

/*

The HTML element <pre> ensures that spaces and line breaks in

the input are preserved in the output.

This allows the result of print_r() to be displayed clearly.

*/

echo "<pre>";

echo "--- Establishing a connection with MySQLi ---\n";

echo "--- Invoices, Customers Relation ---\n\n";

/*

Establish a connection to the database.

The statement new mysqli()creates an object of the mysqli class

is created:

- "localhost" refers to the database server.

- "root" is the MySQL user.

- "" is the password, which is empty here.

- "Invoices is the name of the database.

The variable $con refers to the created mysqli connection object.

*/

$con = new mysqli("localhost", "root", "", "Invoices");

/* Check whether an error occurred while establishing the

connection. If an error occurs, connect_error contains the

corresponding error message. die() outputs the message and

immediately terminates the program.

*/

if ($con->connect_error) {die(

    "MySQLi connection error: ". $con->connect_error. "\n");

}

// Set the character set for the database connection. utf8mb4

// supports all standard Unicode characters.

$con->set_charset("utf8mb4");

/*

Prepare the SQL statement.

The question mark is a placeholder for the customer number.

The actual value is bound to this placeholder later.

prepare() returns an object of the mysqli_stmt class.

The variable $stmt refers to this statement object.

*/

$stmt = $con->prepare(

    "SELECT * FROM Customers WHERE CustomerNo = ?"

);

// Check whether the SQL statement was prepared successfully.

if (!$stmt) {

    die("Failed to prepare the SQL statement: "

      . $con->error . "\n"

    );

}

/*

If successful, continue here:

Bind the value of the variable $id to the placeholder. The type

indicator "i" means that the value is treated as an integer.

*/

$stmt->bind_param("i", $id);

/*

Execute the prepared statement.

Only now is the prepared statement executed using

the bound customer number.

For the flow of control at this point, see the side note below.

*/

if (!$stmt->execute()) {

  die("Failed to execute the SQL statement: "

    . $stmt->error . "\n"

  );

}

/*

Retrieve the result of the SELECT query.

get_result() returns an object of the mysqli_result class.

This result object can then be read row by row.

*/

$result = $stmt->get_result();

// Check whether a customer with the specified customer number

// was found.

if ($result->num_rows === 0) {

    echo "No customer with customer number " . $id . " found.\n";}

/*

Read the result row by row.

fetch_assoc() returns the next row as an associative array.

The attribute names of the relation form the keys, and the

attribute values form the corresponding values of the array.

Since CustomerNo is the primary key, this query returns

at most one row.

*/

while ($row = $result->fetch_assoc()) {

  print_r($row);

  echo "\n";

}

// Close the result object and release its resources.

$result->close();

// Close the statement object and release its resources.

$stmt->close();

// Close the database connection.

$con->close();

// Close the HTML element for the preformatted output.

echo "</pre>";

?>

2.3.3 Browser Output

For customer number 1002, the output is similar to the following:

If the specified customer number does not exist, the program displays, for example:

2.3.4 Side Note

Where does the control flow occur in the following program fragment?

if (!$stmt->execute()) {
die("Failed to execute the SQL statement: "

      . $stmt->error . "\n");

}

The statement

$stmt->execute()

is actually executed while PHP evaluates the condition of the if statement. The result of this method call is then checked immediately. This combines execution and error checking within a single if statement.

Here’s how it works in detail:

First, PHP executes this method:

$stmt->execute()

The method normally returns a Boolean value:

• true - the SQL statement was executed successfully
• false - an error occurred during execution

The logical NOT operator (! ) precedes the method call: It inverts the returned truth value.

Successful Execution

If execute() returns true, this results in !true. The result is therefore false. Thus, the if condition is not met. The error block is skipped:

die(...);

The program continues after the if statement.

Failed execution

If execute() returns false, the result is !false. The result is true. Thus, the if condition is met. The error message is displayed, and the program terminates:

die(

  "Failed to execute the SQL statement: " . $stmt->error

  . "\n"

);

Equivalent, more verbose syntax

You could also split the execution and checking into two statements:

$successful = $stmt->execute();

if (!$successful) {

  die(

    "Failed to execute the SQL statement: "

      . $stmt->error . "\n"

  );

}

2.3.5 Notes

Summary of Classes and Methods

The mysqli class represents a connection to a MySQL database. It is therefore an object-oriented MySQLi interface with the constructor

mysqli

new mysqli(host, user, password, database).

It has the attribute (often referred to as a property in programming)

$con->connect_error

which contains an error message if the connection failed, and, among others, the following methods

$con->prepare(string $sql), which prepares an SQL statement.

$con->close(), which closes the connection to the database.

In general:

mysqli_stmt

• Objects of the mysqli_stmt class each represent a prepared statement.
• The method $stmt->bind_param(string $types, mixed amp;$var, mixed amp;...$vars) binds the PHP variables to the placeholders in the SQL statement. The amp; symbol indicates that the variable is passed by reference. Therefore, variables rather than literal values are passed to bind_param().
• The method $stmt->execute() executes the prepared statement. This occurs on the server.
• The method $stmt->get_result() returns a result object of the mysqli_result class for a query that produces a result set.
• The method $stmt->close() closes the statement.

An example with multiple placeholders is:

$stmt = $con->prepare(

  "select * from customers where CustomerNo = ? AND Name = ?"

);

$stmt->bind_param("is", $id, $lastName);

The first parameter is an integer (i), and the second parameter is a string (s).

The mysqli_result class represents the result of a query that returns rows.

mysqli_result

The method $result->fetch_assoc() reads the next result row and returns it as an associative array. If there are no more rows, it returns null.

2.3.6 About the program

• bind_param() does not insert the value into the SQL statement by ordinary text substitution. The prepared SQL statement and the parameter value are handled separately. Thus, the string SELECT * FROM Customers WHERE CustomerNo = 1002 is not simply constructed internally.
• Prepared statements (see also the Appendix) protect against SQL injection because a bound value is treated as data rather than as part of the SQL code. They should be used whenever values from external sources are incorporated into an SQL statement. This is good practice even in a local XAMPP development environment.
• Since the query searches for CustomerNo, which is the primary key of the Customers relation, the query returns at most one row. The while loop is nevertheless correct and can easily be reused later for queries that return multiple rows.
• In PHP, get_result() requires the MySQL native driver mysqlnd. This driver is normally included in XAMPP installations.
• Output using print_r() is particularly useful for learning and debugging. On a production website, the individual values should normally be incorporated into suitable HTML elements and processed using htmlspecialchars() before being displayed (see Appendix).

2.4 CustomerQueriesPDO_1.php

2.4.1 Program Features

• Class used for establishing a connection: PDO
• Security through prepared statements
• Launched via browser command
• Preformatted output in the browser

The following example shows how to establish a database connection using the PDO class. Once again, the program accesses the Customers relation in the Invoices database.

2.4.2 The program

<?php

/*

Customer number to search for. In this example the search value is

specified directly in the program. In a

web application, it would typically come from an input form.

*/

$id = 1002;

echo "<pre>";

echo "--- Establishing a Connection with PDO ---\n";

echo "--- Invoices, Customers Relation ---\n\n";

try {

/*

Establish a connection to the database.

PDO stands for PHP Data Objects. The variable $pdo refers to the

created PDO object. This object represents the connection to the

database.

The DSN (Data Source Name) contains:

- the database driver used: mysql

- the database server: localhost

- the database name: Invoices

- the character set: utf8mb4

"root" is the MySQL user. The following

quotes mean that no password is used.

PDO::ERRMODE_EXCEPTION causes PDO to throw an exception

when a database error occurs.

*/

$pdo = new PDO(

  "mysql:host=localhost;dbname=Invoices;charset=utf8mb4",

    "root","",[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

/*

Prepare the SQL statement.

:id is a named placeholder for the customer number.

prepare() returns an object of the PDOStatement class.

The variable $stmt refers to this statement object.

*/

$stmt = $pdo->prepare(

  "SELECT * FROM customers WHERE customerNo = :id"

  );

/*

Execute the prepared statement.

The associative array assigns the value of $id to the

named placeholder :id. The colon can be omitted in the

array key.

The SQL statement and the parameter value are handled

separately.

*/

$stmt->execute(["id" => $id]);

/*

Retrieve all rows found.

fetchAll() returns an array containing all result rows.

PDO::FETCH_ASSOC specifies that each row is provided as an associative array. The attribute names of the table form the

keys of the array.

*/

$records = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Check whether a row was found.

if (count($records) === 0) {

  echo "No customer with customer number " . $id . " found.\n";

}

/*

Output the found rows one by one. print_r() is a

built-in PHP function. It outputs the content and structure of a

variable - in particular, an array - in a human-readable form.

If the number of rows is zero, $records is an empty array.

Therefore, the foreach loop is not executed.

*/

foreach ($records as $row) {

  print_r($row);

  echo "\n";

}

//from the try block (at the very top)

} catch (PDOException $e) {

// Handle PDO database errors. getMessage() returns the

// associated error message.

echo "PDO error: " . $e->getMessage() . "\n";

} // closes the catch block

// Close the HTML element for the preformatted output.

echo "</pre>";

?>

Browser Output

2.4.3 Notes

Side Note: What Exactly Is $stmt?

Let's consider the following statement:

$stmt = $pdo->prepare("SELECT * FROM customers

    WHERE CustomerNo = :id");

The prepare() method prepares the SQL statement for later execution. The statement itself is not executed at this point. After prepare() has been successfully executed, the variable $stmt points to an object of the PDOStatement class. This statement object represents the prepared SQL statement and provides methods that can be used to bind placeholders, execute the statement, and retrieve query results.

The named placeholder :id is not yet replaced by a specific value. The value can be bound, for example, using bindValue():

$stmt->bindValue(":id", $id, PDO::PARAM_INT);

The prepared SQL statement is then executed: $stmt->execute(); Alternatively, the value can be passed directly when calling execute():

$stmt->execute(["id" => $id]);

Important methods of the PDOStatement object include:

• bindParam() - binds a variable by reference to a placeholder
• bindValue() - binds a value to a placeholder
• execute() - executes the prepared SQL statement
• fetch() - retrieves the next result row
• fetchAll() - retrieves all result row

About the Program

The program demonstrates how to retrieve and output a single row from the database. It uses the PDO extension, which provides an object-oriented and secure interface to the database.

First, a search criterion is defined using the variable $id. This is an integer that represents the primary key of a specific row. This value is later inserted into the SQL query.

The output is generated using the echo function. By using the HTML tag <pre>, a formatted display is achieved that preserves line breaks and indentation. This is particularly helpful for debugging purposes or for illustrating data structures.

The actual database operation is embedded in a try/catch block. This construct is used for structured error handling. The try block contains the code to be executed, while the catch block is invoked if an error occurs. Unlike a simple termination using die(), this approach allows for a controlled response to errors.

try/catch block

Within the try block, a connection to the database is first established by creating a PDO object. The connection parameters are passed in the form of a so-called DSN (Data Source Name). The input

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION

ensures that errors are reported as exceptions.

Next, a prepared statement is prepared. The SQL statement contains a named placeholder, :id. This placeholder is only populated with a specific value when the statement is executed. This enhances security by protecting against SQL injection and, at the same time, ensures a clear separation between SQL code and data.

The prepared statement is executed using the execute() method. During execution, the placeholder :id is bound to the value of the variable $id.

The result set is then retrieved using fetchAll(PDO::FETCH_ASSOC). This returns the result rows as an array of associative arrays. The attribute names serve as keys. A foreach loop then processes the individual rows and displays them using print_r().

If a PDO database error occurs during execution, an error message is displayed in the catch block. The getMessage() method provides a more detailed description of the problem that occurred.

2.5 CustomerQueriesPDO_2.php

2.5.1 Program Features

• Class used for establishing a connection: PDO
• Input via an HTML form
• Query secured using a prepared statement
• HTML form with POST submission for data entry
• Preformatted output in the browser

To demonstrate how PHP interacts with HTML, the previous program is now extended by adding an input form. Instead of specifying the customer number directly in the PHP program, the user enters it in the browser.

This is what the input form should look like:

And here is the successful result if 1001 was entered:

2.5.2 The program

<?php

/*

As shown several times above, echo is used to send

components of the HTML document to the browser.

The browser interprets the generated HTML code and displays

the web page. It does not execute the PHP code. The PHP code

is executed beforehand on the web server.

*/

echo "<!doctype html>";

echo "<html lang=\"en\">";

echo "<head>";

echo "<meta charset=\"UTF-8\">";

echo "<title>Customer Search</title>";

echo "</head>";

echo "<body>";

echo "<h2>Query Customer Data</h2>";

/*

Output the HTML form.

method="post" specifies that the entered customer number

is transmitted using the HTTP POST method.

Since no action attribute is specified, the form data is

sent to the same page, that is, to the same URL.

Regarding the backslashes:

The PHP echo statement outputs a string containing HTML code.

Since both the PHP string and the HTML attributes use double

quotation marks, the inner quotation marks must be escaped

with a preceding backslash.

The character sequence \" means that the quotation mark is

part of the string and does not terminate it. The backslash

itself is not displayed.

The forward slash / in </form> or </label>, on the other hand,

is part of the HTML code and marks the closing tag of an

HTML element..

*/

echo "<form method=\"post\">";

echo "<label for=\"id\">Customer Number:</label> ";

echo "<input type=\"number\" id=\"id\" name=\"id\" min=\"1\"

    required> ";

echo "<input type=\"submit\" value=\"Start\">";

echo "</form>";

/*

Check whether the form was submitted using the POST HTTP method.

When the page is first loaded, the condition is not met. In that

case, the form is simply displayed.

*/

if ($_SERVER["REQUEST_METHOD"] === "POST") {

/*

Retrieve the input value from the POST data.

The null coalescing operator ?? ensures that, if a

form element is missing, the empty string

is used instead.

The explicit type cast (int) then converts the value

into an integer.

*/

$id = (int)($_POST["id"] ?? "");

echo "<pre>";

echo "--- Establishing a connection with PDO ---\n";

echo "--- Invoices, Customers Relation ---\n\n";

/*

Type conversion alone does not constitute a complete input

validation. Therefore, an additional check is performed to verify

whether the customer number is greater than 0.

*/

if ($id <= 0) {

  echo "Please enter a valid customer number.\n";

} else {

  try {

/*

Establish a connection to the database.

The DSN contains the database driver, the database server,

the database name, and the character set.

PDO::ERRMODE_EXCEPTION causes PDO to throw an exception when a

database error occurs.

*/

$pdo = new PDO("mysql:host=localhost;" . "dbname=Invoices;"

  . "charset=utf8mb4", "root", "",

  [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]

);

// Prepare the SQL statement.

// :id is a named placeholder for the customer number.

$stmt = $pdo->prepare("SELECT * FROM customers WHERE customerNo = :id");

// Execute the prepared statement.

// The value of $id is assigned to the placeholder :id.

$stmt->execute(["id" => $id]);

// Retrieve all found rows as associative arrays.

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Check whether a customer was found.

if (count($rows) === 0) {

  echo "No customer with customer number " . $id . " found.\n";

} else {

// Output the found rows (records) one by one

  foreach ($rows as $row) {

    print_r($row);

    echo "\n";

  }

  }

} catch (PDOException $e) {

// Catch and display PDO database errors.

echo "PDO error: " . $e->getMessage() . "\n";

      }

  }

// Close the HTML element for the preformatted output.

  echo "</pre>";

}

// End the HTML document.

echo "</body>";

echo "</html>";

?>

2.5.3 Notes

• The browser does not execute the HTML output as program statements. PHP runs on the web server. The browser receives the HTML code generated by PHP, interprets it, and displays the resulting web page.
• The label element is associated with the input field by using for="id" in the label element and id="id" in the input element.
• min="1" tells the browser not to accept a customer number below 1. Server-side validation in PHP is still required because browser-side restrictions cannot be relied upon for validating data received by the server.
• (int) is an explicit type cast, but by itself it does not provide complete input validation. For example, a non-numeric string is converted to 0. Therefore, the program subsequently checks whether $id <= 0.
• $_POST["id"] ?? "" uses the null coalescing operator. If $_POST["id"] does not exist, the expression returns the empty string instead. This prevents an "undefined array key" warning and provides a defined value for the subsequent type conversion.
• As in the previous example, the SQL statement uses a prepared statement with the named placeholder :id. The value entered by the user is therefore passed as parameter data rather than being inserted directly into the SQL statement.

3 Inserting Data

This chapter demonstrates how to insert data into relational databases from PHP programs, first using MySQLi and then using PDO.

3.1 InsertCustomerMySQLi

3.1.1 The program

<?php

// Variable for feedback to the user.

// It remains empty for now and will later be populated with a

// success or error message.

$message = "";

// Database connection information.

$host = "localhost";

$user = "root";

$pass = "";

$db = "Invoices";

// Check whether the form was submitted using the POST HTTP method.

// The PHP code for inserting the data is executed only after

3.2 SaveInvoiceMySQLi

3.2.1 Program Features

• Saving data to multiple related relations
• Transaction
• Prepared statements
• MySQLi Class
• No user input

3.2.2 Task

An invoice is to be entered into the Invoices databaseThis requires data to be inserted into two related relations: the invoice header is stored in InvoiceHeaders, while the corresponding line items are stored in InvoiceItems. These operations form a single unit and should therefore be performed within a transaction. Either the invoice header and all its line items are saved successfully, or none of them are saved.

Transactions

This is demonstrated by the program SaveInvoiceMySQLi.php. To keep the example focused on transactions, the data is defined directly in the program rather than entered through an HTML form. The new invoice has invoice number 23012, belongs to the customer with customer number 1002, and contains three line items for products 100, 101, and 102.

After the invoice has been saved, the result can be checked using the following query:

SELECT ih.InvoiceNo, ih.CustomerNo, ih.InvoiceDate, ii.PositionNo,

  ii.ProductNo, ii.Quantity, c.LastName, c.FirstName

FROM InvoiceHeaders AS ih

INNER JOIN Customers AS c ON ih.CustomerNo = c.CustomerNo

INNER JOIN InvoiceItems AS ii ON ih.InvoiceNo = ii.InvoiceNo

WHERE ih.CustomerNo = 1002 AND ih.InvoiceNo = 23012

ORDER BY ii.PositionNo;

The result should be:

Note: Before the invoice can be saved, customer number 1002 must exist in the Customers relation, and product numbers 100, 101, and 102 must exist in the Products relation. Invoice number 23012 must not yet exist in InvoiceHeaders.

3.2.3 The program

<?php

// Data for establishing a connection.

$host = "localhost";

$user = "root";

$pass = "";

$db = "Invoices";

// Invoice header data.

$invoiceNo = 23012;

$customerNo = 1002;

$invoiceDate = "2026-03-28";

// Invoice line item data.

// In this example, the data is defined directly in the program.

// In a web application, it would normally come from a form.

$items = [

  ["positionNo" => 1, "productNo" => 100, "quantity" => 2],

  ["positionNo" => 2, "productNo" => 101, "quantity" => 1],

  ["positionNo" => 3, "productNo" => 102, "quantity" => 5]];

// The variable initially does not contain a connection object.

// After a successful connection is established, it points

// to the created mysqli object.

$con = null;

try {

// Establish a connection to the database.

  $con = new mysqli($host, $user, $pass, $db);

// Check whether an error occurred. In this case, an

// exception is thrown.

    if ($con->connect_error) {throw new Exception(

    "MySQLi connection error: " . $con->connect_error);

}

// Set the character set for the database connection.

$con->set_charset("utf8mb4");

// Start a transaction.

// The invoice header and all invoice line items

// form a single unit of work. They are

// saved either in their entirety or not at all.

$con->begin_transaction();

// Prepare the SQL statement for inserting the invoice header.

// The question marks serve as placeholders.

    $stmtHeader = $con->prepare("INSERT INTO InvoiceHeaders

      (InvoiceNo, CustomerNo, InvoiceDate) VALUES (?, ?, ?)");

// Check whether the statement was prepared successfully.

    if (!$stmtHeader) {throw new Exception(

      "Prepare failed (invoice header): "

      . $con->error);

}

/*

Bind values to the placeholders.

"i" denotes an integer value.

"s" denotes a string value.

"iis" therefore stands for integer, integer, string.

*/

$stmtHeader->bind_param("iis", $invoiceNo, $customerNo,

  $InvoiceDate);

// Execute the prepared statement.

if (!$stmtHeader->execute()) {throw new Exception(

    "Execute failed (invoice header): "

      . $stmtHeader->error);

}

// Close the statement for the invoice header and

// release the resources used for it.

$stmtHeader->close();

// Prepare the SQL statement for inserting the invoice line items.

$stmtItem = $con->prepare("INSERT INTO InvoiceItems

  (InvoiceNo, PositionNo, ProductNo, Quantity)

  VALUES (?, ?, ?, ?)");

// Check whether the prepared statement was prepared successfully.

if (!$stmtItem) {throw new Exception(

    "Prepare failed (invoice items): "

      . $con->error);

}

/*

Variables for the values of an invoice line item.

MySQLi binds variables rather than only their current

values. The values can therefore be changed in the loop

and the prepared statement can be executed repeatedly.

*/

$positionNo = 0;

$productNo = 0;

$quantity = 0;

/*

Bind variables to the placeholders. All four values are integers

numbers. MySQLi binds the variables here, not just their

current values. Therefore, they can be

and the statement can be executed multiple times.

*/

$stmtItem->bind_param("iiii", $invoiceNo, $positionNo, $productNo, $quantity);

// Process the invoice line items one by one.

foreach ($items as $item) {

// Retrieve values from the respective array element

// and convert them to integers.

  $productNo = (int)$item["productNo"];

  $quantity = (int)$item["quantity"];

if ($productNo == 0 amp;amp; $quantity === 0) {

contionue;

}

if ($productNo <= 0 || $quantity <= 0) {

3.3 InsertCustomerPDO.php

3.3.1 Program Features

• Class used to establish the connection: PDO
• Input via an HTML form
• Secured using a prepared statement
• Launched via a browser
• HTML output of the result

3.3.2 The Program

<?php

// Variable for feedback to the user. It remains empty for now and

// will later contain a success or error message.

$message = "";

/*

Data for establishing the connection.

The DSN (Data Source Name) contains:

- the database driver: mysql

- the database server: localhost

- the database name: Invoices

- the character set: utf8mb4

*/

$dsn = "mysql:host=localhost;dbname=Invoices;charset=utf8mb4";

$user = "root";

$pass = "";

/*

Check whether the form was submitted using the HTTP POST method.

The code for inserting the data is executed only after the form

has been submitted.

*/

if ($_SERVER["REQUEST_METHOD"] === "POST") {

/*

Retrieve the input from the form. The ?? operator returns a default

value if no corresponding value was submitted.

trim() removes leading and trailing spaces.

(int) converts the retrieved value to an integer.

*/

$customerNo = (int)($_POST["customerNo"] ?? 0);

$lastName = trim($_POST["lastName"] ?? "");

$firstName = trim($_POST["firstName"] ?? "");

// Check the input values for validity.

// The customer number must be greater than 0.

// Last name and first name must not be empty.

if ($customerNo <= 0 || $lastName === "" || $firstName === "") {

  $message = "Please fill out all fields correctly.";

} else {

try {

/*

Establish a connection to the database. PDO stands for PHP

Data Objects. The variable $pdo refers to the created PDO object.

PDO::ATTR_ERRMODE specifies how PDO handles errors.

PDO::ERRMODE_EXCEPTION causes PDO to throw an exception when a

database error occurs.

*/

$pdo = new PDO($dsn,$user,$pass,

  [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

/*

Prepare the SQL statement.

:customerNo, :lastName, and :firstName are named placeholders.

The actual values are passed separately when the statement is

executed.

*/

$stmt = $pdo->prepare("INSERT INTO Customers (CustomerNo, LastName,

  FirstName) VALUES (:customerNo, :lastName, :firstName)");

// Execute the prepared statement. The array assigns a value to each

// named placeholder. The colon can be omitted in the array keys.

$stmt->execute(["customerNo" => $customerNo,"lastName" => $lastName,

  "firstName" => $firstName]);

// This line is reached only if execute()

// has not thrown an exception.

$message = "Customer successfully added.";

} catch (PDOException $e) {

// Catch PDO database errors and generate an appropriate message.

$message = "Error inserting customer: " . $e->getMessage();

    }

  }

}

?>

<!doctype html>

<html lang="en">

<head>

  <meta charset="utf-8">

  <title>Add a Customer Using PDO</title>

  <style>body {font-family: Arial, sans-serif;}</style>

</head>

<body>

<h2>Add Customer (PDO)</h2>

<!--

Form for entering customer data. method="post" specifies that the

form data is transmitted using the HTTP POST method.

Since no action attribute is specified, the data is sent

to the same PHP file from which the form was loaded.

-->

<form method="post">

  <label for="customerNo">Customer Number:</label>

  <input type="number" id="customerNo" name="customerNo" min="1"

    required>

  <br><br>

  <label for="lastName">Last Name:</label>

  <input type="text" id="lastName" name="lastName" maxlength="10"

    required>

  <br><br>

  <label for="firstName">First Name:</label>

  <input type="text" id="firstName" name="firstName" maxlength="10"

  required>

  <br><br>

<!-- Button to submit the form. -->

  <button type="submit">Save</button>

</form>

<!--

If feedback is available, it is displayed in bold.

See the Appendix for information on htmlspecialchars().

-->

  <?php if ($message !== ""): ?>

  <p><strong>

  <?php echo htmlspecialchars($message,ENT_QUOTES,"UTF-8"); ?>

  </strong></p>

  <?php endif; ?>

</body>

</html>

3.3.3 Program Run

3.3.4 Notes

• PDO provides a unified interface for accessing different database systems. In this example, the MySQL driver is selected by specifying mysql: in the DSN.
• Unlike in the previous MySQLi version, the character set is specified directly in the DSN using charset=utf8mb4.
• PDO uses named placeholders here: :customerNo, :lastName, and :firstName. Their names make it easy to see which input value is associated with each placeholder.
• The values are passed to execute() separately from the SQL statement. This prevents the parameter values from being interpreted as part of the SQL code and therefore provides protection against SQL injection.
• Because PDO::ERRMODE_EXCEPTION is enabled, PDO throws a PDOException when a database error occurs. Program execution then continues in the corresponding catch block.
• Explicitly closing the statement and database connection is normally not necessary with PDO. The associated resources are released when they are no longer referenced, at the latest when the script terminates.
• The HTML attributes required, min, and maxlength provide initial validation in the browser. Validation in PHP is still necessary because client-side validation can be bypassed.
• A transaction is not required in this example because only a single row is inserted with a single INSERT statement. The statement either succeeds or fails as a whole.

3.4 SaveInvoicePDO.php

3.4.1 Program Features:

• Saving data to multiple related relations ("invoice")
• Class used to establish the connection: PDO
• Transactions
• Prepared statements
• No user input
• Launched via a browser
• Browser output of the result

3.4.2 Task

An invoice is entered into the Invoices database. The invoice header is stored in the InvoiceHeaders relation, while the associated line items are stored in the InvoiceItems relation. These operations form a single unit of work and are therefore performed within a transaction. Either the invoice header and all its line items are saved successfully, or none of them are saved.

Transactions

This is demonstrated by the program SaveInvoicePDO.php. To keep the example focused on transactions, the data is defined directly in the program rather than entered through an HTML form.

The new invoice has invoice number 23011, refers to customer number 1004, and contains three line items for products 100, 101, and 102. After the invoice has been saved, the following query can be used to check the result:

SELECT ih.InvoiceNo, ih.CustomerNo, ih.InvoiceDate, ii.PositionNo,

  ii.ProductNo, ii.Quantity, c.LastName, c.FirstName

    FROM InvoiceHeaders AS ih

    INNER JOIN Customers AS c ON ih.CustomerNo = c.CustomerNo

    INNER JOIN InvoiceItems AS ii ON ih.InvoiceNo = ii.InvoiceNo

    WHERE ih.InvoiceNo = 23001

    ORDER BY ii.PositionNo;

3.4.3 The program

<?php

/*

The program saves an invoice header and the associated

invoice line items in a single transaction.

This ensures that the invoice is either saved in its

entirety or not at all.

Data for establishing the connection.

DSN stands for Data Source Name. This string contains:

- the database driver: mysql

- the database server: localhost

- the database name: Invoices

- the character set: utf8mb4

*/

$dsn = "mysql:host=localhost;dbname=Invoices;charset=utf8mb4";

$user = "root";

$pass = "";

// Data for the new invoice header. In a web application, these

// values would typically come from a form.

$invoiceNo = 23011;

$customerNo = 1002;

$invoiceDate = "2026-09-21";

// Data for the invoice line items.

// The outer array contains all invoice line items.

// Each inner array describes a single line item.

$items = [

  ["positionNo" => 1, "productNo" => 100, "quantity" => 2],

  ["positionNo" => 2, "productNo" => 101, "quantity" => 1],

  ["positionNo" => 3, "productNo" => 102, "quantity" => 5]

];

try {//1

/*

Establish a connection to the database. PDO stands for PHP Data

Objects. The variable $pdo refers to the created PDO object.

PDO::ERRMODE_EXCEPTION causes PDO to throw an exception when a

database error occurs.

*/

$pdo = new PDO($dsn,$user,$pass,

    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

/*

Start a transaction.

All subsequent database operations form a single

unit of work until commit() or rollBack() is called.

*/

$pdo->beginTransaction();

/*

Prepare the SQL statement for the invoice header.

:invoiceNo, :customerNo, and :invoiceDate are named placeholders.

*/

$stmtHeader = $pdo->prepare("INSERT INTO InvoiceHeaders

  (InvoiceNo, CustomerNo, InvoiceDate)

    VALUES (:invoiceNo, :customerNo, :invoiceDate)");

// Execute the prepared statement for the invoice header.

$stmtHeader->execute(["invoiceNo" => $invoiceNo,

  "customerNo" => $customerNo,

    "invoiceDate" => $invoiceDate]);

/*

Prepare the SQL statement for the invoice line items.

The statement is prepared only once and then

executed repeatedly for the individual line items.

*/

$stmtItem = $pdo->prepare("INSERT INTO InvoiceItems

  (InvoiceNo, PositionNo, ProductNo, Quantity)

    VALUES (:invoiceNo, :positionNo, :productNo, :quantity)");

// Process the invoice line items one by one.

foreach ($items as $item) {//2

// Retrieve the values of the current invoice line item

// and convert them to integers.

$positionNo = (int)$item["positionNo"];

$productNo = (int)$item["productNo"];

$quantity = (int)$item["quantity"];

// Simple validity check.

// A quantity less than or equal to 0 must not be saved.

if ($quantity <= 0) {throw new Exception("The quantity

  must be greater than 0 " . "(position $positionNo).");}

// Execute the prepared statement for the current invoice line item.

$stmtItem->execute(["invoiceNo" => $invoiceNo,

  "positionNo" => $positionNo,"productNo" => $productNo,

    "quantity" => $quantity]);

} //2

/*

Successfully complete the transaction. Only after commit() are the

invoice header and all invoice line items permanently saved.

*/

$pdo->commit();

echo "Invoice successfully saved.";

} //1

catch (Exception $e) {//3

/*

Check whether the PDO object exists and whether a transaction is

active. If so, rollBack() reverses all changes made since

beginTransaction().

*/

if (isset($pdo) amp;amp; $pdo->inTransaction()) {

  $pdo->rollBack();

}

// Output an error message.

echo "Error saving the invoice. " . "Everything has been rolled

  back: " . $e->getMessage();

}//3

?>

Keep in mind: Running the program again requires deleting the above calculation.

3.4.4 Program Execution

This is what the result looks like after a successful program run.

3.4.5 Notes

• The values of the invoice line items are explicitly converted to int before they are checked and stored.
• The prepared statement for the invoice line items is prepared only once. Within the loop, it is executed multiple times with different values.
• commit() permanently saves all changes made during the transaction. If an error occurs, rollBack() reverses both the insertion of the invoice header and any invoice line items that have already been inserted.
• inTransaction() checks whether a transaction is currently active. This prevents rollBack() from being called when there is no active transaction.
• Customer number 1004 must already exist in the Customers relation. Likewise, product numbers 100, 101, and 102 must exist in the Products relation. Otherwise, a foreign key constraint prevents the invoice from being saved.
• Invoice number 23011 must not already exist in the InvoiceHeaders relation because InvoiceNo is the primary key.
• To run the program successfully again with the same invoice number, invoice 23011 must first be deleted. Because of the foreign key constraint, the corresponding rows in InvoiceItems must be deleted before the row in InvoiceHeaders.

3.4.6 Program Description

The program saves a complete invoice using PDO and performs the necessary database operations within a transaction. This is necessary because an invoice consists of two related parts: the invoice header in the InvoiceHeaders relation and the invoice line items in the InvoiceItems relation. Both parts must be saved completely. It must not happen that the invoice header is saved while one or more line items are missing because an error occurred.

First, the database connection information is defined in the variables $dsn, $user, and $pass. This is followed by the data for the new invoice header: the invoice number, customer number, and invoice date. The corresponding line items are stored in a multidimensional array. Each inner array represents one line item and contains the position number, product number, and quantity.

This is followed by a try/catch block. In the try block, a PDO object is first created to establish the connection to the database. The setting

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION

causes PDO to throw an exception when a database error occurs. This makes it possible to handle database errors centrally in the catch block.

After the connection has been established, a transaction is started with beginTransaction(). From this point on, the subsequent changes belong to the same transaction. They are not permanently saved until commit() is executed.

Next, a prepared statement is created for the invoice header. The SQL statement contains the named placeholders :invoiceNo, :customerNo, and :invoiceDate. The corresponding values are passed separately when execute() is called. A second prepared statement is then created for the invoice line items. This statement is prepared only once and is reused for each line item. The foreach loop processes the elements of the $items array one by one. Each inner array contains the position number, product number, and quantity of one invoice line item.

Before a line item is inserted, a simple validity check is performed: the quantity must be greater than 0. If this is not the case, an exception is thrown using throw new Exception(...). Normal execution of the try block stops immediately, and program execution continues in the catch block.

If the invoice header and all invoice line items have been inserted successfully, commit() completes the transaction. The changes made during the transaction are then permanently saved in the database.

If an error occurs anywhere after the transaction has started, the catch block is executed. If a transaction is still active, rollBack() reverses all changes made since beginTransaction(). This is the essential advantage of the transaction in this example: the complete invoice is saved, or none of it is saved. This keeps the database in a consistent state.

4 Deleting Data

Here, we’ll first demonstrate deletion in a single relation (Customers) and then deletion across multiple related tables (InvoiceHeaeders and InvoiceItems). First using MySQLi, then using PDO.

4.1 DeleteCustomerMySQLi.php

4.1.1 Program Features:

• Class used for establishing a connection: mysqli
• Deleting from a single relatioon (Customers)
• Security through prepared statements
• Launched via a browser
• HTML output of the result

To follow along with the example, first create a customer who is not referenced by an invoice header, for example using the program InsertCustomerMySQLi.php. This customer can then be deleted using the program presented here.

4.1.2 The program

<?php

// Variable for feedback to the user.

// It stores success or error messages,

// which are later displayed in the HTML section.

$message = "";

// Data for establishing a connection.

$host = "localhost";

$user = "root";

$pass = "";

$db = "Invoices";

// Check whether the form was submitted using the HTTP POST method

//.

if ($_SERVER["REQUEST_METHOD"] === "POST") {

// Retrieve the customer number from the form.

// The ?? operator returns 0 if the form entry is missing.

// (int) converts the retrieved value to an integer.

$customerNo = (int)($_POST["customerNo"] ?? 0);

// Validate the input. The customer number must be greater than 0.

if ($customerNo <= 0) {

  $message = "Please enter a valid customer number.";

  } else {

// Establish a connection to the database.

// The variable $con refers to the created mysqli object.

$con = new mysqli($host, $user, $pass, $db);

// Check if an error occurred while establishing the connection.

// die() outputs the message and terminates the program.

  if ($con->connect_error) {die("DB connection failed: "

    . $con->connect_error

  );

}

// Set the character set for the database connection,

// so that special characters are processed correctly.

$con->set_charset("utf8mb4");

// Prepare the SQL statement.

// The question mark serves as a placeholder for the customer number.

    $stmt = $con->prepare("DELETE FROM customers WHERE customerNo = ?");

// Check whether the prepared statement could be created.

if (!$stmt) {

  $message = "Prepare failed: " . $con->error;

} else {

// Bind the customer number to the placeholder.

// "i" denotes the integer data type.

$stmt->bind_param("i", $customerNo);

// Execute the prepared statement.

if ($stmt->execute()) {

// affected_rows contains the number of rows deleted by the

// DELETE statement.

  if ($stmt->affected_rows > 0) {

    $message = "Customer was deleted.";

    } else {$message = "No customer with this number was found: "

      . "customer number.";

    }

  } else {$message = "Error deleting customer: " . $stmt->error;

  }

// Close the prepared statement and release the

// associated resources.

  $stmt->close();

}

// Close the database connection and release the associated

// resources.

  $con->close();

  }

}

?>

<!doctype html>

<html lang="en">

<head>

<meta charset="utf-8">

<title>Delete a customer using MySQLi</title>

  <style>

    body {font-family: Arial, sans-serif;}

  </style>

</head>

<body>

<h2>Delete Customer (MySQLi)</h2>

<!--

Form for entering the customer number.

method="post" specifies that the form data is to be submitted

using the POST HTTP method. Since no action attribute

is specified, the data is sent to the PHP file that submitted

the form.

-->

<form method="post">

<label for="customerNo">Customer Number:</label>

    <input type="number" id="customerNo" name="customerNo" min="1" required>

<button type="submit">Delete</button>

</form>

<!--

For more information on htmlspecialchars(), see the appendix.

-->

<?php if ($message !== ""): ?>

  <p><strong>

  <?php echo htmlspecialchars($message, ENT_QUOTES, "UTF-8"); ?>

  </strong>

  </p>

<?php endif; ?>

</body>

</html>

4.1.3 Program Execution

Important: Enter the customer number of a customer who is not referenced by an invoice header. For example, use the InsertCustomerMySQLi.php program presented earlier to add a new customer. Then use the program presented here to delete that customer.

The input form looks like this.

4.2 DeleteInvoiceMySQLi.php

4.2.1 Program Features

• Class used for establishing a connection: mysqli
• Deleting data from multiple related relations (InvoiceHeaders and InvoiceItems)
• Transaction
• Prepared statements
• Input via an HTML form
• Launched via a browser
• HTML output of the result

4.2.2 Task

An invoice is deleted from the Invoices database. This means that both the invoice header and all associated invoice line items must be deleted. These delete operations form a single unit of work and are therefore performed within a transaction. Either the complete invoice is deleted, including all its line items, or none of the data is deleted.

Transaction

This is demonstrated by the program DeleteInvoiceMySQLi.php. The invoice number is entered using an HTML form. The following example uses invoice number 23001, which belongs to customer number 1002 and contains several invoice line items.

SELECT ih.InvoiceNo, ih.CustomerNo, ih.InvoiceDate, ii.PositionNo,

  ii.ProductNo, ii.Quantity, c.LastName, c.FirstName

  FROM InvoiceHeaders AS ih

    INNER JOIN Customers AS c ON ih.CustomerNo = c.CustomerNo

    INNER JOIN InvoiceItems AS ii ON ih.InvoiceNo = ii.InvoiceNo

    WHERE ih.InvoiceNo = 23001

    ORDER BY ii.PositionNo;

4.2.3 The program

<?php

// Data for establishing the connection.

$host = "localhost";

$user = "root";

$pass = "";

$db = "Invoices";

// Variable for feedback to the user.

$message = "";

// Establish a connection to the database.

// The variable $con refers to the created mysqli object.

$con = new mysqli($host, $user, $pass, $db);

// Check if an error occurred while establishing the connection.

// die() outputs the message and terminates the program.

if ($con->connect_error) {

  die("DB connection failed: " . $con->connect_error);

}

// Set the character set for the database connection.

$con->set_charset("utf8mb4");

// Check whether the form was submitted using the POST HTTP method.

if ($_SERVER["REQUEST_METHOD"] === "POST") {

// Retrieve the invoice number from the form.

// The ?? operator returns 0 if the form entry is missing.

// (int) converts the retrieved value to an integer.

$invoiceNo = (int)($_POST["invoiceNo"] ?? 0);

// Validate the input. The invoice number must be greater than 0.

if ($invoiceNo <= 0) {

  $message = "Please enter a valid invoice number.";

  } else {

// try/catch block to handle potential errors.

try {

// Start the transaction. $con is the MySQLi connection object.

// All subsequent delete operations form a single unit of work.

$con->begin_transaction();

/*

Check whether an invoice header with the entered invoice number

exists.

$stmtCheck refers to a statement object of the mysqli_stmt class.

The prepare() method creates a prepared statement. The

question mark serves as a placeholder for the invoice number.

*/

$stmtCheck = $con->prepare("SELECT 1 FROM invoiceHeaders

  WHERE InvoiceNo = ?");

// Check whether the prepared statement was created.

    if (!$stmtCheck) {throw new Exception(

    "Prepare failed (check): " . $con->error);

}

// Bind the invoice number to the placeholder.

// "i" denotes the integer data type.

$stmtCheck->bind_param("i", $invoiceNo);

// Execute the prepared statement.

if (!$stmtCheck->execute()) {

  throw new Exception("Execute failed (check): "

    . $stmtCheck->error

  );

}

// Temporarily store the result set so that

// the number of rows found can be determined.

$stmtCheck->store_result();

// Check whether an invoice header was found.

if ($stmtCheck->num_rows === 0) {$stmtCheck->close();

// There is nothing to delete. The transaction is

// ended without any permanent changes.

$con->rollback();

$message = "No invoice with invoice number = $invoiceNo found.";

  } else {

    $stmtCheck->close();

/*

1. Delete the line items associated with the invoice.

The line items must be deleted before the invoice header if

the foreign key was not defined with ON DELETE CASCADE.

*/

    $stmtItemDel = $con->prepare("DELETE FROM InvoiceItems

      WHERE InvoiceNo = ?");

    if (!$stmtItemDel) {throw new Exception("Prepare failed "

      . "(delete invoice items): " . $con->error);

}

$stmtItemDel->bind_param("i", $invoiceNo);

if (!$stmtItemDel->execute()) {throw new Exception(

  "Execute failed " . "(delete invoice items): "

  . $stmtItemDel->error);

}

// Determine the number of deleted invoice line items.

$deletedItems = $stmtItemDel->affected_rows;

$stmtItemDel->close();

// 2. Delete the invoice header.

$stmtHeaderDel = $con->prepare("DELETE FROM invoiceHeaders

  WHERE InvoiceNo = ?");

if (!$stmtHeaderDel) {throw new Exception("Prepare failed "

  . "(delete invoice header): " . $con->error);

}

$stmtHeaderDel->bind_param("i", $invoiceNo);

if (!$stmtHeaderDel->execute()) {throw new Exception(

  "Execute failed" . "(delete invoice header):"

    . $stmtHeaderDel->error);

}

// Determine the number of deleted invoice headers. For a

// unique invoice number, the value should be 1.

$deletedHeader = $stmtHeaderDel->affected_rows;

$stmtHeaderDel->close();

// Commit the transaction successfully. Only now are the

// deletions permanently saved in the database.

$con->commit();

$message = "Invoice No. $invoiceNo deleted "

  . "(Header: $deletedHeader, " . "Items: $deletedItems).";

  }

} catch (Exception $e) {

// In case of an error, undo all changes made since the start of the transaction

// Reversing all changes made since the start of the transaction.

$con->rollback();

$message = "Error deleting. " . "Everything has been reset: "

    . $e->getMessage();

    }

  }

}

?>

<!doctype html>

<html lang="en">

<head>

  <meta charset="utf-8">

  <title>Invoices - Delete Invoice</title>

    <style>body {font-family: Arial, sans-serif;}</style>

</head>

<body>

<h2>Delete Invoice (MySQLi)</h2>

<!--

Form for entering the invoice number. Since no action attribute is

specified, the data is sent to the calling PHP file.

-->

<form method="post">

  <label for="invoiceNo">Invoice Number (InvoiceNo):</label>

  <input type="number" id="invoiceNo" name="invoiceNo" min="1"

    required>

  <button type="submit">Delete</button>

</form>

<!--

If feedback is available, it is displayed in bold. For more on

htmlspecialchars(), see the appendix.

-->

<?php if ($message !== ""): ?>

  <p><strong>

  <?php echo htmlspecialchars($message, ENT_QUOTES, "UTF-8"); ?>

  </strong>

</p>

<?php endif; ?>

</body>

</html>

<?php

// Close the database connection and release the associated

// resources.

$con->close();

?>

4.2.4 Program Execution

This is what the result looks like after a successful program run.

4.2.5 Program Description

The program deletes a complete invoice using MySQLi. A transaction is used to ensure database consistency. In the Invoices database, an invoice consists of two related parts: the invoice header in the InvoiceHeaders relation and the associated invoice line items in the InvoiceItems relation. Both parts must be deleted together.

First, the database connection information is defined and a connection is established using new mysqli(...). If the connection cannot be established, die() outputs an error message and terminates the program. The character set for the connection is then set to utf8mb4. When the form is submitted, the program retrieves the entered invoice number and checks whether it is greater than 0. If the input is valid, a transaction is started using begin_transaction().

Before deleting any data, the program checks whether an invoice with the specified invoice number exists. A prepared statement with a placeholder (?) is used for this purpose. store_result() stores the result so that num_rows can be used to determine whether a matching invoice header was found.

If no invoice exists, the transaction is ended using rollback(), and an appropriate message is displayed. No database data has been changed at this point. If the invoice exists, the deletion is performed in two steps. First, all corresponding rows are deleted from InvoiceItems. This must happen before the invoice header is deleted because the foreign key in InvoiceItems refers to InvoiceHeaders.

The invoice header is then deleted from InvoiceHeaders. The affected_rows property is used to determine how many rows were deleted by each DELETE statement. If both delete operations are successful, commit() completes the transaction. Only then are the deletions permanently stored in the database.

If an error occurs during the transaction, an exception is thrown and execution continues in the catch block. rollback() then reverses all changes made since the transaction was started. The invoice is therefore either deleted completely, including all its line items, or not deleted at all.

4.2.6 Notes on Programming

• $message does not contain user input. It contains feedback that is displayed to the user.
• die() does not simply mean "terminate." It outputs a message and then terminates program execution.
• The execution of $stmtCheck->execute() is also checked for errors.

The HTML element

<input type="number" id="invoiceNo" name="invoiceNo" min="1" required>

creates an input field for a number in an HTML form - in this case, for an invoice number. The individual components have the following meanings:

• <input ...> creates an input field in an HTML form.
• type="number" specifies that a number is to be entered. The browser often provides small arrows for increasing or decreasing the value.
• id="invoiceNo" assigns the unique identifier invoiceNo to the input element. This identifier is used, for example, to associate the input element with a <label> or to access it using CSS or JavaScript.
• name="invoiceNo" specifies the name under which the entered value is sent to the server when the form is submitted. With method="post", PHP can access the value using $_POST["invoiceNo"].
• min="1" specifies the smallest permitted value. Thus, only numbers greater than or equal to 1 are accepted by the browser.
• required specifies that the field must be filled in. The browser will normally not allow the form to be submitted without a valid value.

In summary, the HTML element creates a numeric input field with the ID and name invoiceNo. The entered value must be at least 1. The required attribute makes the field mandatory. When the form is submitted, the entered value is sent to the server under the name invoiceNo.

It is important to distinguish between id and name. The id attribute identifies the element within the web page. The name attribute, on the other hand, specifies the name under which the entered value is transmitted when the form is submitted.

4.3 DeleteCustomerPDO.php

A customer row is deleted.

4.3.1 Program Features

• Class used to establish the connection: PDO
• Launched via browser command
• HTML output of the result

4.3.2 The program

<?php

// Variables for success and error messages.

$message = "";

// Connection details for PDO.

// DSN stands for "Data Source Name." The DSN here contains the host,

// the database name, and the character set used.

$dsn = "mysql:host=localhost;dbname=Invoices;charset=utf8mb4";

$user = "root";

$pass = "";

// Check whether the form was submitted using the POST HTTP method.

if ($_SERVER["REQUEST_METHOD"] === "POST") {

// Retrieve the input value from the form.

// The ?? operator returns 0 if the form entry is missing.

// (int) converts the retrieved value to an integer.

$customerNo = (int)($_POST["customerNo"] ?? 0);

// Validate the input. The customer number must be greater than 0.

if ($customerNo <= 0) {

  $message = "Please enter a valid customer number.";

  } else {

// try/catch block to handle potential database errors.

try {

// Establish a database connection using PDO. The error mode ensures

// PDO to throw an exception in the event of an error.

$pdo = new PDO($dsn, $user, $pass,

  [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

// Prepare the SQL statement as a prepared statement.

// :customerNo is a named placeholder.

$stmt = $pdo->prepare("DELETE FROM Customers WHERE CustomerNo = :customerNo");

// Bind the value of the PHP variable $customerNo to the placeholder

// and execute the prepared SQL statement.

$stmt->execute(["customerNo" => $customerNo]);

// Check whether a row was actually deleted.

// rowCount() returns the number of deleted rows here.

if ($stmt->rowCount() > 0) {$message = "Row was deleted.";

  } else {

// If no row was deleted, there was no customer

// with the entered customer number.

  $message = "No row found with this customer number.";

}

} catch (PDOException $e) {

// Error handling: If an error occurs while establishing a connection or

// executing the SQL statement, an

// error message is generated here.

    $message = "Error: " . $e->getMessage();

    }

  }

}

?>

<!doctype html>

<html lang="en">

<head>

  <meta charset="utf-8">

  <title>Delete a customer using PDO</title>

  <style>body {font-family: Arial, sans-serif;}</style>

</head>

<body>

<h2>Delete Customer (PDO)</h2>

<!--