Build a Simple REST API with Slim Framework

REST stands for Representational State Transfer. RESTful Web services are one way of providing interoperability between computer systems on the Internet. Rest API help to communicate between the client app and the server application.REST is an architecture style for designing networked applications. A REST API defines a bunch of functions which developers can perform requests and receive responses via HTTP protocol such as GET and POST.

In this tutorial, you will learn to how create CRUD (create, read, update, delete) operation REST API with Slim Framework. This is a very simple example, you can just copy paste, and change according to your requirement.

Also Read : Build Simple REST API with PHP and MySQL

Before started to implement the REST API with Slim Framework, look files structure:

build-simple-rest-api-with-slim-framework
- logs
- public
  - index.php
  - .htaccess
- src
  - config
    - config.php
  - libs
    - auth.php
    - DBConnection.php
    - mail.php
  - models
    - Employee.php
  - routes
    - api.php
- vendor
- .env

Understanding REST API

REST provides a block of HTTP methods which are used to alter the data. The following are common HTTP methods:

GET — is used for reading and retrieving data.
POST — is used for inserting data.
PUT/PATCH — is used for updating data.
DELETE — is used for deleting data.

Step 1: Create MySQL Database and Table

For this tutorial, you need a MySQL database with the following table:

	CREATE TABLE `employee` (
	  `id` int(11) NOT NULL COMMENT 'primary key',
	  `name` varchar(255) NOT NULL COMMENT 'Employee Name',
	  `email` varchar(255) NOT NULL COMMENT 'Email Address',
	  `salary` float(10,2) NOT NULL COMMENT 'employee salary',
	  `age` int(11) NOT NULL COMMENT 'employee age'
	) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table';

	INSERT INTO `employee` (`id`, `name`, `email`, `salary`, `age`) VALUES
		(1, 'Nixon Tiger', 'tiger@techarise.com', 3208000.00, 61);

	ALTER TABLE `employee`
  		ADD PRIMARY KEY (`id`);

	ALTER TABLE `employee`
  		MODIFY `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', AUTO_INCREMENT=2;

CREATE TABLE `employee` (

`id` int(11) NOT NULL COMMENT 'primary key',

`name` varchar(255) NOT NULL COMMENT 'Employee Name',

`email` varchar(255) NOT NULL COMMENT 'Email Address',

`salary` float(10,2) NOT NULL COMMENT 'employee salary',

`age` int(11) NOT NULL COMMENT 'employee age'

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table';

INSERT INTO `employee` (`id`, `name`, `email`, `salary`, `age`) VALUES

(1, 'Nixon Tiger', 'tiger@techarise.com', 3208000.00, 61);

ALTER TABLE `employee`

ADD PRIMARY KEY (`id`);

ALTER TABLE `employee`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', AUTO_INCREMENT=2;

Step 2: Create file named .env

loads environment variables

	# Configuration for Database
	DB_HOST="localhost"
	DB_USER="root"
	DB_PASS=""
	DB_NAME="techarise_DB"

	# Secret for JWT Auth
	SECRET_KEY="secret"

	# Configuration for Mail
	SERVER_URL="https://techarise.com"
	MAIL_HOST="xxxx.xxxx.com"
	MAIL_USER="xxxx"
	MAIL_PASS="xxxxx"
	MAIL_USER_EMAIL="xxxxx@xxx.com"
	MAIL_NAME="TechArise"

# Configuration for Database

DB_HOST="localhost"

DB_USER="root"

DB_PASS=""

DB_NAME="techarise_DB"

# Secret for JWT Auth

SECRET_KEY="secret"

# Configuration for Mail

SERVER_URL="https://techarise.com"

MAIL_HOST="xxxx.xxxx.com"

MAIL_USER="xxxx"

MAIL_PASS="xxxxx"

MAIL_USER_EMAIL="xxxxx@xxx.com"

MAIL_NAME="TechArise"

Step 3: Connect to Database file named DBConnection.php inside “src/libs/” folder.

The code below shows the database credentials

<?php
/**
 * @package Rest API (DBConnection)
 *
 * @author TechArise Team
 *
 * @email  info@techarise.com
 *   
 */

// Database Connection
class DBConnection {
    
   public static function getConnection() {
        $_dbHostname = DB_HOST;
        $_dbName = DB_NAME;
        $_dbUsername = DB_USER;
        $_dbPassword = DB_PASS;
    	try {
        	$_con = new PDO("mysql:host=$_dbHostname;dbname=$_dbName", $_dbUsername, $_dbPassword);    
        	$_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $_con->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
	    } catch(PDOException $e) {
			echo "Connection failed: " . $e->getMessage();
		}
        return $_con;
    }
    
}
?>

<?php

/**

* @package Rest API (DBConnection)

* @author TechArise Team

* @email info@techarise.com

// Database Connection

class DBConnection {

public static function getConnection() {

$_dbHostname = DB_HOST;

$_dbName = DB_NAME;

$_dbUsername = DB_USER;

$_dbPassword = DB_PASS;

try {

$_con = new PDO("mysql:host=$_dbHostname;dbname=$_dbName", $_dbUsername, $_dbPassword);

$_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$_con->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

} catch(PDOException $e) {

echo "Connection failed: " . $e->getMessage();

}

return $_con;

}

Step 4: Create class

Create a class file named Employee.php inside “src/models/” folder.

The Employee class handles the CRUD process
__construct() — Loads the required DBConnection.
empAdd() — Add Employee Record.
empUpdate — Update Employee Record.
empList() — get Employee all Records.
empDetails() — get Employee single Record.
empDelete() — delete Employee Record.

<?php
/**
 * @package Employee WebAPI
 *
 * @author Techarise Team
 *
 * @email  info@techarise.com
 *   
 * @ Version 1.0.0 
 */
 
class Employee
{
	protected $_db;
	public function __construct() {
        $this->_db = DBConnection::getConnection();
    }

	// add emp Method
    public function empAdd($request) {
    	// @var string $guid - Unique ID
		$guid = uniqid();
		// @var string $name - Name
		$name = $request->getParam("name");
		// @var string $email - Email
		$email = trim(strtolower($request->getParam("email")));
		// @var string $salary - salary
		$salary = $request->getParam("salary");
		// @var string $age - age
		$age = $request->getParam("age");
		try{

			$sql = "SELECT	*
					FROM	employee
					WHERE	email = :email";
			$stmt = $this->_db->prepare($sql);
			$stmt->bindParam(":email", $email);
			$stmt->execute();
			$query = $stmt->fetchObject();

			if($query) {
				$data["status"] = "Error: Your account cannot be created at this time. Please try again later.";
			} else {
				// Gets the user into the database
				$sql = "INSERT INTO	employee (name, email, salary, age)
						VALUES		(:name, :email, :salary, :age)";

				$stmt = $this->_db->prepare($sql);

				$stmt->bindParam(":name", $name);
				$stmt->bindParam(":email", $email);
				$stmt->bindParam(":salary", $salary);
				$stmt->bindParam(":age", $age);

				$stmt->execute();
				$result = $this->_db->lastInsertId();
				if ($result) {
					$data["status"] = "Your account has been successfully created.";
				} else {
					$data["status"] = "Error: Your account cannot be create at this time. Please try again later.";
				}
			}
    
    	return $data; 
    } catch(PDOException $e) {
			echo "Error: ".$e->getMessage();
		}
	}

	// emp listing Method
    public function empUpdate($request) {
    	//@var string $guid - Unique ID
		$guid = uniqid();
		// @var string $age - age
		$emp_id = $request->getParam("emp_id");
		// @var string $name - Name
		$name = $request->getParam("name");
		// @var string $email - Email
		$email = trim(strtolower($request->getParam("email")));
		// @var string $salary - salary
		$salary = $request->getParam("salary");
		// @var string $age - age
		$age = $request->getParam("age");

		try{
			$sql = "UPDATE	employee
					SET name = :name, 
						email = :email, 
						salary = :salary, 
						age = :age 
					WHERE	id = :emp_id";
			$stmt = $this->_db->prepare($sql);
			
			$stmt->bindParam(":name", $name);
			$stmt->bindParam(":email", $email);
			$stmt->bindParam(":salary", $salary);
			$stmt->bindParam(":age", $age);
			$stmt->bindParam(":emp_id", $emp_id);
			$result = $stmt->execute();
			if ($result) {
				$data["status"] = "Your account has been successfully updated.";
			} else {
				$data["status"] = "Error: Your account cannot be updated at this time. Please try again later.";
			}
    	return $data; 
    	} catch(PDOException $e) {
			echo "Error: ".$e->getMessage();
		}
    
	}

    // emp listing Method
    public function empList($request) {
    	//@var string $guid - Unique ID
		$guid = uniqid();
		//@var string $token - Activation token
		$token = bin2hex(openssl_random_pseudo_bytes(16));
		try{
			$sql = "SELECT e.id, e.name, e.email, e.salary, e.age
					FROM employee as e
					ORDER BY e.id DESC";
			$stmt = $this->_db->prepare($sql);
			$stmt->bindParam(":post_status", $publish);
			$stmt->execute();
			$query = $stmt->fetchAll();
			$data = $query;
    	return $data; 
    	} catch(PDOException $e) {
			echo "Error: ".$e->getMessage();
		}
    
	}
 	// get emp details  Method
    public function empDetails($request) {
    	// @var string $guid - Unique ID
		$guid = uniqid();
		$emp_id = $request->getAttribute("emp_id");
		try{
			$sql = "SELECT e.id, e.name, e.email, e.salary, e.age
					FROM employee as e
					WHERE	e.id = :emp_id";
			$stmt = $this->_db->prepare($sql);
			$stmt->bindParam(":emp_id", $emp_id);
			$stmt->execute();
			$query = $stmt->fetch(\PDO::FETCH_ASSOC);
			$data = $query;
	    	return $data; 
	    } catch(PDOException $e){
			echo "Error: ".$e->getMessage();
		}
    
	}

	// delete emp method
	public function empDelete($request) {
		// @var string $guid - Unique ID
		$guid = uniqid();
		$emp_id = $request->getAttribute("emp_id");
		try{
			// Delete the quote
			$sql = "DELETE FROM	employee
					WHERE id = :emp_id";
			$stmt = $this->_db->prepare($sql);
			$stmt->bindParam(":emp_id", $emp_id);
			$result = $stmt->execute();
			if ($result) {
				$data["status"] = "Your account has been successfully deleted.";
			} else {
				$data["status"] = "Error: Your account cannot be delete at this time. Please try again later.";
			}
			return $data; 
		} catch(PDOException $e){
			echo "Error: ".$e->getMessage();
		}
	}
}
?>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

<?php

/**

* @package Employee WebAPI

* @author Techarise Team

* @email info@techarise.com

* @ Version 1.0.0

class Employee

{

protected $_db;

public function __construct() {

$this->_db = DBConnection::getConnection();

}

// add emp Method

public function empAdd($request) {

// @var string $guid - Unique ID

$guid = uniqid();

// @var string $name - Name

$name = $request->getParam("name");

// @var string $email - Email

$email = trim(strtolower($request->getParam("email")));

// @var string $salary - salary

$salary = $request->getParam("salary");

// @var string $age - age

$age = $request->getParam("age");

try{

$sql = "SELECT *

FROM employee

WHERE email = :email";

$stmt = $this->_db->prepare($sql);

$stmt->bindParam(":email", $email);

$stmt->execute();

$query = $stmt->fetchObject();

if($query) {

$data["status"] = "Error: Your account cannot be created at this time. Please try again later.";

} else {

// Gets the user into the database

$sql = "INSERT INTO employee (name, email, salary, age)

VALUES (:name, :email, :salary, :age)";

$stmt = $this->_db->prepare($sql);

$stmt->bindParam(":name", $name);

$stmt->bindParam(":email", $email);

$stmt->bindParam(":salary", $salary);

$stmt->bindParam(":age", $age);

$stmt->execute();

$result = $this->_db->lastInsertId();

if ($result) {

$data["status"] = "Your account has been successfully created.";

} else {

$data["status"] = "Error: Your account cannot be create at this time. Please try again later.";

}

return $data;

} catch(PDOException $e) {

echo "Error: ".$e->getMessage();

}

// emp listing Method

public function empUpdate($request) {

//@var string $guid - Unique ID

$guid = uniqid();

// @var string $age - age

$emp_id = $request->getParam("emp_id");

// @var string $name - Name

$name = $request->getParam("name");

// @var string $email - Email

$email = trim(strtolower($request->getParam("email")));

// @var string $salary - salary

$salary = $request->getParam("salary");

// @var string $age - age

$age = $request->getParam("age");

try{

$sql = "UPDATE employee

SET name = :name,

email = :email,

salary = :salary,

age = :age

WHERE id = :emp_id";

$stmt = $this->_db->prepare($sql);

$stmt->bindParam(":name", $name);

$stmt->bindParam(":email", $email);

$stmt->bindParam(":salary", $salary);

$stmt->bindParam(":age", $age);

$stmt->bindParam(":emp_id", $emp_id);

$result = $stmt->execute();

if ($result) {

$data["status"] = "Your account has been successfully updated.";

} else {

$data["status"] = "Error: Your account cannot be updated at this time. Please try again later.";

}

return $data;

} catch(PDOException $e) {

echo "Error: ".$e->getMessage();

}

// emp listing Method

public function empList($request) {

//@var string $guid - Unique ID

$guid = uniqid();

//@var string $token - Activation token

$token = bin2hex(openssl_random_pseudo_bytes(16));

try{

$sql = "SELECT e.id, e.name, e.email, e.salary, e.age

FROM employee as e

ORDER BY e.id DESC";

$stmt = $this->_db->prepare($sql);

$stmt->bindParam(":post_status", $publish);

$stmt->execute();

$query = $stmt->fetchAll();

$data = $query;

return $data;

} catch(PDOException $e) {

echo "Error: ".$e->getMessage();

}

// get emp details Method

public function empDetails($request) {

// @var string $guid - Unique ID

$guid = uniqid();

$emp_id = $request->getAttribute("emp_id");

try{

$sql = "SELECT e.id, e.name, e.email, e.salary, e.age

FROM employee as e

WHERE e.id = :emp_id";

$stmt = $this->_db->prepare($sql);

$stmt->bindParam(":emp_id", $emp_id);

$stmt->execute();

$query = $stmt->fetch(\PDO::FETCH_ASSOC);

$data = $query;

return $data;

} catch(PDOException $e){

echo "Error: ".$e->getMessage();

}

// delete emp method

public function empDelete($request) {

// @var string $guid - Unique ID

$guid = uniqid();

$emp_id = $request->getAttribute("emp_id");

try{

// Delete the quote

$sql = "DELETE FROM employee

WHERE id = :emp_id";

$stmt = $this->_db->prepare($sql);

$stmt->bindParam(":emp_id", $emp_id);

$result = $stmt->execute();

if ($result) {

$data["status"] = "Your account has been successfully deleted.";

} else {

$data["status"] = "Error: Your account cannot be delete at this time. Please try again later.";

}

return $data;

} catch(PDOException $e){

echo "Error: ".$e->getMessage();

}

Step 5: Add Employee Record — POST Method

Create a route and sends a post request to /employee/add with required data, the app will add a new record to the database.

We will check for POST HTTP request and call method empAdd() to insert emp data to MySQL database table and return JSON data

<?php
$app->post("/employee/add", function (Request $request, Response $response) {	
		$emp = new Employee();		
		try {
			$data = $emp->empAdd($request);
			$response = $response->withHeader("Content-Type", "application/json");
			$response = $response->withStatus(200, "OK");
			$response = $response->getBody()->write(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT));			
			return $response;
		} catch (PDOException $e) {
			$this["logger"]->error("DataBase Error: {$e->getMessage()}");
		} catch (Exception $e) {
			$this["logger"]->error("General Error: {$e->getMessage()}");
		}
		finally {
			// Destroy the database connection
			$conn = null;
		}
	});
?>

<?php

$app->post("/employee/add", function (Request $request, Response $response) {

$emp = new Employee();

try {

$data = $emp->empAdd($request);

$response = $response->withHeader("Content-Type", "application/json");

$response = $response->withStatus(200, "OK");

$response = $response->getBody()->write(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT));

return $response;

} catch (PDOException $e) {

$this["logger"]->error("DataBase Error: {$e->getMessage()}");

} catch (Exception $e) {

$this["logger"]->error("General Error: {$e->getMessage()}");

}

finally {

// Destroy the database connection

$conn = null;

}

});

In this method empAdd() from class Employee.php, we will insert record into emp table.

<?php
// add emp Method
    public function empAdd($request) {
    	// @var string $guid - Unique ID
		$guid = uniqid();
		// @var string $name - Name
		$name = $request->getParam("name");
		// @var string $email - Email
		$email = trim(strtolower($request->getParam("email")));
		// @var string $salary - salary
		$salary = $request->getParam("salary");
		// @var string $age - age
		$age = $request->getParam("age");
		try{

			$sql = "SELECT	*
					FROM	employee
					WHERE	email = :email";
			$stmt = $this->_db->prepare($sql);
			$stmt->bindParam(":email", $email);
			$stmt->execute();
			$query = $stmt->fetchObject();

			if($query) {
				$data["status"] = "Error: Your account cannot be created at this time. Please try again later.";
			} else {
				// Gets the user into the database
				$sql = "INSERT INTO	employee (name, email, salary, age)
						VALUES		(:name, :email, :salary, :age)";

				$stmt = $this->_db->prepare($sql);

				$stmt->bindParam(":name", $name);
				$stmt->bindParam(":email", $email);
				$stmt->bindParam(":salary", $salary);
				$stmt->bindParam(":age", $age);

				$stmt->execute();
				$result = $this->_db->lastInsertId();
				if ($result) {
					$data["status"] = "Your account has been successfully created.";
				} else {
					$data["status"] = "Error: Your account cannot be create at this time. Please try again later.";
				}
			}
    
    	return $data; 
    } catch(PDOException $e) {
			echo "Error: ".$e->getMessage();
		}
	}
?>

<?php

// add emp Method

public function empAdd($request) {

// @var string $guid - Unique ID

$guid = uniqid();

// @var string $name - Name

$name = $request->getParam("name");

// @var string $email - Email

$email = trim(strtolower($request->getParam("email")));

// @var string $salary - salary

$salary = $request->getParam("salary");

// @var string $age - age

$age = $request->getParam("age");

try{

$sql = "SELECT *

FROM employee

WHERE email = :email";

$stmt = $this->_db->prepare($sql);

$stmt->bindParam(":email", $email);

$stmt->execute();

$query = $stmt->fetchObject();

if($query) {

$data["status"] = "Error: Your account cannot be created at this time. Please try again later.";

} else {

// Gets the user into the database

$sql = "INSERT INTO employee (name, email, salary, age)

VALUES (:name, :email, :salary, :age)";

$stmt = $this->_db->prepare($sql);

$stmt->bindParam(":name", $name);

$stmt->bindParam(":email", $email);

$stmt->bindParam(":salary", $salary);

$stmt->bindParam(":age", $age);

$stmt->execute();

$result = $this->_db->lastInsertId();

if ($result) {

$data["status"] = "Your account has been successfully created.";

} else {

$data["status"] = "Error: Your account cannot be create at this time. Please try again later.";

}

return $data;

} catch(PDOException $e) {

echo "Error: ".$e->getMessage();

}

Step 6: Read Employee Record from the Database — GET Method

Create a route /employee and /employee/{emp_id} to get emp records to MySQL database. We will check for GET HTTP request and call method empDetails() or empList() to get emp data to MySQL database table and return JSON response

<?php
$app->get("/employee", function (Request $request, Response $response) {
	$emp = new Employee();			
	try {
		$data = $emp->empList($request);
		$response = $response->withHeader("Content-Type", "application/json");
		$response = $response->withStatus(200, "OK");
		$response = $response->getBody()->write(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT));			
		return $response;
	} catch (PDOException $e) {
		$this["logger"]->error("DataBase Error: {$e->getMessage()}");
	} catch (Exception $e) {
		$this["logger"]->error("General Error: {$e->getMessage()}");
	}
	finally {
		// Destroy the database connection
		$conn = null;
	}
});

$app->get("/employee/{emp_id}", function (Request $request, Response $response) {	
		$emp = new Employee();		
		try {
			$data = $emp->empDetails($request);
			$response = $response->withHeader("Content-Type", "application/json");
			$response = $response->withStatus(200, "OK");
			$response = $response->getBody()->write(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT));			
			return $response;
		} catch (PDOException $e) {
			$this["logger"]->error("DataBase Error: {$e->getMessage()}");
		} catch (Exception $e) {
			$this["logger"]->error("General Error: {$e->getMessage()}");
		}
		finally {
			// Destroy the database connection
			$conn = null;
		}
	});
?>

<?php

$app->get("/employee", function (Request $request, Response $response) {

$emp = new Employee();

try {

$data = $emp->empList($request);

$response = $response->withHeader("Content-Type", "application/json");

$response = $response->withStatus(200, "OK");

$response = $response->getBody()->write(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT));

return $response;

} catch (PDOException $e) {

$this["logger"]->error("DataBase Error: {$e->getMessage()}");

} catch (Exception $e) {

$this["logger"]->error("General Error: {$e->getMessage()}");

}

finally {

// Destroy the database connection

$conn = null;

}

});

$app->get("/employee/{emp_id}", function (Request $request, Response $response) {

$emp = new Employee();

try {

$data = $emp->empDetails($request);

$response = $response->withHeader("Content-Type", "application/json");

$response = $response->withStatus(200, "OK");

$response = $response->getBody()->write(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT));

return $response;

} catch (PDOException $e) {

$this["logger"]->error("DataBase Error: {$e->getMessage()}");

} catch (Exception $e) {

$this["logger"]->error("General Error: {$e->getMessage()}");

}

finally {

// Destroy the database connection

$conn = null;

}

});

In this method empDetails() or empList() from class Employee.php inside “src/models”, we will get record(s) into emp table.

Get All emp Records

<?php
// emp listing Method
public function empList($request) {
	//@var string $guid - Unique ID
	$guid = uniqid();
	//@var string $token - Activation token
	$token = bin2hex(openssl_random_pseudo_bytes(16));
	try{
		$sql = "SELECT e.id, e.name, e.email, e.salary, e.age
				FROM employee as e
				ORDER BY e.id DESC";
		$stmt = $this->_db->prepare($sql);
		$stmt->bindParam(":post_status", $publish);
		$stmt->execute();
		$query = $stmt->fetchAll();
		$data = $query;
	return $data; 
	} catch(PDOException $e) {
		echo "Error: ".$e->getMessage();
	}
}
?>

<?php

// emp listing Method

public function empList($request) {

//@var string $guid - Unique ID

$guid = uniqid();

//@var string $token - Activation token

$token = bin2hex(openssl_random_pseudo_bytes(16));

try{

$sql = "SELECT e.id, e.name, e.email, e.salary, e.age

FROM employee as e

ORDER BY e.id DESC";

$stmt = $this->_db->prepare($sql);

$stmt->bindParam(":post_status", $publish);

$stmt->execute();

$query = $stmt->fetchAll();

$data = $query;

return $data;

} catch(PDOException $e) {

echo "Error: ".$e->getMessage();

}

Get emp single Record

<?php
// get emp details  Method
public function empDetails($request) {
	// @var string $guid - Unique ID
	$guid = uniqid();
	$emp_id = $request->getAttribute("emp_id");
	try{
		$sql = "SELECT e.id, e.name, e.email, e.salary, e.age
				FROM employee as e
				WHERE	e.id = :emp_id";
		$stmt = $this->_db->prepare($sql);
		$stmt->bindParam(":emp_id", $emp_id);
		$stmt->execute();
		$query = $stmt->fetch(\PDO::FETCH_ASSOC);
		$data = $query;
    	return $data; 
    } catch(PDOException $e){
		echo "Error: ".$e->getMessage();
	}

}
?>

<?php

// get emp details Method

public function empDetails($request) {

// @var string $guid - Unique ID

$guid = uniqid();

$emp_id = $request->getAttribute("emp_id");

try{

$sql = "SELECT e.id, e.name, e.email, e.salary, e.age

FROM employee as e

WHERE e.id = :emp_id";

$stmt = $this->_db->prepare($sql);

$stmt->bindParam(":emp_id", $emp_id);

$stmt->execute();

$query = $stmt->fetch(\PDO::FETCH_ASSOC);

$data = $query;

return $data;

} catch(PDOException $e){

echo "Error: ".$e->getMessage();

}

Step 8: Update Employee Record — POST Method

Create a route /employee/update to update emp records to MySQL database. We will check for POST HTTP request and call method empUpdate() to update emp data to MySQL database table and return JSON response

<?php
$app->post("/employee/update", function (Request $request, Response $response) {
		$emp = new Employee();		
		try {
			$data = $emp->empUpdate($request);
			$response = $response->withHeader("Content-Type", "application/json");
			$response = $response->withStatus(200, "OK");
			$response = $response->getBody()->write(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT));			
				return $response;
		} catch (PDOException $e) {
			$this["logger"]->error("DataBase Error: {$e->getMessage()}");
		} catch (Exception $e) {
			$this["logger"]->error("General Error: {$e->getMessage()}");
		}
		finally {
			// Destroy the database connection
			$conn = null;
		}
	});
?>

<?php

$app->post("/employee/update", function (Request $request, Response $response) {

$emp = new Employee();

try {

$data = $emp->empUpdate($request);

$response = $response->withHeader("Content-Type", "application/json");

$response = $response->withStatus(200, "OK");

$response = $response->getBody()->write(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT));

return $response;

} catch (PDOException $e) {

$this["logger"]->error("DataBase Error: {$e->getMessage()}");

} catch (Exception $e) {

$this["logger"]->error("General Error: {$e->getMessage()}");

}

finally {

// Destroy the database connection

$conn = null;

}

});

In this method empUpdate() from class Employee.php inside “src/models”, we will update record into emp table.

<?php
// emp listing Method
public function empUpdate($request) {
	//@var string $guid - Unique ID
	$guid = uniqid();
	// @var string $age - age
	$emp_id = $request->getParam("emp_id");
	// @var string $name - Name
	$name = $request->getParam("name");
	// @var string $email - Email
	$email = trim(strtolower($request->getParam("email")));
	// @var string $salary - salary
	$salary = $request->getParam("salary");
	// @var string $age - age
	$age = $request->getParam("age");

	try{
		$sql = "UPDATE	employee
				SET name = :name, 
					email = :email, 
					salary = :salary, 
					age = :age 
				WHERE	id = :emp_id";
		$stmt = $this->_db->prepare($sql);
		
		$stmt->bindParam(":name", $name);
		$stmt->bindParam(":email", $email);
		$stmt->bindParam(":salary", $salary);
		$stmt->bindParam(":age", $age);
		$stmt->bindParam(":emp_id", $emp_id);
		$result = $stmt->execute();
		if ($result) {
			$data["status"] = "Your account has been successfully updated.";
		} else {
			$data["status"] = "Error: Your account cannot be updated at this time. Please try again later.";
		}
	return $data; 
	} catch(PDOException $e) {
		echo "Error: ".$e->getMessage();
	}

}
?>

<?php

// emp listing Method

public function empUpdate($request) {

//@var string $guid - Unique ID

$guid = uniqid();

// @var string $age - age

$emp_id = $request->getParam("emp_id");

// @var string $name - Name

$name = $request->getParam("name");

// @var string $email - Email

$email = trim(strtolower($request->getParam("email")));

// @var string $salary - salary

$salary = $request->getParam("salary");

// @var string $age - age

$age = $request->getParam("age");

try{

$sql = "UPDATE employee

SET name = :name,

email = :email,

salary = :salary,

age = :age

WHERE id = :emp_id";

$stmt = $this->_db->prepare($sql);

$stmt->bindParam(":name", $name);

$stmt->bindParam(":email", $email);

$stmt->bindParam(":salary", $salary);

$stmt->bindParam(":age", $age);

$stmt->bindParam(":emp_id", $emp_id);

$result = $stmt->execute();

if ($result) {

$data["status"] = "Your account has been successfully updated.";

} else {

$data["status"] = "Error: Your account cannot be updated at this time. Please try again later.";

}

return $data;

} catch(PDOException $e) {

echo "Error: ".$e->getMessage();

}

Step 8: Delete Employee Record from the Database — GET Method

Create a route /employee/delete/{emp_id} to delete emp record to MySQL database. We will check for GET HTTP request and call method empDelete() to delete emp data to MySQL database table and return JSON response

<?php
$app->get("/employee/delete/{emp_id}", function (Request $request, Response $response) {
	$emp = new Employee();			
	try {
		$data = $emp->empDelete($request);
		$response = $response->withHeader("Content-Type", "application/json");
		$response = $response->withStatus(200, "OK");
		$response = $response->getBody()->write(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT));			
		return $response;
	} catch (PDOException $e) {
		$this["logger"]->error("DataBase Error: {$e->getMessage()}");
	} catch (Exception $e) {
		$this["logger"]->error("General Error: {$e->getMessage()}");
	}
	finally {
		// Destroy the database connection
		$conn = null;
	}
});
?>

<?php

$app->get("/employee/delete/{emp_id}", function (Request $request, Response $response) {

$emp = new Employee();

try {

$data = $emp->empDelete($request);

$response = $response->withHeader("Content-Type", "application/json");

$response = $response->withStatus(200, "OK");

$response = $response->getBody()->write(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT));

return $response;

} catch (PDOException $e) {

$this["logger"]->error("DataBase Error: {$e->getMessage()}");

} catch (Exception $e) {

$this["logger"]->error("General Error: {$e->getMessage()}");

}

finally {

// Destroy the database connection

$conn = null;

}

});

In this method empDelete() from class Employee.php inside “src/models”, we will delete record into emp table.

<?php
// delete emp method
public function empDelete($request) {
	// @var string $guid - Unique ID
	$guid = uniqid();
	$emp_id = $request->getAttribute("emp_id");
	try{
		// Delete the quote
		$sql = "DELETE FROM	employee
				WHERE id = :emp_id";
		$stmt = $this->_db->prepare($sql);
		$stmt->bindParam(":emp_id", $emp_id);
		$result = $stmt->execute();
		if ($result) {
			$data["status"] = "Your account has been successfully deleted.";
		} else {
			$data["status"] = "Error: Your account cannot be delete at this time. Please try again later.";
		}
		return $data; 
	} catch(PDOException $e){
		echo "Error: ".$e->getMessage();
	}
}
?>

<?php

// delete emp method

public function empDelete($request) {

// @var string $guid - Unique ID

$guid = uniqid();

$emp_id = $request->getAttribute("emp_id");

try{

// Delete the quote

$sql = "DELETE FROM employee

WHERE id = :emp_id";

$stmt = $this->_db->prepare($sql);

$stmt->bindParam(":emp_id", $emp_id);

$result = $stmt->execute();

if ($result) {

$data["status"] = "Your account has been successfully deleted.";

} else {

$data["status"] = "Error: Your account cannot be delete at this time. Please try again later.";

}

return $data;

} catch(PDOException $e){

echo "Error: ".$e->getMessage();

}

Step 8: Here is the the complete final src/routes/api.php file

<?php
use \Psr\Http\Message\ServerRequestInterface as Request;
use \Psr\Http\Message\ResponseInterface as Response;

// Import Monolog classes into the global namespace
use Monolog\Logger;
use Monolog\Handler\StreamHandler;

$container = $app->getContainer();

$container["logger"] = function ($c) {
	// create a log channel
	$log = new Logger("api");
	$log->pushHandler(new StreamHandler(__DIR__ . "/logs/app.log", Logger::INFO));

	return $log;
};


/**
 * This method restricts access to addresses. <br/>
 * To access is required a valid token.
 */
$app->add(new \Slim\Middleware\JwtAuthentication([
	// The secret key
	"secret" => SECRET,
	"rules" => [
		new \Slim\Middleware\JwtAuthentication\RequestPathRule([
			// Degenerate access to "/ws"
			"path" => "/ws",
			// It allows access to "news" without a token
			"passthrough" => [
				"/ws/employee",
				"/ws/employee/add",
				"/ws/employee/update",
				"/ws/employee/delete"
			]
		])
	]
]));

/**
 * This method settings CORS requests
 */
$app->add(function (Request $request, Response $response, $next) {
	$response = $next($request, $response);
	// Access-Control-Allow-Origin: <domain>, ... | *
	$response = $response->withHeader('Access-Control-Allow-Origin', '*')
		->withHeader('Access-Control-Allow-Headers', 'X-Requested-With, Content-Type, Accept, Origin, Authorization')
		->withHeader('Access-Control-Allow-Methods', 'GET, POST, PUT, DELETE, PATCH, OPTIONS');
	return $response;
});

/**
 * This method creates an urls group. 
 * establishes the base url "/public/ws/".
 */
$app->group("/ws", function () use ($app) {
	
	/**
	 * This method is used for add emp
	 **/
	$app->post("/employee/add", function (Request $request, Response $response) {	
		$emp = new Employee();		
		try {
			$data = $emp->empAdd($request);
			$response = $response->withHeader("Content-Type", "application/json");
			$response = $response->withStatus(200, "OK");
			$response = $response->getBody()->write(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT));			
			return $response;
		} catch (PDOException $e) {
			$this["logger"]->error("DataBase Error: {$e->getMessage()}");
		} catch (Exception $e) {
			$this["logger"]->error("General Error: {$e->getMessage()}");
		}
		finally {
			// Destroy the database connection
			$conn = null;
		}
	});

	/**
	 * This method is used for update emp
	 **/
	$app->post("/employee/update", function (Request $request, Response $response) {
		$emp = new Employee();		
		try {
			$data = $emp->empUpdate($request);
			$response = $response->withHeader("Content-Type", "application/json");
			$response = $response->withStatus(200, "OK");
			$response = $response->getBody()->write(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT));			
				return $response;
		} catch (PDOException $e) {
			$this["logger"]->error("DataBase Error: {$e->getMessage()}");
		} catch (Exception $e) {
			$this["logger"]->error("General Error: {$e->getMessage()}");
		}
		finally {
			// Destroy the database connection
			$conn = null;
		}
	});

	/**
	 * This method is used for list emp
	 **/
	$app->get("/employee", function (Request $request, Response $response) {
		$emp = new Employee();			
		try {
			$data = $emp->empList($request);
			$response = $response->withHeader("Content-Type", "application/json");
			$response = $response->withStatus(200, "OK");
			$response = $response->getBody()->write(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT));			
			return $response;
		} catch (PDOException $e) {
			$this["logger"]->error("DataBase Error: {$e->getMessage()}");
		} catch (Exception $e) {
			$this["logger"]->error("General Error: {$e->getMessage()}");
		}
		finally {
			// Destroy the database connection
			$conn = null;
		}
	});

	/**
	 * This method sets an emp into the database.
	 *
	 **/

	$app->get("/employee/{emp_id}", function (Request $request, Response $response) {	
		$emp = new Employee();		
		try {
			$data = $emp->empDetails($request);
			$response = $response->withHeader("Content-Type", "application/json");
			$response = $response->withStatus(200, "OK");
			$response = $response->getBody()->write(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT));			
			return $response;
		} catch (PDOException $e) {
			$this["logger"]->error("DataBase Error: {$e->getMessage()}");
		} catch (Exception $e) {
			$this["logger"]->error("General Error: {$e->getMessage()}");
		}
		finally {
			// Destroy the database connection
			$conn = null;
		}
	});

	/**
	 * This method is used for delete emp
	 **/
	$app->get("/employee/delete/{emp_id}", function (Request $request, Response $response) {
		$emp = new Employee();			
		try {
			$data = $emp->empDelete($request);
			$response = $response->withHeader("Content-Type", "application/json");
			$response = $response->withStatus(200, "OK");
			$response = $response->getBody()->write(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT));			
			return $response;
		} catch (PDOException $e) {
			$this["logger"]->error("DataBase Error: {$e->getMessage()}");
		} catch (Exception $e) {
			$this["logger"]->error("General Error: {$e->getMessage()}");
		}
		finally {
			// Destroy the database connection
			$conn = null;
		}
	});
});
?>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

<?php

use \Psr\Http\Message\ServerRequestInterface as Request;

use \Psr\Http\Message\ResponseInterface as Response;

// Import Monolog classes into the global namespace

use Monolog\Logger;

use Monolog\Handler\StreamHandler;

$container = $app->getContainer();

$container["logger"] = function ($c) {

// create a log channel

$log = new Logger("api");

$log->pushHandler(new StreamHandler(__DIR__ . "/logs/app.log", Logger::INFO));

return $log;

};

/**

* This method restricts access to addresses. <br/>

* To access is required a valid token.

$app->add(new \Slim\Middleware\JwtAuthentication([

// The secret key

"secret" => SECRET,

"rules" => [

new \Slim\Middleware\JwtAuthentication\RequestPathRule([

// Degenerate access to "/ws"

"path" => "/ws",

// It allows access to "news" without a token

"passthrough" => [

"/ws/employee",

"/ws/employee/add",

"/ws/employee/update",

"/ws/employee/delete"

]

])

]

]));

/**

* This method settings CORS requests

$app->add(function (Request $request, Response $response, $next) {

$response = $next($request, $response);

// Access-Control-Allow-Origin: <domain>, ... | *

$response = $response->withHeader('Access-Control-Allow-Origin', '*')

->withHeader('Access-Control-Allow-Headers', 'X-Requested-With, Content-Type, Accept, Origin, Authorization')

->withHeader('Access-Control-Allow-Methods', 'GET, POST, PUT, DELETE, PATCH, OPTIONS');

return $response;

});

/**

* This method creates an urls group.

* establishes the base url "/public/ws/".

$app->group("/ws", function () use ($app) {

/**

* This method is used for add emp

**/

$app->post("/employee/add", function (Request $request, Response $response) {

$emp = new Employee();

try {

$data = $emp->empAdd($request);

$response = $response->withHeader("Content-Type", "application/json");

$response = $response->withStatus(200, "OK");

$response = $response->getBody()->write(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT));

return $response;

} catch (PDOException $e) {

$this["logger"]->error("DataBase Error: {$e->getMessage()}");

} catch (Exception $e) {

$this["logger"]->error("General Error: {$e->getMessage()}");

}

finally {

// Destroy the database connection

$conn = null;

}

});

/**

* This method is used for update emp

**/

$app->post("/employee/update", function (Request $request, Response $response) {

$emp = new Employee();

try {

$data = $emp->empUpdate($request);

$response = $response->withHeader("Content-Type", "application/json");

$response = $response->withStatus(200, "OK");

$response = $response->getBody()->write(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT));

return $response;

} catch (PDOException $e) {

$this["logger"]->error("DataBase Error: {$e->getMessage()}");

} catch (Exception $e) {

$this["logger"]->error("General Error: {$e->getMessage()}");

}

finally {

// Destroy the database connection

$conn = null;

}

});

/**

* This method is used for list emp

**/

$app->get("/employee", function (Request $request, Response $response) {

$emp = new Employee();

try {

$data = $emp->empList($request);

$response = $response->withHeader("Content-Type", "application/json");

$response = $response->withStatus(200, "OK");

$response = $response->getBody()->write(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT));

return $response;

} catch (PDOException $e) {

$this["logger"]->error("DataBase Error: {$e->getMessage()}");

} catch (Exception $e) {

$this["logger"]->error("General Error: {$e->getMessage()}");

}

finally {

// Destroy the database connection

$conn = null;

}

});

/**

* This method sets an emp into the database.

**/

$app->get("/employee/{emp_id}", function (Request $request, Response $response) {

$emp = new Employee();

try {

$data = $emp->empDetails($request);

$response = $response->withHeader("Content-Type", "application/json");

$response = $response->withStatus(200, "OK");

$response = $response->getBody()->write(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT));

return $response;

} catch (PDOException $e) {

$this["logger"]->error("DataBase Error: {$e->getMessage()}");

} catch (Exception $e) {

$this["logger"]->error("General Error: {$e->getMessage()}");

}

finally {

// Destroy the database connection

$conn = null;

}

});

/**

* This method is used for delete emp

**/

$app->get("/employee/delete/{emp_id}", function (Request $request, Response $response) {

$emp = new Employee();

try {

$data = $emp->empDelete($request);

$response = $response->withHeader("Content-Type", "application/json");

$response = $response->withStatus(200, "OK");

$response = $response->getBody()->write(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT));

return $response;

} catch (PDOException $e) {

$this["logger"]->error("DataBase Error: {$e->getMessage()}");

} catch (Exception $e) {

$this["logger"]->error("General Error: {$e->getMessage()}");

}

finally {

// Destroy the database connection

$conn = null;

}

});

Download