Editing Data - Part 1

In this article we’re going to take a look at basic form validation of form submitted via PHP. The basis of this is to cover backend validation or server-side validation. This should be key to any web development project and you should always implement server-side validation at a bare minimum. Consideration should be made here before you move to frontend validation, which we will cover later in the series.

Prerequisites

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

Starting point

In order to get started with this task we need some data in our database to work with. In this case we’re going to follow on from the Form Validation tutorial (also part of this section) and we will be looking to edit existing product information. In the database this data is stored in a table called product.

product table example

This table is quite basic we have our primary key (or unique identifier) set as product_id a product name stored in the aptly named product_name field and finally a product_price stored as a decimal data type in MySQL.

$_GET

In PHP we have a bunch of variables known as super global variables, the most common super global variables we will come across are $_POST and $_GET. We’re going to be focussing on $_GET today which will help us to collect information from the website URL (website address in your web browser) and pass that information to our PHP script.

Let’s have a look at how this works using some sample code. Let’s say our website is hosted at example.com and we want to allow a user to provide a page number to move through pages. Our URL might looking something like https://marc.developerspace.co.uk?page=2. On the end of this URL you can clearly see the page parameter. This allows us to pass through some specific information - below is a code sample which will show you how this works in your PHP scripting.

echo $_GET['page'];

product table example

In this case we end up with a simple output of just the number 2 on the screen when we access our website. If we passed page=25 on the end of the URL we would end up with the number 3 showing without any further code changes.

product table example

Essentially this is allowing your user to pass dynamic information into the PHP code and we can make use of this for various reasons. In this case we’re going to look at using this parameter to allow us to select information from the database and show it in a HTML form.

Any parameter

At the moment we’re able to pass a dynamic parameter which we can collect using $_GET. This parameter at the moment is called page but it really doesn’t matter what its called. Let’s have a look at that.

If we access the URL https://marc.developerspace.co.uk?page=2 then we would use the following code.

echo $_GET['page'];

This could just as equally be called product in which case our URL should be https://marc.developerspace.co.uk?product=2 and our code to collect this parameter would be…

echo $_GET['product'];

Give this a go and make sure you’re happy with this concept.

The Query Part

Now that we’re happy that we can pass a parameter no matter what its name, lets continue with the product parameter and look at how we can use this to pull information from our database based on the users selection.

At this point in the course you should be fairly well versed with MySQL queries using the PDO extension for PHP and MySQL. We’re going to adapt some of this code now so that we can pass in our dynamic URL parameter.

Below is the full connection and query code, notice how we’re now passing in the $product_id variable which comes from the URL parameter called product.

// 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("SELECT * FROM product WHERE product_id = $product_id"); 
$record->execute(); 
$single_product = $record->fetch();

This code will simply select a product from our product table in the MySQL Database where the product is handed in using the URL. Here’s what the URL will look like for this example.

product table example

In this example notice that the URL is slightly longer, you should read the guide on website structure, located in Dynamic Web Development if you need familiarise yourself with how URL’s work.

Running this will now generate the following MySQL code dynamically for us, which essentially generates the following MySQL query for us.

SELECT * FROM product WHERE product_id = 1;

Running this will give us absolutely no output onto the screen just yet but we can see that we’re gathering the results of this MySQL query into a variable called $single_product.

Using Query Data

Once you have run the query and collected the data into a variable, or array in this case (entirely depending on your PDO return options) we can make use of this data. In this case we’re going to be using an input form to display the information - this will form the basis of our editing functionality which we will finish in next weeks session.

The code we’re going to use for this is as below. This should be place below any existing PHP and after the closing PHP tag (?>).

<form>
	<input type="hidden" value="<?php echo $single_product['product_id']; ?>" />

	<label for="product_name">Product Name</label>
	<input type="text" name="product_name" value="<?php echo $single_product['product_name']; ?>" />

	<label for="price">Product Price</label>
	<input type="text" name="price" value="<?php echo $single_product['price']; ?>" />
</form>

In this code sample we’re displaying a basic HTML input form which displays the values from the database within the inputs. Let’s have a look at some examples.

product table example

This is a very basic form, but you can see that with the change in the product parameter at the end of the URL, we get the relevant result from our database.

Full Code Snippet

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. We will look at making this more secure over the next few sessions.

<?php

	$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("SELECT * FROM product WHERE product_id = $product_id"); 
	$record->execute(); 
	$single_product = $record->fetch();

?>

<form>

	<input type="hidden" value="<?php echo $single_product['product_id']; ?>" />

	<label for="product_name">Product Name</label>
	<input type="text" name="product_name" value="<?php echo $single_product['product_name']; ?>" />

	<label for="price">Product Price</label>
	<input type="text" name="price" value="<?php echo $single_product['price']; ?>" />

</form>