Copyright Derek O'Reilly, Dundalk Institute of Technology (DkIT), Dundalk, Co. Louth, Ireland.

Databases

We use php to connect our website to a database.

Php Tag

All php code needs to be placed inside <?php and ?> tags, as shown below:

<?php 

// place your php code inside the php tags

?>

echo is used to output data onto the screen, as shown below:

<?php 
echo "Hello World";
?>

Php uses a $ to identify variables. Variables do not need to be defined. They are automatically defined the first time they are used. For example:

<?php
$collegeName = "DkIT";
$numberOfSchools = 4;

echo $collegeName;
?>

Php uses a '.' to concatenate strings, as shown below:

<?php
echo "College: " . $collegeName;
?>

Connnecting to a Database

In order to connect to a database you need the following database details:

Normally, these will be stored as variables in a file called configuration.php, as shown below:

 

configuration.php
<?php
/* * ************************ You need to set the values below to match your project ************************ */

// localhost website and localhost database
$localHostSiteFolderName = "D00123456";

$localhostDatabaseName = "D00123456";
$localHostDatabaseHostAddress = "localhost";
$localHostDatabaseUserName = "root";
$localHostDatabasePassword = "";



// remotely hosted website and remotely hosted database       /* you will need to get the server details below from your host provider */
$serverWebsiteName = "http://mysql02.comp.dkit.ie/D00123456"; /* use this address if hosting website on the college students' website server */

$serverDatabaseName = "D00123456";
$serverDatabaseHostAddress = "mysql02.comp.dkit.ie";         /* use this address if hosting database on the college computing department database server */
$serverDatabaseUserName = "D00123456";
$serverDatabasePassword = "ABCD";




$useLocalHost = true;      /* set to false if your database is NOT hosted on localhost */



/* * ******************************* WARNING                                 ******************************** */
/* * ******************************* Do not modify any code BELOW this point ******************************** */

if ($useLocalHost == true)
{
    $siteName = "http://localhost/" . $localHostSiteFolderName;
    $dbName = $localhostDatabaseName;
    $dbHost = $localHostDatabaseHostAddress;
    $dbUsername = $localHostDatabaseUserName;
    $dbPassword = $localHostDatabasePassword;
}
else  // using remote host
{
    $siteName = $serverWebsiteName;
    $dbName = $serverDatabaseName;
    $dbHost = $serverDatabaseHostAddress;
    $dbUsername = $serverDatabaseUserName;
    $dbPassword = $serverDatabasePassword;
}



chmod("configuration.php", 0600); // do not allow anyone to view this file
?>

 

The configuration.php file must be included in any other file that needs to access the database. This is done using the code below:

<?php
require_once "configuration.php";
?>

Creating a Database

A mySQL database can be created using the phpmyadmin on a server. Alternatively, a database can be created programmatically. The example below creates a the database $dbName from the configuration.php file above:

 

create_database.php
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>PHP Create Database Table Example</title>
</head>
<body>

<?php
/* Include "configuration.php" file */
require_once "configuration.php";


/* Connect to the database */
$dbConnection = new PDO("mysql:host=$dbHost", $dbUsername, $dbPassword);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   // set the PDO error mode to exception



/* Create the database */
$query = "CREATE DATABASE IF NOT EXISTS $dbName";
$statement = $dbConnection->prepare($query);
$statement->execute();



/* Provide feedback to the user */
echo "Database '$dbName' created.";
?>

 

Creating a Table In a database

Each database contains one or more tables. Tables can be created using phpmyadmin or they can be created programmatically. The table below will create a table called 'toyota' that will be included in the database defined in the configuration.php file. In the example below, the table contains the following details (id, model, colour, year, price).

 

create_toyota_table.php
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>PHP Create Database Table Example</title>
</head>
<body>

<?php
/* Include "configuration.php" file */
require_once "configuration.php";



/* Connect to the database */
$dbConnection = new PDO("mysql:host=$dbHost;dbname=$dbName", $dbUsername, $dbPassword);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   // set the PDO error mode to exception



