PHP MySQL CRUD Tutorial For Beginners

Learn how you can create CRUD API with PHP and MySQL. It’s a Beginner’s guide for performing select, insert, update and delete operations on the database.

CRUD APIs provide access to database operations from different platforms and environments. One can create a web, desktop or even mobile application by using the same API. The combination of PHP & MySQL is popular world-wide for creating such APIs. This guide includes a full example code of basic CRUD API that can be used to perform all the operations i.e. Create, Read, Update and Delete on the MySQL Database with plain PHP code and without using any other frameworks or libraries.

Table of Contents

PHP MySQL CRUD API Code

The code given below needs to be written in a single PHP file. In our case, this file is named as api.php. More than the four basic operations, this code includes one-additional operation and i.e. select only one. Consequently, the code can be used to create an API that performs the 5 different database operations mentioned below.

Insert A Record Into Database
Select All Records From Database
Select Only One Record According To Condition
Update A Record According To Condition
Delete A Record According To Condition

PHP Code.

<?php
//Accessable From Everywhere
header("Access-Control-Allow-Origin: *");

//Establish Connection
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "demo";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$requestType = $_SERVER["REQUEST_METHOD"];

//Create
if($requestType == "POST"){
    $val_1 = $_POST['val_1'];
        $val_2 = $_POST['val_2'];
    $insertQuery = "INSERT INTO demo (col_1, col_2) VALUES ('$val_1','$val_2');";
    if ($conn->query($insertQuery) === TRUE) echo "New Record Added Successfully.";
    else echo "Error: " . $insertQuery . "<br>" . $conn->error;
}

//Read
if($requestType == "GET" AND !isset($_GET["id"])){
    $selectQuery = "SELECT * FROM demo";
    $result = $conn->query($selectQuery);
    $rows = array();
    while($row = $result->fetch_assoc())
        $rows[] = $row;
    header("Content-Type: application/json");
    echo json_encode($rows);
}

//Read Only One
if($requestType == "GET" AND isset($_GET["id"])){
    $id = $_GET["id"];
    $selectQuery = "SELECT * FROM demo WHERE ID = $id";
    $result = $conn->query($selectQuery);
    $rows = array();
    if ($result->num_rows > 0){
        while($row = $result->fetch_assoc())
            $rows[] = $row;
        header("Content-Type: application/json");
        echo json_encode($rows);
    }
    else echo "Not Found.";
}

//Update
if($requestType == "PUT"){
    $str = file_get_contents("php://input");
    $_PUT = array();
    parse_str($str, $_PUT);
    $val_1 = $_PUT['val_1'];
        $val_2 = $_PUT['val_2'];
        $id = $_PUT['id'];
    $updateQuery = "UPDATE demo SET col_1 = '$val_1', col_2 = '$val_2' WHERE id = $id;";
    if ($conn->query($updateQuery) === TRUE) echo "Record Updated Successfully.";
    else echo "Error: " . $updateQuery . "<br>" . $conn->error;
}

//Delete
if($requestType == "DELETE"){
    $str = file_get_contents("php://input");
    $_DELETE = array();
    parse_str($str, $_DELETE);
    $id = $_DELETE['id'];
    $deleteQuery = "DELETE FROM demo WHERE id = $id";
    if ($conn->query($deleteQuery) === TRUE) echo "Record Deleted Successfully.";
    else echo "Error: " . $deleteQuery . "<br>" . $conn->error;
}
?>

<?php

//Accessable From Everywhere

header("Access-Control-Allow-Origin: *");

//Establish Connection

$servername = "localhost";

$username = "root";

$password = "";

$dbname = "demo";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

$requestType = $_SERVER["REQUEST_METHOD"];

//Create

if($requestType == "POST"){

$val_1 = $_POST['val_1'];

$val_2 = $_POST['val_2'];

$insertQuery = "INSERT INTO demo (col_1, col_2) VALUES ('$val_1','$val_2');";

if ($conn->query($insertQuery) === TRUE) echo "New Record Added Successfully.";

else echo "Error: " . $insertQuery . "<br>" . $conn->error;

}

//Read

