A PHP PDO mysql helper class used in SlimPHP applications

This is a very simple example on how to use SlimPHP and PDO in a basic API that updates, lists and deletes entries from a table. If you are new to building APIs with SlimPHP, then this is a good starting point.

The class itself:

<?php

namespace PHPapi\Classes;

use \PDO;

/**
 * Database helper
 */
class Database
{
    /**
     * Private variable to store the connection
     * @var Object
     */
    private $connection;

    /**
     * Constructor for the database function
     * @param Array $settings List of settings
     */
    public function __construct($settings)
    {
        try {
            $pdo = new PDO("mysql:host=" . $settings['host'] . ";dbname=" . $settings['dbname'], $settings['user'], $settings['pass']);

            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

            $this->connection = $pdo;
        } catch (PDOException $e) {
            throw new Exception('Connection failed: ' . $e->getMessage());
        }
    }

    /**
     * Wrapper to query the Database
     * @param  String $sql    SQL command
     * @param  Array  $params Parameters for the command
     * @return Object         The output of the command
     */
    public function query($sql, $params)
    {
        try {
            $query = $this->connection->prepare($sql);
            $query->execute($params);

            return $query;
        } catch (PDOException $e) {
            throw new Exception('Query failed: ' . $e->getMessage());
        }
    }

    /**
     * Get the last insert id from the SQL Server
     * @return Integer  The last insert id
     */
    public function lastInsertId()
    {
        return $this->connection->lastInsertId();
    }

    /**
     * Date format helper
     * @param  string $format The format
     * @return string         The date formatted as requested
     */
    public function getDate($format="Y-m-d H:i:s")
    {
        return date($format);
    }
}

The database dependency:

<?php

// Mysql query wrapper
// Please load this after the db.php file

$container = $app->getContainer();

$container['db'] = function ($c) {
    $settings = $c->get('settings')['db'];

    $db = new PHPapi\Classes\Database($settings);

    return $db;
};

Example usage:

<?php

$app->group('/users', function () {
    $this->post('/set', function ($request, $response, $args) {
        $body = $request->getParsedBody();

        if (!isset($body['id'])) {
            // create the user
            $this->db->query("insert into users(name, pass) values(:name, :pass)", array(
                'name' => $body['name'],
                'pass' => md5($body['pass'])
            ));

            $id = $this->db->lastInsertId();
        } else {
            $this->db->query("update users set name=:name where user_id=:id", array(
                'name' => $body['name'],
                'id' => $body['id']
            ));

            $id = $body['id'];
        }

        return $response->withStatus(200)->withJson(array(
            'success' => true,
            'data' => array(
                'id' => $id
            ),
            'message' => 'The information has been saved.'
        ));
    });

    $this->get('/list', function ($request, $response, $args) use ($config) {
        $body = $request->getParsedBody();
        $query = $request->getQueryParams();
        $token = $this->token->decode();

        $data = $this->db->query("select * from users order by name asc", array())->fetchAll();

        foreach ($data as $key=>$value) {
            $data[$key]['pass']  = '';
        }

        return $response->withStatus(200)->withJson(array(
            'success' => true,
            'data' => $data,
            'message' => 'The information has been selected.'
        ));
    });

    $this->get('/delete', function ($request, $response, $args) use ($config) {
        $query = $request->getQueryParams();

        // delete the user data
        $this->db->query("delete from users where user_id=:id", array(
            'id' => $query['id']
        ));

        return $response->withStatus(200)->withJson(array(
            'success' => true,
            'data' => array(
                'id' => $query['id'],
            ),
            'message' => 'The information has been deleted.'
        ));
    });
});