Welcome to this tutorial where we’ll unravel the magic behind CRUD Operations using PHP and MySQL. CRUD – Create, Read, Update, and Delete – forms the foundation of countless websites and apps. These actions might sound simple, but they hold immense power in efficiently managing data. In this guide tailored for beginners, we’ll take a plunge into the realm of CRUD operations using PHP, simplifying the concepts for effortless comprehension and practical implementation in your projects.
In this tutorial, we will be demonstrating how to carry out CRUD operations using PDO and MySQLi.
Implementing (CRUD) Create, Read, Update, and Delete
CREATE (INSERT DATA INTO DATABASE) :
The ‘Create’ operation is comparable to the act of introducing something new into existence. To achieve this, databases utilize the ‘INSERT’ statement in SQL to add data into pre-defined tables.
First Step – Designing the Database Structure
| DATABASE NAME | TABLE NAME |
|---|---|
| appwebrestapi | crud_operations |
CREATE TABLE `crud_operations` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` text NOT NULL,
`email_id` text NOT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Demo of CRUD Operations
Configrations.php
<?php
// DEVELOPEMENT SERVER DETAILS ...
$DATABASE_SERVER_IP = "localhost";
$DATABASE_USER_NAME = "root";
$DATABASE_USER_PASSWORD="";
$DATABASE_NAME="appwebrestapi";
Establishing a connection to the database using the PDO
<?php
require_once 'Configurations.php';
try {
$con = new PDO(
"mysql:host=$DATABASE_SERVER_IP;
dbname=$DATABASE_NAME",
$DATABASE_USER_NAME,
$DATABASE_USER_PASSWORD
);
// set the PDO error mode to exception
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//echo "Connected successfully";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
Implementation of Data Insertion into the Database
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
if($_SERVER['REQUEST_METHOD']==='POST'){
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(404),
"status" => false,
"message" => "Invalid Request"
);
echo json_encode($server__response__error);
} Before proceeding, it’s crucial to grasp the reason behind employing headers at this point. If you’re not acquainted with the concept of headers, let’s delve into it briefly.
A “header” refers to the information included at the beginning of an HTTP request or response. Headers contain metadata about the request or response, such as details about the data being sent or received, authentication credentials, content type, and more. Headers are essential for communication between the client (usually a web browser or another application) and the server hosting the REST API.
Headers in a REST API serve various purposes, including:
- Content Negotiation: The “Accept” header in a client request specifies the preferred content type of the response, allowing the client to request data in a particular format (e.g., JSON, XML, HTML).
- Authentication and Authorization: Headers can contain authentication tokens, API keys, or other credentials required to verify the identity and permissions of the client.
- Caching: Headers like “Cache-Control” and “Expires” help control caching behavior, allowing clients and intermediaries to cache responses for efficiency.
- Cross-Origin Resource Sharing (CORS): Headers like “Access-Control-Allow-Origin” and “Access-Control-Allow-Headers” facilitate secure cross-origin requests.
- Response Metadata: Headers such as “Content-Type” inform the client about the type of data being returned in the response.
- Pagination and Range Requests: Headers like “Range” and “Content-Range” enable clients to request partial or paginated responses.
- Response Compression: Headers like “Accept-Encoding” and “Content-Encoding” allow clients and servers to negotiate compressed data transfer.
In PHP, you can interact with headers using the header() function. For example, to set the content type of the response to JSON, you can use:
header('Content-Type: application/json');
Or to allow cross-origin requests:
header('Access-Control-Allow-Origin: *');
It’s important to handle headers properly to ensure the smooth functioning of your REST API and to adhere to the standards and best practices defined by the HTTP protocol.
Implementation of the creation (insertion) process using PDO.
Supported Versions:- (PHP 5 >= 5.1.0, PHP 7, PHP 8, PECL pdo >= 0.1.0)
Reference : Visit Official PHP Documentation
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
if($_SERVER['REQUEST_METHOD']==='POST'){
if(!empty($_POST['name']) && !empty($_POST['emailID'])){
$name = $_POST['name'];
$email = $_POST['emailID'];
try{
// Performing data insertion using PDO.
include 'DBConnect.php';
$SQL__INSERT__QUERY = "INSERT INTO `crud_operations`(
`name`,
`email_id`
)
VALUES(
:name,
:email
);";
$insert__record__statement = $con->prepare($SQL__INSERT__QUERY);
$insert__record__statement->bindParam(':name',$name, PDO::PARAM_STR);
$insert__record__statement->bindParam(':email',$email, PDO::PARAM_STR);
$insert__record__statement->execute();
$insert__record__flag = $insert__record__statement->rowCount();
if($insert__record__flag>0){
http_response_code(200);
$server__response__success = array(
"code"=>http_response_code(),
"status"=>true,
"message"=>"The entry has been successfully added to the database."
);
echo json_encode($server__response__success);
} else {
http_response_code(404);
$server__response__error = array(
"code"=>http_response_code(),
"status"=>false,
"message"=>"Failed to add the record to the database. Please attempt the operation once more."
);
echo json_encode($server__response__error);
}
$con = null; // code database connection
}catch(Exception $ex){
http_response_code(404);
$server__response__error = array(
"code"=>http_response_code(),
"status"=>false,
"message"=>"Opps!!! Something went wrong ".$ex->getMessage()
);
echo json_encode($server__response__error);
}
} else {
http_response_code(404);
$server__response__error = array(
"code"=>http_response_code(),
"status"=>false,
"message"=>"Invalid Parameters!!! Please try with valid parameters!"
);
echo json_encode($server__response__error);
}
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Invalid Request"
);
echo json_encode($server__response__error);
}| URL | http://{YOUR_HOST}/{YOUR_FOLDER}/Create.php |
| Parameters | name,emailID (In accordance with your needs.) |
Output
{
"code": 200,
"status": true,
"message": "The entry has been successfully added to the database."
}Insertion Using MySQLi.
Supported Versions:- (PHP 5, PHP 7, PHP 8) Reference : Visit Official PHP Documentation
First step – Establishing a connection to the database using the MySQLi driver.
<?php
include 'Configurations.php';
$con = mysqli_connect(
$DATABASE_SERVER_IP,
$DATABASE_USER_NAME,
$DATABASE_USER_PASSWORD,
$DATABASE_NAME
);
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
Implementation using MYSQLi
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
if (!empty($_POST['name']) && !empty($_POST['emailID'])) {
$name = $_POST['name'];
$email = $_POST['emailID'];
try {
// Performing data insertion using MySQLi.
include 'MySQLiDBConnect.php';
$SQL__INSERT__QUERY = "INSERT INTO `crud_operations`(
`name`,
`email_id`
)
VALUES(
'$name',
'$email'
);";
if (mysqli_query($con, $SQL__INSERT__QUERY)) {
http_response_code(200);
$server__response__success = array(
"code" => http_response_code(),
"status" => true,
"message" => "The entry has been successfully added to the database Using MYSQLi."
);
echo json_encode($server__response__success);
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Failed to add the record to the database." . mysqli_error($con)
);
echo json_encode($server__response__error);
}
mysqli_close($con); // code database connection
} catch (Exception $ex) {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Opps!!! Something went wrong " . $ex->getMessage()
);
echo json_encode($server__response__error);
}
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Invalid Parameters!!! Please try with valid parameters!"
);
echo json_encode($server__response__error);
}
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Invalid Request"
);
echo json_encode($server__response__error);
}
In this tutorial, we’re diving into the world of CRUD operations, but we’re steering clear of the MYSQL Driver for database access. Why, you ask? Well, the mysql_* functions have been officially deprecated since PHP v5.5.0 and are set to be removed in future versions. Fear not, though! If you’re curious about CRUD with the MYSQL Driver, I’m here to help.
While we won’t cover it here, if you’re keen on learning about implementing CRUD operations using the MYSQL Driver, drop your thoughts in the comment section. I’ll be more than happy to prepare a separate tutorial exclusively for that topic. Your curiosity drives the content we create, so feel free to let your questions flow!
READ (SELECT*) Operation :
‘SELECT’ operation is a gateway to retrieving information stored in databases.
SELECT Using PDO
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
if ($_SERVER['REQUEST_METHOD'] === 'GET') {
try {
// Performing data selection using PDO.
include 'DBConnect.php';
$SQL__SELECT__QUERY = "SELECT * FROM `crud_operations`;";
$select__record__statement = $con->prepare($SQL__SELECT__QUERY);
$select__record__statement->execute();
$user__data = $select__record__statement->fetchAll(PDO::FETCH_ASSOC);
if ($user__data) {
http_response_code(200);
$server__response__success = array(
"code" => http_response_code(),
"status" => true,
"message" => sizeof($user__data) . " Records Found",
"data" => $user__data
);
echo json_encode($server__response__success);
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "No Records Found"
);
echo json_encode($server__response__error);
}
$con = null; // code database connection
} catch (Exception $ex) {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Opps!!! Something went wrong " . $ex->getMessage()
);
echo json_encode($server__response__error);
}
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Invalid Request"
);
echo json_encode($server__response__error);
}
Output:
{
"code": 200,
"status": true,
"message": "2 Records Found",
"data": [
{
"id": "1",
"name": "PRADEEP KUMAR",
"email_id": "pradeep@appwebcoders.in",
"created_at": "2023-08-23 06:25:26",
"updated_at": null
},
{
"id": "2",
"name": "PRADEEP KUMAR",
"email_id": "pradeep@appwebcoders.in",
"created_at": "2023-08-23 06:25:28",
"updated_at": null
}
]
}SELECT with Where
If you intend to retrieve data with specific conditions using a ‘WHERE’ clause, you’ll need to pass a parameter through a GET[] or POST[] request to filter the data based on your criteria. In the code provided below, I am focusing on using the ‘Email’ field for data filtration with the GET[’email’] request.
URL: http://localhost/REST_API_CRUD_OPS/selectWithCondition.php?email=pradeep@appwebcoders.in
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
if ($_SERVER['REQUEST_METHOD'] === 'GET') {
if (!empty($_GET['email'])) {
try {
$user_email = $_GET['email'];
// Performing data selection using PDO.
include 'DBConnect.php';
$SQL__SELECT__QUERY = "SELECT * FROM `crud_operations` WHERE email_id=:user_email;";
$select__record__statement = $con->prepare($SQL__SELECT__QUERY);
$select__record__statement->bindParam('user_email',$user_email,PDO::PARAM_STR);
$select__record__statement->execute();
$user__data = $select__record__statement->fetchAll(PDO::FETCH_ASSOC);
if ($user__data) {
http_response_code(200);
$server__response__success = array(
"code" => http_response_code(),
"status" => true,
"message" => sizeof($user__data) . " Records Found",
"data" => $user__data
);
echo json_encode($server__response__success);
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "No Records Found"
);
echo json_encode($server__response__error);
}
$con = null; // code database connection
} catch (Exception $ex) {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Opps!!! Something went wrong " . $ex->getMessage()
);
echo json_encode($server__response__error);
}
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Invalid Parameter! Please contact the API Administrator or refer to the documentation for clarification."
);
echo json_encode($server__response__error);
}
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Invalid Request"
);
echo json_encode($server__response__error);
}
Select Data Using MySqli Driver
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
if ($_SERVER['REQUEST_METHOD'] === 'GET') {
try {
// Performing data selection using MYSQLi Driver.
include 'MySQLiDBConnect.php';
$SQL__SELECT__QUERY = "SELECT * FROM `crud_operations`;";
$select_result = mysqli_query($con, $SQL__SELECT__QUERY);
$user__data = mysqli_fetch_all($select_result);
if ($user__data) {
http_response_code(200);
$server__response__success = array(
"code" => http_response_code(),
"status" => true,
"message" => sizeof($user__data) . " Records Found",
"data" => $user__data
);
echo json_encode($server__response__success);
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "No Records Found"
);
echo json_encode($server__response__error);
}
mysqli_close($con); // code database connection
} catch (Exception $ex) {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Opps!!! Something went wrong " . $ex->getMessage()
);
echo json_encode($server__response__error);
}
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Invalid Request"
);
echo json_encode($server__response__error);
}
“To select data based on a specific condition, you can modify the query like this:
$SQL__SELECT__QUERY = "SELECT * FROM `crud_operations` Where email_id='$variable_name';Please note that it’s important to ensure that the $variable_name is properly sanitized and validated to prevent SQL injection vulnerabilities.”
Updation (Update)
In PHP, you can perform database updates using PDO (PHP Data Objects), which is a database access library that provides a consistent and secure way to interact with databases. Below is an example of how to perform an update operation using PDO in PHP.
Assuming you have a database connection established using PDO, you can execute an SQL UPDATE statement to modify data in your database.
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
// get all update parameters
if (!empty($_POST['email']) && !empty($_POST['name']) && !empty($_POST['id'])) {
$name = $_POST['name'];
$emailID = $_POST['email'];
$recordID = $_POST['id'];
try {
include 'DBConnect.php';
$UPDATE__SQL__QUERRY = "UPDATE
`crud_operations`
SET
`name` = :name,
`email_id` = :emailID,
`updated_at` = NOW()
WHERE
id = :recordID;";
$update__record__statement = $con->prepare($UPDATE__SQL__QUERRY);
$update__record__statement->bindParam(':name', $name, PDO::PARAM_STR);
$update__record__statement->bindParam(':emailID', $emailID, PDO::PARAM_STR);
$update__record__statement->bindParam(':recordID', $recordID);
$update__record__statement->execute();
$record__count = $update__record__statement->rowCount();
if ($record__count > 0) {
http_response_code(200);
$server__response__success = array(
"code" => http_response_code(),
"status" => true,
"message" => "Record has been updated!!!"
);
echo json_encode($server__response__success);
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Opps!!! Unable to update the record!!!"
);
echo json_encode($server__response__error);
}
} catch (Exception $ex) {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Somehting Went Wrong. Exception! " . $ex->getMessage()
);
echo json_encode($server__response__error);
}
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Please check your parameters. You can try again or refer to the API documentation for additional information."
);
echo json_encode($server__response__error);
}
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Invalid Request"
);
echo json_encode($server__response__error);
}
OUTPUT
{
"code": 200,
"status": true,
"message": "Record has been updated!!!"
}
Update Data Using MySqli Driver
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
if (!empty($_POST['email']) && !empty($_POST['name']) && !empty($_POST['id'])) {
$name = $_POST['name'];
$emailID = $_POST['email'];
$recordID = $_POST['id'];
try {
// Performing data selection using MYSQLi Driver.
include 'MySQLiDBConnect.php';
$UPDATE__SQL__QUERRY = "UPDATE
`crud_operations`
SET
`name` = '$name',
`email_id` = '$emailID',
`updated_at` = NOW()
WHERE
id = '$recordID';";
if (mysqli_query($con, $UPDATE__SQL__QUERRY)) {
http_response_code(200);
$server__response__success = array(
"code" => http_response_code(),
"status" => true,
"message" => "The entry has been successfully Updated to the database Using MYSQLi."
);
echo json_encode($server__response__success);
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Failed to update the record to the database." . mysqli_error($con)
);
echo json_encode($server__response__error);
}
mysqli_close($con); // code database connection
} catch (Exception $ex) {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Opps!!! Something went wrong " . $ex->getMessage()
);
echo json_encode($server__response__error);
}
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Invalid Parameter! Please contact the API Administrator or refer to the documentation for clarification."
);
echo json_encode($server__response__error);
}
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Invalid Request"
);
echo json_encode($server__response__error);
}
Deletion (Delete) PDO Driver
Here’s an example of how to delete data from a database using PDO (PHP Data Objects) in PHP.
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
if (!empty($_POST['id'])) {
$id = $_POST['id'];
if (is_numeric($id)) {
try {
require 'DBConnect.php';
$DELETE__SQL__QUERY = "DELETE FROM `crud_operations` WHERE id =:id";
$delete__statement = $con->prepare($DELETE__SQL__QUERY);
$delete__statement->bindParam(':id', $id);
$delete__statement->execute();
$count = $delete__statement->rowCount();
if ($count > 0) {
http_response_code(200);
$server__response__success = array(
"code" => http_response_code(),
"status" => true,
"message" => "Record has been deleted"
);
echo json_encode($server__response__success);
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Unable to delete the record"
);
echo json_encode($server__response__error);
}
} catch (Exception $ex) {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Exception! " . $ex->getMessage()
);
echo json_encode($server__response__error);
}
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Invalid ID"
);
echo json_encode($server__response__error);
}
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Please check your parameters. You can try again or refer to the API documentation for additional information."
);
echo json_encode($server__response__error);
}
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Invalid Request"
);
echo json_encode($server__response__error);
}
Delete using MYSQLi Driver
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
if (!empty($_POST['id'])) {
$id = $_POST['id'];
try {
include 'MySQLiDBConnect.php';
$DELETE__SQL__QUERY = "DELETE FROM `crud_operations` WHERE id ='$id';";
if (mysqli_query($con, $DELETE__SQL__QUERY)) {
http_response_code(200);
$server__response__success = array(
"code" => http_response_code(),
"status" => true,
"message" => "The entry has been deleted using MYSQLi."
);
echo json_encode($server__response__success);
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Failed to delete the record to the database." . mysqli_error($con)
);
echo json_encode($server__response__error);
}
mysqli_close($con); // code database connection
} catch (Exception $ex) {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Opps!!! Something went wrong " . $ex->getMessage()
);
echo json_encode($server__response__error);
}
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Invalid Parameter! Please contact the API Administrator or refer to the documentation for clarification."
);
echo json_encode($server__response__error);
}
} else {
http_response_code(404);
$server__response__error = array(
"code" => http_response_code(),
"status" => false,
"message" => "Invalid Request"
);
echo json_encode($server__response__error);
}
Conclusion
In this blog, we’ve embarked on a journey to demystify the fundamental principles of database management through CRUD operations – Create, Read, Update, and Delete – using PHP and MySQL. We’ve uncovered how to establish a seamless connection to a MySQL database, allowing us to interact with our data in a way that is both efficient and secure.
Throughout this tutorial, we’ve explored the power of creating new records with the ‘Create‘ operation, retrieving valuable information with the ‘Read‘ operation, updating existing data with ‘Update,’ and removing unnecessary records with ‘Delete.’ We’ve strived for simplicity, breaking down complex concepts into manageable steps, making it accessible for beginners and valuable for seasoned developers.
By grasping these essential CRUD operations, you’ve gained a solid foundation to build upon. You can now implement dynamic web applications, manage user data, and create interactive experiences for your users with confidence. Remember, practice is key, so don’t hesitate to experiment with your newfound knowledge and explore more advanced techniques as you continue your journey in web development.
As technology evolves, the principles discussed here will remain relevant. Whether you’re creating a personal blog, an e-commerce platform, or a robust web application, the ability to perform CRUD operations with ease will always be a vital skill in your developer’s toolkit.
Thank you for joining us on this exploration of PHP and MySQL’s potential for data manipulation. We hope this guide has empowered you to embark on exciting web development projects and continue your quest for knowledge in the ever-evolving world of programming.
Add a Comment