if($requestType == "GET" AND !isset($_GET["id"])){

$selectQuery = "SELECT * FROM demo";

$result = $conn->query($selectQuery);

$rows = array();

while($row = $result->fetch_assoc())

$rows[] = $row;

header("Content-Type: application/json");

echo json_encode($rows);

}

//Read Only One

if($requestType == "GET" AND isset($_GET["id"])){

$id = $_GET["id"];

$selectQuery = "SELECT * FROM demo WHERE ID = $id";

$result = $conn->query($selectQuery);

$rows = array();

if ($result->num_rows > 0){

while($row = $result->fetch_assoc())

$rows[] = $row;

header("Content-Type: application/json");

echo json_encode($rows);

}

else echo "Not Found.";

}

//Update

if($requestType == "PUT"){

$str = file_get_contents("php://input");

$_PUT = array();

parse_str($str, $_PUT);

$val_1 = $_PUT['val_1'];

$val_2 = $_PUT['val_2'];

$id = $_PUT['id'];

$updateQuery = "UPDATE demo SET col_1 = '$val_1', col_2 = '$val_2' WHERE id = $id;";

if ($conn->query($updateQuery) === TRUE) echo "Record Updated Successfully.";

else echo "Error: " . $updateQuery . "<br>" . $conn->error;

}

//Delete

if($requestType == "DELETE"){

$str = file_get_contents("php://input");

$_DELETE = array();

parse_str($str, $_DELETE);

$id = $_DELETE['id'];

$deleteQuery = "DELETE FROM demo WHERE id = $id";

if ($conn->query($deleteQuery) === TRUE) echo "Record Deleted Successfully.";

else echo "Error: " . $deleteQuery . "<br>" . $conn->error;

}

Here below I’ve also provided the Database queries to create the table and to perform different CRUD operations on these tables.

Database Name. demo
Table Name. demo

Create Table Query

CREATE TABLE demo (id int NOT NULL AUTO_INCREMENT, val_1 TEXT, val_2 TEXT, PRIMARY KEY (id));

Insert Query

INSERT INTO demo (val_1, val_2) VALUES ('$val_1', '$val_2');

Update Query

UPDATE demo SET val_1 = '$val_1', val_2 = '$val_2' WHERE id = $id;

Insert Or Update On Duplicate (Not Required For The Tutorial, Just For More Knowledge!)

INSERT INTO demo (val_1, val_2) VALUES ('$val_1', '$val_2') ON DUPLICATE KEY UPDATE val_1 = '$val_1', val_2 = '$val_2';

Select Query

SELECT * FROM demo

SELECT * FROM demo WHERE id = $id

Delete Query

DELETE FROM demo WHERE id = $id

Understanding The Code

Now, let’s understand the code by each atomic segment of the code that performs a single CRUD operation. One this to notice here is the way of programming. I am using the Object-Oriented way but you can also achieve the same thing using procedural programming with PHP.

Setting Required Header

First of all, I’ve specified a header Access-Control-Allow-Origin as *. This will allow the API to be used un-restrictedly from anywhere, rather than just from the local server. Not setting this header will result in Cross-Origin Resource Sharing (CORS) error when being accessed from other servers or static HTML files. You can set any header using the PHP header() function.

header("Access-Control-Allow-Origin: *");

Related Guide. PHP Redirect – How To Redirect With PHP header() Function?

Establishing Connection With The MySQL Database

In order to communicate with the database for each of the CRUD operations, you’ll first require to establish a connection with the database. In PHP, you can easily establish a connection with the MySQL database using the MySQLi extension. Most of the PHP servers these days have this extension pre-installed. If the code does not work on your server, you need to check if you have this extension installed or not.

A new mysqli object has to be created and assigned to the connection variable $conn. This mysqli object function takes four arguments.

Server Name
Database Username
Password for the Database User
Database Name

In our code, all of these are stored in the variables $servername, $username, $password and $dbname respectively and then these variables are passed to the mysqli object function in the same order. Now, the $conn is the connection object which we’ll be used later on to run queries on this database. We’re also checking for any database connectivity errors using the connect_error property of the $conn variable.

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "demo";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$servername = "localhost";

$username = "root";

$password = "";

