Creating a RESTful API with Slim Framework 3, PHP/MySQl

Last updated on February 20, 2018

Slim is a full-featured, open-source PHP micro framework that helps you quickly write simple yet powerful web applications and APIs. It comes with a sophisticated URL dispatcher and middleware architecture that makes it ideal for static websites or API prototyping. It supports all(GET, POST, PUT, DELETE) the HTTP methods.

This article examines Slim in detail, illustrating how you can use it to rapidly build and deploy a REST API with support for authentication and multiple request/response formats.

How to install

If you are using Composer, the PHP dependency manager, simply issue the following command

 $ php composer.phar create-project slim/slim-skeleton [my-app-name]
(or) 
$ composer create-project slim/slim-skeleton [my-app-name]

Replace [my-app-name] with the desired directory name for your new application. The above command will create a project using Slim-Skeleton application and it has below show directory structure.

Now You can run it with PHP’s built-in webserver or you can point your browser with full URL.

$ cd [my-app-name]; 
$ php -S localhost:8080 -t public public/index.php

So after gone through the above steps, if you point your browser to http://locahost:8080/, you would have following output in the browser –

Database design and table

Name your database whatever you want, and run below shown SQL it will create task table.

-- 
-- Table structure for `tasks`
-- 
CREATE TABLE IF NOT EXISTS `tasks` (
  `id` int(11) NOT NULL,
  `task` varchar(200) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `tasks` ADD PRIMARY KEY (`id`);
ALTER TABLE `tasks` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Insert some sample data into the tasks table.

INSERT INTO `tasks` (`id`, `task`, `status`, `created_at`) VALUES
(1, 'Find bugs', 1, '2016-04-10 23:50:40'),
(2, 'Review code', 1, '2016-04-10 23:50:40'),
(3, 'Fix bugs', 1, '2016-04-10 23:50:40'),
(4, 'Refactor Code', 1, '2016-04-10 23:50:40'),
(5, 'Push to prod', 1, '2016-04-10 23:50:50');

Database configuration

Open your src/settings.php file and configure your database setting by adding/editing below showing database config array.

        // Database connection settings
        "db" => [
            "host" => "locahost",
            "dbname" => "your-database-name",
            "user" => "your-mysql-user",
            "pass" => "your-mysql-password"
        ],

Now open your src/dependencies.php file and configure database library. There are many database libraries available for PHP, but this example uses PDO – this is available in PHP as standard so it’s probably useful in every project, or you can use your own libraries by adapting the examples below.

In the below code we are injecting database object into container using dependicy injection, in this case called db:

// PDO database library 
$container['db'] = function ($c) {
    $settings = $c->get('settings')['db'];
    $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);
    return $pdo;
};

We are going to implement following API calls –

Implementing the API calls with Slim

Now that we have our Slim app up and running with the database connection, we need to manage todos in the database.

Getting the Todos list – We are going to create a new route so that when a user hits /todos, it will return a list of all todos in JSON format. Open your src/routes.php and add

// get all todos
	$app->get('/todos', function ($request, $response, $args) {
	 	$sth = $this->db->prepare("SELECT * FROM tasks ORDER BY task");
		$sth->execute();
		$todos = $sth->fetchAll();
	    return $this->response->withJson($todos);
	});

This function simply return all todos information as you can see in this query, to call this API use this URL http://localhost:8080/todos.

Getting single todo – We are going to create a new route so that when a user hits /todo/{id}, it will return a todo in JSON format.

// Retrieve todo with id 
	$app->get('/todo/[{id}]', function ($request, $response, $args) {
	 	$sth = $this->db->prepare("SELECT * FROM tasks WHERE id=:id");
        $sth->bindParam("id", $args['id']);
		$sth->execute();
		$todos = $sth->fetchObject();
	    return $this->response->withJson($todos);
	});

This function check record of given id and return if found anything, to call this API use this URL http://localhost/todo/1.

Find todo by name – We are going to create a new route so that when a user hits /todos/search/{Query}, it will return a list of all matched todos in JSON format.

	// Search for todo with given search teram in their name
	$app->get('/todos/search/[{query}]', function ($request, $response, $args) {
	 	$sth = $this->db->prepare("SELECT * FROM tasks WHERE UPPER(task) LIKE :query ORDER BY task");
        $query = "%".$args['query']."%";
        $sth->bindParam("query", $query);
		$sth->execute();
		$todos = $sth->fetchAll();
	    return $this->response->withJson($todos);
	});

