web-development (ajax)

Showing posts with label web-development (ajax). Show all posts

Monday, March 7, 2022

PHP PDO Mysql Check username availability with Jquery AJAX

PHP PDO Mysql Check username availability with Jquery AJAX

Bootstrap 5

https://getbootstrap.com/docs/5.0/getting-started/introduction/

https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css

https://getbootstrap.com/docs/5.0/components/modal/

Jquery

https://jquery.com/download/

CDN : jsDelivr CDN

https://www.jsdelivr.com/package/npm/jquery

https://cdn.jsdelivr.net/npm/jquery@3.6.0/dist/jquery.min.js

checkusername.php

//checkusername.php
<!doctype html>
<html>
 <head>
<title>PHP PDO Mysql Check username availability with Jquery AJAX </title>
<link rel = "stylesheet" type = "text/css" href = "https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" />		
<script src = "https://cdn.jsdelivr.net/npm/jquery@3.6.0/dist/jquery.min.js"></script>	
</head>
<body>
	<div class="container">
		<div class="row"><p><h1>PHP PDO Mysql Check username availability with Jquery AJAX </h1></p>
			<label class="form-label">Enter UserName</label>
			<input type="text" id="txt_username" class="form-control" name="txt_username" placeholder="Enter Username">
			<!-- Response -->
            <div id="uname_response" ></div>
		</div>
	</div>
    <script type='text/javascript'>
            $(document).ready(function(){

                $("#txt_username").keyup(function(){

                    var username = $(this).val().trim();

                    if(username != ''){

                        $.ajax({
                            url: 'ajaxfile.php',
                            type: 'post',
                            data: {username:username},
                            success: function(response){
                                console.log(response);
                                // Show response
                                $("#uname_response").html(response);

                            }
                        });
                    }else{
                        $("#uname_response").html("");
                    }

                });

            });
	</script>
</body>
</html>

ajaxfile.php

//ajaxfile.php
<?php

$server = "localhost";
$username = "root";
$password = "";
$dbname = "testingdb";

// Create connection
try{
   $conn = new PDO("mysql:host=$server;dbname=$dbname","$username","$password");
   $conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
   die('Unable to connect with the database');
}

if(isset($_POST['username'])){
	$username = $_POST['username'];

	// Check username
	$stmt = $conn->prepare("SELECT count(*) as cntUser FROM users WHERE username=:username");
	$stmt->bindValue(':username', $username, PDO::PARAM_STR);
	$stmt->execute(); 
	$count = $stmt->fetchColumn();
	//echo $count;
	
	if($count == 0){
		$response = "<span style='color: green;'>Available.</span>";	
	}else{
		$response = "<span style='color: red;'>Not Available.</span>";
	}	
	
	echo $response;
	exit;
}

Saturday, February 26, 2022

PHP Mysqli CRUD with JQuery AJAX and Bootstrap 5

PHP Mysqli CRUD (Create, Read, Update and Delete) with JQuery AJAX and Bootstrap 5

Bootstrap 5

https://getbootstrap.com/docs/5.0/getting-started/introduction/

https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css

Jquery

https://jquery.com/download/

CDN : jsDelivr CDN

https://www.jsdelivr.com/package/npm/jquery

https://cdn.jsdelivr.net/npm/jquery@3.6.0/dist/jquery.min.js

Bootstrap icons

https://icons.getbootstrap.com/#install

https://cdn.jsdelivr.net/npm/bootstrap-icons@1.8.1/font/bootstrap-icons.css

index.php

//index.php
<?php
	include('conn.php');
?>
<!DOCTYPE html>
<html lang = "en">
	<head>
		<meta charset = "UTF-8" name = "viewport" content = "width-device=width, initial-scale=1" />
		<link rel = "stylesheet" type = "text/css" href = "https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" />
		<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.8.1/font/bootstrap-icons.css">
		<title>PHP Mysqli CRUD with JQuery AJAX and Bootstrap 5</title>
	</head>
<body>
	<div style="height:30px;"></div>
	<div class = "row">	
		<div class = "col-md-3">
		</div>
		<div class = "col-md-6 well">
			<div class="row">
                <div class="col-lg-12">
                    <center><h2 class = "text-primary">PHP Mysqli CRUD with JQuery AJAX and Bootstrap 5</h2></center>
					<hr>
				<div>
					<form>
						<div class="mb-3">
							<label>Firstname:</label>
							<input type  = "text" id = "firstname" class = "form-control">
						</div>
						<div class="mb-3">
							<label>Lastname:</label>
							<input type  = "text" id = "lastname" class = "form-control">
						</div>
						<div class="mb-3">
							<button type = "button" id="addnew" class = "btn btn-primary"><i class="bi bi-clipboard2-plus-fill"></i> Add</button>
						</div>
					</form>
				</div>
                </div>
            </div><br>
			<div class="row">
			<div id="userTable"></div>
			</div>
		</div>
	</div>
</body>
<script src = "https://cdn.jsdelivr.net/npm/jquery@3.6.0/dist/jquery.min.js"></script>	
<script src = "https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js"></script>
<script type = "text/javascript">
	$(document).ready(function(){
		showUser();
		//Add New
		$(document).on('click', '#addnew', function(){
			if ($('#firstname').val()=="" || $('#lastname').val()==""){
				alert('Please input data first');
			}
			else{
			$firstname=$('#firstname').val();
			$lastname=$('#lastname').val();				
				$.ajax({
					type: "POST",
					url: "addnew.php",
					data: {
						firstname: $firstname,
						lastname: $lastname,
						add: 1,
					},
					success: function(){
						showUser();
					}
				});
			}
		});
		//Delete
		$(document).on('click', '.delete', function(){
			$id=$(this).val();
				$.ajax({
					type: "POST",
					url: "delete.php",
					data: {
						id: $id,
						del: 1,
					},
					success: function(){
						showUser();
					}
				});
		});
		//Update
		$(document).on('click', '.updateuser', function(){
			$uid=$(this).val();
			$('#edit'+$uid).modal('hide');
			$('body').removeClass('modal-open');
			$('.modal-backdrop').remove();
			$ufirstname=$('#ufirstname'+$uid).val();
			$ulastname=$('#ulastname'+$uid).val();
				$.ajax({
					type: "POST",
					url: "update.php",
					data: {
						id: $uid,
						firstname: $ufirstname,
						lastname: $ulastname,
						edit: 1,
					},
					success: function(){
						showUser();
					}
				});
		});
	
	});
	
	//Showing our Table
	function showUser(){
		$.ajax({
			url: 'show_user.php',
			type: 'POST',
			async: false,
			data:{
				show: 1
			},
			success: function(response){
				$('#userTable').html(response);
			}
		});
	}
	
</script>
</html>

conn.php

//conn.php
<?php
$conn = mysqli_connect("localhost","root","","testingdb");
if (!$conn) {
	die("Connection failed: " . mysqli_connect_error());
}
?>

addnew.php

//addnew.php
<?php
	include('conn.php');
	if(isset($_POST['add'])){
		$firstname=$_POST['firstname'];
		$lastname=$_POST['lastname'];
		
		mysqli_query($conn,"insert into usertble (firstname, lastname) values ('$firstname', '$lastname')");
	}
?>

show_user.php

//show_user.php
<?php
	include('conn.php');
	if(isset($_POST['show'])){
		?>
		<table class = "table table-bordered alert-success table-hover">
			<thead>
				<th>Firstname</th>
				<th>Lastname</th>
				<th>Action</th>
			</thead>
				<tbody>
					<?php
						$quser=mysqli_query($conn,"select * from usertble");
						while($urow=mysqli_fetch_array($quser)){
							?>
								<tr>
									<td><?php echo $urow['firstname']; ?></td>
									<td><?php echo $urow['lastname']; ?></td>
									<td><button class="btn btn-success" data-bs-toggle="modal" data-bs-target="#editModal<?php echo $urow['userid']; ?>"><i class="bi bi-pencil"></i> Edit</button> | <button class="btn btn-danger delete" value="<?php echo $urow['userid']; ?>"><i class="bi bi-trash"></i> Delete</button>
									<?php include('edit_modal.php'); ?>
									</td>
								</tr>
							<?php
						}
					
					?>
				</tbody>
			</table>
		<?php
	}

?>

edit_modal.php

//edit_modal.php
<div class="modal fade" id="edit<?php echo $urow['userid']; ?>" tabindex="-1" role="dialog" aria-labelledby="myModalLabel">
	<?php
		$n=mysqli_query($conn,"select * from `user` where userid='".$urow['userid']."'");
		$nrow=mysqli_fetch_array($n);
	?>
  <div class="modal-dialog" role="document">
    <div class="modal-content">
		<div class = "modal-header">
			<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
			<center><h3 class = "text-success modal-title">Update Member</h3></center>
		</div>
		<form>
		<div class="modal-body">
			Firstname: <input type="text" value="<?php echo $nrow['firstname']; ?>" id="ufirstname<?php echo $urow['userid']; ?>" class="form-control">
			Lastname: <input type="text" value="<?php echo $nrow['lastname']; ?>" id="ulastname<?php echo $urow['userid']; ?>" class="form-control">
		</div>
		<div class="modal-footer">
			<button type="button" class="btn btn-default" data-dismiss="modal"><span class = "glyphicon glyphicon-remove"></span> Cancel</button> | <button type="button" class="updateuser btn btn-success" value="<?php echo $urow['userid']; ?>"><span class = "glyphicon glyphicon-floppy-disk"></span> Save</button>
		</div>
		</form>
    </div>
  </div>
</div>

update.php

//update.php
<?php
	include('conn.php');
	if(isset($_POST['edit'])){
		$id=$_POST['id'];
		$firstname=$_POST['firstname'];
		$lastname=$_POST['lastname'];
		
		mysqli_query($conn,"update usertble set firstname='$firstname', lastname='$lastname' where userid='$id'");
	}
?>

//delete.php
<?php
	include('conn.php');
	if(isset($_POST['del'])){
		$id=$_POST['id'];
		mysqli_query($conn,"delete from usertble where userid='$id'");
	}
?>

Monday, January 31, 2022

PHP Mysqli Datatables Server Side CRUD Ajax (Create, Read, Update and Delete)

PHP Mysqli Datatables Server Side CRUD Ajax (Create, Read, Update and Delete)

Bootstrap 5.1 Version

https://getbootstrap.com/docs/5.1/getting-started/introduction/

DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool, built upon the foundations of progressive enhancement, that adds all of these advanced features to any HTML table.

https://datatables.net/