$dbname = "demo";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

Getting The Request Type

If you’re aware of the concept of CRUD APIs, you might be knowing that different request methods are used for different database operations. For all the beginners out there, let me review this concept quickly.

GET Request – Used For Getting the retrieve the data from the database. (Select Query)
POST Request – Used For Inserting the data into the database. (Insert Query)
PUT Request – Used for Updating the records in the database. (Update Query)
DELETE Request – Used for deleting the database records. (Delete Query)

In PHP, we can use the REQUEST_METHOD property of the $_SERVER superglobal variable to get the current request type.

$requestType = $_SERVER["REQUEST_METHOD"];

Now the $requestType variables hold the request type as string i.e. GET or POST or PUT or DELETE.

Shifting Control To Required Block

Now, we’re using if conditions blocks to execute different CRUD operations as per the Request Method. There are 5 different if blocks and only one of which will be executed in one request and that completely depends upon the type of request. So, now let us discuss each of these operations individually.

CRUD Operations

Create or Insert Operation (C)

if($requestType == "POST"){
    $val_1 = $_POST['val_1'];
        $val_2 = $_POST['val_2'];
    $insertQuery = "INSERT INTO demo (col_1, col_2) VALUES ('$val_1','$val_2');";
    if ($conn->query($insertQuery) === TRUE) echo "New Record Added Successfully.";
    else echo "Error: " . $insertQuery . "<br>" . $conn->error;
}

if($requestType == "POST"){

$val_1 = $_POST['val_1'];

$val_2 = $_POST['val_2'];

$insertQuery = "INSERT INTO demo (col_1, col_2) VALUES ('$val_1','$val_2');";

if ($conn->query($insertQuery) === TRUE) echo "New Record Added Successfully.";

else echo "Error: " . $insertQuery . "<br>" . $conn->error;

}

This block of code is executed when the request method is POST. We’re extracting all the key-value pair data present in the request variable $_POST into the individual variables. Now, in the next line, we’re writing the SQL Insert Query and storing it into variable $insertQuery. The next line of code executes the query.

$conn->query($insertQuery) executest the query and return true if the query has been executed successfully. We’re checking the same in an if-else block and printing the success or error output accordingly. That’s it for the create operation of the API.

Read & Read Only One (R)

Read and Read only-one both are similar, even the code for both are similar. To get all the records from the database table we need not any inputs from the request variables and we can simply execute the query to fetch all records. Both of these blocks execute when the request method is GET. But as in one request, we’ve to execute only one block, so we’re checking if the id parameter is set or not.

If the id parameter is not set that means the user requested for all records and if the id parameter is set then it means the user requested for one particular record corresponding to that id. We’re using the PHP’s isset() function to check so in the if the condition that is further AND with request type condition.

//Read
if($requestType == "GET" AND !isset($_GET["id"])){
    $selectQuery = "SELECT * FROM demo";
    $result = $conn->query($selectQuery);
    $rows = array();
    while($row = $result->fetch_assoc())
        $rows[] = $row;
    header("Content-Type: application/json");
    echo json_encode($rows);
}

//Read Only One
if($requestType == "GET" AND isset($_GET["id"])){
    $id = $_GET["id"];
    $selectQuery = "SELECT * FROM demo WHERE ID = $id";
    $result = $conn->query($selectQuery);
    $rows = array();
    if ($result->num_rows > 0){
        while($row = $result->fetch_assoc())
            $rows[] = $row;
        header("Content-Type: application/json");
        echo json_encode($rows);
    }
    else echo "Not Found.";
}

//Read

if($requestType == "GET" AND !isset($_GET["id"])){

$selectQuery = "SELECT * FROM demo";

$result = $conn->query($selectQuery);

$rows = array();

while($row = $result->fetch_assoc())

$rows[] = $row;

header("Content-Type: application/json");

echo json_encode($rows);

}

//Read Only One

if($requestType == "GET" AND isset($_GET["id"])){

$id = $_GET["id"];

$selectQuery = "SELECT * FROM demo WHERE ID = $id";

$result = $conn->query($selectQuery);

$rows = array();

if ($result->num_rows > 0){

while($row = $result->fetch_assoc())

$rows[] = $row;

header("Content-Type: application/json");

echo json_encode($rows);

}

else echo "Not Found.";

}

