create rest api php mysql – Create Simple PHP 8 CRUD REST API with MySQL & PHP PDO – Configure a Database for Your PHP REST API. I will use MySQL to power our simple API.
create rest api php mysql
I have already learned how can we do Create, Read, Update and delete CRUD operation by using PHP PDO, Mysql and Ajax.
In this article you will learn how to create a REST API for an Android app using PHP and MySQL.
Step By Step create rest api php mysql with source code.
MySQL Database Configuration
database creation
CREATE TABLE IF NOT EXISTS `Member` ( `id` int(11) NOT NULL AUTO_INCREMENT, `member_name` varchar(256) NOT NULL, `email_address` varchar(50), `point` int(11) NOT NULL, `serviceable` varchar(255) NOT NULL, `joined_at` datetime NOT NULL, PRIMARY KEY (`id`) )ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=19;
`Member` table in Database.
Populate data into the `Member` table.
INSERT INTO `Member` (`id`, `member_name`, `email_address`, `point`, `serviceable`, `joined_at`) VALUES (1, 'Kajal agraval', '[email protected]', 32, 'Manager', '2029-06-01 02:12:30'), (2, 'Meera Sidhpura', '[email protected]', 29, 'keeper', '2023-03-03 01:20:10'), (3, 'Sejal Ramani', '[email protected]', 36, 'devloper', '2028-09-20 03:10:25'), (4, 'Bhavika Pethani', '[email protected]', 42, 'designer', '2027-04-11 04:11:12'), (5, 'Jalpa dave', '[email protected]', 48, 'main hod', '2026-01-04 05:20:30'), (6, 'ankita Sagpariya', '[email protected]', 37, 'CEO', '2022-01-10 06:40:10'), (7, 'Monika chovatiya', '[email protected]', 44, 'Manager', '2022-05-02 02:20:30'), (8, 'Virat Kohali', '[email protected]', 49, 'Engineer', '2025-01-04 05:15:35'), (9, 'vishal Pandya', '[email protected]', 51, 'Devloper', '2026-01-02 02:20:30'), (10, 'Sagar Dave', '[email protected]', 45, 'Manager OLD Teacher', '2028-02-01 06:22:50');
Make Database Connection
config/db_connection.php
place the following source code for create rest api php mysql.
<?php
class Database {
private $host = "127.0.0.1";
private $database_name = "pakainfo_v1";
private $username = "root";
private $password = "[email protected]";
public $conn;
public function getConnection(){
$this->conn = null;
try{
$this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->database_name, $this->username, $this->password);
$this->conn->exec("set names utf8");
}catch(PDOException $exception){
echo "Database could not be connected: " . $exception->getMessage();
}
return $this->conn;
}
}
?>
Create PHP Class
Now, I will make the PHP Class by the member_name of Member.
class/members.php
define the CRUD methods inside the Member class.
<?php
//create rest api php mysql
class Member{
private $conn;
private $db_table = "Member";
public $id;
public $member_name;
public $email_address;
public $point;
public $serviceable;
public $joined_at;
public function __construct($db){
$this->conn = $db;
}
public function getMembers(){
$sqlQuery = "SELECT id, member_name, email_address, point, serviceable, joined_at FROM " . $this->db_table . "";
$stmt = $this->conn->prepare($sqlQuery);
$stmt->execute();
return $stmt;
}
public function createMember(){
$sqlQuery = "INSERT INTO
". $this->db_table ."
SET
member_name = :member_name,
email_address = :email_address,
point = :point,
serviceable = :serviceable,
joined_at = :joined_at";
$stmt = $this->conn->prepare($sqlQuery);
$this->member_name=htmlspecialchars(strip_tags($this->member_name));
$this->email_address=htmlspecialchars(strip_tags($this->email_address));
$this->point=htmlspecialchars(strip_tags($this->point));
$this->serviceable=htmlspecialchars(strip_tags($this->serviceable));
$this->joined_at=htmlspecialchars(strip_tags($this->joined_at));
$stmt->bindParam(":member_name", $this->member_name);
$stmt->bindParam(":email_address", $this->email_address);
$stmt->bindParam(":point", $this->point);
$stmt->bindParam(":serviceable", $this->serviceable);
$stmt->bindParam(":joined_at", $this->joined_at);
if($stmt->execute()){
return true;
}
return false;
}
public function getSingleMember(){
$sqlQuery = "SELECT
id,
member_name,
email_address,
point,
serviceable,
joined_at
FROM
". $this->db_table ."
WHERE
id = ?
LIMIT 0,1";
$stmt = $this->conn->prepare($sqlQuery);
$stmt->bindParam(1, $this->id);
$stmt->execute();
$dataRow = $stmt->fetch(PDO::FETCH_ASSOC);
$this->member_name = $dataRow['member_name'];
$this->email_address = $dataRow['email_address'];
$this->point = $dataRow['point'];
$this->serviceable = $dataRow['serviceable'];
$this->joined_at = $dataRow['joined_at'];
}
public function updateMember(){
$sqlQuery = "UPDATE
". $this->db_table ."
SET
member_name = :member_name,
email_address = :email_address,
point = :point,
serviceable = :serviceable,
joined_at = :joined_at
WHERE
id = :id";
$stmt = $this->conn->prepare($sqlQuery);
$this->member_name=htmlspecialchars(strip_tags($this->member_name));
$this->email_address=htmlspecialchars(strip_tags($this->email_address));
$this->point=htmlspecialchars(strip_tags($this->point));
$this->serviceable=htmlspecialchars(strip_tags($this->serviceable));
$this->joined_at=htmlspecialchars(strip_tags($this->joined_at));
$this->id=htmlspecialchars(strip_tags($this->id));
$stmt->bindParam(":member_name", $this->member_name);
$stmt->bindParam(":email_address", $this->email_address);
$stmt->bindParam(":point", $this->point);
$stmt->bindParam(":serviceable", $this->serviceable);
$stmt->bindParam(":joined_at", $this->joined_at);
$stmt->bindParam(":id", $this->id);
if($stmt->execute()){
return true;
}
return false;
}
function deleteMember(){
$sqlQuery = "DELETE FROM " . $this->db_table . " WHERE id = ?";
$stmt = $this->conn->prepare($sqlQuery);
$this->id=htmlspecialchars(strip_tags($this->id));
$stmt->bindParam(1, $this->id);
if($stmt->execute()){
return true;
}
return false;
}
}
?>
The Member class manages the CRUD operation
__construct()— Makes the database connection ready.getMembers()— Get all records.getSingleMember()— Get single records.createMember()— Create record.updateMember()— Update record.deleteMember()— Fetch single record.
Fetch MySQL Table Records using PHP REST API Endpoint
api -> read.php
<?php
//create rest api php mysql
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
include_once '../config/db_connection.php';
include_once '../class/members.php';
$db_connection = new Database();
$db = $db_connection->getConnection();
$member_rows = new Member($db);
$stmt = $member_rows->getMembers();
$member_rowCount = $stmt->rowCount();
echo json_encode($member_rowCount);
if($member_rowCount > 0){
$memberArr = array();
$memberArr["body"] = array();
$memberArr["member_rowCount"] = $member_rowCount;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
extract($row);
$e = array(
"id" => $id,
"member_name" => $member_name,
"email_address" => $email_address,
"point" => $point,
"serviceable" => $serviceable,
"joined_at" => $joined_at
);
array_push($memberArr["body"], $e);
}
echo json_encode($memberArr);
}
else{
http_response_code(404);
echo json_encode(
array("message" => "No record found.")
);
}
?>
| Method | Endpoint |
|---|---|
| GET | https://www.pakainfo.com/api/read.php |
Get Single Row from MySQL Database via PHP API
api -> single_read.php
we are selecting the Member values from the MySQL table.
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
include_once '../config/db_connection.php';
include_once '../class/members.php';
$db_connection = new Database();
$db = $db_connection->getConnection();
$member_row = new Member($db);
$member_row->id = isset($_GET['id']) ? $_GET['id'] : die();
$member_row->getSingleMember();
if($member_row->member_name != null){
// make array
$emp_arr = array(
"id" => $member_row->id,
"member_name" => $member_row->member_name,
"email_address" => $member_row->email_address,
"point" => $member_row->point,
"serviceable" => $member_row->serviceable,
"joined_at" => $member_row->joined_at
);
http_response_code(200);
echo json_encode($emp_arr);
}
else{
http_response_code(404);
echo json_encode("Member not found.");
}
?>
| Method | Endpoint |
|---|---|
| GET | https://www.pakainfo.com/api/single_read.php/?id=2 |
Insert or Add Single Record in MySQL Table
api -> create.php
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
include_once '../config/db_connection.php';
include_once '../class/members.php';
$db_connection = new Database();
$db = $db_connection->getConnection();
$member_row = new Member($db);
$data = json_decode(file_get_contents("php://input"));
$member_row->member_name = $data->member_name;
$member_row->email_address = $data->email_address;
$member_row->point = $data->point;
$member_row->serviceable = $data->serviceable;
$member_row->joined_at = date('Y-m-d H:i:s');
if($member_row->createMember()){
echo 'Member joined_at successfully.';
} else{
echo 'Member could not be joined_at.';
}
?>
| Method | Endpoint |
|---|---|
| POST | https://www.pakainfo.com/api/create.php |
Edit/Update MySQL Table
api -> update.php
place the following code.
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
include_once '../config/db_connection.php';
include_once '../class/members.php';
$db_connection = new Database();
$db = $db_connection->getConnection();
$member_row = new Member($db);
$data = json_decode(file_get_contents("php://input"));
$member_row->id = $data->id;
// member values
$member_row->member_name = $data->member_name;
$member_row->email_address = $data->email_address;
$member_row->point = $data->point;
$member_row->serviceable = $data->serviceable;
$member_row->joined_at = date('Y-m-d H:i:s');
if($member_row->updateMember()){
echo json_encode("Member data updated.");
} else{
echo json_encode("Data could not be updated");
}
?>
| Method | Endpoint |
|---|---|
| POST | https://www.pakainfo.com/api/update.php |
Remove/Delete Single Mysql Record using PHP API
api -> delete.php file
i will write the login to delete or remove the single member record from MySQL data table using PHP 8 RESTful API.
I will make the API call using the deletedMember() method.
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
include_once '../config/db_connection.php';
include_once '../class/members.php';
$db_connection = new Database();
$db = $db_connection->getConnection();
$member_row = new Member($db);
$data = json_decode(file_get_contents("php://input"));
$member_row->id = $data->id;
if($member_row->deleteMember()){
echo json_encode("Member deleted.");
} else{
echo json_encode("Data could not be deleted");
}
?>
| Method | Endpoint |
|---|---|
| DELETE | https://www.pakainfo.com/api/delete.php |
Don’t Miss : CRUD operations in PHP with Bootstrap
I hope you get an idea about create rest api php mysql.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.