Saturday, 21 January 2017

PHP PDO Ajax CRUD with Data Tables and Bootstrap Modals



Hi, This is one more post on CRUD system in PHP. But in this tutorial We have made CRUD system by using Jquery Datatables plugin and Bootstrap Modals as front end tools and as back end script we have use PHP PDO Modal with Jquery and Ajax. In this post we have describe how to use Jquery Datatables plugin for Server-Side processing with PHP and Ajax. We have already made different CRUD Operation System in PHP. Here is some post in which we have already CRUD system by using PHP. You can also check demo online. We have also provide demo link with this post.




We have already make CRUD Operation system in Codeigniter Framework also which you can find from above link. In this system User can Create or Insert new Data with Upload Image, Read or Fetch Data from table, Update or Edit data and Delete or Remove data. So in this System user can perform all CRUD Operation on single page without going to another page. So we can also called this Crud system to Single page CRUD System also.

In our previous post we have already make CRUD system by using PHP PDO modal with Ajax JQuery. In that system for display data we have mainualy create table and display data in that table but in this system we have use Jquery Datatables plugin for displaying data on web page. In this system we have also add one more feature like user can also upload image with Inserting or updating of data. In this system we have use Ajax Jquery method with PHP PDO modal, So we can perform all CRUD Operation without refreshing of page and we can also upload image without refreshing of page also. For sending form data to server we have use Ajax FormData() Object, By using FormData() Object we have send form data to server via Ajax request.

For making this system we have use JQuery Datatables plugin so we do want to write code for Searching table data, table column ordering, pagination and many more other features. JQuery Datatables plugin will automatically write code for this type of operation. We have use JQuery Datatables Server-Side Ajax processing for Insert Update Delete and Select of Data. Jquery Datatables plugin is light weight plugin for table grid system. So we have use this plugin in our system. I hope this tutorial will help you to learning this system. If you have any query please comment your query in comment box.



Source Code


index.php


    
<html>
 <head>
  <title>Webslesson Demo - PHP PDO Ajax CRUD with Data Tables and Bootstrap Modals</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>  
  <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
  <style>
   body
   {
    margin:0;
    padding:0;
    background-color:#f1f1f1;
   }
   .box
   {
    width:1270px;
    padding:20px;
    background-color:#fff;
    border:1px solid #ccc;
    border-radius:5px;
    margin-top:25px;
   }
  </style>
 </head>
 <body>
  <div class="container box">
   <h1 align="center">PHP PDO Ajax CRUD with Data Tables and Bootstrap Modals</h1>
   <br />
   <div class="table-responsive">
    <br />
    <div align="right">
     <button type="button" id="add_button" data-toggle="modal" data-target="#userModal" class="btn btn-info btn-lg">Add</button>
    </div>
    <br /><br />
    <table id="user_data" class="table table-bordered table-striped">
     <thead>
      <tr>
       <th width="10%">Image</th>
       <th width="35%">First Name</th>
       <th width="35%">Last Name</th>
       <th width="10%">Edit</th>
       <th width="10%">Delete</th>
      </tr>
     </thead>
    </table>
    
   </div>
  </div>
 </body>
</html>

<div id="userModal" class="modal fade">
 <div class="modal-dialog">
  <form method="post" id="user_form" enctype="multipart/form-data">
   <div class="modal-content">
    <div class="modal-header">
     <button type="button" class="close" data-dismiss="modal">&times;</button>
     <h4 class="modal-title">Add User</h4>
    </div>
    <div class="modal-body">
     <label>Enter First Name</label>
     <input type="text" name="first_name" id="first_name" class="form-control" />
     <br />
     <label>Enter Last Name</label>
     <input type="text" name="last_name" id="last_name" class="form-control" />
     <br />
     <label>Select User Image</label>
     <input type="file" name="user_image" id="user_image" />
     <span id="user_uploaded_image"></span>
    </div>
    <div class="modal-footer">
     <input type="hidden" name="user_id" id="user_id" />
     <input type="hidden" name="operation" id="operation" />
     <input type="submit" name="action" id="action" class="btn btn-success" value="Add" />
     <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
    </div>
   </div>
  </form>
 </div>
</div>