In both of these blocks, before printing the results, we’re also setting the Content-Type header as application/json and then further printing the data in JSON encoded format. It is a recommended practice as reading JSON data is easy for many front-end development tasks.

We’re using the PHP function json_encode() for converting the output array into JSON format. The select code is a bit longer than the other operations and the reason is more than just executing the query, we also have to store the data into variables to further showcase at the end.

First of all, we’re executing the query the same way as other operations and then we’re checking if the result contains any records using the num_rows property of the $result variable. Using the condition, $row = $result->fetch_assoc() in the while loop will keep on executing the loop until all the records are not loop-through. Each data record is stored as an element of the $rows array. This is the same array which we’ll present to the user in JSON format.

Update Operation (U)

In PHP, there is no default $_PUT request method. So, we’ve to get the request variables from the PHP standard input. It’s similar to reading a file located at the address php://input. Now, the $str variable will contain the query string containing all the request parameters. We are using the PHP parse_str function that will convert this query string into a key-value pair array $_PUT like the default request arrays $_POST and $_GET.

if($requestType == "PUT"){
    $str = file_get_contents("php://input");
    $_PUT = array();
    parse_str($str, $_PUT);
    $val_1 = $_PUT['val_1'];
        $val_2 = $_PUT['val_2'];
        $id = $_PUT['id'];
        $updateQuery = "UPDATE demo SET col_1 = '$val_1', col_2 = '$val_2' WHERE id = $id;";
    $updateQuery = "UPDATE demo SET gurmeet = '$gurmeet' WHERE id = $id;";
    if ($conn->query($updateQuery) === TRUE) echo "Record Updated Successfully.";
    else echo "Error: " . $updateQuery . "<br>" . $conn->error;
}

if($requestType == "PUT"){

$str = file_get_contents("php://input");

$_PUT = array();

parse_str($str, $_PUT);

$val_1 = $_PUT['val_1'];

$val_2 = $_PUT['val_2'];

$id = $_PUT['id'];

$updateQuery = "UPDATE demo SET col_1 = '$val_1', col_2 = '$val_2' WHERE id = $id;";

$updateQuery = "UPDATE demo SET gurmeet = '$gurmeet' WHERE id = $id;";

if ($conn->query($updateQuery) === TRUE) echo "Record Updated Successfully.";

else echo "Error: " . $updateQuery . "<br>" . $conn->error;

}

Further, the query execution is similar to the create function. We’re storing the query in the $updateQuery variable and executing the query and checking if it successfully executed or not, exactly like the way we did in the insertion part.

Delete Operation (D)

Nothing different in the delete operation that the condition that this block will execute on the DELETE request method. The API request user also has to provide the id of the record to be deleted.

if($requestType == "DELETE"){
    $str = file_get_contents("php://input");
    $_DELETE = array();
    parse_str($str, $_DELETE);
    $id = $_DELETE['id'];
    $deleteQuery = "DELETE FROM demo WHERE id = $id";
    if ($conn->query($deleteQuery) === TRUE) echo "Record Deleted Successfully.";
    else echo "Error: " . $deleteQuery . "<br>" . $conn->error;
}

if($requestType == "DELETE"){

$str = file_get_contents("php://input");

$_DELETE = array();

parse_str($str, $_DELETE);

$id = $_DELETE['id'];

$deleteQuery = "DELETE FROM demo WHERE id = $id";

if ($conn->query($deleteQuery) === TRUE) echo "Record Deleted Successfully.";

else echo "Error: " . $deleteQuery . "<br>" . $conn->error;

}

Similar to Update block, we’re getting the request input variable id after manually creating a request variables array $_DELETE by first getting the query string and then converting it into an array of key-value pairs. The $deleteQuery variable holds the delete query and executes it inside the if condition to further check if the record has been deleted successfully or not and accordingly it shows the output to the user.

Testing the CRUD API

Once, you’ve successfully written the code for this PHP MySQL CRUD API, now you can proceed further to test it. You can use any API Test tool like PostMan.

