PHP Mysql Ajax Project Step by Step

Posted on June 6, 2021

19 min read

966

This Php Ajax Project will teach you how to do basic database functions that are CREATE RETIEVE UPDATE and DELETE. using Mysql Database using Ajax. The INSERT, SELECT, UPDATE and DELETE statements can be used in any database system, because this is support by all relational database systems.The project is very helpful for learn Ajax technology.

All the libraries i put in to one folder which name is components.

https://drive.google.com/drive/folders/1Wn6kACTV24qFw6ztKUcfQ9CsQUF9PFF1

how to download it i attached the screen shot image below.

First Step Establish the Database Connection

Create the Page db.php.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "studcrud";

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

<?php

$servername = "localhost";

$username = "root";

$password = "";

$dbname = "studcrud";

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

if($conn->connect_error)

{

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

}

After that design the index page here we are going to implement the crud opertation.

Index.php

<html>

<head>
    <link rel="stylesheet" href="components/bootstrap/dist/css/bootstrap.css">
    <link rel="stylesheet" href="components/bootstrap/dist/css/bootstrap.min.css">
    <link rel="stylesheet" href="components/jquery-confirm-master/css/jquery-confirm.css">
    <link rel="stylesheet" href="http://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css">

</head>
<body>


<nav class="nav navbar-inverse">
    <div class="container-fluid">
        <div class="navbar-header" align="center">
            <a class="navbar-brand" href="#"><b>Simple Student Management Crud</b></a>
        </div>
    </div>
</nav>

</br></br>

<div class="container-fluid bg-2 text-center">
    <div class="row">

        <div class="col-sm-4">

            <form id="frmProject" name="frmProject">

                <div class="form-group" align="left">
                    <label class="form-label">Student Name</label>
                    <input type="text" class="form-control" placeholder="Student Name" id="studname" name="studname" size="30px" required>
                </div>
                <div class="form-group" align="left">
                    <label class="form-label">Course</label>
                    <input type="text" class="form-control" placeholder="Course" id="course" name="course" size="30px" required>
                </div>
                <div class="form-group" align="left">
                    <label class="form-label">Fee</label>
                    <input type="text" class="form-control" placeholder="Fee" id="fee" name="fee" size="30px" required>
                </div>

                <div class="form-group">
                    <label for="" class="col-sm-2 control-label">Version Name</label>

                    <div class="col-sm-10">
                        <select class="form-control" id="project_version_id" name="project_version_id" required>
                            <option value="">Please Selecssst</option>

                        </select>

                    </div>
                </div>



            </form>

        </div>


        <div class="col-sm-8">

            <div class="panel-body">

                <table id="tbl-projects" class="table table-responsive table-bordered" cellspacing="0" width="100%">
                    <thead>
                    <tr>
                        <th></th>
                        <th></th>
                        <th></th>
                        <th></th>
                        <th></th>
                    </tr>

                </table>
            </div>

        </div>

    </div>
</div>

<script src="components/jquery/dist/jquery.js"></script>



<script src="components/jquery/dist/jquery.min.js"></script>



<script src="components/jquery-confirm-master/js/jquery-confirm.js"></script>

<script src="http://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>

<script src="components/jquery.validate.min.js"></script>


