Let's create a rest api using PHP PDO. Here we will have a "POST" model and we will store posts in database also we get categories in separate table.
here how our Post model looks like
<?php
error_reporting(E_ALL);
ini_set('display_error', 1);
class Post {
// Database data.
private $connection;
private $table = 'posts';
// Post Properties
public $id;
public $category_id;
public $title;
public $description;
public $created_at;
public function __construct($db)
{
$this->connection = $db;
}
// Get all list of posts.
public function read()
{
// Query to get posts data.
$query = 'SELECT
c.name as category,
p.id,
p.category_id,
p.title,
p.description,
p.created_at
FROM
'.$this->table.' p LEFT JOIN
category c
ON p.category_id = c.id
ORDER BY
p.created_at DESC';
$post = $this->connection->prepare($query);
$post->execute();
return $post;
}
// Get single post.
public function read_single_post($id)
{
$this->id = $id;
// Query to get posts data.
$query = 'SELECT
c.name as category,
p.id,
p.category_id,
p.title,
p.description,
p.created_at
FROM
'.$this->table.' p LEFT JOIN
category c
ON p.category_id = c.id
WHERE p.id= ?
LIMIT 0,1';
$post = $this->connection->prepare($query);
//$post->bindParam(9, $this->id);
$post->execute([$this->id]);
return $post;
}
// Insert a new record.
public function create_new_record($params)
{
try
{
$this->title = $params['title'];
$this->description = $params['description'];
$this->category_id = $params['category_id'];
$query = 'INSERT INTO '. $this->table .'
SET
title = :title,
category_id = :category_id,
description = :details';
$statement = $this->connection->prepare($query);
$statement->bindValue('title', $this->title);
$statement->bindValue('category_id', $this->category_id);
$statement->bindValue('details', $this->description);
if($statement->execute())
{
return true;
}
return false;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
}
// Update a new record.
public function update_new_record($params)
{
try
{
$this->id = $params['id'];
$this->title = $params['title'];
$this->description = $params['description'];
$this->category_id = $params['category_id'];
$query = 'UPDATE '. $this->table .'
SET
title = :title,
category_id = :category_id,
description = :details
WHERE id = :id';
$statement = $this->connection->prepare($query);
$statement->bindValue('id', $this->id);
$statement->bindValue('title', $this->title);
$statement->bindValue('category_id', $this->category_id);
$statement->bindValue('details', $this->description);
if($statement->execute())
{
return true;
}
return false;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
}
}
All methods are given for create, update , delete in model file.
We have created files for route to fire each event here for example to create a new record user will go to "localhost/sky/api/post/insert.php".
Here file code is like
<?php
error_reporting(E_ALL);
ini_set('display_error', 1);
// Headers
header('Access-Control-Allow-Origin: *');
header('Content-Type: application/json');
header('Access-Control-Allow-Methods: POST');
include_once '../../config/Database.php';
include_once '../../models/Post.php';
// Instantiate Database.
$database = new Database;
$db = $database->connect();
$post = new Post($db);
// Get raw posted data.
// For $data to work -> add headers and in postman add content type.
//$data = json_decode(file_get_contents("php://input"));
if(count($_POST))
{
$params = [
'title' => $_POST['title'],
'description' => $_POST['description'],
'category_id' => $_POST['category_id'],
];
if($post->create_new_record($params))
{
echo json_encode(array('message' => 'Post created'));
}
}
else if(isset($data))
{
$params = [
'title' => $data->title,
'description' => $data->description,
'category_id' => $data->category_id,
];
if($post->create_new_record($params))
{
echo json_encode(array('message' => 'Post created'));
}
}
For all this to work we need database connection and for that we have a file under config folder.
<?php
class Database {
// Database properties.
private $host = 'localhost';
private $db_name = 'test';
private $username = 'root';
private $password = '';
private $connection;
public function connect()
{
$this->connection = null;
try
{
$this->connection = new PDO('mysql:host='.$this->host.';dbname='.$this->db_name,
$this->username,
$this->password,
);
}
catch(PDOException $e)
{
echo $e->getMessage();
}
return $this->connection;
}
}
Important links:
For detailed explanation check our video guide.