But just for you people, I’ve also created a Full GUI interface that you can use to send requests for all CRUD operations. This interface sends requests to the API using $.ajax() function provided with jQuery.

This code show all the table data using DataTable and the options are provided to perform the various options.

Steps to Run This Code.

Replace http://localhost/smart/api.php with your API URL.
Save the code in an HTML file and run it.

<!DOCTYPE html>
<html>
<head>
<title>Testing the API With jQuery AJAX</title>
<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.19/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.19/css/jquery.dataTables.css">
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
</head>
<body class="container">
<div class="row">
    <div class="col col-md-12">
        <center class="m-3">
            <button class="btn btn-success" onclick="addModal();">Add New <i class="fa fa-plus"></i></button>
        </center>
    </div>
</div>
<table id="dataTable" class="table table-hover">
    <thead>
        <td>ID</td>
        <td>val_1</td><td>val_2</td>        <td>Options</td>
    </thead>
</table>
<script>
$(document).ready(function(){
    $('#dataTable').DataTable({
        "processing" : true,
        "ajax" : {
            "url" : "http://localhost/smart/api.php",
            dataSrc : ''
        },
        "columns" : [
        {"data" : "id"},
                {"data" : "val_1"},
                {"data" : "val_2"},
                {"render": function (data, type, row, meta) {
                return '<button class="m-1 btn btn-secondary" onclick="editModal(' + row.id + ')">Edit <i class="fa fa-edit"></i></button>'
                + '<button class="m-1 btn btn-danger" onclick="deleteModal(' + row.id + ')">Delete <i class="fa fa-trash"></i></button>';
            }
        }
        ]
    });
});

function editModal(id){
    $('#editModal').modal('show');
    $('#serial').attr("count",id);
        $('#editModal [name=val_1]').val("");
    $('#editModal [name=val_1]').prop('disabled', true);
        $('#editModal [name=val_2]').val("");
    $('#editModal [name=val_2]').prop('disabled', true);
        $.ajax({
        type: 'GET',
        url: "http://localhost/smart/api.php",
        data: {id: id},
        success: function(response){
            data = response[0];
                        $('#editModal [name=val_1]').val(data.val_1);
            $('#editModal [name=val_1]').prop('disabled', false);
                        $('#editModal [name=val_2]').val(data.val_2);
            $('#editModal [name=val_2]').prop('disabled', false);
                    }
    });
}

function deleteModal(id){
    $('#deleteModal').modal('show');
    $('#deleteSerial').attr("count",id);
}

function deleteField(id){
    $.ajax({
        type: 'DELETE',
        url: "http://localhost/smart/api.php",
        data: {id: id},
        success: function(response){
            $('#dataTable').DataTable().ajax.reload();
                toast(response);
            }
    });
    $('#deleteModal').modal('hide');
}

function addModal(){
    $('#addModal').modal('show');
        type = $('#addModal [name=val_1]').attr('type');
    if(type != 'checkbox' && type != 'radio'){
    $('#addModal [name=val_1]').val("");
    }
        type = $('#addModal [name=val_2]').attr('type');
    if(type != 'checkbox' && type != 'radio'){
    $('#addModal [name=val_2]').val("");
    }
    }

function addNew(){
    formData = $("#addModal form").serializeArray();
    $.ajax({
        type: 'POST',
        url: "http://localhost/smart/api.php",
        data: formData,
        success: function(response){
            $('#dataTable').DataTable().ajax.reload();
                toast(response);
            }
    });
    $('#addModal').modal('hide');
}

function save(id){
    formData = $("#editModal form").serializeArray();
    formData.push({name: "id", value: id});
    $.ajax({
        type: 'PUT',
        url: "http://localhost/smart/api.php",
        data: formData,
        success: function(response){
            $('#dataTable').DataTable().ajax.reload();
                toast(response);
            }
    });
    $('#editModal').modal('hide');
}

function toast(message){
    $('#toastMessage').text(message);
    $('#toast').toast('show');
}

</script>