/* If the table already exists, then delete it */
$query = "DROP TABLE IF EXISTS toyota";
$statement = $dbConnection->prepare($query);
$statement->execute();



/* Create table */
$query = "CREATE TABLE toyota (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
                               model VARCHAR(30) NOT NULL,
                               colour VARCHAR(20),
                               year INT,
                               price INT
                              )";
$statement = $dbConnection->prepare($query);
$statement->execute();



/* Provide feedback to the user */
echo "Table 'toyota' created.";
?>

</body>
</html>

General Layout of PHP Database manipulation webpages

In general, we need to do the following six steps in php files that are being used to add, delete or modify database records.

Validate and assign input data
If there is any data being sent from the calling webpage, then this data needs to be validated to ensure that it matches expected data types. This is very important, as databases are commonly attacked by hackers passing malicious data to a php webpage.
Include "configuration.php" file
This contains the website and database details that are needed for the steps below
Connect to the database
Perform query
In order to add, delete or modify a database, you will need to construct a query. This query will normally be based on the input.
Manipulate the query result
Search queries (SELECT) will return a set of results, which will need to be manipulated. Queries for adding (INSERT), deleting (DELETE) or updating (UPDATE) a record will not need to be manipulated.
Provide feedback to the user
Where appropriate, you should provide some feedback to the user.
Provide a link for the user to proceed to a new webpage or automatically redirect to a new webpage
Generally, you need to provide the user with a way to proceed, either by providing a link or by automatically redirecting to another webpage.

The code below shows the typical layout of a php file that is being used to manipulate a database. In all of the examples that follow in these notes, the six steps are clearly marked. If a particular step is not needed in any of the examples, then a comment has been provided to explain why it is not needed.

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Webpage title</title>
</head>
<body>

<?php
/* Validate and assign input data */



/* Include "configuration.php" file */



/* Connect to the database */



/* Perform query */



/* Manipulate the query result */



/* Provide feedback to the user */



/* Provide a link for the user to proceed to a new webpage or automatically redirect to a new webpage */

?>        
</body>
</html>

Data Integrity

The input being passed to a php file can be manipulated to maliciously access and/delete databases. It is important that the input matches the expected input. Two types of f ilter are available to do this.

SANITIZE filters remove all characters that do not match the filter type. SANITIZE filters include:

A full list of SANITIZE filters can be found at this link.

VALIDATE filters are used to ensure that the input is valid. For example, it ensures that the '@' and '.' in an email address appear at the correct place. VALIDATE filters include:

Note: There is no STRING filter.

A full list of VALIDATE filters can be found at this link.

 

In addition to sanitizing and validating input, it is often required that the input does not contain whitespace at either the start or end and is not empty.

An example showing how to validate a string and an integer input is shown below:

$name = ltrim(rtrim(filter_input(INPUT_POST, "name", FILTER_SANITIZE_STRING)));
if (empty($name))
{
    header("location: error.php"); // deal with invalid input
    exit();
}

$age = ltrim(rtrim(filter_input(INPUT_POST, "age", FILTER_SANITIZE_NUMBER_INT)));
if(!isset($age) || !(filter_var($age, FILTER_VALIDATE_INT) !== false))
{
    header("location: error.php"); // deal with invalid input
    exit();
}

 

Note: It a string input is allowed to contain spaces at the start or at the end or if an input can be empty, then the code above will need to be modified, as shown below:

// NOTE: Only use this code if the imput is allowed to be empty

$name = filter_input(INPUT_POST, "name", FILTER_SANITIZE_STRING);

$age = filter_input(INPUT_POST, "age", FILTER_SANITIZE_NUMBER_INT);
if (!isset($age))
   {
      if(!filter_var($age, FILTER_VALIDATE_INT))
      {
         header("location: error.php"); // deal with invalid input
         exit();
      }
}

