MySQL Database Connection Tutorial In PHP

While developing a dynamic website, the first thing we need to connect our database to a PHP application. Even small application which requires dynamic data needs a database connection. Without a database connection, we can't perform any operations like create, read, update, and delete.

In this tutorial, we will connect our PHP application with MySQL database using MySQLi and PDO easily with a practical example.

Before starting, we need to create a database in MySQL. It doesn't matter if you are using XAMPP or WAMPP for development. Open phpmyadmin or use a terminal to create database.

In PHP, there are two ways to connect MySQL database: MySQLi and PDO. We will cover them one by one in further tutorials.

MySQLi Database Connection In PHP

MySQLi stands for MySQL Improved. MySQLi is relational database driver which provides an interface with MySQL database. MySQLi provides both ways for procedural and object-oriented.

MySQLi gives you prepared statements - a safer way of sending data to MySQL and protecting you from SQL injection.

Let's take an example to connect a database to PHP with MySQLi object-oriented.

<?php
    $servername = "localhost";
    $username = "username";
    $password = "password";

    // Create connection
    $conn = new mysqli($servername, $username, $password);

    // Check connection
    if ($conn->connect_error) {
      die("Failed: " . $conn->connect_error);
    }
    echo "Connected";

    $conn->close(); //Close connection
?>

The above code will print output based on your server name, user name, and password. It will print a success message if connected to the database otherwise it will show error logs.

Let's take another example for the same in a Procedural way.

<?php
    $servername = "localhost";
    $username = "username";
    $password = "password";

    // Create connection
    $conn = mysqli_connect($servername, $username, $password);

    // Check connection
    if (!$conn) {
      die("Failed: " . mysqli_connect_error());
    }
    echo "Connected";

    mysqli_close($conn); //Close Connection
?>

In the above code, we use mysqli_connect() function to establish a connection and check whether it's connected or not. Lastly, we will close the connection using mysqli_close() function.

PDO Database Connection In PHP

The PDO stands for PHP Data Object. The major advantage of PDO is it works with 12 different database systems whereas MySQLi only works with MySQL databases.

In this example, we will connect to a specific database using a PDO connection.

<?php
    $servername = "localhost";
    $username = "username";
    $password = "password";

    try {
        $conn = new PDO("mysql:host=$servername;dbname=DATABASE_NAME", $username, $password);

        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        echo "Connected";
    } catch(PDOException $e) {
        echo "Failed: " . $e->getMessage();
    }

    $conn = null; //Close Connection
?>

Creating a PDO object requires three parameters first one is DNS(Data Source Name), second is a user name and last one is password.

In the above code, we have tried to connection to try and catch block. It will try to run code of try block and if any type of error occurs then it will print an error message or handle an error using catch block's code. Here, we are simply printing messages for connection.

At last, we are closing connection by setting connection object to null.

Conclusion

Here, We have learned to connect MySQL database using MySQLi and PDO and closed it. There may be common errors like username and password errors. So make sure user name, password, and database name before implementation.