<!-- Edit Modal -->
<div class="modal fade" id="editModal" data-backdrop="static" tabindex="-1" role="dialog" aria-labelledby="staticBackdropLabel" aria-hidden="true">
  <div class="modal-dialog" role="document">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title" id="staticBackdropLabel">Edit Field</h5>
        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body">
        <form action="" method="POST" enctype="multipart/form-data">

<!-- val_1 -->
<div class="form-group">
<label>val_1</label>
<input class="form-control" type="text" name="val_1" placeholder="val_1">
</div>


<!-- val_2 -->
<div class="form-group">
<label>val_2</label>
<input class="form-control" type="text" name="val_2" placeholder="val_2">
</div>


</form>      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
        <button id="serial" type="button" class="btn btn-primary" onclick="save(this.getAttribute('count'));">Save Changes</button>
      </div>
    </div>
  </div>
</div>

<!-- Insert Modal -->
<div class="modal fade" id="addModal" data-backdrop="static" tabindex="-1" role="dialog" aria-labelledby="staticBackdropLabel" aria-hidden="true">
  <div class="modal-dialog" role="document">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title" id="staticBackdropLabel">Add New Field</h5>
        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body">
        <form action="" method="POST" enctype="multipart/form-data">

<!-- val_1 -->
<div class="form-group">
<label>val_1</label>
<input class="form-control" type="text" name="val_1" placeholder="val_1">
</div>


<!-- val_2 -->
<div class="form-group">
<label>val_2</label>
<input class="form-control" type="text" name="val_2" placeholder="val_2">
</div>


</form>      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
        <button type="button" class="btn btn-primary" onclick="addNew();">Submit Data</button>
      </div>
    </div>
  </div>
</div>

<!-- Delete Modal -->
<div class="modal fade" id="deleteModal" data-backdrop="static" tabindex="-1" role="dialog" aria-labelledby="staticBackdropLabel" aria-hidden="true">
  <div class="modal-dialog" role="document">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title" id="staticBackdropLabel">Delete Field</h5>
        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body">
        <p>Are you sure that you want to delete this field?</p>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-secondary" data-dismiss="modal">NO, TAKE ME BACK</button>
        <button id="deleteSerial" type="button" class="btn btn-primary" onclick="deleteField(this.getAttribute('count'));">YES, DELETE</button>
      </div>
    </div>
  </div>
</div>

<div id="toast" class="toast" role="toast" aria-live="assertive" aria-atomic="true" data-delay="5000" style="display: block; position: fixed; top: 10px; right: 10px; min-width: 200px;">
  <div class="toast-header">
    <i class="fa fa-paper-plane"></i>&nbsp;
    <strong class="mr-auto">Response</strong>
    <small class="text-muted">Just Now</small>
    <button type="button" class="ml-2 mb-1 close" data-dismiss="toast" aria-label="Close">
      <span aria-hidden="true">&times;</span>
    </button>
  </div>
  <div class="toast-body" id="toastMessage">
  </div>
</div>

</body>
</html>

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

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

<!DOCTYPE html>

<html>

<head>

<title>Testing the API With jQuery AJAX</title>

</head>

</center>

</div>

<thead>

<td>val_1</td><td>val_2</td> <td>Options</td>

</thead>

</table>