An example that allows input fields to be empty is shown in the "Search by Model and/or Colour" question further down in this webpage.

Parameter Binding

We need to associate (bind) any variables that are being used in a database query. Binding ensures that only the integrety of the data that is placed into a database. The bind types are:

PDO::PARAM_BOOL
Represents a boolean data type.
PDO::PARAM_NULL
Represents the SQL NULL data type.
PDO::PARAM_INT
Represents the SQL INTEGER data type.
PDO::PARAM_STR
Represents the SQL CHAR, VARCHAR, or other string data type.
PDO::PARAM_LOB
Represents the SQL large object data type.
$statement->bindParam(":model", $model, PDO::PARAM_STR);
$statement->bindParam(":year", $year, PDO::PARAM_INT);

The query, its statment parameter binding and the statement execution are done as per the code below:

$query = "INSERT INTO toyota (model, colour, year, price) VALUES(:model, :colour, :year, :price)";
$statement = $dbConnection->prepare($query);
$statement->bindParam(":model", $model, PDO::PARAM_STR);
$statement->bindParam(":colour", $colour, PDO::PARAM_STR);
$statement->bindParam(":year", $year, PDO::PARAM_INT);
$statement->bindParam(":price", $price, PDO::PARAM_INT);
$statement->execute();

Adding a Record to a database Table

In order to add (insert) a record into a database, we need to create two seperate files. The first file allows us to enter the details of the new record. This information will be passed to the second file. The second file will add (insert) the new record into the database.

First File (enter details of new record)

insert.php
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Add a new record to a database</title>

</head>
<body>
<form action="insert_transaction.php" method="post">
<label for="model">Model: </label>
<input type="text" id = "model" name = "model" required autofocus><br>
<label for="colour">Colour: </label>
<input type="text" id = "colour" name = "colour" required><br>
<label for="year">Year: </label>
<input type="number" id = "year" name = "year" min = "1990"><br>
<label for="price">Price: </label>
<input type="number" id = "price" name = "price" required><br>            
<input type="submit" value="Add Record">
</form>

<script>
// Set the current year and the maximum year to be the current year 
let currentYear = new Date();
document.getElementById("year").value = currentYear.getFullYear();
document.getElementById("year").max = currentYear.getFullYear();
</script>
</body>
</html>

 

Second File (add the new record to the database)

insert_transaction.php
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>PHP Insert Example</title>
</head>
<body>

<?php
/* Validate and assign input data */
$model = ltrim(rtrim(filter_input(INPUT_POST, "model", FILTER_SANITIZE_STRING)));
if (empty($model))
{
    header("location: insert.php"); // deal with invalid input
    exit();
}

$colour = ltrim(rtrim(filter_input(INPUT_POST, "colour", FILTER_SANITIZE_STRING)));
if (empty($colour))
{
    header("location: insert.php"); // deal with invalid input
    exit();
}

$year = ltrim(rtrim(filter_input(INPUT_POST, "year", FILTER_SANITIZE_NUMBER_INT)));
if ((empty($year)) || (!filter_var($year, FILTER_VALIDATE_INT)))
{
    header("location: insert.php"); // deal with invalid input
    exit();
}

$price = ltrim(rtrim(filter_input(INPUT_POST, "price", FILTER_SANITIZE_NUMBER_INT)));
if ((empty($price)) || (!filter_var($price, FILTER_VALIDATE_INT))) // deal with invalid input
{
    header("location: insert.php");
    exit();
}



/* Include "configuration.php" file */
require_once "configuration.php";



/* Connect to the database */
$dbConnection = new PDO("mysql:host=$dbHost;dbname=$dbName", $dbUsername, $dbPassword);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   // set the PDO error mode to exception



/* Perform Query */
$query = "INSERT INTO toyota (model, colour, year, price) VALUES(:model, :colour, :year, :price)";
$statement = $dbConnection->prepare($query);
$statement->bindParam(":model", $model, PDO::PARAM_STR);
$statement->bindParam(":colour", $colour, PDO::PARAM_STR);
$statement->bindParam(":year", $year, PDO::PARAM_INT);
$statement->bindParam(":price", $price, PDO::PARAM_INT);
$statement->execute();



