Connecting to a MySQL Database Part 5

The Finished Page

In part 5 we are going to create our insert.php file, which if you remember from part 3, is the file that the form, Add Clients, sends its information to. So let’s dive straight in.

Again let’s start by creating a copy of our template.html file, but this time save it as insert.php. Once this is done we can add the php code required to process, sanitize and insert the values collected by our form.

Between the opening and closing contianer div insert a opening and closing php tag <?php ……. ?>. Next, below the opening php tag add a couple of empty lines for readability and then add the following code:-

// connecting to the database
include 'dbconnection.php;

Again if you remember from part 2, this establishes a connection to the database.

Next we are going to create some empty variables ready to accept our form values, so add the following line of code.

// define variables and set to empty values
$business = $firstName = $lastName = $raw_email = $telephone = " ";

Now we are going to assign values to those variables and do some basic sanitizing, so enter the following code and then I will explain what is going on.

// Note* This is basic sanitizing but you can be more careful with this.

if ($_SERVER["REQUEST_METHOD"] == "POST") {
  $business = test_input($_POST['business']);
  $firstName = test_input($_POST['firstName']);
  $lastName = test_input($_POST['lastName']);
  $raw_email = test_input($_POST['email']);
  $telephone = test_input($_POST['telephone']);
}

function test_input($data) {
     $data = trim($data);
     $data = stripslashes($data);
     $data = htmlspecialchars($data);
     return $data;
   }

$email = filter_var($raw_email, FILTER_VALIDATE_EMAIL);

In the above code, we firstly create an if statement which checks that the request method used to access the page is equal to POST. If it is, it then assigns the contents of each named input to the corresponding empty variable. For example the input from our form with the name ‘business’ gets assigned to the php variable $business, and so on.

But before we assign them, we are doing some basic sanitizing by running the input data through our function – test_input. This function takes the inputs in turn as a parameter and strips unnecessary characters (extra space, tab, newline) from the user input data (with the PHP trim() function)

Then removes backslashes \ from the user input data (with the PHP stripslashes() function)

After that it runs the htmlspecialchars() function. This function saves any HTML code as HTML escaped code. You can read more about this here. Finally we return the $data.

You may have noticed we assigned the input ’email’ to the php variable $raw_email. That is because we are going to do one more check before assigning it to $email. That check is to use php’s filter_var function to check that $raw_email is a well-formed email address using php’s FILTER_VALIDATE_EMAIL Filter. This filter validates whether the value is a valid e-mail address.

The next bit of code is optional, but i have included it to show you what we have collected.

   echo "<br>";
   echo $business;
   echo "<br>";
   echo $firstName;
   echo "<br>";
   echo $lastName;
   echo "<br>";
   echo $email;
   echo "<br>";
   echo $telephone;
   echo "<br>";
   echo "<br>";

This will echoed out on our insert.php file whenever someone fills out the form and submits it.

Now we are going to actually insert the data into our database.

//insert some data
$query = "INSERT INTO clients_tbl (id, business, firstName, lastName, email, telephone) 
VALUES 
(NULL,'$business', '$firstName', '$lastName', '$email', '$telephone')";

$run_query = mysqli_query($connection, $query);

if($run_query){
      echo "Data has been inserted in your database";
    }else{
    echo "Data could not be inserted " . mysqli_error($connection);   }

mysqli_close($connection);

echo "<br> <br>";

Let’s take a look at what is happening:

First of all we have created a variable called $query and assigned it the SQL statement INSERT INTO, which requires the table name in this case clients_tbl and the columns we are inserting into, followed by the values, Which are NULL for the id Column as this is the primary key and is auto incremented and created for us, followed by the variables that we have collected form our form.

Next we create a variable $run_query and assign it the value mysqli_query($connection, $query)

The mysqli_query() is a special php function performs a query against the database. In our case it takes two parameters $connection and $query. Going back to part 2, $connection is the variable that holds the mysqli_connect($server, $username, $password, $database) information. From this part $query stores our SQL INSERT INTO information.

If everything is okay, (determined by the if statement above), we receive the message “Data has been inserted in your database” else we receive the message “Data could not be inserted ” . mysqli_error($connection); which will try to identify the error.

finally we close the connection, mysqli_close($connection);

I have also included a button at the bottom of the page, so that we can return to the home page. So the complete code looks like:-

<!DOCTYPE html>
<html lang="en" dir="ltr">
 <head>
     
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width initial-scale=1.0">
  <meta name="Author" content="David Boland">
  <title>PHP MySQL Practice</title>
  <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
  <link rel="stylesheet" href="./css/style.css">

 </head>

 <body>
  <div class="w3-container container">

   <?php   // <!-- connecting to a database -->
   include 'dbconnection.php';

   // define variables and set to empty values
   $business = $firstName = $lastName = $raw_email = $telephone = " ";

   // Note* This is basic sanitizing but you can be more careful with this

   if ($_SERVER["REQUEST_METHOD"] == "POST") {
     $business = test_input($_POST['business']);
     $firstName = test_input($_POST['firstName']);
     $lastName = test_input($_POST['lastName']);
     $raw_email = test_input($_POST['email']);
     $telephone = test_input($_POST['telephone']);
   }   

function test_input($data) {
     $data = trim($data);
     $data = stripslashes($data);
     $data = htmlspecialchars($data);
     return $data;
   }   

$email = filter_var($raw_email, FILTER_VALIDATE_EMAIL);

   echo "<br>";
   echo $business;
   echo "<br>";
   echo $firstName;
   echo "<br>";
   echo $lastName;
   echo "<br>";
   echo $email;
   echo "<br>";
   echo $telephone;
   echo "<br>";
   echo "<br>";

   //insert some data
   $query = "INSERT INTO clients_tbl (id, business, firstName, lastName, email, telephone)
   VALUES (NULL,'$business', '$firstName', '$lastName', '$email', '$telephone')";

   $run_query = mysqli_query($connection, $query);

   if($run_query){
      echo "Data has been inserted in your database";
    }else{
    echo "Data could not be inserted " . mysqli_error($connection);
   }

   mysqli_close($connection);

   echo "<br> <br>";

   ?>

   <a href="index.php" class="w3-btn w3-teal client_btn">Home</a>

 </div>

 </body>
</html>

If everything looks okay, save this file and upload it to your server. You should now be able to insert data into your database, using the form on your index.php file.

In part 6, I will show you how to delete individual records from your database.


« |