How to mock MySQLi when unit testing with PHPUnit

PHPUnit is the most used unit testing framework for PHP. Today I wanted to unit test some PHP code that relies on MySQLi. The dilemma is that you either need an actual database and load a fixture or you need to mock the database. As Claudio Lasalla clearly puts:

Unit tests are not “unit” tests if they test things other than the System Under Test (SUT).

And further explains:

Unit tests check on the behavior of units. Think of a class as being a unit. Classes, more often than not, have external dependencies. Tests for such classes should not use their real dependencies because if the dependencies have defects, the tests fail, even though the code inside the class may be perfectly fine.

This theory made total sense to me. That’s why I decided to mock the MySQLi dependency. In this post I will show you just how far I came before I realized this was not going to work out (for me).

The code

The test class, that extends “PHPUnit Framework TestCase”, has an extra method “expectQueries()”. The class looks like this:


class MySQL_CRUD_API_Test extends PHPUnit_Framework_TestCase
	private function expectQueries($queries)
		$mysqli = $this->getMockBuilder('mysqli')
			->will($this->returnCallback(function($str) { return addslashes($str); }));
			->will($this->returnCallback(function($query) use ($queries) {
				$results = $queries[$query];
				$mysqli_result = $this->getMockBuilder('mysqli_result')
					->will($this->returnCallback(function() use ($results) {
						static $r = 0;
						return isset($results[$r])?$results[$r++]:false;
				return $mysqli_result;

		return $mysqli;

	public function testSomeSubjectThatUsesMysqli()
		$mysqli = $this->expectQueries(array(
			"SELECT * FROM `table`" =>array(array('1','value1'),array('2','value2'),array('3','value3')),
			"SELECT * FROM `table` LIMIT 2" =>array(array('1','value1'),array('2','value2')),
			// other queries that may be called
		// do something that uses $mysqli

The subject-under-test is actually doing something like this:

$result = $mysqli->query("SELECT * FROM `table`");
while ($row = $result->fetch_row()) {
	// do something with the data in $row

And in the test it will return the corresponding rows for the queries that you execute. Nice huh?

Not ready

This is a proof-of-concept of a mock of the MySQLi component for PHPUnit. The ‘real_escape_string’ function has a sloppy implementation. It does not (yet) support the much used ‘prepare’, ‘execute’ or ‘fetch_fields’ methods. To give an idea of the completeness, for MySQLi it now support 2/62 functions and properties, for MySQLi Statement 0/28 and for MySQLi Result 2/15. Apart from this incompleteness there is the problem that you may need to support meta information, such as field names and types, to have a fully working mock. If you feel like continuing my work, then feel free to take my code.


Although this was a nice exercise and it may even be the right thing to do in theory, it did not seem to make much sense (to me) in practice. So I gave up on this approach and my current implementation runs all tests against a real database. It loads a database from a SQL file (fixture) in the static ‘setUpBeforeClass()’ function. This may not be so ‘correct’ or ‘clean’ (from a unit testing point of view), but it is much faster to write and easier to maintain.

My question for you: Am I wrong or is the theory wrong? Please tell me using the comments.


Testing your project with PHPUnit and Selenium

PHPUnit is a very handy tool you can use to test and detect possible mistakes in your PHP project.

Functional testing is also possible with PHPUnit using default headless browser (crawler). It works perfect as long as you don’t need to test functionality which uses JavaScript. For this purpose, you can use PHPUnit and Selenium.

Selenium is a powerful test tool that allows you to perform tests for web applications in any programming language using any mainstream browser.

Installation requirements

Before you start using Selenium to test your PHP project, you need to install the following:

  1. Selenium Server
  2. PHPUnit_Selenium package

1. Installing Selenium Server

Perform the following steps to install the Selenium Server:

    1. Download a distribution archive of Selenium Server.
    2. Unzip the distribution archive and copy selenium-server-standalone-2.35.0.jar (check the version suffix) to a folder from where you will run the server (such as /usr/local/bin).
    3. Start the Selenium Server by running the following command:
java -jar /usr/local/bin/selenium-server-standalone-2.35.0.jar

2. Installing PHPUnit_Selenium package

      The PHPUnit_Selenium package is necessary for natively accessing the Selenium Server from PHPUnit.
      To install it, run the following command:
pear install phpunit/PHPUnit_Selenium

Using Selenium in PHPUnit tests

There are two Selenium test cases:

    • PHPUnit_Extensions_Selenium2TestCase
    • PHPUnit_Extensions_SeleniumTestCase

PHPUnit_Extensions_Selenium2TestCase test case allows you to use the WebDriver API (partially implemented).

class WebTest extends PHPUnit_Extensions_Selenium2TestCase
    protected function setUp()

    public function testTitle()
        $this->assertEquals('Example WWW Page', $this->title());


PHPUnit_Extensions_SeleniumTestCase test case implements the client/server protocol to talk to Selenium Server as well as specialized assertion methods for web testing.

require_once 'PHPUnit/Extensions/SeleniumTestCase.php';

class WebTest extends PHPUnit_Extensions_SeleniumTestCase
    protected function setUp()

    public function testTitle()
        $this->assertTitle('Example WWW Page');

Using different browsers with WebDrivers

For running tests with different browsers you should have WebDriver of that browser. As an example, we’ll try to use the Google Chrome browser and its WebDriver.

  1. Download WebDriver.
  2. To make Selenium Server aware of this WebDriver, perform one of the following tasks:
    • Store the Chrome WebDriver binary in the system path
    • Start the Selenium Server with

You can now set Chrome as a browser for your functional test:


Running headless Selenium Server with Xvfb

Sometimes you don’t want the browser to be launched at your desktop during testing, or you may not be using Xserver. To make it work, one of the easiest solutions is to use Xvfb.

Xvfb is an X11 server that performs various graphical operations in memory, without displaying any screen output.

So, let’s try it. At first ensure that you have Xvfb installed on your server. If not, you can install it from your OS repository. For example, to install it on Ubuntu, run the following command:

sudo apt-get install xvfb

Once installed, to run your Selenium Server in Xvfb, run the following command:

DISPLAY=:1 xvfb-run java -jar selenium-server-standalone-2.35.0.jar

Once the server starts, run any of the test case examples. The output should display as follows:

PHPUnit 3.8.0 by Sebastian Bergmann.


Time: 4 seconds, Memory: 6.25Mb

There was 1 failure:

1) WebTest::testTitle
Failed asserting that two strings are equal.
--- Expected
+++ Actual
@@ @@
-'Example WWW Page'
+'Example Domain'


Tests: 1, Assertions: 1, Failures: 1.

That’s all for now. Enjoy testing!

Source references:

  1. PHPUnit and Selenium (official documentation)
  2. Selenium Server downloads list