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.
Comments on this post