Connecting to a MYSQL Database Part 2

The Finished Page

Create PHP Files

To keep things as simple as possible we will create 6 files:-

  1. dbconnection.php – Used to store our database credentials and to connect and verify our connection to the database.
  2. index.php – used to create our frontend form for inserting data, and the buttons required to view and delete data from our database.
  3. insert.php – used to validate and insert our data.
  4. select.php – used to display our data.
  5. selectOne.php – used to select a single record from our database.
  6. delete.php – used to delete our single record selected from above.

In the interest of style and continuity we are going to hollow out a standard html file before embedding our php code.

So lets start by creating a basic template for our site:- (keep a copy of this file as template.html, it will form the basis of future files).

<!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 Connection</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">
     </div>
</body>
</html>

Okay so we have created our basic html file with all the usual stuff. I am linking to w3.css stylesheet and also to my own style.css, but you can style however you prefer. Within the body create a div and give it the class w3-container if you are using w3.css and container (which we will define in our style.css file.)

The only styling we are going to use in our style.css file is as follows:-

body {
   background-color: #fffeee; 
 }
 .container {
   max-width: 1024px;
   margin-left: auto;
   margin-right: auto;
   border-left: 1px solid black;
   border-right: 1px solid black;
   padding: 5px 15px; 
 }
 .title {
   text-align: center; 
 }
 .client_btn {
   margin-top: 10px;
   margin-bottom: 10px; 
 }

Effectively all we are doing is changing the body background colour to a very pale yellow,
Giving the container a max-width of 1024px, centering it the screen, giving it a left and right border and some padding. We are also centering anything with the .title class and giving anything with the .client_btn a bit of top and bottom margin.

dbconnection.php

So the first file we are going to create is dbconnection.php, which will contain the code required to establish a connection to our newly created database. We will not be using the template for this file, instead we shall create our first php file.

  1. Open a new blank document and add the following tags <?php ?> . These tags tell the browser that whatever code is inserted in between them is PHP and should be interpreted as such. Place your cursor between the two php tags and hit enter to give yourself a bit of room to enter some code
  2. Underneath the opening <?php tag enter the following code:
$server = 'dbxxxxxxxx7.hosting-data.io'; //your server name
$username = ‘xxxx155'; //your username
$password = ‘xxxxxxxxxx'; //your password
$database = ‘dbxxx’; //your database name

We have now created four php variables $server, $username, $password, and $database and assigned them the values we were provided with when we first created our database. We create variables in php by adding a $ to the variable name.

Next create a variable called $connection which we are going to assign the following:- $connection = mysqli_connect($server, $username, $password, $database);

mysqli_connect(); is a special function that opens a connection to a MYSQL server and can take up to six parameters host, username, password, dbname, port and socket, but usually only the first four are required. Notice that my variable names, $server, $username, $password, and $database are different to the parameter names, host, username, password, dbname. We can name variables whatever we want, remember it is the information the variable is storing that gets passed as each parameter I.E. dbxxxxxxxx7.hosting-data.io, xxxx155, xxxxxxxxxx and dbxxx.

Now we are going to add a bit of code to check if our connection was successful.

if ($connection) {
        echo "Connected to database clients ";
       }else{
        echo "Connection to database unsuccessfull. " . mysqli_connect_error($connection);
        die($connection);
       }
 echo "<br>";

So the finished file should look like this

<?php
 $server = 'dbxxxxxxxx7.hosting-data.io';
 $username = ‘xxxx155';
 $password = 'xxxxxxxxxx';
 $database = 'dbxxx';

 $connection = mysqli_connect($server, $username, $password, $database);

 if ($connection) {
     echo "Connected to database clients ";
  }else{
     echo "Connection to database unsuccessfull. " . mysqli_connect_error($connection);
     die($connection);
 }
 echo "<br>";
?>    
     

Remember the $server, $username, $password, $database variables above should = your information (provided to you when you created your database).

Save this file as dbconnection.php Now upload this file to your server using whatever method you are familiar with (I use FileZilla). When you have uploaded your file go to your browser and type the following http(s)://yourdomain.com/dbconnection.php

yourdomain.com should be replaced by your actual domain in my case https://dboland.co.uk/development-sites/blog-posts/dbconnection.php because I have placed my file in a directory called blog-posts which is inside another directory called development-sites.

If everything went well you should see the following:-

Connected to database clients

If you get the message “Connection to database unsuccessfull” followed by an error message, then read the message and it should give you an indication of what went wrong. Assuming you created your database correctly, then the chances are that you have made a mistake assigning one or more of your variables $server, $username, $password, $database.

DO NOT PROCEED TO PART 3 UNTIL YOU HAVE MADE A SUCCESSFUL CONNECTION


« | »