Lesson Weekend

If you are working on this project from a personal device you may see an error when testing that looks like this:

PHP Fatal error:  Uncaught exception 'PDOException'...

This is a sign that the PDO object you've instantiated does not match the location or credentials of your test database. Most likely the localhost port number in your Class.php and ClassTest.php file doesn't match the MySQL port number in your MAMP preferences. To fix the error open MAMP, click Preferences, click Ports, and make sure the MySQL port number is set to '8889'.

Saving to a Database

In real-world development any of the information a user inputs needs to be saved somewhere. Sometimes that means saving information to a server, but in order for that information to be further curated, you need a complete set of methods that executed different SQL commands. The basic set of methods needed is:

  1. Create (with sql command, INSERT INTO...)
  2. Read (with sql command SELECT...)
  3. Update (with sql command UPDATE...)
  4. Delete (with sql command DELETE...)

You'll hear about this core set of methods refered to from here on out as CRUD. Now that we know the sql commands required to build out CRUD functionality, let's build a to-do list app together. We'll reopen our To Do list app that we made with Silex.

Before we start changing our code, we'll need to open MySQL, create a database named to_do, and make a table called tasks with a primary key and a varchar column called description:

> CREATE DATABASE to_do;
> USE to_do;
> CREATE TABLE tasks (id serial PRIMARY KEY, description VARCHAR (255));

The to_do database will be our production database which eventually becomes our real, working database. We'll also create a test database to use as our development database. This allows us to have a database that we can fill with test data and modify and delete as needed through the development process. Let's create another database called to_do_test with the same schema as the original to_do database. We'll do this by using an application called phpMyAdmin.

phpMyAdmin is a PHP application much like the applications we've been writing. We could access it by navigating to its directory and running $ php -S localhost:8000, but this is a good time to switch from using the built-in PHP server to using a more powerful and commonly-used server called Apache.

Apache is bundled with MAMP. Starting the server is as easy as opening MAMP and clicking "Start Servers".

Next you can browse directly to localhost:8888/phpmyadmin. This opens our phpMyAdmin homepage. phpMyAdmin has the power to manage our databases, users, and much more. For now we're just going to use it to make a copy of our to_do database.

When working at Epicodus, you may be prompted for a username and password. As with our MySQL installation, the default user we'll be logging in with is username: root and password: root.

On the left sidebar of the phpMyAdmin screen you'll see a tree with the names of our databases. In that tree, select the to_do database and a new screen will appear. This view shows us our tables.

We want to select Operations from the tabs at the top of the screen, and that will bring us to the screen where we can create a copy of our to_do database.

phpMyAdmin view 1

In the box labelled Copy database to: we enter the name of our new database, to_do_test. We want to carry over only the tables and columns, but none of the data which may be present, so we'll choose Structure only radio button, and also select the boxes for CREATE DATABASE before copying and Add AUTO_INCREMENT value.

Click Go and you'll see a new database has appeared in the sidebar called to_do_test.

You can now see this database back in the MySQL shell when you run > SHOW DATABASES;

Because we have a lot of the basic functionality of the Task class built already, we will be altering some methods and specs to work with the to_do_test database. Let's start by altering the app.php file.

app/app.php
<?php
    require_once __DIR__."/../vendor/autoload.php";
    require_once __DIR__."/../src/Task.php";

    $app = new Silex\Application();

    $server = 'mysql:host=localhost:8889;dbname=to_do';
    $username = 'root';
    $password = 'root';
    $DB = new PDO($server, $username, $password);

    $app->register(new Silex\Provider\TwigServiceProvider(), array(
        'twig.path' => __DIR__.'/../views'
    ));

    $app->get("/", function() use ($app) {
        return $app['twig']->render('tasks.html.twig', array('tasks' => Task::getAll()));
    });

    $app->post("/tasks", function() use ($app) {
        $task = new Task($_POST['description']);
        $task->save();
        return $app['twig']->render('create_task.html.twig', array('newtask' => $task));
    });

    $app->post("/delete_tasks", function() use ($app) {
        Task::deleteAll();
        return $app['twig']->render('delete_tasks.html.twig');
    });


    return $app;
?>

In our new app.php file, we removed the session_start(); and the creation of an array with the $_SESSION global variable. This is because we no longer need to store information in a user's cookies; we can store it in our database! To set up a connection to our database, we run new PDO, and pass in the information about the server's host, the name of the database we'll use, and the username and password to log in to the database.

Now, let's look at our previous Task.php file.

src/Task.php
<?php
    class Task
    {
        private $description;

        function __construct($description)
        {
            $this->description = $description;
        }

        function setDescription($new_description)
        {
            $this->description = (string) $new_description;
        }

        function getDescription()
        {
            return $this->description;
        }

        function save()
        {
            array_push($_SESSION['list_of_tasks'], $this);
        }

        static function getAll()
        {
            return $_SESSION['list_of_tasks'];
        }

        static function deleteAll()
        {
            $_SESSION['list_of_tasks'] = array();
        }
    }
?>

Since we are no longer using $_SESSION to store our user's tasks, we need to change these methods to work with the database. Let's start with the save() method. Using good BDD practice, we need to start with a test. We know we'll need to connect our test files to the test database:

