Connecting MySQL and PHP
In the last lesson you learned about Relational Tables in MySQL. You created a table called pages and used a foreign key to connect records within the pages table to records in the sections table. You also were given a task to create a number of records in pages, did you succeed?
Here’s the code that would create the required records:
mysql>INSERT INTO pages (section_id, menu_title, position, visible)
-> VALUES (1, ‘PHP’, 2, 1);
INSERT INTO pages (section_id, menu_title, position, visible)
-> VALUES (1, ‘HTML’, 2, 1);
INSERT INTO pages (section_id, menu_title, position, visible)
-> VALUES (1, ‘CSS’, 2, 1);
INSERT INTO pages (section_id, menu_title, position, visible)
-> VALUES (2, ‘Training’, 2, 1);
INSERT INTO pages (section_id, menu_title, position, visible)
-> VALUES (2, ‘SEO Consulting’, 2, 1);
INSERT INTO pages (section_id, menu_title, position, visible)
-> VALUES (2,’Web Development’, 2, 1);
INSERT INTO pages (section_id, menu_title, position, visible)
-> VALUES (3, ‘Our Team’, 2, 1);
INSERT INTO pages (section_id, menu_title, position, visible)
-> VALUES (3, ‘Contact’, 2, 1);
To check that these page were successfully inserted you can use the SELECT * FROM query:
mysql>select * from pages;
______________________________________________________________________________________
| id | section_id | menu_title | position | visible | content |
______________________________________________________________________________________
| 1 | 1 | MySQL | 1 | 1 | Welcome to the MySQL cour |
| 2 | 1 | PHP | 2 | 1 | NULL |
| 3 | 1 | HTML | 2 | 1 | NULL |
| 4 | 1 | CSS | 2 | 1 | NULL |
| 5 | 2 | Training | 2 | 1 | NULL |
| 6 | 2 | SEO Consulting | 2 | 1 | NULL |
| 7 | 2 | Web Development | 2 | 1 | NULL |
| 8 | 3 | Our Team | 2 | 1 | NULL |
| 9 | 3 | Contact | 2 | 1 | NULL |
______________________________________________________________________________________
9 rows in set (0.00 sec)
Now that you have your sections and pages tables populated, let’s turn our attention to PHP and understand how to connect PHP web page to your MySQL database and tables.
Before you continue, be sure that you are familiar with PHP and completing our PHP essentials course will get you up to speed.
PHP API’s for MySQL
Different technologies like PHP and MySQL need additional technologies to talk to each other, these are called API’s. An API is an Application Programming Interface that provides the necessary communication environment (like files and protocols) for one technology to talk to another. PHP has 2 different API’s for connecting to MySQL:
- MySQLi extension (the i stands for improved)
- PDO or PHP Data Objects
Which one you choose to use will depend on your needs, coding preference and if portability is important to you. Here are some important features of both:
- PDO offer excellent portability and will work on 12 different database systems. MySQLi on the other hand will only work with MySQL databases and is therefore pre-configured for MySQL. So if you need your web application to work on other database systems as well as MySQL you should consider using PDO over MySQLi.
- If your coding is exclusively object-oriented then either PDO or MySQLi will be a good choice as both support OOP (object-oriented programming). However, if you have any procedural code you will have to choose MySQLi.
- Both support Prepared Statements that help protect from SQL injections.
Deciding which API to use for this course is pretty straight forward. We are not going to use any database other than MySQL and we will be using procedural code so for this course we will use MySQLi.
5 Steps of PHP-MySQL interaction
When PHP interacts with MySQL there are 5 steps that we need to be aware of:
- The first step is to make the connection. This involves using API appropriate code to locate the database and provide it with username and password credentials to securely connect with it. Once this has been successful, the connection to the database has been established and will remain open until it is deliberately closed by either a script or the conclusion of a PHP page.
- The next stage is the performance of queries. This can involve create, read, update and delete queries among others.
- The next stage is to use data that is returned from queries in stage 2 in some way. This data could then be stored in an array for use later in the page, for example.
- The next stage is tot release the data that had been returned by the query. This free up valuable memory that would otherwise be occupied holding data the is no longer required.
- The final step is to terminate the database connection.
All of these steps will be present in a typical PHP page but steps 1 and 5 will occur only once per page. Step 1 is typically positioned at the very top of the PHP page and step 5 is typically and the bottom of the PHP page. Steps 2-4 can repeated as many times as necessary.
We will use the MySQLi API to connect to the database and the are a number of functions that you will need to know to connect, disconnect and identify connection errors, they are:
- mysqli_connect() – this will be used to connect to the database
- mysqli_connect_errno() – this will identify error number associated with a connection error
- mysqli_connect_error() – this will identify error types associated with a connection error
- mysqli_close() – this will be used to disconnect from the database
Let’s step through how to use these functions to connect and disconnect, and later perform steps 2, 3 and 4 to see how it all works together.
Setting up a project folder
Start by creating a new folder called learningglue in the www directory of your WAMP installation. You will find the www directory by clicking on the WAMP icon in the Windows Taskbar.
The www directory is commonly referred to as the web server root folder as this is where all files for your site are served from, and from this point on we will refer to it as the root directory.
Open the learningglue folder and create two new folders
- one called site – this is where all of your web site development files will live
- another called backups – this is where all of your back up files and folder will be stored
Inside the newly created learningglue\site directory (which is inside the root directory) create a blank PHP document.
If you took our PHP our PHP essentials course you will have a PHP template file that you can use straight away. If you haven’t taken that course we suggest you do, it will make everything that comes next much easier.
Your PHP page should have the following content:
<!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <title></title> </head> <body> </body> </html>
Save this file as db_process.php into your learningglue\site folder.
Backing up your work
When you are working with any kind of software development it is good practice to back up your work regularly.
You have created a project folder called learningglue and inside that is another folder called site that will contain all the files and folders for your web site. You also created a folder called backups within the learningglue folder. You should now have two folders inside your learningglue folder.
To back up your work, all you need to do is make a copy of the site folder (copy and paste into the same folder) and rename the copy by appending the date and time. I like to use the format YYMMDD-HHMM when naming my backup folders, so at this moment my latest backup would be called site-180118-2332.
Now drag that folder into the backups folder and carry on working in the site folder. Back up your site folder often, every couple of hours at the very least but more often if you are making a lot of code changes. The idea is that you will only lose minutes of work if something happens to your development files.
To further strengthen your backup strategy, consider copying or moving your backup folder to a cloud synchronised folder, that way your local backup folder is also present in a remote location (like Google docs or Microsoft one-drive). If the absolute worst happened and your computer is lost, stolen or just falls over never to be revived, you will have your back-up files safely stored on the cloud.
With db_process.php open in your code editor, let’s start with step 1, connecting to the database using the mysqli_connect() function.
Connecting to the database
The mysqli_connect() requires four arguments:
- host: usually a URL but as we are running a web set locally the URL will be localhost.
- username: you may have more than one MySQL username, be sure to choose one with enough permissions to perform the queries your will require. The user “root” is usually the admin users with all privileges and we will use this user for our site.
- password: this is the password for the user you have chosen.
- database name: there may be other databases that exist on the server so we must specify exactly which database this PHP script will connect to.
Typically you will create a variable for each of these arguments, and the names you choose are pretty much down to you as long as you are consistent in their naming and use. We will use db for database followed by the name of the argument:
<?php // Step 1 - Connect to the database $dbhost = "localhost"; $dbuser = "root"; $dbpass = ""; $dbname = "learningglue"; $dbconnection = mysqi_connect($dbhost, $dbuser, $dbpass, $dbname); ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <title></title> </head> <body> </body> </html>
The function that establishes the connection, mysqli_connect(), together with it’s arguments should itself be assigned to a variable so that you can call it whenever you need it from from any PHP page. Again, what you name that variable is completely down to you, we will use dbconnection to be consistent and clear about what this variable does.
Handling connection errors
Assuming there is a successful connection this is all that we need, however there may be times when a connection is not achieved and for those moments you will need o consider how to deal with those errors. This is where the mysqli_connect_errno() and mysqli_connect_error() come in, we use these to identify the error number associated with a connection error and identify the error types associated with a connection error respectively. Lete’s update our connection PHP script:
<?php // Step 1 - Connect to the database $dbhost = "localhost"; $dbuser = "root"; $dbpass = ""; $dbname = "learningglue"; $dbconnection = mysqi_connect($dbhost, $dbuser, $dbpass, $dbname); // This will test if the connection occurred if (mysqli_connect_errno()) { die("Database connection failed: " . mysqli_connect_error() . " (" . mysqli_connect_errno(). ")" ; } ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <title></title> </head> <body> </body> </html>
Here we are checking to if mysqli_connect_errno() exists, and if it does we know there is an error number, and therefore an error. In this scenario there is not a lot we can do other than try to identify the problem by displaying the error message, using mysqli_connect_error(), and the error number, using mysqli_connect_errno(). The die function will force the page to exit any further code execution, so nothing after this line will be visible in the client window. This is pretty drastic but if the PHP page cannot connect to the database there is little point in continuing with the rest of the page since most, if not all, of the content needs the database to work at all.
If you run this page in a browser and you do not see any error message then you can be sure that you connected successfully. If not, then you have a problem and will need to check the login and host details, and ensure that your code is error free.
Now that you have connected to the database your page is ready to start making queries and creating content that we discussed earlier in steps 2, 3 and 4, but we will get onto that later but. For now let’s jump to the final stage, stage 5, where you close the database connection.
Disconnecting from the database
Like the connection code, the disconnection code occurs only one during within a PHP page. As you saw earlier, the connection code is typically located at the top of the page so that it is executed early. The disconnection code, on the other hand, should be one of the last things to happen within a PHP page, after every other line PHP code has been executed and therefore any requirement for the database connection.
To close a database connection you will use the mysqli_close() function:
<?php // Close the database connection mysqli_close($connection); ?>
The $connection argument that is used with out mysqli_close function is a reference to the handler created earlier during the connection process.
Be sure to save your db_process.php file as we will pick it up in the next lesson.
What you have learned
In this lesson you have learnt how to create a connection to a MySQL database from within a PHP page using the mysqli API. You understand that this connection must happen at the top of the PHP page before any database queries are executed. You have also learnt ho to disconnect from the database, this time at the very bottom of the PHP page. We discussed connection error handling and understand what is required to make a successful database connection.
What’s next
In the next lesson you will learn how to execute MySQL queries and retrieve data from a database table to display it within your PHP page.