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.' )); }); });