How to Connect Database to PHP?

Introduction on How to Connect Database to PHP

In order to make optimum usage of any database, one should be able to manipulate it as required. The best way to manipulate and manage the databases you created is to connect your database with PHP. First, let us understand what PHP is. PHP is like a control panel that can be used to manage your database. Connecting to PHP gives you the liberty to retrieve data from databases as needed for a particular query. MySQL is the most popular open source RDBMS which can be easily connected to PHP. The data in MySQL is arranged in tables and in a row and column structure. It can be easily used for small and large applications and works on the server.

How to Create a Database?

Creating a database in MySQL is as simple as performing a single command in SQL. For beginners, an easy query of “[CREATE DATABASE]” can be used for database creation. Another option to create a database is using another query as “[CREATE SCHEMA]”.

For example, suppose you want to create a database named “movies”. This can be done by executing the command CREATE DATABASE movies;

Adding more parameters and to avoid confusion between existing databases on a single MySQL server, one can use the command “[IF NOT EXISTS]”. What this query does is it checks whether any existing database has the same name. If yes, the command will not execute the creation of the database. If there is no such conflict of database names, the below command will be executing and creating a database. Although “[IF NOT EXISTS]” is non-mandatory it is a good practice.

This newly created database will be empty waiting for the inclusion of tables with data. SQL command for tables is a simple query “[CREATE TABLE]” with syntax as below.

This newly created database will be empty waiting for the inclusion of tables with data. SQL command for tables is a simple query “[CREATE TABLE]” with syntax as below.

Connecting Database to PHP

PHP versions below 5 use MySQL extension. But this extension has been derogated in 2012.

The 5^th version of PHP and newer versions can work with below:

1) MySQLi extension

2) PDO (PHP Data Objects)

Anyone of the above can be used according to their own supremacy and one’s needs.

The MySQLi extension can be used only with MySQL databases whereas PDO can be used to connect with 12 different variety of database systems. So, if one is switching databases PDO might come handy as it only requires to change a few connections. But in the case of MySQLi, we need to write the entire code and query to switch database. Working with MySQLi requires MySQLi to be enabled on PHP.  MySQLi also provides a procedural programming interface along with an object-oriented one. Prepared statements are principal in web security which are allowed in both PDO and MySQLi.

Working with PHP and MySQL

PHP and MySQL are one of the most common stacks for web development. Let’s look at a few examples.

• Object-oriented MySQLi
• Procedural MySQLi
• PDO

To start with any of these, it is required to know some important details such as your database system’s server address, username, database name, and password. Mainly, we will be using mysqli_connect in all three procedures. This command is used to set up a connection between the database and PHP. First of all, we have to create a separate connection file. This saves time to write code every time you want to insert data and information from the database and insert this data in multiple files. We can just use the PHP file connection name along with include function and insert the data instead of rewriting the code each time. This is also useful when you need to transfer your entire project from one system to another. When you change the values in one file it automatically changes all values in each and every file as well as saving you the efforts of making changes in every file. Once this is achieved, we have the option of using different procedures to establish a connection with the database.

Let’s look into each of them one by one:

• Using Object-Oriented MySQLi

This can be used to build an association with the database using PHP script using the following syntax.

Explanation: Localhost is basically the location of the server. The host can be something else but in most cases, the server runs on the local host.

Username is the root and password it is the same which you are using for php admin.

To establish this link, provide necessary details such as localhost, username, and password. This will create an instance of MySQLi resulting in a successful connection.

• Using MySQLi Procedural

The procedure to establish a connection between database and PHP using MySQLi procedural is described as below.

Explanation: The main difference for the procedural procedure is that it uses the function mysqli_connect which fetches the necessary details of the host, username and password, etc. When successfully connected, it will provide with a link identifier.

• Using PDO

PDO represents PHP Data objects. So in this process of creating a connection, PHP data objects are used as below:

Explanation: The exception function in PDO helps to throw in any exceptions that are to be considered and manages any issues that may occur while establishing connections.

All the above methods help access and manage the database you need.

Checking the Connection

Below syntax can be used to check whether your connection is successfully connected.

Db_connection is the php file name.

Ending the Connection

Once you establish a connection of database using PHP scripts, you should also close the connection once your work is finished. With an assumption of reference to the connection is stored in $ conn variable, below are the closing syntax which can be used in the above-given procedures.

• Using MySQLi object-oriented procedure

• Using MySQLi procedural procedure

• Using PDO procedure

Conclusion:

Accessing and managing your database is quite easily performed when connected using PHP. It also offers a variety of ways to establish this connection to suit varying levels of requirements. As the connection is established, one can perform queries to extract data from the tables. This information can be printed out easily. Closing the connection once your work is completed is also important and a part of connecting the database to PHP.

Recommended Articles

This has been a guide on How to Connect Database to PHP. Here we discussed How to create and connect Database to PHP. You can also go through our other suggested articles to learn more –

1. How to Connect Database in Java?
2. What is PHP Array?
3. How to Connect Database to MySQL?
4. PHP Commands | Basic and Advanced