In this article, we will see how to test database operations using PHPUnit.

In this example, we will create a class called GuestBook, which will have a function called addGuest(). The addGuest() will add a row to database table. Using PHPUnit, we will test, whether the addGuest() is adding new row to database table.

1. Create a directory called GuestBook for this example

$mkdir GuestBook

2. Download Composer installer from here and save to GuestBook directory

3. Execute the installer in terminal with the given below command

$php installer

4. Add PHPUnit library to this project

$php composer.phar require --dev phpunit/phpunit

5. Add DBUnit library to this project

$php composer.phar require --dev phpunit/dbunit

6. Enable Autoload for source class files

Open Composer.json file in a text editor and add the autoload instruction as given below :

{
    "autoload": {
        "classmap": [
            "src/"
        ]
    },  

    "require-dev": {
        "phpunit/phpunit": "^7.0",
        "phpunit/dbunit": "^4.0"
    }
}

Execute the given below command at the root of the project :

$php composer.phar update

7. Create directories for source and tests in GuestBook

$mkdir src
$mkdir db
$mkdir tests

8. Create GuestBook.php file in the src directory

<?php
class GuestBook{
    
    public function addGuest($name, $address, $phone){
        $dbhost = "localhost";
        $dbname = "guestbook";
        $dbuser = "root";
        $dbpass = "mysql";
        $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
        $sql = "insert into guestbook(name, address, phone) values ( ?, ?, ?)";
        if($stmt = $mysqli->prepare($sql)){
            if($stmt->bind_param("sss", $name, $address, $phone)){
                if($stmt->execute()){
                    $stmt->close();
                    $mysqli->close();
                }
            }
        }
    }
}

9. Create a file tables.sql in the directory db

drop table if exists guestbook;
create table guestbook(
    id      integer primary key auto_increment,
    name    varchar(100),
    address text,
    phone   varchar(50),
    ttime   timestamp default now()
)engine="innodb";

10. Create a database in MySQL with name guestbook and create the above table

11. Create a xml file guestbook_fixture.xml and save in tests directory

<?xml version="1.0" ?>
<dataset>
    <guestbook id="1" name="thomas" address="Kerala, India" phone="12345" />
    <guestbook id="2" name="anna" address="TN, India" phone="54322" /> 
</dataset> 

12. Create an xml file guestbook_expected.xml and save in tests directory

<?xml version="1.0" ?>
<dataset>
    <guestbook id="1" name="thomas" address="Kerala, India" phone="12345" />
    <guestbook id="2" name="anna" address="TN, India" phone="54322" /> 
    <guestbook id="3" name="George" address="AP, India" phone="4545" /> 
</dataset>

13. Create the file phpunit.php and save in GuestBook directory

<?xml version="1.0" encoding="UTF-8" ?>
<phpunit>
    <php>
        <var name="DB_DSN" value="mysql:dbname=guestbook;host=localhost" />
        <var name="DB_USER" value="root" />
        <var name="DB_PASSWD" value="mysql" />
        <var name="DB_DBNAME" value="guestbook" />
    </php>
</phpunit>

14. Create test case file GuestBookTest.php in tests directory

<?php

use PHPUnit\Framework\TestCase;
use PHPUnit\DbUnit\TestCaseTrait;

class GuestBookTest extends TestCase{
    use TestCaseTrait;

    // only instantiate pdo once for test clean-up/fixture load
    static private $pdo = null;

    // only instantiate PHPUnit\DbUnit\Database\Connection once per test
    private $conn = null;

    final public function getConnection() {
        if ($this->conn === null) {
            if (self::$pdo == null) {
                self::$pdo = new PDO( $GLOBALS['DB_DSN'], $GLOBALS['DB_USER'], $GLOBALS['DB_PASSWD'] );
            }
            $this->conn = $this->createDefaultDBConnection(self::$pdo, $GLOBALS['DB_DBNAME']);
        }

        return $this->conn;
    }

    public function getDataSet() {
        return $this->createFlatXmlDataSet('./tests/guestbook_fixture.xml');
    }

    public function testRowCount() {
        $this->assertSame(2, $this->getConnection()->getRowCount('guestbook'), "Pre-Condition");
    }

    public function testAddGuest() {

        $guestbook = new GuestBook();
        $guestbook->addGuest("George", "AP, India", "4545");

        $queryTable = $this->getConnection()->createQueryTable(
            'guestbook', 'SELECT id, name, address, phone FROM guestbook'
        );

        $expectedTable = $this->createFlatXmlDataSet("./tests/guestbook_expected.xml")
                              ->getTable("guestbook");

        $this->assertTablesEqual($expectedTable, $queryTable);

    }
}

15. Runn the test

We can run the test by executing the given below command at the root of the project

$./vendor/bin/phpunit --testdox tests/GuestBookTest.php

On executing the test, PHPUnit will display the test result as given below :

 

 

 

16. Reference

This article is based on the PHPUnit’s documentation given here.