This function search in database for your given query, to call this API use this URL http://localhost/todos/search/bug

Add todo – We are going to create a new route so that when a user sends a post request to /todo with required data, the app will add a new record to the database.

	// Add a new todo
	$app->post('/todo', function ($request, $response) {
        $input = $request->getParsedBody();
        $sql = "INSERT INTO tasks (task) VALUES (:task)";
	 	$sth = $this->db->prepare($sql);
        $sth->bindParam("task", $input['task']);
		$sth->execute();
		$input['id'] = $this->db->lastInsertId();
	    return $this->response->withJson($input);
	});

This API accepts post request and insert submitted data into your database. To call this API use this URL http://localhost/todo

Delete Task – We are going to create a new route so that when a user sends a delete request to /todo/{id}, the app will delete a record from the database.

	// DELETE a todo with given id
	$app->delete('/todo/[{id}]', function ($request, $response, $args) {
	 	$sth = $this->db->prepare("DELETE FROM tasks WHERE id=:id");
        $sth->bindParam("id", $args['id']);
		$sth->execute();
		$todos = $sth->fetchAll();
	    return $this->response->withJson($todos);
	});

Update Task – We are going to create a new route so that when a user sends a put request to /todo/{id} with required data, the app will update a record based on match parameter in the database.

// Update todo with given id
	$app->put('/todo/[{id}]', function ($request, $response, $args) {
        $input = $request->getParsedBody();
        $sql = "UPDATE tasks SET task=:task WHERE id=:id";
	 	$sth = $this->db->prepare($sql);
        $sth->bindParam("id", $args['id']);
        $sth->bindParam("task", $input['task']);
		$sth->execute();
        $input['id'] = $args['id'];
	    return $this->response->withJson($input);
	});

This API accept put request and updates submitted data in your database. To call this API use this URL http://localhost/todo/{id}

Here is the the complete final src/routes.php file

	// get all todos
	$app->get('/todos', function ($request, $response, $args) {
	 	$sth = $this->db->prepare("SELECT * FROM tasks ORDER BY task");
		$sth->execute();
		$todos = $sth->fetchAll();
	    return $this->response->withJson($todos);
	});

	// Retrieve todo with id 
	$app->get('/todo/[{id}]', function ($request, $response, $args) {
	 	$sth = $this->db->prepare("SELECT * FROM tasks WHERE id=:id");
        $sth->bindParam("id", $args['id']);
		$sth->execute();
		$todos = $sth->fetchObject();
	    return $this->response->withJson($todos);
	});


	// Search for todo with given search teram in their name
	$app->get('/todos/search/[{query}]', function ($request, $response, $args) {
	 	$sth = $this->db->prepare("SELECT * FROM tasks WHERE UPPER(task) LIKE :query ORDER BY task");
        $query = "%".$args['query']."%";
        $sth->bindParam("query", $query);
		$sth->execute();
		$todos = $sth->fetchAll();
	    return $this->response->withJson($todos);
	});

	// Add a new todo
	$app->post('/todo', function ($request, $response) {
        $input = $request->getParsedBody();
        $sql = "INSERT INTO tasks (task) VALUES (:task)";
	 	$sth = $this->db->prepare($sql);
        $sth->bindParam("task", $input['task']);
		$sth->execute();
		$input['id'] = $this->db->lastInsertId();
	    return $this->response->withJson($input);
	});
        

	// DELETE a todo with given id
	$app->delete('/todo/[{id}]', function ($request, $response, $args) {
	 	$sth = $this->db->prepare("DELETE FROM tasks WHERE id=:id");
        $sth->bindParam("id", $args['id']);
		$sth->execute();
		$todos = $sth->fetchAll();
	    return $this->response->withJson($todos);
	});

	// Update todo with given id
	$app->put('/todo/[{id}]', function ($request, $response, $args) {
        $input = $request->getParsedBody();
        $sql = "UPDATE tasks SET task=:task WHERE id=:id";
	 	$sth = $this->db->prepare($sql);
        $sth->bindParam("id", $args['id']);
        $sth->bindParam("task", $input['task']);
		$sth->execute();
        $input['id'] = $args['id'];
	    return $this->response->withJson($input);
	});