$(document).ready(function(){

$('#dataTable').DataTable({

"processing" : true,

"ajax" : {

"url" : "http://localhost/smart/api.php",

dataSrc : ''

"columns" : [

{"data" : "id"},

{"data" : "val_1"},

{"data" : "val_2"},

{"render": function (data, type, row, meta) {

return '<button class="m-1 btn btn-secondary" onclick="editModal(' + row.id + ')">Edit <i class="fa fa-edit"></i></button>'

+ '<button class="m-1 btn btn-danger" onclick="deleteModal(' + row.id + ')">Delete <i class="fa fa-trash"></i></button>';

}

]

});

function editModal(id){

$('#editModal').modal('show');

$('#serial').attr("count",id);

$('#editModal [name=val_1]').val("");

$('#editModal [name=val_1]').prop('disabled', true);

$('#editModal [name=val_2]').val("");

$('#editModal [name=val_2]').prop('disabled', true);

$.ajax({

type: 'GET',

url: "http://localhost/smart/api.php",

data: {id: id},

success: function(response){

data = response[0];

$('#editModal [name=val_1]').val(data.val_1);

$('#editModal [name=val_1]').prop('disabled', false);

$('#editModal [name=val_2]').val(data.val_2);

$('#editModal [name=val_2]').prop('disabled', false);

}

});

}

function deleteModal(id){

$('#deleteModal').modal('show');

$('#deleteSerial').attr("count",id);

}

function deleteField(id){

$.ajax({

type: 'DELETE',

url: "http://localhost/smart/api.php",

data: {id: id},

success: function(response){

$('#dataTable').DataTable().ajax.reload();

toast(response);

}

});

$('#deleteModal').modal('hide');

}

function addModal(){

$('#addModal').modal('show');

type = $('#addModal [name=val_1]').attr('type');

if(type != 'checkbox' && type != 'radio'){

$('#addModal [name=val_1]').val("");

}

type = $('#addModal [name=val_2]').attr('type');

if(type != 'checkbox' && type != 'radio'){

$('#addModal [name=val_2]').val("");

}

function addNew(){

formData = $("#addModal form").serializeArray();

$.ajax({

type: 'POST',

url: "http://localhost/smart/api.php",

data: formData,

success: function(response){

$('#dataTable').DataTable().ajax.reload();

toast(response);

}

});

$('#addModal').modal('hide');

}

function save(id){

formData = $("#editModal form").serializeArray();

formData.push({name: "id", value: id});

$.ajax({

type: 'PUT',

url: "http://localhost/smart/api.php",

data: formData,

success: function(response){

$('#dataTable').DataTable().ajax.reload();

toast(response);

}

});

$('#editModal').modal('hide');

}

function toast(message){

$('#toastMessage').text(message);

$('#toast').toast('show');

}

</script>

<h5 class="modal-title" id="staticBackdropLabel">Edit Field</h5>

</button>

</div>

</div>

</div>

</form> </div>

<button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>

<button id="serial" type="button" class="btn btn-primary" onclick="save(this.getAttribute('count'));">Save Changes</button>

</div>

<h5 class="modal-title" id="staticBackdropLabel">Add New Field</h5>

</button>

</div>

</div>

</div>

</form> </div>

<button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>

<button type="button" class="btn btn-primary" onclick="addNew();">Submit Data</button>

</div>

<h5 class="modal-title" id="staticBackdropLabel">Delete Field</h5>

</button>

</div>

<p>Are you sure that you want to delete this field?</p>

</div>

<button id="deleteSerial" type="button" class="btn btn-primary" onclick="deleteField(this.getAttribute('count'));">YES, DELETE</button>

</div>

<strong class="mr-auto">Response</strong>

</button>

</div>

</div>

</body>

</html>

Conclusion

This is a basic CRUD tutorial using plain PHP and MYSQL. In a production environment, APIs can be made much more powerful, robust and secure. Most of the time in the industry, we do not use plain PHP for creating CRUD APIs with MySQL or any other database environment. There are more powerful frameworks like Laravel and Codeignitor which can be used for better CRUD APIs.

I personally prefer to use Laravel for creating any kind of CRUD APIs. But if you’re a beginner you should start with Codeignitor and then further should move to Laravel as it is a bit more complex than Codeignitor. Both of these follow the MVC (Model View Controller) approach but with Codeignitor understanding this concept becomes much easier.

Related Articles.

That’s it for the PHP MySQL CRUD tutorial. I hope as a beginner, you found this article useful. If so, do share it with others who might get benefits from this article as well. Feel free to ask any kind of questions related to this article in the comments section.

Also, don’t forget to Subscribe to WTMatter!

PHP, Programming

PHP MySQL CRUD Tutorial For Beginners

PHP MySQL CRUD API Code

Understanding The Code

Setting Required Header

Establishing Connection With The MySQL Database

Getting The Request Type

Shifting Control To Required Block

CRUD Operations

Create or Insert Operation (C)

Read & Read Only One (R)

Update Operation (U)

Delete Operation (D)

Testing the CRUD API

Conclusion

Subscribe To WTMatter!

Related Posts:

Gurmeet Singh

Leave a Reply Cancel reply