<script>
    var isNew =true;
    var project_id = null;
    get_all();

    function addStudent()
    {
        if($("#frmProject").valid())
        {

            var url= '';
            var dat = '';
            var method = '';
            if(isNew == true)
            {
                url = 'add_project.php';
                data = $('#frmProject').serialize();
                method = 'POST';
            }

            else
            {
                url = 'update_project.php';
                data = $('#frmProject').serialize() + "&project_id=" + project_id;
                method = 'POST';
            }

            $.ajax({

                type : method,
                url : url,
                dataType : 'JSON',
                data : data,



                success : function (data)
                {
                    $('#frmProject')[0].reset();

                    $('#save').html('');
                    $('#save').append('Add');

                    var msg;
                    get_all();
                    if(isNew)
                    {
                        msg = "Course Created";
                    }
                    else
                    {
                        msg = "Course Updated";
                    }

                    $.alert({
                        title: 'Success!',
                        content: msg,
                        type: 'green',
                        boxWidth: '400px',
                        theme: 'light',
                        useBootstrap: false,
                        autoClose: 'ok|2000'
                    });
                    isNew = true;
                },
                error: function (xhr, status, error) {
                    alert(xhr);
                    console.log(xhr.responseText);

                    $.alert({
                        title: 'Fail!',
                        //            content: xhr.responseJSON.errors.product_code + '<br>' + xhr.responseJSON.msg,
                        type: 'red',
                        autoClose: 'ok|2000'

                    });
                    $('#save').prop('disabled', false);
                    $('#save').html('');
                    $('#save').append('Save');
                }

            });

        }
    }


    function get_all()
        {
            $('#tbl-projects').dataTable().fnDestroy();
            $.ajax({

                url : "all_project.php",
                type : "GET",
                dataType : "JSON",


                success:function(data)
                {

                    $('#tbl-projects').html(data);

                    $('#tbl-projects').dataTable({
                        "aaData": data,
                        "scrollX": true,
                        "aoColumns": [
                            {"sTitle": "StudentName", "mData": "name"},
                            {"sTitle": "Course", "mData": "course"},
                            {"sTitle": "Fee", "mData": "fee"},
                            {
                                "sTitle": "Status","mData": "status", "render": function (mData, type, row, meta) {
                                if (mData == 1) {
                                    return '<span class="label label-info">Active</span>';
                                }
                                else if (mData == 2) {
                                    return '<span class="label label-warning">Deactive</span>';
                                }
                            }
                            },
                            {
                                "sTitle": "Edit",
                                "mData": "id",
                                "render": function (mData, type, row, meta) {
                                    return '<button class="btn btn-xs btn-success" onclick="get_project_details(' + mData + ')">Edit</button>';
                                }
                            },
                            {
                                "sTitle": "Delete",
                                "mData": "id",
                                "render": function (mData, type, row, meta) {
                                    return '<button class="btn btn-xs btn-primary" onclick="Remove_details(' +  mData + ')">Delete</button>';
                                }
                            }
                        ]
                    });

                },

                error: function (xhr, status, error) {
                    alert(xhr);
                    console.log(xhr.responseText);

                    $.alert({
                        title: 'Fail!',
                        //            content: xhr.responseJSON.errors.product_code + '<br>' + xhr.responseJSON.msg,
                        type: 'red',
                        autoClose: 'ok|2000'

                    });
                    $('#save').prop('disabled', false);
                    $('#save').html('');
                    $('#save').append('Save');
                }





            });

        }




    function get_project_details(id) {
        $.ajax({
            type: 'POST',
            url: 'project_return.php',
            dataType: 'JSON',
            data: {project_id: id},
            success: function (data) {
                $("html, body").animate({scrollTop: 0}, "slow");
                isNew = false
                console.log(data);

                project_id = data.id
                $('#studname').val(data.name);
                $('#course').val(data.course);
                $('#fee').val(data.fee);
                $('#status').val(data.status);
                $('#frmProject').modal('show');
            },
            error: function (xhr, status, error) {
                alert(xhr);
                console.log(xhr.responseText);

                $.alert({
                    title: 'Fail!',
                    //            content: xhr.responseJSON.errors.product_code + '<br>' + xhr.responseJSON.msg,
                    type: 'red',
                    autoClose: 'ok|2000'

                });
            }

        });
    }


    function Remove_details(id)
    {
        $.confirm({

            theme: 'supervan',
            buttons: {

                yes : function()
                {
                    $.ajax({
                        type: 'POST',
                        url: 'delete_project.php',
                        dataType: 'JSON',
                        data: {project_id: id},

                        success: function (data) {

                            get_all();


                        },


                        error: function (xhr, status, error) {
                            alert(xhr);
                            console.log(xhr.responseText);

                            $.alert({
                                title: 'Fail!',
                                //            content: xhr.responseJSON.errors.product_code + '<br>' + xhr.responseJSON.msg,
                                type: 'red',
                                autoClose: 'ok|2000'

                            });
                        }



                    });


                },
                no : function()
                {

                }



            }




        });

    }