CREATE TABLE `users` (

`id` int(11) NOT NULL,

`username` varchar(50) NOT NULL,

`email` varchar(50) NOT NULL,

`mobile` varchar(20) NOT NULL,

`city` varchar(50) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `users` (`id`, `username`, `email`, `mobile`, `city`) VALUES

(7, 'Airi Satou', 'AiriSatou@gmail.com', '8887919632', 'Tokyo'),

(8, 'Angelica Ramos', 'AngelicaRamos@gmail.com', '8887919632', 'London'),

(9, 'Ashton Cox', 'AshtonCox@gmail.com', '8887919632', 'San Francisco'),

(10, 'Bradley Greer', 'BradleyGreer@gmail.com', '434334', 'London'),

(11, 'Brenden Wagner', 'BrendenWagner@gmail.com', '434334', 'San Francisco'),

(12, 'Brielle Williamson', 'BrielleWilliamson@gmail.com', '434334', 'New York'),

(13, 'Bruno Nash', 'BrunoNash@gmail.com', '9988999999', 'London'),

(15, 'Caesar Vance', 'CaesarVance@gmail.com', '8127956219', 'New York'),

(16, 'Cara Stevens', 'CaraStevens@gmail.com', '8127956219', 'New York'),

(18, 'Cedric Kelly', 'CedricKelly@gmail.com', '8127956219', 'Edinburgh'),

(19, 'Charde Marshall', 'ChardeMarshall@Gmail.com', '5464654654', 'San Francisco'),

(20, 'Colleen Hurst', 'ColleenHurst@Gmail.com', '5465465465', 'San Francisco'),

(21, 'Garrett Winters', 'GarrettWinters@gmail.com', '346546465', 'Olongapo City');

ALTER TABLE `users`

ADD PRIMARY KEY (`id`);

ALTER TABLE `users`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;

index.php

//index.php
<?php include('connection.php');?>
<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-+0n0xVW2eSR5OomGNYDnhzAbDsOXxcvSN1TPprVMTNDbiYZCxYbOOl7+AMvyTG2x" crossorigin="anonymous">
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.4/css/jquery.dataTables.min.css"/>
  <title>PHP Mysqli Datatables Server Side CRUD Ajax (Create, Read, Update and Delete)</title>
  <style type="text/css">
    .btnAdd {
      text-align: right;
      width: 83%;
      margin-bottom: 20px;
    }
  </style>
</head>
<body>
<div class="container-fluid">
    <h2 class="text-center">PHP Mysqli Datatables Server Side CRUD Ajax (Create, Read, Update and Delete)</h2>
    <div class="row">
      <div class="container">
        <div class="btnAdd">
         <a href="#!" data-id="" data-bs-toggle="modal" data-bs-target="#addUserModal"   class="btn btn-success btn-sm" >Add User</a>
       </div>
       <div class="row">
        <div class="col-md-2"></div>
        <div class="col-md-8">
         <table id="example" class="table">
          <thead>
            <th>Id</th>
            <th>Name</th>
            <th>Email</th>
            <th>Mobile</th>
            <th>City</th>
            <th>Options</th>
          </thead>
          <tbody>
          </tbody>
        </table>
      </div>
      <div class="col-md-2"></div>
    </div>
  </div>
</div>
<?php include('modal.php'); ?>
</div>

<script src="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.1/dist/js/bootstrap.bundle.min.js" integrity="sha384-gtEjrD/SeCtmISkJkNUaaKMoLD0//ElJ19smozuHV6z3Iehds+3Ulb9Bn9Plx0x4" crossorigin="anonymous"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.11.4/js/jquery.dataTables.min.js"></script>
  <script type="text/javascript">
    $(document).ready(function() {
      $('#example').DataTable({
        "fnCreatedRow": function( nRow, aData, iDataIndex ) {
          $(nRow).attr('id', aData[0]);
        },
        'serverSide':'true',
        'processing':'true',
        'paging':'true',
        'order':[],
        'ajax': {
          'url':'fetch_data.php',
          'type':'post',
        },
        "columnDefs": [{
          'target':[5],
          'orderable' :false,
        }]
      });
    } );
    $(document).on('submit','#addUser',function(e){
      e.preventDefault();
      var city= $('#addCityField').val();
      var username= $('#addUserField').val();
      var mobile= $('#addMobileField').val();
      var email= $('#addEmailField').val();
      if(city != '' && username != '' && mobile != '' && email != '' )
      {
       $.ajax({
         url:"add_user.php",
         type:"post",
         data:{city:city,username:username,mobile:mobile,email:email},
         success:function(data)
         {
           var json = JSON.parse(data);
           var status = json.status;
           if(status=='true')
           {
            mytable =$('#example').DataTable();
            mytable.draw();
            $('#addUserModal').modal('hide');
          }
          else
          {
            alert('failed');
          }
        }
      });
     }
     else {
      alert('Fill all the required fields');
    }
  });
    $(document).on('submit','#updateUser',function(e){
      e.preventDefault();
       var city= $('#cityField').val();
       var username= $('#nameField').val();
       var mobile= $('#mobileField').val();
       var email= $('#emailField').val();
       var trid= $('#trid').val();
       var id= $('#id').val();
       if(city != '' && username != '' && mobile != '' && email != '' )
       {
         $.ajax({
           url:"update_user.php",
           type:"post",
           data:{city:city,username:username,mobile:mobile,email:email,id:id},
           success:function(data)
           {
             var json = JSON.parse(data);
             var status = json.status;
             if(status=='true')
             {
              table =$('#example').DataTable();
              var button =   '<td><a href="javascript:void();" data-id="' +id + '" class="btn btn-info btn-sm editbtn">Edit</a>  <a href="#!"  data-id="' +id + '"  class="btn btn-danger btn-sm deleteBtn">Delete</a></td>';
              var row = table.row("[id='"+trid+"']");
              row.row("[id='" + trid + "']").data([id,username,email,mobile,city,button]);
              $('#exampleModal').modal('hide');
            }
            else
            {
              alert('failed');
            }
          }
        });
       }
       else {
        alert('Fill all the required fields');
      }
    });
    $('#example').on('click','.editbtn ',function(event){
      var table = $('#example').DataTable();
      var trid = $(this).closest('tr').attr('id');
     var id = $(this).data('id');
     $('#exampleModal').modal('show');

     $.ajax({
      url:"get_single_data.php",
      data:{id:id},
      type:'post',
      success:function(data)
      {
       var json = JSON.parse(data);
       $('#nameField').val(json.username);
       $('#emailField').val(json.email);
       $('#mobileField').val(json.mobile);
       $('#cityField').val(json.city);
       $('#id').val(id);
       $('#trid').val(trid);
     }
   })
   });

    $(document).on('click','.deleteBtn',function(event){
       var table = $('#example').DataTable();
      event.preventDefault();
      var id = $(this).data('id');
      if(confirm("Are you sure want to delete this User ? "))
      {
      $.ajax({
        url:"delete_user.php",
        data:{id:id},
        type:"post",
        success:function(data)
        {
          var json = JSON.parse(data);
          status = json.status;
          if(status=='success')
          {
             $("#"+id).closest('tr').remove();
          }
          else
          {
            alert('Failed');
            return;
          }
        }
      });
      }
      else
      {
        return null;
      }
    })
 </script>
</body>
</html>

modal.php

//modal.php
<!-- Add user Modal -->
<div class="modal fade" id="addUserModal" tabindex="-1" aria-labelledby="exampleModalLabel" aria-hidden="true">
  <div class="modal-dialog">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title" id="exampleModalLabel">Add User</h5>
        <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
      </div>
      <div class="modal-body">
        <form id="addUser" action="">
          <div class="mb-3 row">
            <label for="addUserField" class="col-md-3 form-label">Name</label>
            <div class="col-md-9">
              <input type="text" class="form-control" id="addUserField" name="name" >
            </div>
          </div>
          <div class="mb-3 row">
            <label for="addEmailField" class="col-md-3 form-label">Email</label>
            <div class="col-md-9">
              <input type="email" class="form-control" id="addEmailField" name="email">
            </div>
          </div>
          <div class="mb-3 row">
            <label for="addMobileField" class="col-md-3 form-label">Mobile</label>
            <div class="col-md-9">
              <input type="text" class="form-control" id="addMobileField" name="mobile">
            </div>
          </div>
          <div class="mb-3 row">
            <label for="addCityField" class="col-md-3 form-label">City</label>
            <div class="col-md-9">
              <input type="text" class="form-control" id="addCityField" name="City">
            </div>
          </div>
          <div class="text-center">
            <button type="submit" class="btn btn-primary">Submit</button>
          </div>
        </form> 
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
      </div>
    </div>
  </div>
</div>
 <!-- Modal -->
 <div class="modal fade" id="exampleModal" tabindex="-1" aria-labelledby="exampleModalLabel" aria-hidden="true">
  <div class="modal-dialog">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title" id="exampleModalLabel">Update User</h5>
        <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
      </div>
      <div class="modal-body">
        <form id="updateUser" >
          <input type="hidden" name="id" id="id" value="">
          <input type="hidden" name="trid" id="trid" value="">
          <div class="mb-3 row">
            <label for="nameField" class="col-md-3 form-label">Name</label>
            <div class="col-md-9">
              <input type="text" class="form-control" id="nameField" name="name" >
            </div>
          </div>
          <div class="mb-3 row">
            <label for="emailField" class="col-md-3 form-label">Email</label>
            <div class="col-md-9">
              <input type="email" class="form-control" id="emailField" name="email">
            </div>
          </div>
          <div class="mb-3 row">
            <label for="mobileField" class="col-md-3 form-label">Mobile</label>
            <div class="col-md-9">
              <input type="text" class="form-control" id="mobileField" name="mobile">
            </div>
          </div>
          <div class="mb-3 row">
            <label for="cityField" class="col-md-3 form-label">City</label>
            <div class="col-md-9">
              <input type="text" class="form-control" id="cityField" name="City">
            </div>
          </div>
          <div class="text-center">
            <button type="submit" class="btn btn-primary">Submit</button>
          </div>
        </form> 
      </div>
      <div class="modal-footer">
        <button type="button"  class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
      </div>
    </div>
  </div>
</div>

connection.php

//connection.php
<?php
$con  = mysqli_connect('localhost','root','','testingdb');
if(mysqli_connect_errno())
{
    echo 'Database Connection Error';
}

fetch_data.php

//fetch_data.php
<?php include('connection.php');

$output= array();
$sql = "SELECT * FROM users ";

$totalQuery = mysqli_query($con,$sql);
$total_all_rows = mysqli_num_rows($totalQuery);

if(isset($_POST['search']['value']))
{
	$search_value = $_POST['search']['value'];
	$sql .= " WHERE username like '%".$search_value."%'";
	$sql .= " OR email like '%".$search_value."%'";
	$sql .= " OR mobile like '%".$search_value."%'";
	$sql .= " OR city like '%".$search_value."%'";
}

if(isset($_POST['order']))
{
	$column_name = $_POST['order'][0]['column'];
	$order = $_POST['order'][0]['dir'];
	$sql .= " ORDER BY ".$column_name." ".$order."";
}
else
{
	$sql .= " ORDER BY id desc";
}

if($_POST['length'] != -1)
{
	$start = $_POST['start'];
	$length = $_POST['length'];
	$sql .= " LIMIT  ".$start.", ".$length;
}	

$query = mysqli_query($con,$sql);
$count_rows = mysqli_num_rows($query);
$data = array();
while($row = mysqli_fetch_assoc($query))
{
	$sub_array = array();
	$sub_array[] = $row['id'];
	$sub_array[] = $row['username'];
	$sub_array[] = $row['email'];
	$sub_array[] = $row['mobile'];
	$sub_array[] = $row['city'];
	$sub_array[] = '<a href="javascript:void();" data-id="'.$row['id'].'"  class="btn btn-info btn-sm editbtn" >Edit</a>  <a href="javascript:void();" data-id="'.$row['id'].'"  class="btn btn-danger btn-sm deleteBtn" >Delete</a>';
	$data[] = $sub_array;
}

$output = array(
	'draw'=> intval($_POST['draw']),
	'recordsTotal' =>$count_rows ,
	'recordsFiltered'=>   $total_all_rows,
	'data'=>$data,
);
echo  json_encode($output);

add_user.php

//add_user.php
<?php 
include('connection.php');
$username = $_POST['username'];
$email = $_POST['email'];
$mobile = $_POST['mobile'];
$city = $_POST['city'];

$sql = "INSERT INTO `users` (`username`,`email`,`mobile`,`city`) values ('$username', '$email', '$mobile', '$city' )";
$query= mysqli_query($con,$sql);
$lastId = mysqli_insert_id($con);
if($query ==true)
{
    $data = array(
        'status'=>'true',
    );
    echo json_encode($data);
}
else
{
     $data = array(
        'status'=>'false',
    );
    echo json_encode($data);
} 
?>

get_single_data.php

//get_single_data.php
<?php include('connection.php');
$id = $_POST['id'];
$sql = "SELECT * FROM users WHERE id='$id' LIMIT 1";
$query = mysqli_query($con,$sql);
$row = mysqli_fetch_assoc($query);
echo json_encode($row);
?>

update_user.php

//update_user.php
<?php 
include('connection.php');
$username = $_POST['username'];
$email = $_POST['email'];
$mobile = $_POST['mobile'];
$city = $_POST['city'];
$id = $_POST['id'];

$sql = "UPDATE `users` SET  `username`='$username' , `email`= '$email', `mobile`='$mobile',  `city`='$city' WHERE id='$id' ";
$query= mysqli_query($con,$sql);
$lastId = mysqli_insert_id($con);
if($query ==true)
{
   
    $data = array(
        'status'=>'true',
    );

    echo json_encode($data);
}
else
{
     $data = array(
        'status'=>'false',
    );
    echo json_encode($data);
} 
?>

delete_user.php

//delete_user.php
<?php 
include('connection.php');
$user_id = $_POST['id'];
$sql = "DELETE FROM users WHERE id='$user_id'";
$delQuery =mysqli_query($con,$sql);
if($delQuery==true)
{
	 $data = array(
        'status'=>'success',
    );
    echo json_encode($data);
}
else
{
     $data = array(
        'status'=>'failed',
    );
    echo json_encode($data);
} 
?>

Wednesday, January 5, 2022

PHP Mysqli OOP Date Range Filter Ajax DataTables

PHP Mysqli OOP Date Range Filter Ajax DataTables

Bootstrap CDN

https://getbootstrap.com/docs/4.3/getting-started/introduction/

https://jqueryui.com/datepicker/

The datepicker is tied to a standard form input field. Focus on the input (click, or use the tab key) to open an interactive calendar in a small overlay. Choose a date, click elsewhere on the page (blur the input), or hit the Esc key to close.

https://datatables.net/

DataTables is a powerful jQuery plugin for creating table listings and adding interactions to them. It provides searching, sorting and pagination without any configuration.

CREATE TABLE `employee` (

`id` int(11) NOT NULL,

`name` varchar(100) NOT NULL,

`position` varchar(100) NOT NULL,

`office` varchar(100) NOT NULL,

`age` int(11) NOT NULL,

`salary` int(11) NOT NULL,

`created_at` date DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `employee` (`id`, `name`, `position`, `office`, `age`, `salary`, `created_at`) VALUES

(1, 'Tiger Wood', 'Accountant', 'Tokyo', 36, 5689, '2020-01-09'),

(2, 'Mark Oto Ednalan', 'Chief Executive Officer (CEO)', 'London', 56, 5648, '2020-02-15'),

(3, 'Jacob thompson', 'Junior Technical Author', 'San Francisco', 23, 5689, '2020-03-01'),

(4, 'cylde Ednalan', 'Software Engineer', 'Olongapo', 23, 54654, '2020-01-24'),

(5, 'Rhona Davidson', 'Software Engineer', 'San Francisco', 26, 5465, '2020-01-11'),

(6, 'Quinn Flynn', 'Integration Specialist', 'New York', 53, 56465, '2020-02-23'),

(8, 'Tiger Nixon', 'Software Engineer', 'London', 45, 456, '2020-03-04'),

(9, 'Airi Satou updated', 'Pre-Sales Support updated', 'New York', 25, 4568, '2020-04-28'),

(10, 'Angelica Ramos updated', 'Sales Assistant updated', 'New York', 45, 456, '2020-01-12'),

(11, 'Ashton updated', 'Senior Javascript Developer', 'Olongapo', 45, 54565, '2020-02-06'),

(12, 'Bradley Greer', 'Regional Director', 'San Francisco', 27, 5485, '2020-03-21'),

(13, 'Brenden Wagner', 'Javascript Developer', 'San Francisco', 38, 65468, '2020-04-14'),

(14, 'Brielle Williamson', 'Personnel Lead', 'Olongapo', 56, 354685, '2020-01-29'),

(15, 'Bruno Nash', 'Customer Support', 'New York', 36, 65465, '2020-02-22'),

(16, 'cairocoders', 'Sales Assistant', 'Sydney', 45, 56465, '2020-03-10'),

(17, 'Zorita Serrano', 'Support Engineer', 'San Francisco', 38, 6548, '2020-04-26'),

(18, 'Zenaida Frank', 'Chief Operating Officer (COO)', 'San Francisco', 39, 545, '2020-01-17'),

(19, 'Sakura Yamamoto', 'Support Engineer', 'Tokyo', 48, 5468, '2020-02-01'),

(20, 'Serge Baldwin', 'Data Coordinator', 'Singapore', 85, 5646, '2020-03-19'),

(21, 'Shad Decker', 'Regional Director', 'Tokyo', 45, 4545, '2020-04-21');

ALTER TABLE `employee`

ADD PRIMARY KEY (`id`);

ALTER TABLE `employee`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;

index.php

//index.php
<!doctype html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">

	<script src="https://code.jquery.com/jquery-3.6.0.js"></script>
    <link rel="stylesheet" href="https://code.jquery.com/ui/1.13.0/themes/base/jquery-ui.css">
	<script src="https://code.jquery.com/ui/1.13.0/jquery-ui.js"></script>
	
	<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>

    <!-- Datatables -->
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css" />
	<script type="text/javascript"src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>
	
    <title>PHP Mysqli OOP Date Range Filter Ajax Datatables</title>
</head>
<body>
    <div class="container">
        <div class="row">
            <div class="col-md-12 mt-5">
                <h1 class="text-center">PHP Mysqli OOP Date Range Filter Ajax DataTables</h1>
                <hr>
            </div>
        </div>
        <div class="row">
            <div class="col-md-12">
                <div class="row">
                    <div class="col-md-6">
                        <div class="input-group mb-3">
                            <div class="input-group-prepend">
                                <span class="input-group-text bg-info text-white" id="basic-addon1"><i class="fas fa-calendar-alt"></i></span>
                            </div>
                            <input type="text" class="form-control" id="start_date" placeholder="Start Date">
                        </div>
                    </div>
                    <div class="col-md-6">
                        <div class="input-group mb-3">
                            <div class="input-group-prepend">
                                <span class="input-group-text bg-info text-white" id="basic-addon1"><i class="fas fa-calendar-alt"></i></span>
                            </div>
                            <input type="text" class="form-control" id="end_date" placeholder="End Date">
                        </div>
                    </div>
                </div>
                <div>
                    <button id="filter" class="btn btn-outline-info btn-sm">Filter</button>
                    <button id="reset" class="btn btn-outline-warning btn-sm">Reset</button>
                </div>
                <div class="row mt-3">
                    <div class="col-md-12">
                        <div class="table-responsive">
                            <table class="table table-borderless display nowrap" id="records" style="width:100%">
                                <thead>
                                    <tr>
                                        <th>ID</th>
                                        <th>Name</th>
                                        <th>Position</th>
                                        <th>Office</th>
                                        <th>Salary</th>
                                        <th>Date</th>
                                    </tr>
                                </thead>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <!-- Font Awesome -->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.13.0/js/all.min.js"></script>
    <script>
    $(function() {
        $("#start_date").datepicker({
            "dateFormat": "yy-mm-dd"
        });
        $("#end_date").datepicker({
            "dateFormat": "yy-mm-dd"
        });
    });
    // Fetch records
    function fetch(start_date, end_date) {
        $.ajax({
            url: "records.php",
            type: "POST",
            data: {
                start_date: start_date,
                end_date: end_date
            },
            dataType: "json",
            success: function(data) {
                // Datatables
                var i = "1";

                $('#records').DataTable({
                    "data": data,
                    "dom": "<'row'<'col-sm-12 col-md-4'l><'col-sm-12 col-md-4'B><'col-sm-12 col-md-4'f>>" +
                        "<'row'<'col-sm-12'tr>>" +
                        "<'row'<'col-sm-12 col-md-5'i><'col-sm-12 col-md-7'p>>",
                    // responsive
                    "responsive": true,
                    "columns": [{
                            "data": "id"
                        },
                        {
                            "data": "name"
                        },
                        {
                            "data": "position"
                        },
                        {
                            "data": "office"
                        },
                        {
                            "data": "salary"
                        },
                        {
                            "data": "created_at"
                        }
                    ]
                });
            }
        });
    }
    fetch();

    // Filter
    $(document).on("click", "#filter", function(e) {
        e.preventDefault();

        var start_date = $("#start_date").val();
        var end_date = $("#end_date").val();

        if (start_date == "" || end_date == "") {
            alert("both date required");
        } else {
            $('#records').DataTable().destroy();
            fetch(start_date, end_date);
        }
    });

    // Reset
    $(document).on("click", "#reset", function(e) {
        e.preventDefault();

        $("#start_date").val(''); // empty value
        $("#end_date").val('');

        $('#records').DataTable().destroy();
        fetch();
    });
    </script>
</body>
</html>

records.php

//records.php
<?php
include 'model.php';

$model = new Model();

if (isset($_POST['start_date']) && isset($_POST['end_date'])) {
    $start_date = $_POST['start_date'];
    $end_date = $_POST['end_date'];

    $rows = $model->date_range($start_date, $end_date);
} else {
    $rows = $model->fetch();
}
echo json_encode($rows);

model.php

//model.php
<?php
class Model
{
    private $server = "localhost";
    private $username = "root";
    private $password = "";
    private $db = "testingdb";
    private $conn;

    public function __construct()
    {
        try {
            $this->conn = new mysqli($this->server, $this->username, $this->password, $this->db);
        } catch (\Throwable $th) {
            //throw $th;
            echo "Connection error " . $th->getMessage();
        }
    }

    public function fetch()
    {
        $data = [];

        $query = "SELECT * FROM employee";
        if ($sql = $this->conn->query($query)) {
            while ($row = mysqli_fetch_assoc($sql)) {
                $data[] = $row;
            }
        }

        return $data;
    }

    public function date_range($start_date, $end_date)
    {
        $data = [];

        if (isset($start_date) && isset($end_date)) {
            $query = "SELECT * FROM employee WHERE created_at > '$start_date' AND created_at < '$end_date'";
            if ($sql = $this->conn->query($query)) {
                while ($row = mysqli_fetch_assoc($sql)) {
                    $data[] = $row;
                }
            }
        }

        return $data;
    }
}

Friday, December 17, 2021

PHP Mysql PDO CRUD (Create, Read, Update and Delete) Server Side Ajax Boostrap data table and Modal Form

PHP Mysql PDO CRUD (Create, Read, Update and Delete) Server Side Ajax Boostrap data table and Modal Form

Bootstrap Datatable Server-side processing https://datatables.net/examples/server_side/simple.html

Bootstrap Modal https://getbootstrap.com/docs/4.0/components/modal/

Database Table

CREATE TABLE `members` (

`id` int(11) NOT NULL,

`firstname` varchar(30) NOT NULL,

`lastname` varchar(30) NOT NULL,

`address` text NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `members` (`id`, `firstname`, `lastname`, `address`) VALUES

(1, 'Airi ', 'Satou', 'Tokyo'),

(2, 'Angelica ', 'Ramos', 'London'),

(3, 'Ashton ', 'Cox', 'San Francisco'),

(4, 'Bradley ', 'Greer', 'London'),

(5, 'Brenden ', 'Wagner', 'San Francisco'),

(6, 'Brielle', 'Williamson', 'New York'),

(7, 'Bruno', 'Nash', 'London'),

(8, 'Caesar', 'Vance', 'New York'),

(9, 'Cara', 'Stevens', 'New York'),

(10, 'Cedric', 'Kelly', 'Edinburgh'),

(11, 'Charde', 'Marshall', 'San Francisco');

ALTER TABLE `members`

ADD PRIMARY KEY (`id`);

ALTER TABLE `members`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

index.php

//index.php
<!doctype html>
<head>
    <title>PHP Mysql PDO CRUD (Create, Read, Update and Delete) Server Side Ajax Boostrap data table and Modal Form</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>

    <link rel="stylesheet" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.css">
    <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
    <script src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.js"></script>
    
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container"> 
	<div class="row">
		<div class="col-3"></div>
		<div class="col-6">
		<h1>PHP Mysql PDO CRUD (Create, Read, Update and Delete) Server Side Ajax Boostrap data table and Modal Form</h1>                    
        <table id="member_table" class="table table-striped">  
            <thead>
                <tr class="table-primary">
                    <th width="5%">ID</th>
                    <th width="50%">First Name</th>  
                    <th width="50%">Last Name</th>
                    <th width="50%">Address</th>
                    <th scope="col" width="5%">Edit</th>
                    <th scope="col" width="5%">Delete</th>
                </tr>
            </thead>
        </table>
        </br>
        <div align="right">
            <button type="button" id="add_button" data-toggle="modal" data-target="#userModal" class="btn btn-success btn-lg">Add New Member</button>
        </div>
		</div>
		<div class="col-3"></div>
	</div>	
	<div id="userModal" class="modal fade">
		<div class="modal-dialog">
			<form method="post" id="member_form" enctype="multipart/form-data">
				<div class="modal-content">
					<div class="modal-header">
						<button type="button" class="close" data-dismiss="modal">×</button>
						<h4 class="modal-title">Add Member</h4>
					</div>
					<div class="modal-body">
						<label>Enter First Name</label>
						<input type="text" name="firstname" id="firstname" class="form-control" />
						<br />
						<label>Enter Last Name</label>
						<input type="text" name="lastname" id="lastname" class="form-control" />
						<br /> 						
						<label>Enter Address</label>
						<input type="text" name="address" id="address" class="form-control" />
						<br /> 
					</div>
					<div class="modal-footer">
						<input type="hidden" name="member_id" id="member_id" />
						<input type="hidden" name="operation" id="operation" />
						<input type="submit" name="action" id="action" class="btn btn-primary" value="Add" />
						<button type="button" class="btn btn-danger" data-dismiss="modal">Close</button>
					</div>
				</div>
			</form>
		</div>
	</div>
</div> 
<script type="text/javascript" language="javascript" >
$(document).ready(function(){
    $('#add_button').click(function(){
        $('#member_form')[0].reset();
        $('.modal-title').text("Add Member Details");
        $('#action').val("Add");
        $('#operation').val("Add");
    });
    
    var dataTable = $('#member_table').DataTable({
        "paging":true,
        "processing":true,
        "serverSide":true,
        "order": [],
        "info":true,
        "ajax":{
            url:"fetch.php",
            type:"POST"
               },
        "columnDefs":[
            {
                "targets":[0,3,4],
                "orderable":false,
            },
        ],    
    });

    $(document).on('submit', '#member_form', function(event){
        event.preventDefault();
        var id = $('#id').val();
        var firstname = $('#firstname').val();
        var lastname = $('#lastname').val();
        var address = $('#address').val();
        
        if(firstname != '' && lastname != '' && address != '')
        {
            $.ajax({
                url:"insert.php",
                method:'POST',
                data:new FormData(this),
                contentType:false,
                processData:false,
                success:function(data)
                {
                    $('#member_form')[0].reset();
                    $('#userModal').modal('hide');
                    dataTable.ajax.reload();
                }
            });
        }
        else
        {
            alert("Member First Name, Last Name Fields are Required");
        }
    });
    
    $(document).on('click', '.update', function(){
        var member_id = $(this).attr("id"); alert(member_id);
        $.ajax({
            url:"fetch_single.php",
            method:"POST",
            data:{member_id:member_id},
            dataType:"json",
            success:function(data)
            {
                $('#userModal').modal('show');
                $('#id').val(data.id);
                $('#firstname').val(data.firstname);
                $('#lastname').val(data.lastname);
                $('#address').val(data.address);
                $('.modal-title').text("Edit Member Details");
                $('#member_id').val(member_id);
                $('#action').val("Save");
                $('#operation').val("Edit");
            }
        })
    });
    
    $(document).on('click', '.delete', function(){
        var member_id = $(this).attr("id");
        if(confirm("Are you sure you want to delete this member?"))
        {
            $.ajax({
                url:"delete.php",
                method:"POST",
                data:{member_id:member_id},
                success:function(data)
                {
                    dataTable.ajax.reload();
                }
            });
        }
        else
        {
            return false;   
        }
    });
    
    
});
</script>              
</body>
</html>

db.php

//db.php
<?php
$connection = new PDO( 'mysql:host=localhost;dbname=testingdb', 'root', '' );
?>

insert.php

//insert.php
<?php
include('db.php');
include('function.php');
if(isset($_POST["operation"]))
{
	if($_POST["operation"] == "Add")
	{
		$statement = $connection->prepare("
			INSERT INTO members (firstname, lastname, address) VALUES (:firstname, :lastname, :address)");
		$result = $statement->execute(
			array(
				':firstname'	=>	$_POST["firstname"],
				':lastname'	=>	$_POST["lastname"],
				':address'	=>	$_POST["address"]
			)
		);
	}
	if($_POST["operation"] == "Edit")
	{
		$statement = $connection->prepare(
			"UPDATE members
			SET firstname = :firstname, lastname = :lastname, address = :address WHERE id = :id");
		$result = $statement->execute(
			array(
				':firstname'	=>	$_POST["firstname"],
				':lastname'	=>	$_POST["lastname"],
				':address'	=>	$_POST["address"],
				':id'			=>	$_POST["member_id"]
			)
		);
	}
}

?>

fetch.php

//fetch.php
<?php
include('db.php');
include('function.php');
$query = '';
$output = array();
$query .= "SELECT * FROM members ";
if(isset($_POST["search"]["value"]))
{
	$query .= 'WHERE firstname LIKE "%'.$_POST["search"]["value"].'%" ';
	$query .= 'OR lastname LIKE "%'.$_POST["search"]["value"].'%" ';
}

if(isset($_POST["order"]))
{
	$query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}
else
{
	$query .= 'ORDER BY id ASC ';
}

if($_POST["length"] != -1)
{
	$query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
} 
$statement = $connection->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
$data = array();
$filtered_rows = $statement->rowCount();
foreach($result as $row)
{
	$sub_array = array();
	
	$sub_array[] = $row["id"];
	$sub_array[] = $row["firstname"];
	$sub_array[] = $row["lastname"];                                                                              
	$sub_array[] = $row["address"];                                                                              //https://getbootstrap.com/docs/3.3/components/
	$sub_array[] = '<button type="button" name="update" id="'.$row["id"].'" class="btn btn-primary btn-sm update"><i class="glyphicon glyphicon-pencil"> </i>Edit</button></button>';
	$sub_array[] = '<button type="button" name="delete" id="'.$row["id"].'" class="btn btn-danger btn-sm delete"><i class="glyphicon glyphicon-remove">  Delete</button>';
	$data[] = $sub_array;
}
$output = array(
	"draw"				=>	intval($_POST["draw"]),
	"recordsTotal"		=> 	$filtered_rows,
	"recordsFiltered"	=>	get_total_all_records(),
	"data"				=>	$data
);
echo json_encode($output);
?>

function.php

//function.php
<?php
function get_total_all_records()
{
	include('db.php');
	$statement = $connection->prepare("SELECT * FROM members");
	$statement->execute();
	$result = $statement->fetchAll();
	return $statement->rowCount();
}
?>

fetch_single.php

//fetch_single.php
<?php
include('db.php');
include('function.php');
if(isset($_POST["member_id"]))
{
	$output = array();
	$statement = $connection->prepare(
		"SELECT * FROM members WHERE id = '".$_POST["member_id"]."' LIMIT 1"
	);
	$statement->execute();
	$result = $statement->fetchAll();
	foreach($result as $row)
	{
		$output["id"] = $row["id"];
		$output["firstname"] = $row["firstname"];
		$output["lastname"] = $row["lastname"];
		$output["address"] = $row["address"];
	}
	echo json_encode($output);
}
?>

delete.php

//delete.php
<?php
include('db.php');
include('function.php');

if(isset($_POST["member_id"]))
{
	$statement = $connection->prepare(
		"DELETE FROM members WHERE id = :id"
	);
	$result = $statement->execute(

		array(':id'	=>	$_POST["member_id"])
		
	);
}
?>

Thursday, December 9, 2021

PHP MYSQL PDO Todo List with JQuery AJAX

PHP MYSQL PDO Todo List with JQuery AJAX

CREATE TABLE `todos` (

`id` int(11) NOT NULL,

`title` text NOT NULL,

`date_time` datetime NOT NULL DEFAULT current_timestamp(),

`checked` tinyint(1) NOT NULL DEFAULT 0

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `todos`

ADD PRIMARY KEY (`id`);

ALTER TABLE `todos`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

index.php

//index.php
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>PHP MYSQL PDO Todo List with JQuery AJAX</title>
	<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
    <link rel="stylesheet" href="css/style.css">
</head>
<body>
<?php require 'dbconn.php'; ?>
    <div class="container"><h1>PHP MYSQL PDO Todo List with JQuery AJAX</h1>
        <div class="row">
			<div class="col-2"></div>
			<div class="col-9" style="border-radius: 5px;background-color:#fff;padding:30px;">
			 <form action="add.php" method="POST" autocomplete="off">
				 <?php if(isset($_GET['mess']) && $_GET['mess'] == 'error'){ ?>
					<p><input type="text" name="title" class="form-control form-control-lg" placeholder="This field is required" /></p>
					<p><button type="submit" class="btn btn-primary btn-lg">Add   <span>+</span></button></p>

				 <?php }else{ ?>
				  <p><input type="text" name="title" class="form-control form-control-lg" placeholder="What do you need to do?" /></p>
				  <p><button type="submit" class="btn btn-primary btn-lg">Add   <span>+</span></button></p>
				 <?php } ?>
			</form>
			</div>
			<div class="col-2"></div>
       </div>
       <?php 
          $todos = $conn->query("SELECT * FROM todos ORDER BY id DESC");
       ?>
       <div class="row">
			<div class="col-2"></div>
			<div class="col-9" style="border-radius: 5px;background-color:#fff;padding:30px;margin-top:20px;">
            <?php if($todos->rowCount() <= 0){ ?>
                <div class="todo-item">
                    <div class="empty">
                        <h1>No Record </h1>
                    </div>
                </div>
            <?php } ?>

            <?php while($todo = $todos->fetch(PDO::FETCH_ASSOC)) { ?>
                <div class="todo-item">
                    <span id="<?php echo $todo['id']; ?>" class="remove-to-do">x</span>
                    <?php if($todo['checked']){ ?> 
                        <input type="checkbox" class="check-box" data-todo-id ="<?php echo $todo['id']; ?>" checked />
                        <h2 class="checked"><?php echo $todo['title'] ?></h2>
                    <?php }else { ?>
                        <input type="checkbox" data-todo-id ="<?php echo $todo['id']; ?>" class="check-box" />
                        <h2><?php echo $todo['title'] ?></h2>
                    <?php } ?>
                    <br>
                    <small>created: <?php echo $todo['date_time'] ?></small> 
                </div>
            <?php } ?>
			</div>
			<div class="col-2"></div>
       </div>
    </div>

<script src="https://code.jquery.com/jquery-3.6.0.min.js"
  integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4="
  crossorigin="anonymous"></script>
<!-- https://code.jquery.com/ -->
<script>
        $(document).ready(function(){
            $('.remove-to-do').click(function(){
                const id = $(this).attr('id');
                
                $.post("remove.php", 
                      {
                          id: id
                      },
                      (data)  => {
                         if(data){
                             $(this).parent().hide(600);
                         }
                      }
                );
            });

            $(".check-box").click(function(e){
                const id = $(this).attr('data-todo-id');
                
                $.post('check.php', 
                      {
                          id: id
                      },
                      (data) => {
                          if(data != 'error'){
                              const h2 = $(this).next();
                              if(data === '1'){
                                  h2.removeClass('checked');
                              }else {
                                  h2.addClass('checked');
                              }
                          }
                      }
                );
            });
        });
</script>
</body>
</html>

dbconn.php

//dbconn.php
<?php 
$sName = "localhost";
$uName = "root";
$pass = "";
$db_name = "testingdb";

try {
    $conn = new PDO("mysql:host=$sName;dbname=$db_name", 
                    $uName, $pass);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
  echo "Connection failed : ". $e->getMessage();
}

add.php

//add.php
<?php
if(isset($_POST['title'])){
    require 'dbconn.php';

    $title = $_POST['title'];

    if(empty($title)){
        header("Location: index.php?mess=error");
    }else {
        $stmt = $conn->prepare("INSERT INTO todos(title) VALUE(?)");
        $res = $stmt->execute([$title]);

        if($res){
            header("Location: index.php?mess=success"); 
        }else {
            header("Location: index.php");
        }
        $conn = null;
        exit();
    }
}else {
    header("Location: index.php?mess=error");
}

check.php

//check.php
<?php
if(isset($_POST['id'])){
    require 'dbconn.php';

    $id = $_POST['id'];

    if(empty($id)){
       echo 'error';
    }else {
        $todos = $conn->prepare("SELECT id, checked FROM todos WHERE id=?");
        $todos->execute([$id]);

        $todo = $todos->fetch();
        $uId = $todo['id'];
        $checked = $todo['checked'];

        $uChecked = $checked ? 0 : 1;

        $res = $conn->query("UPDATE todos SET checked=$uChecked WHERE id=$uId");

        if($res){
            echo $checked;
        }else {
            echo "error";
        }
        $conn = null;
        exit();
    }
}else {
    header("Location: index.php?mess=error");
}

remove.php

//remove.php
<?php
if(isset($_POST['id'])){
    require 'dbconn.php';

    $id = $_POST['id'];

    if(empty($id)){
       echo 0;
    }else {
        $stmt = $conn->prepare("DELETE FROM todos WHERE id=?");
        $res = $stmt->execute([$id]);

        if($res){
            echo 1;
        }else {
            echo 0;
        }
        $conn = null;
        exit();
    }
}else {
    header("Location: index.php?mess=error");
}

css/style.css

//css/style.css
body {
    background: #6d5ce8;
  }
  
  * {
    padding: 0px;
    margin: 0px;
    box-sizing: border-box;
  }
  
  .todo-item {
    width: 95%;
    margin: 10px auto;
    padding: 20px 10px;
    box-shadow: 0 4px 8px 0 #ccc, 0 6px 20px 0 #ccc;
    border-radius: 5px;
  }
  
  .todo-item h2 {
    display: inline-block;
    padding: 5px 0px;
    font-size: 17px;
    font-family: sans-serif;
    color: #555;
  }
  
  .todo-item small {
    display: block;
    width: 100%;
    padding: 5px 0px;
    color: #888;
    padding-left: 30px;
    font-size: 14px;
    font-family: sans-serif; 
  }
  
  .remove-to-do {
    display: block;
    float: right;font-size:20px;
    width: 35px;
    height: 35px;
    color: rgb(139, 97, 93);
    text-decoration: none;
    padding: 0px 5px 8px 10px;
    border-radius: 50%;
    transition: background 1s;
    cursor: pointer;
  }
  
  .remove-to-do:hover {
    background: rgb(139, 97, 93);
    color: #fff;
  }
  
  .checked {
    color: #999 !important;
    text-decoration: line-through;
  }
  
  .todo-item input {
    margin: 0px 5px;
  }
  
  .empty {
    font-family: sans-serif;
    font-size: 16px;
    text-align: center;
    color: #cccc;
  }

PHP Mysql PDO CRUD(Create, Read, Update and Delete) Search and Pagination with jQuery Ajax Bootstrap

PHP Mysql PDO CRUD(Create, Read, Update and Delete) Search and Pagination with jQuery Ajax Bootstrap

PHP Class Mysql PDO CRUD(Create, Read, Update and Delete) with Search Functionality and Ajax Pagination

Bootstrap https://getbootstrap.com/docs/4.5/getting-started/introduction/

https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css

Modal

https://getbootstrap.com/docs/4.5/components/modal/

https://getbootstrap.com/docs/4.5/components/spinners/#size

CREATE TABLE `employee` (

`id` int(11) NOT NULL,

`name` varchar(100) NOT NULL,

`position` varchar(100) NOT NULL,

`office` varchar(100) NOT NULL,

`age` int(11) NOT NULL,

`salary` int(11) NOT NULL,

`photo` varchar(255) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `employee` (`id`, `name`, `position`, `office`, `age`, `salary`, `photo`) VALUES

(1, 'Tiger Wood', 'Accountant', 'Tokyo', 36, 5689, '15739efb98977a7540932d5b49dbf930.jpg'),

(2, 'Mark Oto Ednalan', 'Chief Executive Officer (CEO)', 'London', 56, 5648, '1.jpg'),

(3, 'Jacob thompson', 'Junior Technical Author', 'San Francisco', 23, 5689, '1.jpg'),

(4, 'cylde Ednalan', 'Software Engineer', 'Olongapo', 23, 54654, '1.jpg'),

(5, 'Rhona Davidson', 'Software Engineer', 'San Francisco', 26, 5465, '1.jpg'),

(6, 'Quinn Flynn', 'Integration Specialist', 'New York', 53, 56465, '1.jpg'),

(8, 'Tiger Nixon', 'Software Engineer', 'London', 45, 456, '1.jpg'),

(9, 'Airi Satou updated', 'Pre-Sales Support updated', 'New York', 25, 4568, '1.jpg'),

(10, 'Angelica Ramos updated', 'Sales Assistant updated', 'New York', 45, 456, '1.jpg'),

(11, 'Ashton updated', 'Senior Javascript Developer', 'Olongapo', 45, 54565, '1.jpg'),

(12, 'Bradley Greer', 'Regional Director', 'San Francisco', 27, 5485, '5.jpg'),

(13, 'Brenden Wagner', 'Javascript Developer', 'San Francisco', 38, 65468, '4.jpg'),

(14, 'Brielle Williamson', 'Personnel Lead', 'Olongapo', 56, 354685, '3.jpg'),

(15, 'Bruno Nash', 'Customer Support', 'New York', 36, 65465, '2.jpg'),

(16, 'cairocoders', 'Sales Assistant', 'Sydney', 45, 56465, '1.jpg'),

(17, 'Zorita Serrano', 'Support Engineer', 'San Francisco', 38, 6548, '5.jpg'),

(18, 'Zenaida Frank', 'Chief Operating Officer (COO)', 'San Francisco', 39, 545, '4.jpg'),

(19, 'Sakura Yamamoto', 'Support Engineer', 'Tokyo', 48, 5468, '3.jpg'),

(20, 'Serge Baldwin', 'Data Coordinator', 'Singapore', 85, 5646, '2.jpg'),

(21, 'Shad Decker', 'Regional Director', 'Tokyo', 45, 4545, '1.jpg');

ALTER TABLE `employee`

ADD PRIMARY KEY (`id`);

ALTER TABLE `employee`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;

index.php

//index.php
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>PHP Mysql PDO CRUD(Create, Read, Update and Delete) Search and Pagination with jQuery Ajax Bootstrap</title>
  <link rel="stylesheet" href="css/style.css">
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css" integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2" crossorigin="anonymous">
</head>
<body>
<div class="container">
    <div class="row text-center"> 
      <h2>PHP Mysql PDO CRUD(Create, Read, Update and Delete) Search and Pagination with jQuery Ajax Bootstrap</h2>
    </div>
    <div class="alert alert-success text-center message" role="alert"></div>
    
	<div class="row mb-3">
      <div class="col-3">
        <button type="button" class="btn btn-primary" data-toggle="modal" data-target="#employeeModal" id="addnewbtn">Add New </button>
      </div>
      <div class="col-9">
        <div class="input-group input-group-lg">
          <div class="input-group-prepend">
            <span class="input-group-text" id="basic-addon2"><svg xmlns="http://www.w3.org/2000/svg" width="16" height="16" fill="currentColor" class="bi bi-search" viewBox="0 0 16 16">
  <path d="M11.742 10.344a6.5 6.5 0 1 0-1.397 1.398h-.001c.03.04.062.078.098.115l3.85 3.85a1 1 0 0 0 1.415-1.414l-3.85-3.85a1.007 1.007 0 0 0-.115-.1zM12 6.5a5.5 5.5 0 1 1-11 0 5.5 5.5 0 0 1 11 0z"/>
</svg></span>
          </div>
          <input type="text" class="form-control" aria-label="Sizing example input" aria-describedby="inputGroup-sizing-lg" placeholder="Search..." id="searchinput">

        </div>
      </div>
    </div>
	
	<table class="table" id="employeetable">
	  <thead>
		<tr>
		  <th scope="col"></th>
		  <th scope="col">Name</th>
		  <th scope="col">Position</th>
		  <th scope="col">Office</th>
		  <th scope="col">Age</th>
		  <th scope="col">Salary</th>
		  <th scope="col"></th>
		</tr>
	  </thead>
	  <tbody>
	  </tbody>
	</table>

    <nav id="pagination"></nav>
    <input type="hidden" name="currentpage" id="currentpage" value="1">
<?php
//modal
include_once 'formModal.php';
include_once 'viewModal.php';
?>
</div>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
<script src="js/script.js"></script>
<div id="overlay" style="display:none;">
    <div class="spinner-border text-danger" style="width: 3rem; height: 3rem;"></div>
    <br />
    Loading...
</div> 
</body>
</html>

formModal.php

//formModal.php
<!-- add/edit form modal -->
<div class="modal fade" id="employeeModal" tabindex="-1" role="dialog" aria-labelledby="employeeModalLabel" aria-hidden="true">
  <div class="modal-dialog">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title" id="exampleModalLabel">Add/Edit Employee</h5>
        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">×</span>
        </button>
      </div>
      <form id="addform" method="POST" enctype="multipart/form-data">
        <div class="modal-body">
          <div class="form-group">
            <label for="recipient-name" class="col-form-label">Name:</label>
            <div class="input-group mb-3">
              <input type="text" class="form-control" id="name" name="name" required="required">
            </div>
          </div>
          <div class="form-group">
            <label for="message-text" class="col-form-label">Position:</label>
            <div class="input-group mb-3">
              <input type="text" class="form-control" id="position" name="position" required="required">
            </div>
          </div>
          <div class="form-group">
            <label for="message-text" class="col-form-label">Office:</label>
            <div class="input-group mb-3">
              <input type="text" class="form-control" id="office" name="office" required="required">
            </div>
          </div>
          <div class="form-group">
            <label for="message-text" class="col-form-label">Photo:</label>
            <div class="input-group mb-3">
              <div class="custom-file">
                <input type="file" class="custom-file-input" name="photo" id="userphoto">
                <label class="custom-file-label" for="userphoto">Choose file</label>
              </div>
            </div>
          </div>

        </div>
        <div class="modal-footer">
          <button type="button" class="btn btn-danger" data-dismiss="modal">Close</button>
          <button type="submit" class="btn btn-success" id="addButton">Submit</button>
          <input type="hidden" name="action" value="addnew">
          <input type="hidden" name="employeeid" id="employeeid" value="">
        </div>
      </form>
    </div>
  </div>
</div>
<!-- add/edit form modal end -->

viewModal.php

//viewModal.php
<!-- profile modal start -->
<div class="modal fade" id="empViewModal" tabindex="-1" role="dialog" aria-labelledby="empViewModalLabel"
  aria-hidden="true">
  <div class="modal-dialog">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title" id="exampleModalLabel">Profile <i class="fa fa-user-circle-o"
            aria-hidden="true"></i></h5>
        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">×</span>
        </button>
      </div>
      <div class="modal-body">
        <div class="container" id="profile"></div>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
      </div>
      </form>
    </div>
  </div>
</div>
<!-- profile modal end -->

js/script.js

//js/script.js
// get pagination
function pagination(totalpages, currentpage) {
  var pagelist = "";
  if (totalpages > 1) {
    currentpage = parseInt(currentpage);
    pagelist += `<ul class="pagination justify-content-center">`;
    const prevClass = currentpage == 1 ? " disabled" : "";
    pagelist += `<li class="page-item${prevClass}"><a class="page-link" href="#" data-page="${
      currentpage - 1
    }">Previous</a></li>`;
    for (let p = 1; p <= totalpages; p++) {
      const activeClass = currentpage == p ? " active" : "";
      pagelist += `<li class="page-item${activeClass}"><a class="page-link" href="#" data-page="${p}">${p}</a></li>`;
    }
    const nextClass = currentpage == totalpages ? " disabled" : "";
    pagelist += `<li class="page-item${nextClass}"><a class="page-link" href="#" data-page="${
      currentpage + 1
    }">Next</a></li>`;
    pagelist += `</ul>`;
  }

  $("#pagination").html(pagelist);
}

// get employee row
function getemployeerow(employee) {
  var employeeRow = "";
  if (employee) {
    const userphoto = employee.photo ? employee.photo : "default.png";
    employeeRow = `<tr>
          <td class="align-middle"><img src="uploads/${userphoto}" height="50" class="img-thumbnail rounded float-left"></td>
          <td class="align-middle">${employee.name}</td>
          <td class="align-middle">${employee.position}</td>
          <td class="align-middle">${employee.office}</td>
          <td class="align-middle">${employee.age}</td>
          <td class="align-middle">${employee.salary}</td>
          <td class="align-middle">
            <a href="#" class="btn btn-success mr-3 profile" data-toggle="modal" data-target="#empViewModal"
              title="Prfile" data-id="${employee.id}">View</a>
            <a href="#" class="btn btn-warning mr-3 editemployee" data-toggle="modal" data-target="#employeeModal"
              title="Edit" data-id="${employee.id}">Edit</a>
            <a href="#" class="btn btn-danger deletetemployee" data-id="${employee.id}" title="Delete" data-id="${employee.id}">Delete</a>
          </td>
        </tr>`;
  }
  return employeeRow;
}

// get employees list
function listemployee() {
  var pageno = $("#currentpage").val();
  $.ajax({
    url: "/devtest/phpcrudajax/ajax.php",
    type: "GET",
    dataType: "json",
    data: { page: pageno, action: "getusers" },
    beforeSend: function () {
      $("#overlay").fadeIn();
    },
    success: function (rows) {
      console.log(rows);  
      if (rows.jsonemplyee) { 
        var employeeslist = "";
        $.each(rows.jsonemplyee, function (index, employee) {
          employeeslist += getemployeerow(employee);
        });
        $("#employeetable tbody").html(employeeslist);
        let totalemployees = rows.count;
        let totalpages = Math.ceil(parseInt(totalemployees) / 4);
        const currentpage = $("#currentpage").val();
        pagination(totalpages, currentpage);
        $("#overlay").fadeOut();
      }
    },
    error: function () {
      console.log("something went wrong");
    },
  });
}


$(document).ready(function () {
	
  // pagination
  $(document).on("click", "ul.pagination li a", function (e) {
    e.preventDefault();
    var $this = $(this);
    const pagenum = $this.data("page");
    $("#currentpage").val(pagenum);
    listemployee();
    $this.parent().siblings().removeClass("active");
    $this.parent().addClass("active");
  });
  // form reset on new button
  $("#addnewbtn").on("click", function () {
    $("#addform")[0].reset();
    $("#employeeid").val("");
  });
  
    // searching
  $("#searchinput").on("keyup", function () {
    const searchText = $(this).val();
    if (searchText.length > 1) {
      $.ajax({
        url: "/devtest/phpcrudajax/ajax.php",
        type: "GET",
        dataType: "json",
        data: { searchQuery: searchText, action: "search" },
        success: function (employees) {
          if (employees) {
            var employeeslist = "";
            $.each(employees, function (index, employee) {
              employeeslist += getemployeerow(employee);
            });
            $("#employeetable tbody").html(employeeslist);
            $("#pagination").hide();
          }
        },
        error: function () {
          console.log("something went wrong");
        },
      });
    } else {
      listemployee();
      $("#pagination").show();
    }
  });
  
  // add/edit user
  $(document).on("submit", "#addform", function (event) {
    event.preventDefault();
    var alertmsg =
      $("#employeeid").val().length > 0
        ? "Employee has been updated Successfully!"
        : "New employee has been added Successfully!";
    $.ajax({
      url: "/devtest/phpcrudajax/ajax.php",
      type: "POST",
      dataType: "json",
      data: new FormData(this),
      processData: false,
      contentType: false,
      beforeSend: function () {
        $("#overlay").fadeIn();
      },
      success: function (response) {
        console.log(response);
        if (response) {
          $("#employeeModal").modal("hide");
          $("#addform")[0].reset();
          $(".message").html(alertmsg).fadeIn().delay(3000).fadeOut();
          listemployee();
          $("#overlay").fadeOut();
        }
      },
      error: function () {
        console.log("Oops! Something went wrong!");
      },
    });
  });
  
  //get user
  $(document).on("click", "a.editemployee", function () {
    var empid = $(this).data("id");

    $.ajax({
      url: "/devtest/phpcrudajax/ajax.php",
      type: "GET",
      dataType: "json",
      data: { id: empid, action: "getuser" },
      beforeSend: function () {
        $("#overlay").fadeIn();
      },
      success: function (employee) {
        if (employee) {
          $("#name").val(employee.name);
          $("#position").val(employee.position);
          $("#office").val(employee.office);
          $("#employeeid").val(employee.id);
        }
        $("#overlay").fadeOut();
      },
      error: function () {
        console.log("something went wrong");
      },
    });
  });
  
  // get profile
  $(document).on("click", "a.profile", function () {
    var pid = $(this).data("id");
    $.ajax({
      url: "/devtest/phpcrudajax/ajax.php",
      type: "GET",
      dataType: "json",
      data: { id: pid, action: "getuser" },
      success: function (employee) {
        if (employee) {
          const userphoto = employee.photo ? employee.photo : "default.png";
          const profile = `<div class="row">
                <div class="col-sm-6 col-md-4">
                  <img src="uploads/${userphoto}" class="rounded responsive" />
                </div>
                <div class="col-sm-6 col-md-8">
                  <h4 class="text-primary">${employee.name}</h4>
                  <p class="text-secondary">
                    Position : ${employee.position}
                    <br />
                    Office : ${employee.office}
                  </p>
                </div>
              </div>`;
          $("#profile").html(profile);
        }
      },
      error: function () {
        console.log("something went wrong");
      },
    });
  });
  
  // delete user
  $(document).on("click", "a.deletetemployee", function (e) {
    e.preventDefault();
    var pid = $(this).data("id");
    if (confirm("Are you sure want to delete this?")) {
      $.ajax({
        url: "/devtest/phpcrudajax/ajax.php",
        type: "GET",
        dataType: "json",
        data: { id: pid, action: "deletetemployee" },
        beforeSend: function () {
          $("#overlay").fadeIn();
        },
        success: function (res) {
          if (res.deleted == 1) {
            $(".message")
              .html("employee has been deleted successfully!")
              .fadeIn()
              .delay(3000)
              .fadeOut();
            listemployee();
            $("#overlay").fadeOut();
          }
        },
        error: function () {
          console.log("something went wrong");
        },
      });
    }
  });
  
  // load employees
  listemployee();
});

ajax.php

//ajax.php
<?php
$action = $_REQUEST['action'];

if (!empty($action)) {
    require_once 'Employee.php';
    $obj = new Employee();
}

if ($action == 'addnew' && !empty($_POST)) {
    $name = $_POST['name'];
    $position = $_POST['position'];
    $office = $_POST['office'];
    $photo = $_FILES['photo'];
    $employeeID = (!empty($_POST['employeeid'])) ? $_POST['employeeid'] : '';

    // file (photo) upload
    $imagename = '';
    if (!empty($photo['name'])) {
        $imagename = $obj->uploadPhoto($photo);
        $employeeData = [
            'name' => $name,
            'position' => $position,
            'office' => $office,
            'photo' => $imagename,
        ];
    } else {
        $employeeData = [
            'name' => $name,
            'position' => $position,
            'office' => $office,
        ];
    }

    if ($employeeID) {
        $obj->update($employeeData, $employeeID);
    } else {
        $employeeID = $obj->add($employeeData);
    }

    if (!empty($employeeID)) {
        $employeejson = $obj->getRow('id', $employeeID);
        echo json_encode($employeejson);
        exit();
    }
}

if ($action == "getusers") {
    $page = (!empty($_GET['page'])) ? $_GET['page'] : 1;
    $limit = 4;
    $start = ($page - 1) * $limit;

    $employee = $obj->getRows($start, $limit);
    if (!empty($employee)) {
        $employeelist = $employee;
    } else {
        $employeelist = [];
    }
    $total = $obj->getCount();
    $empjson = ['count' => $total, 'jsonemplyee' => $employeelist];
    echo json_encode($empjson);
    exit();
}

if ($action == "getuser") {
    $employeeID = (!empty($_GET['id'])) ? $_GET['id'] : '';
    if (!empty($employeeID)) {
        $employeejsongetuser = $obj->getRow('id', $employeeID);
        echo json_encode($employeejsongetuser);
        exit();
    }
}

if ($action == "deletetemployee") {
    $employeeID = (!empty($_GET['id'])) ? $_GET['id'] : '';
    if (!empty($employeeID)) {
        $isDeleted = $obj->deleteRow($employeeID);
        if ($isDeleted) {
            $message = ['deleted' => 1];
        } else {
            $message = ['deleted' => 0];
        }
        echo json_encode($message);
        exit();
    }
}

if ($action == 'search') {
    $queryString = (!empty($_GET['searchQuery'])) ? trim($_GET['searchQuery']) : '';
    $results = $obj->searchEmployee($queryString);
    echo json_encode($results);
    exit();
}

Db.php

//Db.php
<?php
class Database
{
    private $dbServer = 'localhost';
    private $dbUser = 'root';
    private $dbPassword = '';
    private $dbName = 'testingdb';
    protected $conn;

    public function __construct()
    {
        try {
            $dsn = "mysql:host={$this->dbServer}; dbname={$this->dbName}; charset=utf8";
            $options = array(PDO::ATTR_PERSISTENT);
            $this->conn = new PDO($dsn, $this->dbUser, $this->dbPassword, $options);
        } catch (PDOException $e) {
            echo "Connection Error: " . $e->getMessage();
        }

    }
}

Employee.php

//Employee.php
<?php
require_once 'Db.php';

class Employee extends Database
{
    // table name
    protected $tableName = 'employee';

    //function is used to add record
    public function add($data)
    {
        if (!empty($data)) {
            $fileds = $placholders = [];
            foreach ($data as $field => $value) {
                $fileds[] = $field;
                $placholders[] = ":{$field}";
            }
        }

        $sql = "INSERT INTO {$this->tableName} (" . implode(',', $fileds) . ") VALUES (" . implode(',', $placholders) . ")";
        $stmt = $this->conn->prepare($sql);
        try {
            $this->conn->beginTransaction();
            $stmt->execute($data);
            $lastInsertedId = $this->conn->lastInsertId();
            $this->conn->commit();
            return $lastInsertedId;
        } catch (PDOException $e) {
            echo "Error: " . $e->getMessage();
            $this->conn->rollback();
        }

    }

	//function update record
    public function update($data, $id)
    {
        if (!empty($data)) {
            $fileds = '';
            $x = 1;
            $filedsCount = count($data);
            foreach ($data as $field => $value) {
                $fileds .= "{$field}=:{$field}";
                if ($x < $filedsCount) {
                    $fileds .= ", ";
                }
                $x++;
            }
        }
        $sql = "UPDATE {$this->tableName} SET {$fileds} WHERE id=:id";
        $stmt = $this->conn->prepare($sql);
        try {
            $this->conn->beginTransaction();
            $data['id'] = $id;
            $stmt->execute($data);
            $this->conn->commit();
        } catch (PDOException $e) {
            echo "Error: " . $e->getMessage();
            $this->conn->rollback();
        }

    }

    //function to get records

    public function getRows($start = 0, $limit = 4)
    {
        $sql = "SELECT * FROM {$this->tableName} ORDER BY id DESC LIMIT {$start},{$limit}";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        if ($stmt->rowCount() > 0) {
            $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        } else {

            $results = [];
        }
        return $results;
    }

    // delete row using id
    public function deleteRow($id)
    {
        $sql = "DELETE FROM {$this->tableName}  WHERE id=:id";
        $stmt = $this->conn->prepare($sql);
        try {
            $stmt->execute([':id' => $id]);
            if ($stmt->rowCount() > 0) {
                return true;
            }
        } catch (PDOException $e) {
            echo "Error: " . $e->getMessage();
            return false;
        }

    }

    public function getCount()
    {
        $sql = "SELECT count(*) as pcount FROM {$this->tableName}";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result['pcount'];
    }
	
    //function to get single record based on the column value
    public function getRow($field, $value)
    {

        $sql = "SELECT * FROM {$this->tableName}  WHERE {$field}=:{$field}";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute([":{$field}" => $value]);
        if ($stmt->rowCount() > 0) {
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
        } else {
            $result = [];
        }

        return $result;
    }

	//function for search table fields name
    public function searchEmployee($searchText, $start = 0, $limit = 4)
    {
        $sql = "SELECT * FROM {$this->tableName} WHERE name LIKE :search ORDER BY id DESC LIMIT {$start},{$limit}";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute([':search' => "{$searchText}%"]);
        if ($stmt->rowCount() > 0) {
            $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        } else {
            $results = [];
        }

        return $results;
    }
	
    //funciton to upload file
    public function uploadPhoto($file)
    {
        if (!empty($file)) {
            $fileTempPath = $file['tmp_name'];
            $fileName = $file['name'];
            $fileSize = $file['size'];
            $fileType = $file['type'];
            $fileNameCmps = explode('.', $fileName);
            $fileExtension = strtolower(end($fileNameCmps));
            $newFileName = md5(time() . $fileName) . '.' . $fileExtension;
            $allowedExtn = ["jpg", "png", "gif", "jpeg"];
            if (in_array($fileExtension, $allowedExtn)) {
                $uploadFileDir = getcwd() . '/uploads/';
                $destFilePath = $uploadFileDir . $newFileName;
                if (move_uploaded_file($fileTempPath, $destFilePath)) {
                    return $newFileName;
                }
            }

        }
    }
} //End Class Employee

css/style.css

//css/style.css
.container {
  padding-top: 10px;
}
.rounded {
  width: 100px;
}
.img-thumbnail {
  width: 80px !important;
}
.message {
  display: none;
}
#overlay {
  background: #ffffff;
  color: #666666;
  position: fixed;
  height: 100%;
  width: 100%;
  z-index: 5000;
  top: 0;
  left: 0;
  float: left;
  text-align: center;
  padding-top: 25%;
  opacity: 0.8;
}

Thursday, November 25, 2021

PHP Mysql PDO CRUD (Create, Read, Update and Delete ) with jquery Ajax and Bootstrap4 Modal

PHP Mysql PDO CRUD (Create, Read, Update and Delete ) with jquery Ajax and Bootstrap4 Modal

Bootstrap 4 CDN

https://getbootstrap.com/docs/4.3/getting-started/introduction/

https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css

https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js

Modal

https://getbootstrap.com/docs/4.3/components/modal/

jquery

https://releases.jquery.com/jquery/

CREATE TABLE `members` (

`id` int(11) NOT NULL,

`firstname` varchar(30) NOT NULL,

`lastname` varchar(30) NOT NULL,

`address` text NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `members` (`id`, `firstname`, `lastname`, `address`) VALUES

(1, 'Airi ', 'Satou', 'Tokyo'),

(2, 'Angelica ', 'Ramos', 'London'),

(3, 'Ashton ', 'Cox', 'San Francisco'),

(4, 'Bradley ', 'Greer', 'London'),

(5, 'Brenden ', 'Wagner', 'San Francisco'),

(6, 'Brielle ', 'Williamson', 'New York');

ALTER TABLE `members`

ADD PRIMARY KEY (`id`);

ALTER TABLE `members`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

index.php

//index.php
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>PHP Mysql PDO CRUD (Create, Read, Update and Delete ) with jquery Ajax and Bootstrap4 Modal</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
</head>
<body>
<div class="container">
	<h1 class="page-header text-center">PHP Mysql PDO CRUD (Create, Read, Update and Delete ) with jquery Ajax and Bootstrap4 Modal</h1>
	<div class="row">
		<div class="col-12">
			<button id="addnew" class="btn btn-primary"><span class="glyphicon glyphicon-plus"></span> Add New Member</button>
            <div id="alert" class="alert alert-info text-center" style="margin-top:20px; display:none;">
            	<button class="close"><span aria-hidden="true">×</span></button>
                <span id="alert_message"></span>
            </div>  
			<table class="table table-bordered table-striped" style="margin-top:20px;">
				<thead>
					<th>ID</th>
					<th>Firstname</th>
					<th>Lastname</th>
					<th>Address</th>
					<th width="150">Action</th>
				</thead>
				<tbody id="tbody"></tbody>
			</table>
		</div>
	</div>
</div>
<!-- Modals -->
<?php include('modal.html'); ?>
<script src="https://code.jquery.com/jquery-3.5.1.js" integrity="sha256-QWo7LDvxbWT2tbbQ97B53yJnYU3WhH/C8ycbRAkjPDc=" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
<script src="script.js"></script>
</body>
</html>

modal.html

//modal.html
<!-- Add New -->
<div class="modal fade" id="add" tabindex="-1" role="dialog">
    <div class="modal-dialog" role="document">
        <div class="modal-content">
			<div class="modal-header">
				<h5 class="modal-title">Add New</h5>
				<button type="button" class="close" data-dismiss="modal" aria-label="Close">
				  <span aria-hidden="true">×</span>
				</button>
			</div>
            <div class="modal-body">
			<form id="addForm">
				<div class="row form-group">
					<div class="col-sm-2">
						<label class="control-label" style="position:relative; top:7px;">Firstname:</label>
					</div>
					<div class="col-sm-10">
						<input type="text" class="form-control" name="firstname">
					</div>
				</div>
				<div class="row form-group">
					<div class="col-sm-2">
						<label class="control-label" style="position:relative; top:7px;">Lastname:</label>
					</div>
					<div class="col-sm-10">
						<input type="text" class="form-control" name="lastname">
					</div>
				</div>
				<div class="row form-group">
					<div class="col-sm-2">
						<label class="control-label" style="position:relative; top:7px;">Address:</label>
					</div>
					<div class="col-sm-10">
						<input type="text" class="form-control" name="address">
					</div>
				</div>
			</div>
            <div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Cancel</button>
                <button type="submit" class="btn btn-primary"><span class="glyphicon glyphicon-floppy-disk"></span> Save</a>
			</form>
            </div>

        </div>
    </div>
</div>

<!-- Edit -->
<div class="modal fade" id="edit" tabindex="-1" role="dialog">
    <div class="modal-dialog">
        <div class="modal-content">
		    <div class="modal-header">
				<h5 class="modal-title">Edit Member</h5>
				<button type="button" class="close" data-dismiss="modal" aria-label="Close">
				  <span aria-hidden="true">×</span>
				</button>
			</div>
            <div class="modal-body">
			<form id="editForm">
				<input type="hidden" class="id" name="id">
				<div class="row form-group">
					<div class="col-sm-2">
						<label class="control-label" style="position:relative; top:7px;">Firstname:</label>
					</div>
					<div class="col-sm-10">
						<input type="text" class="form-control firstname" name="firstname">
					</div>
				</div>
				<div class="row form-group">
					<div class="col-sm-2">
						<label class="control-label" style="position:relative; top:7px;">Lastname:</label>
					</div>
					<div class="col-sm-10">
						<input type="text" class="form-control lastname" name="lastname">
					</div>
				</div>
				<div class="row form-group">
					<div class="col-sm-2">
						<label class="control-label" style="position:relative; top:7px;">Address:</label>
					</div>
					<div class="col-sm-10">
						<input type="text" class="form-control address" name="address">
					</div>
				</div>
			</div>
            <div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Cancel</button>
                <button type="submit" class="btn btn-success"><span class="glyphicon glyphicon-check"></span> Update</a>
			</form>
            </div>

        </div>
    </div>
</div>

<!-- Delete -->
<div class="modal fade" id="delete" tabindex="-1" role="dialog">
    <div class="modal-dialog">
        <div class="modal-content">
		    <div class="modal-header">
				<h5 class="modal-title">Delete Member</h5>
				<button type="button" class="close" data-dismiss="modal" aria-label="Close">
				  <span aria-hidden="true">×</span>
				</button>
			</div>
            <div class="modal-body">	
            	<p class="text-center">Are you sure you want to Delete</p>
				<h2 class="text-center fullname"></h2>
			</div>
            <div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Cancel</button>
                <button type="button" class="btn btn-danger id"><span class="glyphicon glyphicon-trash"></span> Yes</button>
            </div>

        </div>
    </div>
</div>

script.js

//script.js
$(document).ready(function(){
	fetch();
	//add
	$('#addnew').click(function(){
		$('#add').modal('show');
	});
	$('#addForm').submit(function(e){
		e.preventDefault();
		var addform = $(this).serialize();
		//console.log(addform);
		$.ajax({
			method: 'POST',
			url: 'add.php',
			data: addform,
			dataType: 'json',
			success: function(response){
				$('#add').modal('hide');
				if(response.error){
					$('#alert').show();
					$('#alert_message').html(response.message);
				}
				else{
					$('#alert').show();
					$('#alert_message').html(response.message);
					fetch();
				}
			}
		});
	});
	//

	//edit
	$(document).on('click', '.edit', function(){
		var id = $(this).data('id');
		getDetails(id);
		$('#edit').modal('show');
	});
	$('#editForm').submit(function(e){
		e.preventDefault();
		var editform = $(this).serialize();
		$.ajax({
			method: 'POST',
			url: 'edit.php',
			data: editform,
			dataType: 'json',
			success: function(response){
				if(response.error){
					$('#alert').show();
					$('#alert_message').html(response.message);
				}
				else{
					$('#alert').show();
					$('#alert_message').html(response.message);
					fetch();
				}

				$('#edit').modal('hide');
			}
		});
	});
	//

	//delete
	$(document).on('click', '.delete', function(){
		var id = $(this).data('id');
		getDetails(id);
		$('#delete').modal('show');
	});

	$('.id').click(function(){
		var id = $(this).val();
		$.ajax({
			method: 'POST', 
			url: 'delete.php',
			data: {id:id},
			dataType: 'json',
			success: function(response){
				if(response.error){
					$('#alert').show();
					$('#alert_message').html(response.message);
				}
				else{
					$('#alert').show();
					$('#alert_message').html(response.message);
					fetch();
				}
				
				$('#delete').modal('hide');
			}
		});
	});
	//

	//hide message
	$(document).on('click', '.close', function(){
		$('#alert').hide();
	});

});

function fetch(){
	$.ajax({
		method: 'POST',
		url: 'fetch.php',
		success: function(response){
			$('#tbody').html(response);
		}
	});
}

function getDetails(id){
	$.ajax({
		method: 'POST',
		url: 'fetch_row.php',
		data: {id:id},
		dataType: 'json',
		success: function(response){
			if(response.error){
				$('#edit').modal('hide');
				$('#delete').modal('hide');
				$('#alert').show();
				$('#alert_message').html(response.message);
			}
			else{
				$('.id').val(response.data.id);
				$('.firstname').val(response.data.firstname);
				$('.lastname').val(response.data.lastname);
				$('.address').val(response.data.address);
				$('.fullname').html(response.data.firstname + ' ' + response.data.lastname);
			}
		}
	});
}

db.php

//db.php
<?php
Class connDB{
 
	private $server = "mysql:host=localhost;dbname=testingdb";
	private $username = "root";
	private $password = "";
	private $options  = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,);
	protected $conn;
 	
	public function open(){
 		try{
 			$this->conn = new PDO($this->server, $this->username, $this->password, $this->options);
 			return $this->conn;
 		}
 		catch (PDOException $e){
 			echo "There is some problem in connection: " . $e->getMessage();
 		}
 
    }
 
	public function close(){
   		$this->conn = null;
 	}
}
?>

fetch.php

//fetch.php
<?php
	include_once('db.php');
	$database = new connDB();
	$db = $database->open();

	try{	
	    $sql = 'SELECT * FROM members';
	    foreach ($db->query($sql) as $row) {
	    	?>
	    	<tr>
	    		<td><?php echo $row['id']; ?></td>
	    		<td><?php echo $row['firstname']; ?></td>
	    		<td><?php echo $row['lastname']; ?></td>
	    		<td><?php echo $row['address']; ?></td>
	    		<td>
	    			<button class="btn btn-success btn-sm edit" data-id="<?php echo $row['id']; ?>"><span class="glyphicon glyphicon-edit"></span> Edit</button>
	    			<button class="btn btn-danger btn-sm delete" data-id="<?php echo $row['id']; ?>"><span class="glyphicon glyphicon-trash"></span> Delete</button>
	    		</td>
	    	</tr>
	    	<?php 
	    }
	}
	catch(PDOException $e){
		echo "There is some problem in connection: " . $e->getMessage();
	}

	//close connection
	$database->close();
?>

add.php

//add.php
<?php
	include_once('db.php');
	$output = array('error' => false);

	$database = new connDB();
	$db = $database->open();
	try{
		//use of prepared statement to prevent sql injection
		$stmt = $db->prepare("INSERT INTO members (firstname, lastname, address) VALUES (:firstname, :lastname, :address)");
		//if-else statement executing prepared statement
		if ($stmt->execute(array(':firstname' => $_POST['firstname'] , ':lastname' => $_POST['lastname'] , ':address' => $_POST['address'])) ){
			$output['message'] = 'Member added successfully';
		}
		else{
			$output['error'] = true;
			$output['message'] = 'Something went wrong. Cannot add member';
		} 
		   
	}
	catch(PDOException $e){
		$output['error'] = true;
 		$output['message'] = $e->getMessage();
	}

	//close connection
	$database->close();

	echo json_encode($output);
?>

edit.php

//edit.php
<?php
	include_once('db.php');

	$output = array('error' => false);

	$database = new connDB();
	$db = $database->open();
	try{
		$id = $_POST['id'];
		$firstname = $_POST['firstname'];
		$lastname = $_POST['lastname'];
		$address = $_POST['address'];

		$sql = "UPDATE members SET firstname = '$firstname', lastname = '$lastname', address = '$address' WHERE id = '$id'";
		//if-else statement executing query
		if($db->exec($sql)){
			$output['message'] = 'Member updated successfully';
		} 
		else{
			$output['error'] = true;
			$output['message'] = 'Something went wrong. Cannot update member';
		}

	}
	catch(PDOException $e){
		$output['error'] = true;
		$output['message'] = $e->getMessage();
	}

	//close connection
	$database->close();

	echo json_encode($output);
?>

fetch_row.php

//fetch_row.php
<?php
	include_once('db.php');

	$output = array('error' => false);

	$database = new connDB();
	$db = $database->open();

	try{
		$id = $_POST['id'];
		$stmt = $db->prepare("SELECT * FROM members WHERE id = :id");
		$stmt->bindParam(':id', $id);
		$stmt->execute();
		$output['data'] = $stmt->fetch();
	}
	catch(PDOException $e){
		$output['error'] = true;
		$output['message'] = $e->getMessage();
	}

	//close connection
	$database->close();

	echo json_encode($output);
?>

delete.php

//delete.php
<?php
	include_once('db.php');

	$output = array('error' => false);

	$database = new connDB();
	$db = $database->open();
	try{
		$sql = "DELETE FROM members WHERE id = '".$_POST['id']."'";
		//if-else statement executing query
		if($db->exec($sql)){
			$output['message'] = 'Member deleted successfully';
		}
		else{
			$output['error'] = true;
			$output['message'] = 'Something went wrong. Cannot delete member';
		} 
	}
	catch(PDOException $e){
		$output['error'] = true;
		$output['message'] = $e->getMessage();;
	}

	//close connection
	$database->close();

	echo json_encode($output);
?>

Tutorial101

article

Monday, March 7, 2022

PHP PDO Mysql Check username availability with Jquery AJAX

Saturday, February 26, 2022

PHP Mysqli CRUD with JQuery AJAX and Bootstrap 5

Monday, January 31, 2022

PHP Mysqli Datatables Server Side CRUD Ajax (Create, Read, Update and Delete)

Wednesday, January 5, 2022

PHP Mysqli OOP Date Range Filter Ajax DataTables

Friday, December 17, 2021

PHP Mysql PDO CRUD (Create, Read, Update and Delete) Server Side Ajax Boostrap data table and Modal Form

Thursday, December 9, 2021

PHP MYSQL PDO Todo List with JQuery AJAX

PHP Mysql PDO CRUD(Create, Read, Update and Delete) Search and Pagination with jQuery Ajax Bootstrap

Thursday, November 25, 2021

PHP Mysql PDO CRUD (Create, Read, Update and Delete ) with jquery Ajax and Bootstrap4 Modal

Related Post

Search This Blog

HOME

Category

Popular Posts