tests/TaskTest.php
<?php

    /**
    * @backupGlobals disabled
    * @backupStaticAttributes disabled
    */

    require_once "src/Task.php";

    $server = 'mysql:host=localhost:8889;dbname=to_do_test';
    $username = 'root';
    $password = 'root';
    $DB = new PDO($server, $username, $password);


    class TaskTest extends PHPUnit_Framework_TestCase
    {

        function test_save()
        {
            //Arrange
            $description = "Wash the dog";
            $test_task = new Task($description);

            //Act
            $executed = $test_task->save();

            // Assert
            $this->assertTrue($executed, "Task not successfully saved to database");
        }
    }
?>

Notice that we are setting a different $server variable at the top to connect to the to_do_test database, instead of the main database that's used in app.php. We also have a couple little configurations added, @backupGlobals disabled and @backupStaticAttributes disabled. You don't have to worry about what they do; just know that they look like comments but are code that needs to be added to your tests when you're using a database as we are here. If you get an error indicating, "You cannot serialize or unserialize PDO instances", you'll need to make sure these lines look as they do in this sample file.

In our save test we're instantiating a new task, saving it, and then testing whether or not our SQL command returned a truthy value. We'll discuss what that means in a second. For now, we know if we run the test it will fail, since we have no save functionality built in.

So now let's write out our new save() method:

src/Task.php
function save()
{
      $executed = $GLOBALS['DB']->exec("INSERT INTO tasks (description) VALUES ('{$this->getDescription()}');");
      if ($executed) {
            return true;
      } else {
            return false;
      }
}

There are a couple things to break down here. In PHP we use a special kind of associative array called $GLOBALS to access global variables. It should only be used very rarely, and generally only for application-wide configuration settings like your database connection.

So we're calling the PDO class method 'exec()' on our PHP object 'DB'. We can see in the documentation for this method that the return value of this method is the number of rows that were modified or deleted by our executed command. If no rows were modified the method returns 0. This is convenient for us because it allows us to write a quick branch to find out whether our command was successful or not.

You may be asking yourself, "how can we write an if statement using just a number rather than a boolean? Isn't a number different than a 'true' or 'false'?"

Great question! Actually, PHP has a quality commonly referred to as truthiness. That means data types have assigned boolean comparisons. Here's an exhaustive list of comparisons. You'll notice in the documentation that every non-zero integer is comparable to 'True' and the integer '0' is comparable to 'False.'

So in plain english the code snippet above reads: First let's execute this SQL command, then let's save the return value as a new variable, '$executed'. $executed is an integer (specifically, the number of rows our command has affected in the database). If that number is NOT ZERO (comparable to 'True'), we're going to return true. Otherwise we'll return false to represent that the information was not saved.

Running Tests from the Terminal

Go into your project folder in the Terminal and run the command $ ./vendor/bin/phpunit tests to run all of the test functions in your tests folder. If you are using a Mac you can run $ export PATH=$PATH:./vendor/bin first and then you will only have to run $ phpunit tests each time you want to run your tests.

Terminology

production database: The database that is designated to ultimately be the real, working environment that will be used by users and store live data. Development and testing does not happen with this database.

development database: The database that mirrors the production database and is used to complete development and testing tasks. It is safely modified and deleted, as needed, without fear of losing important data.


app/app.php
<?php
    require_once __DIR__."/../vendor/autoload.php";
    require_once __DIR__."/../src/Task.php";

    $app = new Silex\Application();

    $server = 'mysql:host=localhost8889;dbname=to_do';
    $username = 'root';
    $password = 'root';
    $DB = new PDO($server, $username, $password);

    $app->register(new Silex\Provider\TwigServiceProvider(), array(
        'twig.path' => __DIR__.'/../views'
    ));

    $app->get("/", function() use ($app) {
        return $app['twig']->render('tasks.html.twig', array('tasks' => Task::getAll()));
    });

    $app->post("/tasks", function() use ($app) {
        $task = new Task($_POST['description']);
        $task->save();
        return $app['twig']->render('create_task.html.twig', array('newtask' => $task));
    });

    $app->post("/delete_tasks", function() use ($app) {
        Task::deleteAll();
        return $app['twig']->render('delete_tasks.html.twig');
    });


    return $app;
?>
tests/TaskTest.php
<?php

    /**
    * @backupGlobals disabled
    * @backupStaticAttributes disabled
    */

    require_once "src/Task.php";

    $server = 'mysql:host=localhost8889;dbname=to_do_test';
    $username = 'root';
    $password = 'root';
    $DB = new PDO($server, $username, $password);


    class TaskTest extends PHPUnit_Framework_TestCase
    {

        function test_save()
        {
            //Arrange
            $description = "Wash the dog";
            $test_task = new Task($description);

            //Act
            $test_task->save();

            //Assert
            $result = Task::getAll();
            $this->assertEquals($test_task, $result[0]);
        }
    }
?>
src/Task.php
function save()
{
      $GLOBALS['DB']->exec("INSERT INTO tasks (description) VALUES ('{$this->getDescription()}');");
}