</script>

</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

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

<html>

<head>

</head>

<body>

<a class="navbar-brand" href="#"><b>Simple Student Management Crud</b></a>

</div>

</nav>

</br></br>

<label class="form-label">Student Name</label>

</div>

<label class="form-label">Course</label>

</div>

</div>

<label for="" class="col-sm-2 control-label">Version Name</label>

<option value="">Please Selecssst</option>

</select>

</div>

</form>

</div>

<thead>

<tr>

</tr>

</table>

</div>

var isNew =true;

var project_id = null;

get_all();

function addStudent()

{

if($("#frmProject").valid())

{

var url= '';

var dat = '';

var method = '';

if(isNew == true)

{

url = 'add_project.php';

data = $('#frmProject').serialize();

method = 'POST';

}

else

{

url = 'update_project.php';

data = $('#frmProject').serialize() + "&project_id=" + project_id;

method = 'POST';

}

$.ajax({

type : method,

url : url,

dataType : 'JSON',

data : data,

success : function (data)

{

$('#frmProject')[0].reset();

$('#save').html('');

$('#save').append('Add');

var msg;

get_all();

if(isNew)

{

msg = "Course Created";

}

else

{

msg = "Course Updated";

}

$.alert({

title: 'Success!',

content: msg,

type: 'green',

boxWidth: '400px',

theme: 'light',

useBootstrap: false,

autoClose: 'ok|2000'

});

isNew = true;

error: function (xhr, status, error) {

alert(xhr);

console.log(xhr.responseText);

$.alert({

title: 'Fail!',

// content: xhr.responseJSON.errors.product_code + '<br>' + xhr.responseJSON.msg,

type: 'red',

autoClose: 'ok|2000'

});

$('#save').prop('disabled', false);

$('#save').html('');

$('#save').append('Save');

}

});

}

function get_all()

{

$('#tbl-projects').dataTable().fnDestroy();

$.ajax({

url : "all_project.php",

type : "GET",

dataType : "JSON",

success:function(data)

{

$('#tbl-projects').html(data);

$('#tbl-projects').dataTable({

"aaData": data,

"scrollX": true,

"aoColumns": [

{"sTitle": "StudentName", "mData": "name"},

{"sTitle": "Course", "mData": "course"},

{"sTitle": "Fee", "mData": "fee"},

{

"sTitle": "Status","mData": "status", "render": function (mData, type, row, meta) {

if (mData == 1) {

return '<span class="label label-info">Active</span>';

}

else if (mData == 2) {

return '<span class="label label-warning">Deactive</span>';

}

{

"sTitle": "Edit",

"mData": "id",

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

return '<button class="btn btn-xs btn-success" onclick="get_project_details(' + mData + ')">Edit</button>';

}

{

"sTitle": "Delete",

"mData": "id",

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

return '<button class="btn btn-xs btn-primary" onclick="Remove_details(' + mData + ')">Delete</button>';

}

]

});

error: function (xhr, status, error) {

alert(xhr);

console.log(xhr.responseText);

$.alert({

title: 'Fail!',

// content: xhr.responseJSON.errors.product_code + '<br>' + xhr.responseJSON.msg,

type: 'red',

autoClose: 'ok|2000'

});

$('#save').prop('disabled', false);

$('#save').html('');

$('#save').append('Save');

}

});

}

function get_project_details(id) {

$.ajax({

type: 'POST',

url: 'project_return.php',

dataType: 'JSON',

data: {project_id: id},

success: function (data) {

$("html, body").animate({scrollTop: 0}, "slow");

isNew = false

console.log(data);

project_id = data.id

$('#studname').val(data.name);

$('#course').val(data.course);

$('#fee').val(data.fee);

$('#status').val(data.status);

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

error: function (xhr, status, error) {

alert(xhr);

console.log(xhr.responseText);

$.alert({

title: 'Fail!',

// content: xhr.responseJSON.errors.product_code + '<br>' + xhr.responseJSON.msg,

type: 'red',

autoClose: 'ok|2000'

});

}

});

}

function Remove_details(id)

{

$.confirm({

theme: 'supervan',

buttons: {

yes : function()

{

$.ajax({

type: 'POST',

url: 'delete_project.php',

dataType: 'JSON',

data: {project_id: id},

success: function (data) {

get_all();

error: function (xhr, status, error) {

alert(xhr);

console.log(xhr.responseText);

$.alert({

title: 'Fail!',

// content: xhr.responseJSON.errors.product_code + '<br>' + xhr.responseJSON.msg,

type: 'red',

autoClose: 'ok|2000'

});

}

});

no : function()

{

}

});

}

</script>

</body>

</html>

Add records :- when you get add request from the Ajax call

add_project.php

<?php

include("db.php");

if($_SERVER['REQUEST_METHOD'] == 'POST')
{
    $stmt = $conn->prepare("insert into records(name,course,fee,status)values(?,?,?,?)");
    $stmt->bind_param("ssss",$name,$course,$fee,$status);


    $name = $_POST['studname'];
    $course = $_POST['course'];
    $fee = $_POST['fee'];
    $status = $_POST['status'];

    if($stmt->execute())
    {
        echo 1;
    }
    else
    {
        echo 0;
    }

    $stmt->close();
}

?>

<?php

include("db.php");

if($_SERVER['REQUEST_METHOD'] == 'POST')

{

$stmt = $conn->prepare("insert into records(name,course,fee,status)values(?,?,?,?)");

$stmt->bind_param("ssss",$name,$course,$fee,$status);

$name = $_POST['studname'];

$course = $_POST['course'];

$fee = $_POST['fee'];

$status = $_POST['status'];

if($stmt->execute())

{

echo 1;

}

else

{

echo 0;

}

$stmt->close();

}

View the Record from database to datatable:- when you get view request from the Ajax call

<?php

include ("db.php");

$stmt = $conn->prepare("select id,name,course,fee,status from records order by id Desc");
$stmt->bind_result($id,$name,$course,$fee,$status);

if($stmt->execute())
{
    while($stmt->fetch())
    {
        $output[] = array('id' => $id,'name' => $name,'course' => $course,'fee' => $fee,'status' => $status);
    }
    echo json_encode($output);
}


$stmt->close();


?>

<?php

include ("db.php");

$stmt = $conn->prepare("select id,name,course,fee,status from records order by id Desc");

$stmt->bind_result($id,$name,$course,$fee,$status);

if($stmt->execute())

{

while($stmt->fetch())

{

$output[] = array('id' => $id,'name' => $name,'course' => $course,'fee' => $fee,'status' => $status);

}

echo json_encode($output);

}

$stmt->close();

After load the Datatable we have two option Edit and Delete. before going edit the data we have to select particular row Edit button in the datatable .click edit button particular row data will pass in to the form. after will make the chages and click save data will updated. in the application we do save and edit using same form.

This following code use to pass the value in to the form.

<?php

include ("db.php");

if($_SERVER['REQUEST_METHOD'] == 'POST') {

    $stmt = $conn->prepare("select id,name,course,fee,status from records where id = ?");
    $stmt->bind_param("s",$project_id);
    $stmt->bind_result($id, $name, $course, $fee, $status);


    $project_id = $_POST['project_id'];

    if ($stmt->execute()) {
        while ($stmt->fetch()) {
            $output = array('id' => $id, 'name' => $name, 'course' => $course, 'fee' => $fee, 'status' => $status);
        }
        echo json_encode($output);
    }


    $stmt->close();

}


?>

<?php

include ("db.php");

if($_SERVER['REQUEST_METHOD'] == 'POST') {

$stmt = $conn->prepare("select id,name,course,fee,status from records where id = ?");

$stmt->bind_param("s",$project_id);

$stmt->bind_result($id, $name, $course, $fee, $status);

$project_id = $_POST['project_id'];

if ($stmt->execute()) {

while ($stmt->fetch()) {

$output = array('id' => $id, 'name' => $name, 'course' => $course, 'fee' => $fee, 'status' => $status);

}

echo json_encode($output);

}

$stmt->close();

}

Edit

After pass the value in to form click edit button record will be updated.

<?php

include("db.php");

if($_SERVER['REQUEST_METHOD'] == 'POST')
{
    $stmt = $conn->prepare("update records set name= ? , course = ?, fee = ?, status = ? where id = ?");
    $stmt->bind_param("sssss",$name,$course,$fee,$status,$project_id);

    $project_id = $_POST['project_id'];
    $name = $_POST['studname'];
    $course = $_POST['course'];
    $fee = $_POST['fee'];
    $status = $_POST['status'];

    if($stmt->execute())
    {
        echo 1;
    }
    else
    {
        echo 0;
    }

    $stmt->close();

}


?>

<?php

include("db.php");

if($_SERVER['REQUEST_METHOD'] == 'POST')

{

$stmt = $conn->prepare("update records set name= ? , course = ?, fee = ?, status = ? where id = ?");

$stmt->bind_param("sssss",$name,$course,$fee,$status,$project_id);

$project_id = $_POST['project_id'];

$name = $_POST['studname'];

$course = $_POST['course'];

$fee = $_POST['fee'];

$status = $_POST['status'];

if($stmt->execute())

{

echo 1;

}

else

{

echo 0;

}

$stmt->close();

}

Delete

which record need delete select particular row delete button and click.paricular row has been deleted.

<?php

include("db.php");

if($_SERVER['REQUEST_METHOD'] == 'POST')
{
    $stmt = $conn->prepare("delete from records where id = ?");

    $stmt->bind_param("s",$project_id);

    $project_id = $_POST['project_id'];



    if($stmt->execute())
    {
        echo 1;
    }
    else
    {
        echo 0;
    }

    $stmt->close();

}


?>

<?php

include("db.php");

if($_SERVER['REQUEST_METHOD'] == 'POST')

{

$stmt = $conn->prepare("delete from records where id = ?");

$stmt->bind_param("s",$project_id);

$project_id = $_POST['project_id'];

if($stmt->execute())

{

echo 1;

}

else

{

echo 0;

}

$stmt->close();

}

PHP Mysql Ajax Project Step by Step

First Step Establish the Database Connection

Edit

Delete

i have attached the video link below. which will do this tutorials step by step.

Restaurant Shop Inventory Management System using Laravel 8

CoffeeShop Inventory Management System using Laravel 8

Inventory Management System using Laravel 8

Node js React Mongodb Search Functionality

Node js Angular Mongodb Search Functionality

How to Search Records Node js Express Mongodb

PHP MYSQL CRUD Application || Create,Read,Update,Delete

MilkBar Inventory Management using Php

Registration System using Php Mysql

Leave a Reply Cancel reply

Check Also

Node js React Mongodb Search Functionality

Recent Posts

Ads Blocker Detected!!!

First Step Establish the Database Connection

Edit

(adsbygoogle = window.adsbygoogle || []).push({});

Delete

i have attached the video link below. which will do this tutorials step by step.

Leave a Reply Cancel reply

Check Also

Recent Posts

Ads Blocker Detected!!!