/* Provide feedback that the record has been added */
if ($statement->rowCount() > 0)
{
    echo "<p>Record successfully added to database.</p>";
}
else
{
    echo "<p>Record not added to database.</p>";
}



/* Provide a link for the user to proceed to a new webpage or automatically redirect to a new webpage */
echo "<a href=" . $siteName . "/insert.php>Click here to add another record</>";
?>

</body>
</html>

Displaying all Records in a Database

The example below reads all of the records from a database.

display_all_records_unformatted.php
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Unformatted PHP Display All records Example</title>
</head>
<body>
<?php
/* Validate and assign input data */
/* As displaying all records does not require any input from the calling webpage, we do not need any input values */ 



/* Include "configuration.php" file */
require_once "configuration.php";



/* Connect to the database */
$dbConnection = new PDO("mysql:host=$dbHost;dbname=$dbName", $dbUsername, $dbPassword);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   // set the PDO error mode to exception



/* Perform Query */
$query = "SELECT id, model, colour, year, price FROM toyota";
$statement = $dbConnection->prepare($query);
$statement->execute();



/* Manipulate the query result */
$result = $statement->fetchAll(PDO::FETCH_OBJ);
foreach($result as $row) 
{
    echo $row->model . $row->colour . $row->year . $row->price . $row->id;
}
?> 
</body>
</html>

 

To output the table details in a readable way, we can echo appropriate HTML code from within the php.