<script type="text/javascript" language="javascript" >
$(document).ready(function(){
 $('#add_button').click(function(){
  $('#user_form')[0].reset();
  $('.modal-title').text("Add User");
  $('#action').val("Add");
  $('#operation').val("Add");
  $('#user_uploaded_image').html('');
 });
 
 var dataTable = $('#user_data').DataTable({
  "processing":true,
  "serverSide":true,
  "order":[],
  "ajax":{
   url:"fetch.php",
   type:"POST"
  },
  "columnDefs":[
   {
    "targets":[0, 3, 4],
    "orderable":false,
   },
  ],

 });

 $(document).on('submit', '#user_form', function(event){
  event.preventDefault();
  var firstName = $('#first_name').val();
  var lastName = $('#last_name').val();
  var extension = $('#user_image').val().split('.').pop().toLowerCase();
  if(extension != '')
  {
   if(jQuery.inArray(extension, ['gif','png','jpg','jpeg']) == -1)
   {
    alert("Invalid Image File");
    $('#user_image').val('');
    return false;
   }
  } 
  if(firstName != '' && lastName != '')
  {
   $.ajax({
    url:"insert.php",
    method:'POST',
    data:new FormData(this),
    contentType:false,
    processData:false,
    success:function(data)
    {
     alert(data);
     $('#user_form')[0].reset();
     $('#userModal').modal('hide');
     dataTable.ajax.reload();
    }
   });
  }
  else
  {
   alert("Both Fields are Required");
  }
 });
 
 $(document).on('click', '.update', function(){
  var user_id = $(this).attr("id");
  $.ajax({
   url:"fetch_single.php",
   method:"POST",
   data:{user_id:user_id},
   dataType:"json",
   success:function(data)
   {
    $('#userModal').modal('show');
    $('#first_name').val(data.first_name);
    $('#last_name').val(data.last_name);
    $('.modal-title').text("Edit User");
    $('#user_id').val(user_id);
    $('#user_uploaded_image').html(data.user_image);
    $('#action').val("Edit");
    $('#operation').val("Edit");
   }
  })
 });
 
 $(document).on('click', '.delete', function(){
  var user_id = $(this).attr("id");
  if(confirm("Are you sure you want to delete this?"))
  {
   $.ajax({
    url:"delete.php",
    method:"POST",
    data:{user_id:user_id},
    success:function(data)
    {
     alert(data);
     dataTable.ajax.reload();
    }
   });
  }
  else
  {
   return false; 
  }
 });
 
 
});
</script>
   

db.php


    
<?php

$username = 'root';
$password = '';
$connection = new PDO( 'mysql:host=localhost;dbname=crud', $username, $password );

?>
   

function.php


    
<?php

function upload_image()
{
 if(isset($_FILES["user_image"]))
 {
  $extension = explode('.', $_FILES['user_image']['name']);
  $new_name = rand() . '.' . $extension[1];
  $destination = './upload/' . $new_name;
  move_uploaded_file($_FILES['user_image']['tmp_name'], $destination);
  return $new_name;
 }
}

function get_image_name($user_id)
{
 include('db.php');
 $statement = $connection->prepare("SELECT image FROM users WHERE id = '$user_id'");
 $statement->execute();
 $result = $statement->fetchAll();
 foreach($result as $row)
 {
  return $row["image"];
 }
}

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

?>
   

fetch.php


    
<?php
include('db.php');
include('function.php');
$query = '';
$output = array();
$query .= "SELECT * FROM users ";
if(isset($_POST["search"]["value"]))
{
 $query .= 'WHERE first_name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR last_name LIKE "%'.$_POST["search"]["value"].'%" ';
}
if(isset($_POST["order"]))
{
 $query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}
