Watch YouTube Video

Different ways to connect to the database

We will discuss three types of connections here from PHP. These are:

1. MySQLi Procedural
2. MySQLi Object Oriented
3. PDO (PHP Data Objects)

Let us take a look at each of these types of connections:

MySQLi Procedural Connection to MySQL database from PHP

Using this type of connection we can use MySQLi extension to connect to a database. We can use mysqli_connect() method to connect to the database. See the below code:

<php
// connect to database using mysqli_connect procedural
$server = "localhost";
$userid = "root";
$pwd = "";
$dbname = "test";

$conn = mysqli_connect($server, $userid, $pwd, $dbname);

// check the connection
if (!$conn)
die ("Connection Error:". mysqli_connect_error());

echo "Procedural Connection successful";

mysqli_connect() method is used to connect to the database. This method takes four parameters. These are Server, userid, password and database name. The database name is not mandatory in this method. In our case, we have provided these values using four variables to connect to a specific database. If the connection is successful, the method returns true. If it cannot connect to the database because of some issues, then it returns false. In case of a connection error, we display the actual error message by using mysqli_connect_error() function.

Create a directory named "dbconnect" under your xampp/htdocs, and save the above code in a file named "dbconnect_pro.php" in that folder.

Now, open your browser and type localhost/dbconnect/dbconnect.php and enter. Make sure in your XAMPP control panel, services are running. You will see the below result which shows the connection is successful.

You can see database connection is successful in this case. If the connection could not be established, it would have displayed an error message.

MySQLi Object Oriented Style connection to MySQL database from PHP

For this also we can use MySQLi extension to connect to the database, but in this case, we will create a mysqli instance by using new mysqli(). See the below code:

<php
//connect to database using mysqli_connect Object Oriented style
$server = "localhost";
$userid = "root";
$pwd = "";
$dbname = "test";

$conn = new mysqli($server, $userid, $pwd, $dbname);

// check the connection
if ($conn->connect_error)
die ("Connection Error: ". $conn->connect_error);

echo "Object oriented style Connection successful";

We are using the same four parameters here - Server, userid, password and database to create the mysqli instance using new mysqli(). Here also database name is not mandatory. In case of error in connection, we display the actual error message by using $conn->connect_error function.

Save the above code in a file named dbconnect_oo.php in the same directory as earlier.

Now, open your browser and type localhost/dbconnect/dbconnect_oo.php and enter. Make sure in your XAMPP control panel, services are running. You will see the below result which shows the connection is successful.

You can see database connection is successful in this case. If connection could not be established, it would have displayed an error message.

PDO connection to MySQL database from PHP

For this we will create a PDO instance by using new PDO() to connect to the database. See the below code:

<php
// PDO connection
$server = "localhost";
$userid = "root";
$pwd = "";
$dbname = "test";

try {
    $conn = new PDO("mysql:host=$server;dbname=$dbname", $userid, $pwd);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "PDO Connection successful";
    
  } catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
  }

We are using try...catch here and the same four parameters are used - Server, userid, password and database to create the PDO instance using new PDO(). In this case, database name is mandatory. PDO needs a string with server and database name. setAttribute() method is used to set the error mode to exception. In case of an error, we display the actual error message by using PDOException.

Save the above code in a file named dbconnect_pdo.php in the same directory as earlier.

Now, open your browser and type localhost/dbconnect/dbconnect_pdo.php and enter. Make sure in your XAMPP control panel, services are running. You will see below result which shows the connection is successful.

You can see the database connection is successful in this case. If the connection could not be established, it would have displayed an error message.

PHP program to fetch and display data using these connections.

Now, let us write a simple PHP program to connect to the database, fetch data and display in an html table. We will use these three types of connections separately to show how you can use them in a PHP program.

Since we have already written database connection code, we will just include it in our program, advantage of this is that we can reuse it in aother program.

We will create a table named 'category' in the test database. Just run the below create-table script. Copy the entire code and paste it in SQL window and then click on go. Table will be created and data will be inserted in it.

CREATE TABLE `category` (
  `category_id` int(11) NOT NULL,
  `category_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `category` (`category_id`, `category_name`) VALUES
(1, 'Furniture'),
(2, 'Electronics'),
(3, 'Dress Material'),
(4, 'Food');

ALTER TABLE `category`
  ADD PRIMARY KEY (`category_id`);

ALTER TABLE `category`
  MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

The structure of the table and the rows in it are given below:

Table: category

Table data

Use MySQLi Procedural connection

We will create index.php program to display the data in an html table. We will connect to the database using MySQLi procedural connection as explained earlier. So, we will include dbconnect_proc.php in index.php. See below code:

<!DOCTYPE html>
<html lang="en">

<head>
    <title>MySQL database connection example</title>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="css/style.css" rel="stylesheet">
</head>

<body>
    <div class="container">
        <h1>MySQL database connection example</h1>
        <?php
        include "dbconnect_pro.php";   // MySQLi procedural connection
        $sql = "select * from category";
        $result = mysqli_query($conn,$sql);
        ?>
        <div class="table-responsive">
            <table class="table table-bordered table-striped">
                <thead>
                    <tr>
                        <th>Sr No.</th>
                        <th>Category id</th>
                        <th>Category Name</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    $counter = 0;
                    foreach ($result as $row) {
                        $counter++; ?>
                        <tr>
                            <td><?= $counter; ?></td>
                            <td><?= $row['category_id'] ?></td>
                            <td><?= $row['category_name'] ?></td>
                        </tr>
                    <?php } ?>
                </tbody>
            </table>
        </div>
    </div>
</body>

</html>

See lines 16, 17 and 18. We have included connection script in line 16. Next we have our select statment and then executed it using mysqli_query(). Then in line 32, we are using a foreach loop to fetch the data and display in a html table.

Run in the browser using localhost/dbconnect you will see the below output:

Here, we do not need to print the connection successful message, you can remove it. We have used it just to show you which connection type we are using.

Use MySQLi Object-Oriented style connection

Here we have to include dbconnect_oo.php, so replace line 16 with include "dbconnect_oo.php"; // MySQLi object-oriented connection and also replace line 18 with $result = $conn->query($sql); to execute the query. Rest of the code will remain same.

Run in the browser using localhost/dbconnect you will see below output:

Use PDO connection

Here we have to include dbconnect_pdo.php, so replace line 16 with include "dbconnect_pdo.php"; // PDO connection and also replace line 18 with $result = $conn->query($sql); to execute the query. Rest of the code will remain same.

Run in the browser using localhost/dbconnect you will see below output:

This way, we can include a database connection program to access the database. Write once and use it multiple times whenever required. So, write a separate program to connect to the database and include it in all other programs that need to access the database. Also, in case you need to change your database connection parameters, you have to change them in one place only. So, this is easier for code maintenance.

Which type of connection is better?

While MySQLi supports only MySQL database, PDO connection supports many other databases. So, in case there is a change to connect to a different database then you do not have to change the code much for PDO connection. But with MySQLi connection a significant amount of code change is required.

Watch YouTube Video