In this article we’re going to take a look at removing information from the database using a basic PHP script. Before you can continue with this guide you should have already covered the pages above in the Vanilla PHP Web App section.
Before we get started you will need the following in place
A working web server
Access to a MySQL database
Tables in your database which contain data
You should have existing knowledge of the $_GET parameter
We first of all need to make sure that we have access to the database, in order to do this we will need to make an active connection to the database which holds our data. We have seen this before in this series and it looks like below.
$servername = "hostdomain.co.uk";
$username = "database_username";
$password = "database_password";
$database = "database_name";
// attemp to connect to the server
$conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
Following this we use a $_GET parameter to dynamically select a product_id. This will later be used to influence an SQL query which will allow us to remove data from the database. This can just be placed directly below the database connection code and looks like this…
// select a product_id to remove from the database
$product_id = $_GET['product'];
It’s important that we recognise here that this isn’t validated in any way, shape or form here and therefore this could leave your database and web application wide open to SQL Injection and XSS style attacks amongst others. You should review the OWASP Top 10 to keep on top of current web application attack types and trends at regular intervals throughout your career and keep on top of the requirements. This series has covered basic validation however you should expand your knowledge in this area to keep you and your customers data as safe as possible.
At the moment we have allowed the user to select a product based on their input URL, for example this could look like any of the following (where remove.php is the name of this PHP file and product = is followed by a product_id)…
Now that we have the product_id in a dynamic, influenceable manner we need to use this information to carry out an SQL command which will remove the data from the database.
In order to now remove the data from the database we need to run an SQL statement, passing in our $product_id variable which we collected from a GET parameter above. Again we have seen SQL queries using PDO in PHP already in this series, notice in this case we’re passing in the $product_id variable.
$record = $conn->prepare("DELETE FROM product WHERE product_id = $product_id");
$record->execute();
When your PHP script reaches this statement it will remove the data from the database and you should instantly see this reflected in your database itself when using an IDE such as MySQL Workbench. However your user will not see this change, it won’t be as instant for them and so we have to handle this from the point of view of the web application itself. The easiest way to achieve this is to redirect the user back to a list of products from your application (in this example), this will show the product list again, however, this time the product selected will be removed.
Again we have seen an example of a header redirect in PHP already, however below is a snippet to show you how you can achieve this.
// redirect back to the product list page
header("Location: /admin/product/list.php");
And that’s it.
Below is the full code snippet for this article so you can see the full structure. Remember you can use this for different things, and there are some fundamental flaws, particularly around security with using this method.
$servername = "hostdomain.co.uk";
$username = "database_username";
$password = "database_password";
$database = "database_name";
// attemp to connect to the server
$conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// select a product_id
$product_id = $_GET['product'];
$record = $conn->prepare("DELETE FROM product WHERE product_id = $product_id");
$record->execute();
header("Location: /admin/product/list.php");