Connect to MySQL database in PHP
In other to connect to a MySQL database, we can use either of the two methods which are the two most popular methods. They both work with PHP 5 and newer. They are: MySQLi Extension and PDO.
Connect to MySQL database in PHP
Table of content
- About MySQL database connection in PHP
- Different methods of PHP to MYSQL database connection
- How to connect to MySQL database using PDO and MySQLi
- Closing of connection in MySQLi and PDO
- MySQLi installation and PDO installation
About MySQL database connection in PHP
In other to input or save information in MYSQL database, a connection must be made from our PHP code to MYSQL database.
In this content, we are going to learn how to create a MySQL database connection using PDO and MySQLi extension.
Different methods of PHP to MYSQL database connection
In other to connect to a MySQL database, we can use either of the two methods which are the two most popular methods. They both work with PHP 5 and newer. They are:
- MySQLi Extension
- PDO
MySQLi Extension
MySQLi Extension ( i means improved ) is one of the methods to connect to MySQL database.
MySQLi extension only supports MYSQL database which means it only works with Mysql database. This method supports prepared statements which is a great security for the application it’s been built with, such as preventing SQL injection. MySQLi is also object oriented.
PDO
PHP data objects known as PDO is another set of PHP extension to connect to MySQL database. This method called PDO can connect and work with 12 different database systems unlike MySQLi which can only connect to MySQL database.
PDO works with the following databases:
- MySQL
- Oracle
- IBM
- Cubrid
- Informix
- Sybase
- Firebird
- MS SQL Server
- PostgreSQL
- FreeTDS
- SQLite
- 4D
PDO allows easy switching of databases which you only change the connection string and some queries. If it were MySQLi extension, you have to rewrite all the code and queries.
PDO also support prepared statement both client-side and server-side prepared statement which protect against some malicious attack like SQL injection. This method is object oriented.
How to connect to MySQL database using PDO and MySQLi
Connection to MySQL database using MySQLi Extension (object oriented)
The PHP script or syntax to connect to MYSQL database:
<?php
$dbserver = "localhost";
$username = "username";
$dbName = "database";
$dbpassword = "password";
// Create the connection
$conn = new mysqli($dbserver, $username, $dbpassword, $dbName);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connection successful";
?> Explained:
The values of the first four variables (dbserver, username, dbName, dbpassword) should be same with your connection details as any mistake or wrong value will throw an error.
Mysqli() : is used to create the connect to the database.
The if() statement is used to check if there is an error in the connection, if there is no error it will display connection successful on the screen, else die() statement will display the message and ends.
Connection to MySQL database using MySQLi Extension
<?php
$dbserver = "localhost";
$username = "username";
$dbName = "database name";
$dbpassword = "password";
// Create the connection
$conn = mysqli_connect($dbserver, $username, $dbpassword, $dbName);
// Check the connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connection successful";
?>
Explained:
Mysqli_connect() : is used to create the connect to the database.
The if() statement is used to check if there is an error in the connection, if there is no error it will display connection successful on the screen, else die() statement will display the message and ends
Connection to MySQL database using PDO (PHP data objects)
<?php
$dbserver = "localhost";
$username = "username";
$dbName = "database name";
$password = "password";
try {
$conn = new PDO("mysql:host=$dbserver;dbname=$dbName ", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connection successful";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>
Explained:
The values of the first four variables (dbserver, username, dbName, password) should be same with your connection details as any mistake or wrong value will throw an exception. This method requires a valid database to connect to.
One of the greatest advantage of PDO is that it has an exception class that handle any issues with the database queries. If an issue or exception is thrown in the try() block, the code stops execution and catch() the issue. The catch() block shows the error message or run any code written within the catch() block.
PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTIONIs used to tell the PDO to run an exception if there is error in the query.
Closing of connection in MySQLi and PDO
Connection will be closed automatically when the code ends, but we can close it manually using:
For MySQLi:
mysqli_close($connection); For MySQLi (OOP):
$connection->close();For PDO:
$connection = null; MySQLi installation and PDO installation
For installation, visit the links.
