How to Connect Database to PHP?

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 allows you 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. MySQL organizes data in tables using a row and column structure. As a result, MySQL is suitable for both small and large applications, and it operates on the server.

ADVERTISEMENT Popular Course in this category PHP DEVELOPER - Specialization | 8 Course Series | 3 Mock Tests

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

How to Create a Database?

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

For instance, if you wish to create a database named “movies,” you can achieve this by executing the command CREATE DATABASE movies;

Adding more parameters and avoiding confusion between existing databases on a single MySQL server can use the command “[IF NOT EXISTS].” This query checks whether any existing database has the same name. If yes, the command will not execute the creation of the database. On the other hand, if there is no such conflict of database names, the below command will execute and create 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 was derogated in 2012.

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

1. MySQLi extension

2. PDO (PHP Data Objects)

According to their own supremacy and needs, any of the above options can be utilized.

The MySQLi extension can be used only with MySQL databases, whereas PDO can connect with 12 different database systems. So, PDO might be helpful if one is switching databases as it only requires changing a few connections. But in the case of MySQLi, we need to write the entire code and query to switch databases. Enabling MySQLi on PHP is necessary to work with MySQLi. MySQLi also provides a procedural programming interface along with an object-oriented one. PDO and MySQLi support prepared statements, which are crucial aspects of web security.

Working with PHP and MySQL

PHP and MySQL are some of the most common stacks for web development.

• Object-oriented MySQLi
• Procedural MySQLi
• PDO

To start with any of these, you must know some essential 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 sets up a connection between the database and PHP. First of all, we have to create a separate connection file. This saves time in writing code every time you want to insert data and information from the database and insert this data in multiple files.

We can use the PHP file connection name along with the include function and insert the data instead of rewriting the code each time. This is also useful when transferring your entire project from one system to another. When you change the values in one file, it automatically changes all values in every file and saves you the effort of making changes in every file. Once you have successfully established the connection, you can employ various methods to interact with the database.

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

1. 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 the location of the server. The host can be something else, but the server runs on the localhost in most cases.
• The username is the root, and the password; is the same which you use for php admin.
• To establish this link, provide the necessary details such as localhost, username, and password. This will create an instance of MySQLi, resulting in a successful connection.

2. Using MySQLi Procedural

To establish a connection between the database and PHP using MySQLi procedural, you can follow the procedure outlined 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 a link identifier.

3. 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 assists in throwing and handling any exceptions that may arise while establishing connections, ensuring proper management of any encountered issues.

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 database connection using PHP scripts, you should close the connection once your work is finished. With an assumption of reference to the connection stored in the $ conn variable, below are the closing syntax used in the above-given procedures.

• Using MySQLi object-oriented procedure

• Using MySQLi procedural procedure

• Using PDO procedure

Conclusion – How to Connect Database to PHP?

Accessing and managing your database becomes straightforward when you establish a connection using PHP. It also offers a variety of ways to establish this connection to suit varying levels of requirements. After establishing the connection, you can actively execute queries to extract data from the tables. You can easily print out this information. Closing the connection is an essential part of connecting the database to PHP, and it should be done once your work is completed.

The above is the detailed content of How to Connect Database to PHP?. For more information, please follow other related articles on the PHP Chinese website!