else
{
 $query .= 'ORDER BY id DESC ';
}
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)
{
 $image = '';
 if($row["image"] != '')
 {
  $image = '<img src="upload/'.$row["image"].'" class="img-thumbnail" width="50" height="35" />';
 }
 else
 {
  $image = '';
 }
 $sub_array = array();
 $sub_array[] = $image;
 $sub_array[] = $row["first_name"];
 $sub_array[] = $row["last_name"];
 $sub_array[] = '<button type="button" name="update" id="'.$row["id"].'" class="btn btn-warning btn-xs update">Update</button>';
 $sub_array[] = '<button type="button" name="delete" id="'.$row["id"].'" class="btn btn-danger btn-xs delete">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);
?>
   

insert.php


    
<?php
include('db.php');
include('function.php');
if(isset($_POST["operation"]))
{
 if($_POST["operation"] == "Add")
 {
  $image = '';
  if($_FILES["user_image"]["name"] != '')
  {
   $image = upload_image();
  }
  $statement = $connection->prepare("
   INSERT INTO users (first_name, last_name, image) 
   VALUES (:first_name, :last_name, :image)
  ");
  $result = $statement->execute(
   array(
    ':first_name' => $_POST["first_name"],
    ':last_name' => $_POST["last_name"],
    ':image'  => $image
   )
  );
  if(!empty($result))
  {
   echo 'Data Inserted';
  }
 }
 if($_POST["operation"] == "Edit")
 {
  $image = '';
  if($_FILES["user_image"]["name"] != '')
  {
   $image = upload_image();
  }
  else
  {
   $image = $_POST["hidden_user_image"];
  }
  $statement = $connection->prepare(
   "UPDATE users 
   SET first_name = :first_name, last_name = :last_name, image = :image  
   WHERE id = :id
   "
  );
  $result = $statement->execute(
   array(
    ':first_name' => $_POST["first_name"],
    ':last_name' => $_POST["last_name"],
    ':image'  => $image,
    ':id'   => $_POST["user_id"]
   )
  );
  if(!empty($result))
  {
   echo 'Data Updated';
  }
 }
}

?>
   

fetch_single.php


    
<?php
include('db.php');
include('function.php');
if(isset($_POST["user_id"]))
{
 $output = array();
 $statement = $connection->prepare(
  "SELECT * FROM users 
  WHERE id = '".$_POST["user_id"]."' 
  LIMIT 1"
 );
 $statement->execute();
 $result = $statement->fetchAll();
 foreach($result as $row)
 {
  $output["first_name"] = $row["first_name"];
  $output["last_name"] = $row["last_name"];
  if($row["image"] != '')
  {
   $output['user_image'] = '<img src="upload/'.$row["image"].'" class="img-thumbnail" width="50" height="35" /><input type="hidden" name="hidden_user_image" value="'.$row["image"].'" />';
  }
  else
  {
   $output['user_image'] = '<input type="hidden" name="hidden_user_image" value="" />';
  }
 }
 echo json_encode($output);
}
?>
   

delete.php


    
<?php

include('db.php');
include("function.php");

if(isset($_POST["user_id"]))
{
 $image = get_image_name($_POST["user_id"]);
 if($image != '')
 {
  unlink("upload/" . $image);
 }
 $statement = $connection->prepare(
  "DELETE FROM users WHERE id = :id"
 );
 $result = $statement->execute(
  array(
   ':id' => $_POST["user_id"]
  )
 );
 
 if(!empty($result))
 {
  echo 'Data Deleted';
 }
}



?>
   

Database


    
--
-- Database: `crud`
--

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(150) NOT NULL,
  `last_name` varchar(150) NOT NULL,
  `image` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=74 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `first_name`, `last_name`, `image`) VALUES
(18, 'Joseph', 'Harman', '1.jpg'),
(19, 'John', 'Moss', '4.jpg'),
(20, 'Lillie', 'Ferrarium', '3.jpg'),
(21, 'Yolanda', 'Green', '5.jpg'),
(22, 'Cara', 'Gariepy', '7.jpg'),
(23, 'Christine', 'Johnson', '11.jpg'),
(24, 'Alana', 'Decruze', '12.jpg'),
(25, 'Krista', 'Correa', '13.jpg'),
(26, 'Charles', 'Martin', '14.jpg'),
(70, 'Cindy', 'Canady', '18211.jpg'),
(73, 'Daphne', 'Frost', '8288.jpg'),
(69, 'Frank', 'Lemons', '22610.jpg'),
(66, 'Margaret', 'Ault', '14365.jpg'),
(71, 'Christina', 'Wilke', '9248.jpg'),
(68, 'Roy', 'Newton', '27282.jpg');
   





Saturday, 7 January 2017

PHP PDO CRUD with Ajax Jquery and Bootstrap



This is one more post on How to create Crud system in PHP Language. But in this post we have make Crud System in PHP by using PHP PDO Modal class with Ajax Jquery with Bootstrap. In this system we have use Ajax JQuery method with PHP PDO modal, so we can perform all CRUD operation like Create, Read, Update and Delete on single page event without refresh of page.

We have already make this type of Crud system by using Simple PHP Object Oriented Programming and we have also make Crud system in Codeigniter Framework also. But now we have use PHP PDO Modal class for making this Crud system in PHP. There are many benefits of using PHP PDO for our web development projects. Following are the benefits of using PHP PDO.

  • It is an Object Oriented.
  • Bind parameters in statements.
  • Allows for prepared statements and rollback functionality
  • Throws catch able exceptions for better error handling.. and many more
If you have use PHP PDO in your project, then you can use different databases with same code, you have not re-write new code for different database engine. You can use same code for your system operation like Insert, Update, Delete and Fetch data from database in PHP.

In this tutorial you can find how to Insert or Add data, Update or Edit Data, Delete or remove data and Select or Fetch data from database by using PHP PDO with Ajax JQuery. You can find Source source code for how to perform this crud operation in PHP PDO with JQuery Ajax method. By using this source code you can make single page CRUD application by using PHP PDO with Ajax JQuery.



Source Code


index.php


 
  <html>
 <head>
  <title>How to Read Mysql Data by using PHP PDO with Ajax - PHP PDO CRUD with Ajax</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
  <style>
   body
   {
    margin:0;
    padding:0;
    background-color:#f1f1f1;
   }
   .box
   {
    width:1270px;
    padding:20px;
    background-color:#fff;
    border:1px solid #ccc;
    border-radius:5px;
    margin-top:100px;
   }
  </style>
 </head>
 <body>
  <div class="container box">
   <h1 align="center">PHP PDO CRUD with Ajax Jquery and Bootstrap</h1>
   <br />
   <div align="right">
    <button type="button" id="modal_button" class="btn btn-info">Create Records</button>
    <!-- It will show Modal for Create new Records !-->
   </div>
   <br />
   <div id="result" class="table-responsive"> <!-- Data will load under this tag!-->

   </div>
  </div>
 </body>
</html>

<!-- This is Customer Modal. It will be use for Create new Records and Update Existing Records!-->
<div id="customerModal" class="modal fade">
 <div class="modal-dialog">
  <div class="modal-content">
   <div class="modal-header">
    <h4 class="modal-title">Create New Records</h4>
   </div>
   <div class="modal-body">
    <label>Enter First Name</label>
    <input type="text" name="first_name" id="first_name" class="form-control" />
    <br />
    <label>Enter Last Name</label>
    <input type="text" name="last_name" id="last_name" class="form-control" />
    <br />
   </div>
   <div class="modal-footer">
    <input type="hidden" name="customer_id" id="customer_id" />
    <input type="submit" name="action" id="action" class="btn btn-success" />
    <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
   </div>
  </div>
 </div>
</div>

<script>
$(document).ready(function(){
 fetchUser(); //This function will load all data on web page when page load
 function fetchUser() // This function will fetch data from table and display under <div id="result">
 {
  var action = "Load";
  $.ajax({
   url : "action.php", //Request send to "action.php page"
   method:"POST", //Using of Post method for send data
   data:{action:action}, //action variable data has been send to server
   success:function(data){
    $('#result').html(data); //It will display data under div tag with id result
   }
  });
 }

 //This JQuery code will Reset value of Modal item when modal will load for create new records
 $('#modal_button').click(function(){
  $('#customerModal').modal('show'); //It will load modal on web page
  $('#first_name').val(''); //This will clear Modal first name textbox
  $('#last_name').val(''); //This will clear Modal last name textbox
  $('.modal-title').text("Create New Records"); //It will change Modal title to Create new Records
  $('#action').val('Create'); //This will reset Button value ot Create
 });

 //This JQuery code is for Click on Modal action button for Create new records or Update existing records. This code will use for both Create and Update of data through modal
 $('#action').click(function(){
  var firstName = $('#first_name').val(); //Get the value of first name textbox.
  var lastName = $('#last_name').val(); //Get the value of last name textbox
  var id = $('#customer_id').val();  //Get the value of hidden field customer id
  var action = $('#action').val();  //Get the value of Modal Action button and stored into action variable
  if(firstName != '' && lastName != '') //This condition will check both variable has some value
  {
   $.ajax({
    url : "action.php",    //Request send to "action.php page"
    method:"POST",     //Using of Post method for send data
    data:{firstName:firstName, lastName:lastName, id:id, action:action}, //Send data to server
    success:function(data){
     alert(data);    //It will pop up which data it was received from server side
     $('#customerModal').modal('hide'); //It will hide Customer Modal from webpage.
     fetchUser();    // Fetch User function has been called and it will load data under divison tag with id result
    }
   });
  }
  else
  {
   alert("Both Fields are Required"); //If both or any one of the variable has no value them it will display this message
  }
 });

 //This JQuery code is for Update customer data. If we have click on any customer row update button then this code will execute
 $(document).on('click', '.update', function(){
  var id = $(this).attr("id"); //This code will fetch any customer id from attribute id with help of attr() JQuery method
  var action = "Select";   //We have define action variable value is equal to select
  $.ajax({
   url:"action.php",   //Request send to "action.php page"
   method:"POST",    //Using of Post method for send data
   data:{id:id, action:action},//Send data to server
   dataType:"json",   //Here we have define json data type, so server will send data in json format.
   success:function(data){
    $('#customerModal').modal('show');   //It will display modal on webpage
    $('.modal-title').text("Update Records"); //This code will change this class text to Update records
    $('#action').val("Update");     //This code will change Button value to Update
    $('#customer_id').val(id);     //It will define value of id variable to this customer id hidden field
    $('#first_name').val(data.first_name);  //It will assign value to modal first name texbox
    $('#last_name').val(data.last_name);  //It will assign value of modal last name textbox
   }
  });
 });

 //This JQuery code is for Delete customer data. If we have click on any customer row delete button then this code will execute
 $(document).on('click', '.delete', function(){
  var id = $(this).attr("id"); //This code will fetch any customer id from attribute id with help of attr() JQuery method
  if(confirm("Are you sure you want to remove this data?")) //Confim Box if OK then
  {
   var action = "Delete"; //Define action variable value Delete
   $.ajax({
    url:"action.php",    //Request send to "action.php page"
    method:"POST",     //Using of Post method for send data
    data:{id:id, action:action}, //Data send to server from ajax method
    success:function(data)
    {
     fetchUser();    // fetchUser() function has been called and it will load data under divison tag with id result
     alert(data);    //It will pop up which data it was received from server side
    }
   })
  }
  else  //Confim Box if cancel then 
  {
   return false; //No action will perform
  }
 });
});
</script>
 

action.php


 
  <?php
//Database connection by using PHP PDO
$username = 'root';
$password = '';
$connection = new PDO( 'mysql:host=localhost;dbname=crud', $username, $password ); // Create Object of PDO class by connecting to Mysql database

if(isset($_POST["action"])) //Check value of $_POST["action"] variable value is set to not
{
 //For Load All Data
 if($_POST["action"] == "Load") 
 {
  $statement = $connection->prepare("SELECT * FROM customers ORDER BY id DESC");
  $statement->execute();
  $result = $statement->fetchAll();
  $output = '';
  $output .= '
   <table class="table table-bordered">
    <tr>
     <th width="40%">First Name</th>
     <th width="40%">Last Name</th>
     <th width="10%">Update</th>
     <th width="10%">Delete</th>
    </tr>
  ';
  if($statement->rowCount() > 0)
  {
   foreach($result as $row)
   {
    $output .= '
    <tr>
     <td>'.$row["first_name"].'</td>
     <td>'.$row["last_name"].'</td>
     <td><button type="button" id="'.$row["id"].'" class="btn btn-warning btn-xs update">Update</button></td>
     <td><button type="button" id="'.$row["id"].'" class="btn btn-danger btn-xs delete">Delete</button></td>
    </tr>
    ';
   }
  }
  else
  {
   $output .= '
    <tr>
     <td align="center">Data not Found</td>
    </tr>
   ';
  }
  $output .= '</table>';
  echo $output;
 }

 //This code for Create new Records
 if($_POST["action"] == "Create")
 {
  $statement = $connection->prepare("
   INSERT INTO customers (first_name, last_name) 
   VALUES (:first_name, :last_name)
  ");
  $result = $statement->execute(
   array(
    ':first_name' => $_POST["firstName"],
    ':last_name' => $_POST["lastName"]
   )
  );
  if(!empty($result))
  {
   echo 'Data Inserted';
  }
 }

 //This Code is for fetch single customer data for display on Modal
 if($_POST["action"] == "Select")
 {
  $output = array();
  $statement = $connection->prepare(
   "SELECT * FROM customers 
   WHERE id = '".$_POST["id"]."' 
   LIMIT 1"
  );
  $statement->execute();
  $result = $statement->fetchAll();
  foreach($result as $row)
  {
   $output["first_name"] = $row["first_name"];
   $output["last_name"] = $row["last_name"];
  }
  echo json_encode($output);
 }

 if($_POST["action"] == "Update")
 {
  $statement = $connection->prepare(
   "UPDATE customers 
   SET first_name = :first_name, last_name = :last_name 
   WHERE id = :id
   "
  );
  $result = $statement->execute(
   array(
    ':first_name' => $_POST["firstName"],
    ':last_name' => $_POST["lastName"],
    ':id'   => $_POST["id"]
   )
  );
  if(!empty($result))
  {
   echo 'Data Updated';
  }
 }

 if($_POST["action"] == "Delete")
 {
  $statement = $connection->prepare(
   "DELETE FROM customers WHERE id = :id"
  );
  $result = $statement->execute(
   array(
    ':id' => $_POST["id"]
   )
  );
  if(!empty($result))
  {
   echo 'Data Deleted';
  }
 }

}

?>
 

Database


 
 --
-- Database: `crud`
--

-- --------------------------------------------------------

--
-- Table structure for table `customers`
--

CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=68 ;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` (`id`, `first_name`, `last_name`) VALUES
(18, 'Joseph', 'Harman'),
(19, 'John', 'Moss'),
(20, 'Lillie', 'Ferrarium'),
(21, 'Yolanda', 'Green'),
(22, 'Cara', 'Gariepy'),
(55, 'Opal', 'Goree'),
(23, 'Christine', 'Johnson'),
(24, 'Alana', 'Decruze'),
(25, 'Krista', 'Correa'),
(26, 'Charles', 'Martin'),
(54, 'Rhonda', 'Ocampo'),
(51, 'Cecilia', 'Roy'),
(52, 'Otto', 'Estes'),
(53, 'Richardson', 'Fishback'),
(56, 'Rudy', 'Buckley');
 

Thursday, 29 December 2016

PHP Mysql Ajax Crud using OOPS - Pagination


In the web project, pagination is the most essential element in which we can displayed large number of data from Mysql database to on webpage. Then in that time Pagination make by Ajax is a very Ajax pagination is a excellent idea because it will assist to increase visibility of your website User Interface. This tutorial will help you about how can you create the Ajax pagination in PHP Object Oriented programming Script using jQuery and MySQL. Here We have made the very simple but very powerful code to developed pagination with jQuery, Ajax, PHP, and MySQL.

By using Object Oriented PHP Script, We have first load data from the MySQL database with the Ajax pagination links. By Click on this Ajax pagination links, you can fetch the database table records except the records which you want to displayed. jQuery and Ajax will assist to fetch the data from the mysql database based on pagination links without refreshing of the page.

First We have make changes in load_data() function, in this function we have add first one argument like page.

function load_data(page)

After this in that function we have add that page argument data has been passed in Ajax data option to send the value of this page variable to action.php page.

data:{action:action, page:page},

Write JQuery code on click on pagination links. Here .pagination_link is the class name of Ajax Pagination Link. When click on pagination link this code will execute and it will fetch value from id attribute of particular pagination link. In an id attribute we have store page number. After fetching page number we have called load_data(page) function and in that function we have pass value of page variable and that function load data for that page.

              $(document).on('click', '.pagination_link', function(){
   var page = $(this).attr("id");
   load_data(page);
  });

This code write on action.php page under load data if condition. Here first $record_per_page = 5; That means we have set display five records on web page. Then after we have define $page variable. In this variable store the value of $_POST["page"] variable which is comes from ajax method. If it not received $_POST["page] variable then we have set $page variable value to 1.

                $record_per_page = 5;
  $page = '';

  if(isset($_POST["page"]))
  {
   $page = $_POST["page"];
  }
  else
  {
   $page = 1;
  }

After this we have calculate from where we have start fetching data from database.

$start_from = ($page - 1) * $record_per_page;

After this we have called get_data_in_table() function which we have made under crud class, this function will execute query and display result in table format

echo $object->get_data_in_table("SELECT * FROM users ORDER BY id DESC LIMIT $start_from, $record_per_page");

Make one function with name like make_pagination_link() in crud class, this function has generate pagination link and send pagination link.

        function make_pagination_link($query, $record_per_page)
 {
  $output = '';
  $result = $this->execute_query($query);
  $total_records = mysqli_num_rows($result);
  $total_pages = ceil($total_records/$record_per_page);
  for($i=1; $i<=$total_pages; $i++)
  {
   $output .= '<span class="pagination_link" style="cursor:pointer; padding:6px; border:1px solid #ccc;" id="'.$i.'">'.$i.'</span>';
  }
  return $output;
 }

Again go to action.php and called make_pagination_link() which return pagination link html format and which we have send to index page.

echo $object->make_pagination_link("SELECT * FROM users ORDER by id", $record_per_page);

Conclusion

So Pagination is the last part of our Crud system which is developed by Object Oriented PHP programming with Ajax JQuery method. Lastly Pagination is a very important features of any website, specially if you have many large number of data stored into your database and at that time you want to group that data into different part and display in different pages then at that time Ajax Pagination is done best job like, you can access different pages of data without going to next page but stay on single page. So it is very import for any web application.





Source Code of Whole System with Insert, Update Delete, Searching and Pagination


crud.php


<?php
class Crud
{
 //crud class
 public $connect;
 private $host = "localhost";
 private $username = 'root';
 private $password = '';
 private $database = 'crud';

 function __construct()
 {
  $this->database_connect();
 }

 public function database_connect()
 {
  $this->connect = mysqli_connect($this->host, $this->username, $this->password, $this->database);
 }

 public function execute_query($query)
 {
  return mysqli_query($this->connect, $query);
 }

 public function get_data_in_table($query)
 {
  $output = '';
  $result = $this->execute_query($query);
  $output .= '
  <table class="table table-bordered table-striped">
   <tr>
    <th width="10%">Image</th>
    <th width="35%">First Name</th>
    <th width="35%">Last Name</th>
    <th width="10%">Update</th>
    <th width="10%">Delete</th>
   </tr>
  ';
  if(mysqli_num_rows($result) > 0)
  {
   while($row = mysqli_fetch_object($result))
   {
    $output .= '
    <tr>
     <td><img src="upload/'.$row->image.'" class="img-thumbnail" width="50" height="35" /></td>
     <td>'.$row->first_name.'</td>
     <td>'.$row->last_name.'</td>
     <td><button type="button" name="update" id="'.$row->id.'" class="btn btn-success btn-xs update">Update</button></td>
     <td><button type="button" name="delete" id="'.$row->id.'" class="btn btn-danger btn-xs delete">Delete</button></td>
    </tr>
    ';
   }
  }
  else
  {
   $output .= '
    <tr>
     <td colspan="5" align="center">No Data Found</td>
    </tr>
   ';
  }
  $output .= '</table>';
  return $output;
 } 
 function upload_file($file)
 {
  if(isset($file))
  {
   $extension = explode('.', $file["name"]);
   $new_name = rand() . '.' . $extension[1];
   $destination = './upload/' . $new_name;
   move_uploaded_file($file['tmp_name'], $destination);
   return $new_name;
  }
 }

 function make_pagination_link($query, $record_per_page)
 {
  $output = '';
  $result = $this->execute_query($query);
  $total_records = mysqli_num_rows($result);
  $total_pages = ceil($total_records/$record_per_page);
  for($i=1; $i<=$total_pages; $i++)
  {
   $output .= '<span class="pagination_link" style="cursor:pointer; padding:6px; border:1px solid #ccc;" id="'.$i.'">'.$i.'</span>';
  }
  return $output;
 }
}
?>

index.php


<?php
//index.php
include 'crud.php';
$object = new Crud();
?>
<html>
 <head>
  <title>PHP Mysql Ajax Crud using OOPS - Pagination</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
  <style>
   body
   {
    margin:0;
    padding:0;
    background-color:#f1f1f1;
   }
   .box
   {
    width:1270px;
    padding:20px;
    background-color:#fff;
    border:1px solid #ccc;
    border-radius:5px;
    margin-top:10px;
   }
  </style>
 </head>
 <body>
  <div class="container box">
   <h3 align="center">PHP Mysql Ajax Crud using OOPS - Pagination</h3><br /><br />
   <div class="col-md-8">
    <button type="button" name="add" id="add" class="btn btn-success" data-toggle="collapse" data-target="#user_collapse">Add</button>
   </div>
   <div class="col-md-4">
    <input type="text" name="search" id="search" placeholder="Search" class="form-control" />
   </div>
   <br />
   <br />
   <div id="user_collapse" class="collapse">
    <form method="post" id="user_form">
     <label>Enter First Name</label>
     <input type="text" name="first_name" id="first_name" class="form-control" />
     <br />
     <label>Enter Last Name</label>
     <input type="text" name="last_name" id="last_name" class="form-control" />
     <br />
     <label>Select User Image</label>
     <input type="file" name="user_image" id="user_image" />
     <input type="hidden" name="hidden_user_image" id="hidden_user_image" />
     <span id="uploaded_image"></span>
     <br />
     <div align="center">
      <input type="hidden" name="action" id="action" />
      <input type="hidden" name="user_id" id="user_id" />
      <input type="submit" name="button_action" id="button_action" class="btn btn-default" value="Insert" />
     </div>
    </form>
   </div>
   <br /><br />
   <div id="user_table" class="table-responsive">
   </div>
  </div>
 </body>
</html>

<script type="text/javascript">
 $(document).ready(function(){

  load_data();

  $('#action').val("Insert");

  $('#add').click(function(){
   $('#user_form')[0].reset();
   $('#uploaded_image').html('');
   $('#button_action').val("Insert");
  });
  function load_data(page)
  {
   var action = "Load";
   $.ajax({
    url:"action.php",
    method:"POST",
    data:{action:action, page:page},
    success:function(data)
    {
     $('#user_table').html(data);
    }
   });
  }

  $(document).on('click', '.pagination_link', function(){
   var page = $(this).attr("id");
   load_data(page);
  });

  $('#user_form').on('submit', function(event){
   event.preventDefault();
   var firstName = $('#first_name').val();
   var lastName = $('#last_name').val();
   var extension = $('#user_image').val().split('.').pop().toLowerCase();
   if(extension != '')
   {
    if(jQuery.inArray(extension, ['gif','png','jpg','jpeg']) == -1)
    {
     alert("Invalid Image File");
     $('#user_image').val('');
     return false;
    }
   }
   if(firstName != '' && lastName != '')
   {
    $.ajax({
     url:"action.php",
     method:"POST",
     data:new FormData(this),
     contentType:false,
     processData:false,
     success:function(data)
     {
      alert(data);
      $('#user_form')[0].reset();
      load_data();      
      $('#action').val("Insert");
      $('#button_action').val("Insert");
      $('#uploaded_image').html('');
     }
    })
   }
   else
   {
    alert("Both Fields are Required");
   }
  });

  $(document).on('click', '.update', function(){
   var user_id = $(this).attr("id");
   var action = "Fetch Single Data";
   $.ajax({
    url:"action.php",
    method:"POST",
    data:{user_id:user_id, action:action},
    dataType:"json",
    success:function(data)
    {
     $('.collapse').collapse("show");
     $('#first_name').val(data.first_name);
     $('#last_name').val(data.last_name);
     $('#uploaded_image').html(data.image);
     $('#hidden_user_image').val(data.user_image);
     $('#button_action').val("Edit");
     $('#action').val("Edit");
     $('#user_id').val(user_id);
    }
   });
  });
  
  $(document).on('click', '.delete', function(){
   var user_id = $(this).attr("id");
   var action = "Delete";
   if(confirm("Are you sure you want to delete this?"))
   {
    $.ajax({
     url:"action.php",
     method:"POST",
     data:{user_id:user_id, action:action},
     success:function(data)
     {
      alert(data);
      load_data();
     }
    });
   }
   else
   {
    return false;
   }
  });
  
  $('#search').keyup(function(){
   var query = $('#search').val();
   var action = "Search";
   if(query != '')
   {
    $.ajax({
     url:"action.php",
     method:"POST",
     data:{query:query, action:action},
     success:function(data)
     {
      $('#user_table').html(data);
     }
    });
   }
   else
   {
    load_data();
   }
  });
  
 });
</script>

action.php


<?php
//action.php
include 'crud.php';
$object = new Crud();
if(isset($_POST["action"]))
{
 if($_POST["action"] == "Load")
 {
  $record_per_page = 5;
  $page = '';

  if(isset($_POST["page"]))
  {
   $page = $_POST["page"];
  }
  else
  {
   $page = 1;
  }
  $start_from = ($page - 1) * $record_per_page;

  echo $object->get_data_in_table("SELECT * FROM users ORDER BY id DESC LIMIT $start_from, $record_per_page");
  echo '<br /><div align="center">';
  echo $object->make_pagination_link("SELECT * FROM users ORDER by id", $record_per_page);
  echo '</div><br />';

 }
 if($_POST["action"] == "Insert")
 {
  $first_name = mysqli_real_escape_string($object->connect, $_POST["first_name"]);
  $last_name = mysqli_real_escape_string($object->connect, $_POST["last_name"]);
  $image = $object->upload_file($_FILES["user_image"]);
  $query = "
  INSERT INTO users
  (first_name, last_name, image) 
  VALUES ('".$first_name."', '".$last_name."', '".$image."')
  ";
  $object->execute_query($query);
  echo 'Data Inserted';
 }
 if($_POST["action"] == "Fetch Single Data")
 {
  $output = '';
  $query = "SELECT * FROM users WHERE id = '".$_POST["user_id"]."'";
  $result = $object->execute_query($query);
  while($row = mysqli_fetch_array($result))
  {
   $output["first_name"] = $row['first_name'];
   $output["last_name"] = $row['last_name'];
   $output["image"] = '<img src="upload/'.$row['image'].'" class="img-thumbnail" width="50" height="35" />';
   $output["user_image"] = $row['image'];
  }
  echo json_encode($output);
 }

 if($_POST["action"] == "Edit")
 {
  $image = '';
  if($_FILES["user_image"]["name"] != '')
  {
   $image = $object->upload_file($_FILES["user_image"]);
  }
  else
  {
   $image = $_POST["hidden_user_image"];
  }
  $first_name = mysqli_real_escape_string($object->connect, $_POST["first_name"]);
  $last_name = mysqli_real_escape_string($object->connect, $_POST["last_name"]);
  $query = "UPDATE users SET first_name = '".$first_name."', last_name = '".$last_name."', image = '".$image."' WHERE id = '".$_POST["user_id"]."'";
  $object->execute_query($query);
  echo 'Data Updated';
  //echo $query;
 }
 
 if($_POST["action"] == "Delete")
 {
  $query = "DELETE FROM users WHERE id = '".$_POST["user_id"]."'";
  $object->execute_query($query);
  echo "Data Deleted";
 }
 
 if($_POST["action"] == "Search")
 {
  $search = mysqli_real_escape_string($object->connect, $_POST["query"]);
  $query = "
  SELECT * FROM users 
  WHERE first_name LIKE '%".$search."%' 
  OR last_name LIKE '%".$search."%' 
  ORDER BY id DESC
  ";
  //echo $query;
  echo $object->get_data_in_table($query);  
 }
 
}
?>

Friday, 23 December 2016

How to Create SEO Friendly URL in PHP with htaccess





In this post describe how to make Clean or Semantic or SEO Friendly URL with Mysql Table Dynamic Content by using .htaccess mod_rewrite in PHP Script. This post will explain how can we Rewrite Dynamic dirty url with id to Semantic URL in PHP with using .htaccess. Clean url will increase your web site presence on search engine like Google, yahoo, bing etc and it will improve ranking on Search Engines. If you want to make SEO Friendly or Clean or Semantic Url for your site, so first you want to enable Apache re-write_module at your wamp server. By enabling Apache rewrite_module you can make clean SEO friendly url from your dynamic dirty url by writing few lines of PHP code and .htaccess file. For Semantic url you want to create .htaccess file in your working folder.

Source Code


.htaccess


RewriteEngine On

RewriteRule ^post/([a-zA-Z0-9-/]+)$ post.php?post_url=$1
RewriteRule ^post/([a-zA-Z-0-9-]+)/ post.php?post_url=$1

index.php


<?php
$connect = mysqli_connect("localhost", "root", "", "test_db");
if(isset($_POST["submit_btn"]))
{
 //mysqli_real_escape_string() - mysqli_real_escape_string() function escapes special characters in a string for use in an SQL statement
 //htmlentities() - htmlentities() function converts special characters to HTML entities.
 $post_title = mysqli_real_escape_string($connect, $_POST["post_title"]);
 $post_text = mysqli_real_escape_string($connect, $_POST["post_text"]);
 $post_title = htmlentities($post_title);
 $post_text = htmlentities($post_text);
 $sql = "INSERT INTO tbl_post (post_title, post_text, post_url) VALUES ('".$post_title."', '".$post_text."', '".php_slug($post_title)."')";
 if(mysqli_query($connect, $sql))
 {
  header("location:post/".php_slug($post_title)."");
 }
}

function php_slug($string)
{
 $slug = preg_replace('/[^a-z0-9-]+/', '-', trim(strtolower($string)));
 return $slug;
}

?>
<html>
 <head>
  <title>Make SEO Friendly / Clean Url in PHP using .htaccess</title>
  <style>
  .container
  {
   width:700px;
   margin:0 auto;
   border:1px solid #ccc;
   padding:16px;
  }
  .form_text
  {
   width:100%;
   padding:6px;
  }
  </style>
 </head>
 <body>
  <div class="container">
   <h3 align="center">Make SEO Friendly / Clean Url in PHP using .htaccess</h3>
   
   <form name="submit_form" method="post">
    <p>Post Title
    <input type="text" name="post_title" class="form_text" maxlength="200" />
    </p>
    <p>Post Text
    <textarea name="post_text" class="form_text" rows="10"></textarea>
    </p>
    <p><input type="submit" name="submit_btn" value="Submit" />
   </form>
  </div>
 </body>
</html>

post.php


<?php
//post.php
$connect = mysqli_connect("localhost", "root", "", "test_db");
$post_url = $_GET["post_url"];

$sql = "SELECT * FROM tbl_post WHERE post_url = '".$post_url."'";
$result = mysqli_query($connect, $sql);


?>


<html>
 <head>
  <title>Make SEO Friendly / Clean Url in PHP using .htaccess</title>
  <style>
  .container
  {
   width:700px;
   margin:0 auto;
   border:1px solid #ccc;
   padding:16px;
  }
  .form_text
  {
   width:100%;
   padding:6px;
  }
  </style>
 </head>
 <body>
  <div class="container">
   <h3 align="center">Make SEO Friendly / Clean Url in PHP using .htaccess</h3>
   <?php
   if(mysqli_num_rows($result) > 0)
   {
    while($row = mysqli_fetch_array($result))
    {
     echo '<h3>'.$row["post_title"].'</h3>';
     echo '<p>'.$row["post_text"].'</p>';
    }
   }
   else
   {
    echo '404 Page';
   }
   
   ?>
   
  </div>
 </body>
</html>