Unittesting in PHP and SQL

Question

I'm trying to write a unittest for a PHP project using PHPUnit. The project is mainly working with an SQL database.

Any function I have uses SQL, and I don't know how to start with writing tests. I have a problem finding what tests to do, since they all work with SQL, and most of them doesn't return anything, but just manipulate the database and session variables.

This is one of the attempts for creating a test:

    <?php
    require_once '../../test/admin/admin.php';

    class checkIsAdminTest extends PHPUnit_Framework_TestCase {
        private $ID = '123456789';
        private $Class = 0;
        private $Pass = 'Aa12345678';
        private $First = "Bla";
        private $Last = "Bla";
        private $Email = "[email protected]";

        protected function setUp() {
            parent::setUp();

            $servername = "localhost";
            $username = "root";
            $password = "";

            $dbname = 'trivia';
            $conn = mysqli_connect($servername, $username, $password, $dbname);

            $sql = "INSERT INTO `users` (`ID`, `Class`, `Password`, `First`, `Last`, `Email`) VALUES ('" . $this->ID . "', '" . $this->Class . "', '" . sha1($this->Pass) . "', '" . $this->First . "', '" . $this->Last . "', '" . $this->Email . "');";
            mysqli_query($conn, $sql);

            session_start();
            $_SESSION["ID"] = $this->ID;
        }

        protected function tearDown() {
            parent::tearDown();
        }

        public function __construct() {
        }

        public function testCheckIsAdmin() {
            $this->assertEquals($this->Class == 0 ? False : True, checkIsAdmin());
        }
    }
?>

The function checks if the vale of the field "Class" of a record in a Table named "Users" is 0 or 1, returns "true" if it's 1 and false otherwise.

GordonM · Accepted Answer · 2017-01-26 16:52:23Z

This issue you are having is an indication of a bigger problem with your current design (a "code smell"), namely that you have one class that is mixing two concerns, that of storage (the mysql stuff) and the business logic itself. These should be separate concerns and therefore separate classes, and you should think about how to untangle the two concerns (and then you should use Dependency Injection to couple these two concerns together, by passing the database object into the business object by something such as a constructor argument).

Once you've managed to separate them out you will be able to unit the two modules separately. For the class that implements the business logic you can substitute the SQL class with a mock object where you can control what response the object being tested receives and not have to rely on a real database that may or may not have the right data for the test in it.

An important aspect of unit testing is that you must be able to divide your code into distinct units that can be tested as independently as possible. This requires you to think about the architecture of your application more than you normally would. This doesn't only have advantages for testability, it also makes your code a lot more flexible and adaptable. For example, what if you decide to add a cache layer between the database and the business object? With modular code it should be relatively easy to write an object that can act in the same way as the database class but maintains its own internal cache, only passing requests to the actual database if the data requested isn't in the cache. This should be invisible to the client class. The same is also true if you decide to replace MySQL with, for example, a BigTable in Google Cloud.

Collectives™ on Stack Overflow

Unittesting in PHP and SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related