display_all_records.php
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Unformatted PHP Display All records Example</title>
</head>
<body>
    <?php
    /* Validate and assign input data */
    /* As displaying all records does not require any input from the calling webpage, we do not need any input values */


    /* Include "configuration.php" file */
    require_once "configuration.php";



    /* Connect to the database */
    $dbConnection = new PDO("mysql:host=$dbHost;dbname=$dbName", $dbUsername, $dbPassword);
    $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   // set the PDO error mode to exception



    /* Perform Query */
    $query = "SELECT id, model, colour, year, price FROM toyota";
    $statement = $dbConnection->prepare($query);
    $statement->execute();

    
    /* Manipulate the query result */
    if ($statement->rowCount() > 0)
    {
        echo "<table>";
        $result = $statement->fetchAll(PDO::FETCH_OBJ);
        foreach ($result as $row)
        {
            echo "<tr>";
            echo "<td>" . $row->model . "</td><td>" . $row->colour . "</td><td>" . $row->year . "</td><td>" . $row->price . "</td><td>" . $row->id . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
    echo "<p>" . $statement->rowCount() . " records found.</p>";
    ?> 
</body>
</html>

Write CSS code to improve the look of the output table in the example above.

Searching a database

In order to search a database, we need to create two seperate files. The first file allows us to enter a search term. This information will be passed to the second file. The second file will perform the search and output the results of the search.

First File (enter search term)

search.php
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Search a Database</title>
</head>

<body>
<form action="search_transaction.php" method="post">
<label for="model">Model: </label>
<input type="text" id = "model" name = "model" required autofocus><br>
<input type="submit" value="Search">
</form>
</body>
</html>

 

Second File (perform the search on the database)

search_transaction.php
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Php Prepared Statement Example</title>
</head>
<body>

<?php
/* Validate and assign input data */
$model = ltrim(rtrim(filter_input(INPUT_POST, "model", FILTER_SANITIZE_STRING)));
if (empty($model))
{
    header("location: search.php"); // deal with invalid input
    exit();
}



/* Include "configuration.php" file */
require_once "configuration.php";



/* Connect to the database */
$dbConnection = new PDO("mysql:host=$dbHost;dbname=$dbName", $dbUsername, $dbPassword);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   // set the PDO error mode to exception



/* Perform Query */
$query = "SELECT id, model, colour, year, price FROM toyota WHERE model = :model";
$statement = $dbConnection->prepare($query);
$statement->bindParam(":model", $model, PDO::PARAM_STR);
$statement->execute();



/* Manipulate the query result */
if ($statement->rowCount() > 0)
    {
        echo "<table>";
        $result = $statement->fetchAll(PDO::FETCH_OBJ);
        foreach ($result as $row)
        {
            echo "<tr>";
            echo "<td>" . $row->model . "</td><td>" . $row->colour . "</td><td>" . $row->year . "</td><td>" . $row->price . "</td><td>" . $row->id . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
echo "<p>". $statement->rowCount() . " records found.</p>";



/* Provide a link for the user to proceed to a new webpage or automatically redirect to a new webpage */
/* We could provide a link to search.php using the code below */
echo "<a href='search.php'>Perform another search</a>";
?>        
</body>
</html>

Write code to perform a search that allows a user to select the model and/or the colour of a car. Your code should allow empty input values for model or colour as meaning all models or all colours.

Solution php files for the above question:

search_colour_and_model.php 
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Advanced Search of a database</title>
</head>
<body>
<form action="search_colour_and_model_transaction.php" method="post">
<label for="model">Model: </label>
<input type="text" id = "model" name = "model" autofocus><br>

<label for="colour">Colour: </label>
<input type="text" id = "colour" name = "colour"><br>

<input type="submit" value="Search">
</form>
</body>
</html>


search_colour_and_model_transaction.php 
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Php Advanced Search Example</title>
</head>
<body>

<?php
/* Validate and assign input data */
/* Note that, in this example, both model and colour are allowed to be empty */
$model = filter_input(INPUT_POST, "model", FILTER_SANITIZE_STRING);
$colour = filter_input(INPUT_POST, "colour", FILTER_SANITIZE_STRING);



/* Include "configuration.php" file */
require_once "configuration.php";



/* Connect to the database */
$dbConnection = new PDO("mysql:host=$dbHost;dbname=$dbName", $dbUsername, $dbPassword);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   // set the PDO error mode to exception



/* Perform Query */
$query = "SELECT id, model, colour, year, price FROM toyota WHERE ";

/* Deal with empty and not empty cases for input variable */
if (empty($model)) 
{
    $query .= "model != :model";
}
else 
{
    $query .= "model = :model";
}

$query .= " AND ";

/* Deal with empty and not empty cases for input variable */
if (empty($colour)) 
{
    $query .= "colour != :colour";
} 
else 
{
    $query .= "colour = :colour";
}

$statement = $dbConnection->prepare($query);
$statement->bindParam(":model", $model, PDO::PARAM_STR);
$statement->bindParam(":colour", $colour, PDO::PARAM_STR);
$statement->execute();


/* Manipulate the query result */
if ($statement->rowCount() > 0)
{
    echo "<table>";
    $result = $statement->fetchAll(PDO::FETCH_OBJ);
    foreach ($result as $row)
    {
        echo "<tr>";
        echo "<td>" . $row->model . "</td><td>" . $row->colour . "</td><td>" . $row->year . "</td><td>" . $row->price . "</td><td>" . $row->id . "</td>";
        echo "</tr>";
    }
    echo "</table>";
}
echo "<p>" . $statement->rowCount() . " records found.</p>";



/* Provide a link for the user to proceed to a new webpage or automatically redirect to a new webpage */
echo "<a href='search_colour_and_model.php'>Perform another search</a>";
?>        
</body>
</html>

Modifying a Record in a database

In order to modify (update) a database, we need to create two seperate files. The first file allows us to enter the new details of the record. This information will be passed to the second file. The second file will modify (update) the record in the database.

First File (enter the new details for the record)

update.php
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Update database example</title>
<script>    function getURLValue(name)
    {
        name = name.replace(/[\[]/, "\\\[").replace(/[\]]/, "\\\]");
        let regexS = "[\\?&]" + name + "=([^&#]*)";
        let regex = new RegExp(regexS);
        let results = regex.exec(window.location.href);
        if (results === null)
        {
            return null;
        }
        else
        {
            return results[1];
        }
    }
</script>
</head>

<body>
<form action="update_transaction.php" method="post">
<label for="id">Id: </label>
<input type="text" id = "id" name = "id" required><br>
<label for="model">Model: </label>
<input type="text" id = "model" name = "model" required autofocus><br>
<label for="colour">Colour: </label>
<input type="text" id = "colour" name = "colour" required><br>
<label for="year">Year: </label>
<input type="number" id = "year" name = "year" min = "1990"><br>
<label for="price">Price: </label>
<input type="number" id = "price" name = "price" required><br>            
<input type="submit" value="Update Record">
</form>

<script>
    if (getURLValue('id') !== null)
    {
        document.getElementById('id').value = getURLValue('id');
        document.getElementById('model').value = getURLValue('model');
        document.getElementById('colour').value = getURLValue('colour');
        document.getElementById('year').value = getURLValue('year');
        document.getElementById('price').value = getURLValue('price');
    }
    
    // Set the current year and the maximum year to be the current year
    let currentYear = new Date();
    document.getElementById("year").value = currentYear.getFullYear();
    document.getElementById("year").max = currentYear.getFullYear();
</script>

</body>
</html>

 

Second File (update the record in the database)

update_transaction.php
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>PHP Insert Example</title>
</head>
<body>

<?php
/* Validate and assign input data */
$id = ltrim(rtrim(filter_input(INPUT_POST, "id", FILTER_SANITIZE_NUMBER_INT)));
if ((empty($id)) || (!filter_var($id, FILTER_VALIDATE_INT)))
{
    header("location: update.php"); // deal with invalid input
    exit();
}

$model = ltrim(rtrim(filter_input(INPUT_POST, "model", FILTER_SANITIZE_STRING)));
if (empty($model))
{
    header("location: update.php"); // deal with invalid input
    exit();
}

$colour = ltrim(rtrim(filter_input(INPUT_POST, "colour", FILTER_SANITIZE_STRING)));
if (empty($colour))
{
    header("location: update.php"); // deal with invalid input
    exit();
}

$year = ltrim(rtrim(filter_input(INPUT_POST, "year", FILTER_SANITIZE_NUMBER_INT)));
if ((empty($year)) || (!filter_var($year, FILTER_VALIDATE_INT))) // deal with invalid input
{
    header("location: update.php");
    exit();
}

$price = ltrim(rtrim(filter_input(INPUT_POST, "price", FILTER_SANITIZE_NUMBER_INT)));
if ((empty($price)) || (!filter_var($price, FILTER_VALIDATE_INT))) // deal with invalid input
{
    header("location: update.php");
    exit();
}



/* Include "configuration.php" file */
require_once "configuration.php";



/* Connect to the database */
$dbConnection = new PDO("mysql:host=$dbHost;dbname=$dbName", $dbUsername, $dbPassword);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   // set the PDO error mode to exception



/* Perform query */
$query = "UPDATE toyota SET model = :model, colour = :colour, year = :year, price = :price WHERE id = :id";
$statement = $dbConnection->prepare($query);
$statement->bindParam(":model", $model, PDO::PARAM_STR);
$statement->bindParam(":colour", $colour, PDO::PARAM_STR);
$statement->bindParam(":year", $year, PDO::PARAM_INT);
$statement->bindParam(":price", $price, PDO::PARAM_INT);
$statement->bindParam(":id", $id, PDO::PARAM_INT);
$statement->execute();



/* Provide feedback that the record has been modified */
if ($statement->rowCount() > 0)
{
    echo "<p>Record successfully modified.</p>";   
}
else
{
    echo "<p>Record not updated. Either the record does not exist or no changes were made to its details.</p>";
}



/* Provide a link for the user to proceed to a new webpage or automatically redirect to a new webpage */
echo "<a href=" . $siteName . "/update.php>Update another record</>";
?>        
</body>
</html>

Deleting a Record From a database

In order to delete a record from a database, we need to create two seperate files. The first file allows us to select the record that is to be deleted. This information will be passed to the second file. The second file will delete the record from the database.

First File (select the record to delete)

delete.php
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Delete a record from a database</title>
<script>
    function getURLValue(name)
    {
        name = name.replace(/[\[]/, "\\\[").replace(/[\]]/, "\\\]");
        let regexS = "[\\?&]" + name + "=([^&#]*)";
        let regex = new RegExp(regexS);
        let results = regex.exec(window.location.href);
        if (results === null)
        {
            return null;
        }
        else
        {
            return results[1];
        }
    }
</script>
</head>

<body>
<form action="delete_transaction.php" method="post">
<label for="id">Id:  </label>
<input type="text" id = "id" name = "id" required><br>
<input type="submit" value="Delete Record">
</form>

<script>
    if (getURLValue('id') !== null)
    {
        document.getElementById('id').value = getURLValue('id');
    }
</script>

</body>
</html>

 

Second File (delete the record from the database)

delete_transaction.php
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>PHP Insert Example</title>
</head>
<body>

<?php
/* Validate and assign input data */
$id = ltrim(rtrim(filter_input(INPUT_POST, "id", FILTER_SANITIZE_NUMBER_INT)));
if ((empty($id)) || (!filter_var($id, FILTER_VALIDATE_INT)))
{
    header("location: delete.php"); // deal with invalid input
    exit();
}


/* Include "configuration.php" file */
require_once "configuration.php";



/* Connect to the database */
$dbConnection = new PDO("mysql:host=$dbHost;dbname=$dbName", $dbUsername, $dbPassword);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   // set the PDO error mode to exception



/* Perform query */
$query = "DELETE FROM toyota WHERE id = :id";
$statement = $dbConnection->prepare($query);
$statement->bindParam(":id", $id, PDO::PARAM_INT);
$statement->execute();



/* Provide feedback that the record has been deleted */
if ($statement->rowCount() > 0)
{
    echo "<p>Record successfully deleted.</p>";   
}
else
{
    echo "<p>Record does not exist, so it cannot be deleted.</p>";
}



/* Provide a link for the user to proceed to a new webpage or automatically redirect to a new webpage */
echo "<a href=" . $siteName . "/delete.php>Click here to delete another record</>";
?>        
</body>
</html>

Click here to download a .zip file containing the basic set of files that were presented above.

Administrator Control Panel

Note that setting the $isAdministrator flag to "false" will hide the "edit", "delete" and "Add New Record" options.

 

display_all_records.php
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Formatted PHP Display Records Example</title>
</head>
<body>

<?php
/* Validate and assign input data */
/* As displaying all records does not require any input from the calling webpage, we do not need any input values */


/* Set $isAdministrator to true to turn on the administrator functionality */
/* Set to false to turn off */
$isAdministrator = true; // set to false if this is not the administrator



/* Include "configuration.php" file */
require_once "configuration.php";



/* Connect to the database */
$dbConnection = new PDO("mysql:host=$dbHost;dbname=$dbName", $dbUsername, $dbPassword);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   // set the PDO error mode to exception



/* Perform query */
$query = "SELECT id, model, colour, year, price FROM toyota";
$statement = $dbConnection->prepare($query);
$statement->execute();



/* Manipulate the query result */
if ($statement->rowCount() > 0)
{
    echo "<table>";
    $result = $statement->fetchAll(PDO::FETCH_OBJ);
    foreach ($result as $row)
    {
        echo "<tr>";
        echo "<td>" . $row->model . "</td><td>" . $row->colour . "</td><td>" . $row->year . "</td><td>" . $row->price . "</td><td>" . $row->id . "</td>";
        if ($isAdministrator)
        {
            echo "<td><a href='update.php?id=" . $row->id .
            "&model=" . $row->model .
            "&colour=" . $row->colour .
            "&year=" . $row->year .
            "&price=" . $row->price . "'>edit</a></td>" .
            "<td><a href='delete.php?id=" . $row->id . "'>delete</a></td>";
        }
        echo "</tr>";
    }
    echo "</table>";
}
echo "<p>" . $statement->rowCount() . " records found.</p>";



/* Provide a link for the user to proceed to a new webpage or automatically redirect to a new webpage */
if ($isAdministrator)
{
    echo "<form action='insert.php'>";
    echo "<input type ='submit' value ='Add New Record'>";
    echo "</form>";
}
?> 
</body>
</html>

Click here to download a .zip file containing the basic control panel presented above.

In the above example, the delete redirects the user to 'delete.php'. This requires that the user confirms the id before the record is deleted. The confirmation step can be skipped if the user is sent to 'delete_transaction.php' when they click on a record's 'delete' link. Write code to do this. HINT: To be able POST the id, you will need to have a form that holds the id. You will need to write javascript code to do this.

Solution php files for the above question:
Adjust the code in delete.php, so that it automatically submits the form if and id is passed as a parameter in the url. 

delete.php
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Delete a record from a database</title>
<script>
    function getURLValue(name)
    {
        name = name.replace(/[\[]/, "\\\[").replace(/[\]]/, "\\\]");
        let regexS = "[\\?&]" + name + "=([^&#]*)";
        let regex = new RegExp(regexS);
        let results = regex.exec(window.location.href);
        if (results === null)
        {
            return null;
        }
        else
        {
            return results[1];
        }
    }
</script>
</head>

<body>
<form id="deleteRecord" action="delete_transaction.php" method="post">
<label for="id">Id:  </label>
<input type="text" id = "id" name = "id" required><br>
<input type="submit" value="Delete Record">
</form>

<script>
    if (getURLValue('id') !== null)
    {
        document.getElementById('id').value = getURLValue('id');
        document.getElementById('deleteRecord').submit(); // automatically submit the form if id is a parameter in the url
    }
</script>

</body>
</html>

In the above code, the user is redirected to "update.php" after editing a new record, "delete.php" after deleting a record and "insert.php" after adding a new record. It is better that the user is sent back to the control panel (i.e. "display_all_records.php" after editing, deleting or adding a new record. Amend the "update_transaction.php", "delete_transaction.php" and "insert_transaction.php" files to do this.

Solution for the above question:

The three files that need to be changed are:
insert_transaction.php
update_transaction.php
delete_transaction.php


In all three files, we need to amend the code that is used to provide the user with a link to move to a new page with an automatic redirect to "display_all_records.php" 
To do this, we need to replace the code below that occurs in the three files:

In "insert_transaction.php" we need to replace the line of code
echo "<a href=" . $siteName . "/insert.php>Click here to add another record</>";

In "update_transaction.php" we need to replace the line of code
echo "<a href=" . $siteName . "/update.php>Update another record</>";

In "delete_transaction.php" we need to replace the line of code
echo "<a href=" . $siteName . "/delete.php>Click here to delete another record</>";


The replacement code for all three files is the same. The replacement code is:
header("location: " . $siteName . "/display_all_records.php");

This code will automatically redirect the user back to the "display_all_records.php" file.




You should set the 'id' input to readonly in the update.php file, as shown below:
<input type="text" id = "id" name = "id" required readonly><br>

Click here to download a .zip file containing the advanced control panel presented above.

 

Copyright Derek O' Reilly, Dundalk Institute of Technology (DkIT), Dundalk, Co. Louth, Ireland.

 
<div align="center"><a href="../versionC/index.html" title="DKIT Lecture notes homepage for Derek O&#39; Reilly, Dundalk Institute of Technology (DKIT), Dundalk, County Louth, Ireland. Copyright Derek O&#39; Reilly, DKIT." target="_parent" style='font-size:0;color:white;background-color:white'>&nbsp;</a></div>