Thursday, 13 December 2018

Live Table Add Edit Delete in Laravel using Ajax jQuery

Live Table Add Edit Delete in Laravel using Ajax jQuery - 1




Live Table Add Edit Delete in Laravel using Ajax jQuery - 2




Live Table Add Edit Delete in Laravel using Ajax jQuery - 3




Live Table Add Edit Delete in Laravel using Ajax jQuery - 4





If you are looking for web tutorial on how to make live table application in Laravel framework by using Ajax and jQuery. So, you are come on right place, here you can find step by step by not only web tutorial but also video tutorial on Live table Insert Update Delete mysql table records in Laravel framework with Ajax and jQuery. We all know Laravel is a robust PHP framework for develop enterprise level application from scratch with write small PHP code and reuse same code. If we have use Ajax jQuery with Laravel, then it will make standard level web application.

In this post we have make Single page Live table or Inline table application in Laravel by using Ajax jQuery. In this application we will perform all CRUD operation like Create, Read, Update and Delete mysql database data from Laravel Live table or Inline table application. In this application use can Create or Add or Insert new data into mysql table from table, User can Update or edit exisiting records of Mysql table from this Laravel Live table. User can delete or remove mysql data from this Application. He can perform all this operation without going to other page, but from single html table he can perform all CRUD operation. That means here we will make Single page Live table crud application in Laravel using Ajax and jQuery.

Most of the application there main function is insert, update, delete and read mysql data. So, here we have make Single page Inline table application in Laravel with Ajax jquery, and perform all function from single page without refresh of web page. So, it will increase the speed of your web application, and it will also advance feature in your application. It will improve your application efficiency also. So, if Laravel framework will work with Ajax and jQuery, then it will increase your web presense user interface from user also. Below you can find step by step source code of Live table insert update delete mysql data using Ajax jQuery in Laravel framework.






Source Code


First we have to create Livetable controller, for this we have to go command prompt, and write following command, this command will create LiveTable.php controller file under app/HTTP/controllers folder.


php artisan make:controller LiveTable


Livetable.php


Once controller has been created under app/HTTP/controllers folder. In this class you can find following method for handle HTTP request of Live table crud operation in Laravel.

index() - This is the root method of this class, it will load live_table.blade.php view file in browser, once this live table controller has been called in browser.

fetch_data() - This method has receieved ajax request for fetch data from live_table.blade.php file, and this function return data in json format.

add_data() - This method has received ajax request for insert or add new records in mysql table from live_table.blade.php file, and this method return response once data has been successfully inserted.

update_data() - This method is used for update or edit mysql table records using Ajax. Ajax request has been send from view file to this method for edit or update existing of data of Mysql in Laravel using Ajax.

delete_data() - This method has received delete mysql table data request from Ajax, This method mainly used for delete or remove data operation in Laravel. Once this method has delete data then it send back response to Ajax request.


<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use DB;

class LiveTable extends Controller
{
    function index()
    {
        return view('live_table');
    }

    function fetch_data(Request $request)
    {
        if($request->ajax())
        {
            $data = DB::table('tbl_sample')->orderBy('id','desc')->get();
            echo json_encode($data);
        }
    }

    function add_data(Request $request)
    {
        if($request->ajax())
        {
            $data = array(
                'first_name'    =>  $request->first_name,
                'last_name'     =>  $request->last_name
            );
            $id = DB::table('tbl_sample')->insert($data);
            if($id > 0)
            {
                echo '<div class="alert alert-success">Data Inserted</div>';
            }
        } 
    }

    function update_data(Request $request)
    {
        if($request->ajax())
        {
            $data = array(
                $request->column_name       =>  $request->column_value
            );
            DB::table('tbl_sample')
                ->where('id', $request->id)
                ->update($data);
            echo '<div class="alert alert-success">Data Updated</div>';
        }
    }

    function delete_data(Request $request)
    {
        if($request->ajax())
        {
            DB::table('tbl_sample')
                ->where('id', $request->id)
                ->delete();
            echo '<div class="alert alert-success">Data Deleted</div>';
        }
    }
}
?>





livetable.blade.php


This is view file of Live table application in Laravel and this file you can find under resources/view/livetable.blade.php. This file is used for display output on browser. In this view file you can find html, jquery and Ajax source code for fetch and insert data into mysql table in Laravel. For fetch data using Ajax, here we have make fetch_data() jQuery function which send Ajax request to LiveTable.php controller. For Insert, Update and delete mysql data using Ajax also, here you can find jQuery code in which Ajax request has been send to LiveTable.php controller class.


<!DOCTYPE html>
<html>
 <head>
  <title>Live Table Insert Update Delete in Laravel using Ajax jQuery</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.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.7/js/bootstrap.min.js"></script>
 </head>
 <body>
  <br />
  <div class="container box">
   <h3 align="center">Live Table Insert Update Delete in Laravel using Ajax jQuery</h3><br />
   <div class="panel panel-default">
    <div class="panel-heading">Sample Data</div>
    <div class="panel-body">
     <div id="message"></div>
     <div class="table-responsive">
      <table class="table table-striped table-bordered">
       <thead>
        <tr>
         <th>First Name</th>
         <th>Last Name</th>
         <th>Delete</th>
        </tr>
       </thead>
       <tbody>
       
       </tbody>
      </table>
      {{ csrf_field() }}
     </div>
    </div>
   </div>
  </div>
 </body>
</html>

<script>
$(document).ready(function(){

 fetch_data();

 function fetch_data()
 {
  $.ajax({
   url:"/livetable/fetch_data",
   dataType:"json",
   success:function(data)
   {
    var html = '';
    html += '<tr>';
    html += '<td contenteditable id="first_name"></td>';
    html += '<td contenteditable id="last_name"></td>';
    html += '<td><button type="button" class="btn btn-success btn-xs" id="add">Add</button></td></tr>';
    for(var count=0; count < data.length; count++)
    {
     html +='<tr>';
     html +='<td contenteditable class="column_name" data-column_name="first_name" data-id="'+data[count].id+'">'+data[count].first_name+'</td>';
     html += '<td contenteditable class="column_name" data-column_name="last_name" data-id="'+data[count].id+'">'+data[count].last_name+'</td>';
     html += '<td><button type="button" class="btn btn-danger btn-xs delete" id="'+data[count].id+'">Delete</button></td></tr>';
    }
    $('tbody').html(html);
   }
  });
 }

 var _token = $('input[name="_token"]').val();

 $(document).on('click', '#add', function(){
  var first_name = $('#first_name').text();
  var last_name = $('#last_name').text();
  if(first_name != '' && last_name != '')
  {
   $.ajax({
    url:"{{ route('livetable.add_data') }}",
    method:"POST",
    data:{first_name:first_name, last_name:last_name, _token:_token},
    success:function(data)
    {
     $('#message').html(data);
     fetch_data();
    }
   });
  }
  else
  {
   $('#message').html("<div class='alert alert-danger'>Both Fields are required</div>");
  }
 });

 $(document).on('blur', '.column_name', function(){
  var column_name = $(this).data("column_name");
  var column_value = $(this).text();
  var id = $(this).data("id");
  
  if(column_value != '')
  {
   $.ajax({
    url:"{{ route('livetable.update_data') }}",
    method:"POST",
    data:{column_name:column_name, column_value:column_value, id:id, _token:_token},
    success:function(data)
    {
     $('#message').html(data);
    }
   })
  }
  else
  {
   $('#message').html("<div class='alert alert-danger'>Enter some value</div>");
  }
 });

 $(document).on('click', '.delete', function(){
  var id = $(this).attr("id");
  if(confirm("Are you sure you want to delete this records?"))
  {
   $.ajax({
    url:"{{ route('livetable.delete_data') }}",
    method:"POST",
    data:{id:id, _token:_token},
    success:function(data)
    {
     $('#message').html(data);
     fetch_data();
    }
   });
  }
 });


});
</script>


web.php


Once you have completed working on Controller class code and view file code, lastly you have to set route for LiveTable.php controller class. In this class we have create index(), fetch_data(), add_data(), update_data() and delete_data() method for fetch, insert and update data using Ajax in Laravel. For set route in Laravel, we have to go to routes/web.php file. And in this file we have to write following code for set route.


Route::get('/livetable', 'LiveTable@index');
Route::get('/livetable/fetch_data', 'LiveTable@fetch_data');
Route::post('/livetable/add_data', 'LiveTable@add_data')->name('livetable.add_data');
Route::post('/livetable/update_data', 'LiveTable@update_data')->name('livetable.update_data');
Route::post('/livetable/delete_data', 'LiveTable@delete_data')->name('livetable.delete_data');


Lastly for run Laravel Live table add edit delete mysql data application using Ajax jQuery, we have to go to command prompt, and write following command.


php artisan serve


Once this command has been run under command prompt in which you have already run composer command, then you will provide you this http://127.0.0.1:8000 link, and just run following link for run Live table application.


http://127.0.0.1:8000/livetable



Friday, 7 December 2018

How to Create Entire Div Clickable using jQuery



In this post you can find the solution of How to make whole Div tag clickable by using jQuery. There are many ways we can make Div tag clickable like by using CSS, HTML or javascript. But here we will seen how can use simple jQuery code to create whole Div clickable. In web development there are many places in which we have to put link on whole div tag not only single text. Then at that time we have put simple javascript in onclick attribute of div tag and make whole div tag clickable. Or by using CSS we have convert simple div tag to clickable div tag.

But by this method it has affect SEO score of our web page, because in html code we have add more attribute. But if we have use simple jQuery for this things then in single line of code it will convert div tag to clickable div tag without adding any extra tag in our html code. This source you can also implement other tag like span tag, html 5 article tag or any other tag which we can use in place of Div. So, by this code we not only convert Div tag to clickable tag, but also we can aslo use same code for convert other tag to clickable tag. Below you can find source code and online demo of How to make whole Div clickable by using jQuery.







Source Code



<!DOCTYPE html>
<html lang="en">

<head>

    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta name="description" content="">
    <meta name="author" content="">

    <title>How to make Whole Div Clickable using jquery</title>

    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
 <style>
 .clickable
 {
  border:1px solid #ccc;
  cursor:pointer;
  padding-top:12px;
  padding-bottom:12px;
 }
 </style>
</head>
<body>
 <div class="container">
  <br />
  <h3 align="center">How to make Whole Div Clickable using jquery</h3>
  <br />
  
  <div class="row">
   <div class="col-md-4"></div>
   <div class="col-md-4 clickable">
    <img src="image.jpg" class="img-thumbnail" />
    <h3>Exporting Data into Multiple Excel sheets in PHP</h3>
    <p>If you are working with large amount of data and mainly we have use Excel and CSV file format for export data in web development. So, in this post we have describe how to split huge amount of mysql datable data and export into multiple excel or csv file by using PHPExcel library in PHP.</p>
    <a href="https://www.webslesson.info/2018/12/exporting-data-into-multiple-excel-sheets-in-php.html">Read more</a>
   </div>
   <div class="col-md-4"></div>
  </div>
  
 </div>
</body>
</html>
<script>
$(document).ready(function(){
 
 $('.clickable').click(function(){
  window.location = $(this).find("a").attr("href");
 });
 
});
</script>





Thursday, 6 December 2018

Exporting Data into Multiple Excel sheets in PHP



If you are working with large amount of data and mainly we have use Excel and CSV file format for export data in web development. So, in this post we have describe how to split huge amount of mysql datable data and export into multiple excel or csv file by using PHPExcel library in PHP. Suppose there are millions of data in your database, and that amount of data you cannot export into single excel file. At that time you have to divide your data into different files like what we have do in pagination. Like a pagination we have not load all data in single page, but we have divided same amount of data in different page. Same thing we have to do for export huge amount of data into multiple file using PHP script with PHPExcel library.

There are number of ways we can export database using PHP script can be done number of ways. But here we will export data into multiple excel file, and that file will be store under folder. By click on link, we can download multiple file one by one. So, here exporting data and downloading file has been divided, so chances of crash of our website will be very less.

The output of any web application is to get data in Excel, CSV or PDF format. If you want to make report from your web based application then data must be required in PDF format, but if you want to reuse data in other form, then data must be exported into Excel or CSV format. Because this two format data type is widely used for exchange between two web application. So, here in this post we have make this tutorial for export mysql data into multiple excel file using PHPExcel library in PHP script. Below you can find complete source code of this tutorial.







Source Code


index.php


This is index.php file, in this file first we have make database connection, and fetch data from customer table and display on web page. After this we have make form with input select option for select how many records you want to export in single Excel file and below you can find submit button. Once click on submit button then in that block first it will include PHPExcel library. After this first it will calculate how many file want to make from mysql database. After this here object of PHPEcel library has been created and start exporting mysql data in to multiple excel file. Once all data has been exported into multiple files then that file has been stored under folder. These all file download link will be display on web page, and for download file, below you can fine download.php file.


<?php

$connect = new PDO("mysql:host=localhost;dbname=testing","root","");

$query = "SELECT * FROM tbl_customer ORDER BY CustomerID";

$statement = $connect->prepare($query);

$statement->execute();

$result = $statement->fetchAll();

$total_rows = $statement->rowCount();

$download_filelink = '<ul class="list-unstyled">';

if(isset($_POST["export"]))
{
 require_once 'class/PHPExcel.php';
 $last_page = ceil($total_rows/$_POST["records_no"]);
 $start = 0;
 $file_number = 0;
 for($count = 0; $count < $last_page; $count++)
 {
  $file_number++;
  $object = new PHPExcel();
  $object->setActiveSheetIndex(0);
  $table_columns = array("Nos", "Customer Name", "Gender", "Address", "City", "Postal Code", "Country");
  $column = 0;
  foreach($table_columns as $field)
  {
   $object->getActiveSheet()->setCellValueByColumnAndRow($column, 1, $field);
   $column++;
  }

  $query = "
  SELECT * FROM tbl_customer ORDER BY CustomerID LIMIT ".$start.", ".$_POST["records_no"]."
  ";
  $statement = $connect->prepare($query);
  $statement->execute();
  $excel_result = $statement->fetchAll();
  $excel_row = 2;
  foreach($excel_result as $sub_row)
  {
   $object->getActiveSheet()->setCellValueByColumnAndRow(0, $excel_row, $excel_row-1);
   $object->getActiveSheet()->setCellValueByColumnAndRow(1, $excel_row, $sub_row["CustomerName"]);
   $object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, $sub_row["Gender"]);
   $object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, $sub_row["Address"]);
   $object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $sub_row["City"]);
   $object->getActiveSheet()->setCellValueByColumnAndRow(5, $excel_row, $sub_row["PostalCode"]);
   $object->getActiveSheet()->setCellValueByColumnAndRow(6, $excel_row, $sub_row["Country"]);
   $excel_row++;
  }
  $start = $start + $_POST["records_no"];
  $object_writer = PHPExcel_IOFactory::createWriter($object, 'Excel5');
  $file_name = 'File-'.$file_number.'.xls';
  $object_writer->save($file_name);
  $download_filelink .= '<li><label><a href="download.php?filename='.$file_name.'" target="_blank">Download - '.$file_name.'</a></label></li>';
 }
 $download_filelink .= '</ul>';
}

?>
<html>
 <head>
  <title>Export Mysql Data into Multiple Excel File using PHP</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>
 </head>
 <body>
  <div class="container box">
   <h3 align="center">Export Mysql Data into Multiple Excel File using PHP</h3>
   <br />
   <br />
   <form method="post">
    <div class="row">
     <div class="col-md-3" align="right"><label>No. of Records in Each File</label></div>
     <div class="col-md-2">
      <select name="records_no" class="form-control">
       <option value="5">5 per file</option>
       <option value="10">10 per file</option>
       <option value="15">15 per file</option>
      </select> 
     </div>
     <div class="col-md-2">
      <input type="submit" name="export" class="btn btn-success" value="Export to Excel" />
     </div>
     <div class="col-md-5">
      <?php echo $download_filelink; ?>
     </div>
    </div>
   </form>
   <br />
   <div class="table-responsive">
    <table id="customer_data" class="table table-bordered table-striped">
     <thead>
      <tr>
       <th>Customer Name</th>
       <th>Gender</th>
       <th>Address</th>
       <th>City</th>
       <th>Postal Code</th>
       <th>Country</th>
      </tr>
     </thead>
     <tbody>
     <?php
     foreach($result as $row)
     {
      echo '
      <tr>
       <td>'.$row["CustomerName"].'</td>
       <td>'.$row["Gender"].'</td>
       <td>'.$row["Address"].'</td>
       <td>'.$row["City"].'</td>
       <td>'.$row["PostalCode"].'</td>
       <td>'.$row["Country"].'</td>
      </tr>
      ';
     }
     ?>
     </tbody>
    </table>
   </div>
  </div>
  <br />
  <br />
 </body>
</html>



download.php


This file script is used for download exported excel file from folder. Once user click on excel download file then this file script has been execute and it will download particular excel file.


<?php

//download.php

if(isset($_GET["filename"]))
{
 if(file_exists($_GET["filename"]))
 {
  header("Content-Type: application/octet-stream");
  header("Content-Disposition: attachment; filename=" .  $_GET["filename"]);
  readfile($_GET["filename"]);
  unlink($_GET["filename"]);
 }
 else
 {
  echo 'No File Found';
 }
}

?>


Database


Run below SQL script, it will make customer table in your Database.


--
-- Database: `testing`
--

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

--
-- Table structure for table `tbl_customer`
--

CREATE TABLE `tbl_customer` (
  `CustomerID` int(11) NOT NULL,
  `CustomerName` varchar(250) NOT NULL,
  `Gender` varchar(30) NOT NULL,
  `Address` text NOT NULL,
  `City` varchar(250) NOT NULL,
  `PostalCode` varchar(30) NOT NULL,
  `Country` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_customer`
--

INSERT INTO `tbl_customer` (`CustomerID`, `CustomerName`, `Gender`, `Address`, `City`, `PostalCode`, `Country`) VALUES
(1, 'Maria Anders', 'Female', 'Obere Str. 57', 'Berlin', '12209', 'Germany'),
(2, 'Ana Trujillo', 'Female', 'Avda. de la Construction 2222', 'Mexico D.F.', '5021', 'Mexico'),
(3, 'Antonio Moreno', 'Male', 'Mataderos 2312', 'Mexico D.F.', '5023', 'Mexico'),
(4, 'Thomas Hardy', 'Male', '120 Hanover Sq.', 'London', 'WA1 1DP', 'United Kingdom'),
(5, 'Paula Parente', 'Female', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),
(6, 'Wolski Zbyszek', 'Male', 'ul. Filtrowa 68', 'Walla', '01-012', 'Poland'),
(7, 'Matti Karttunen', 'Male', 'Keskuskatu 45', 'Helsinki', '21240', 'Finland'),
(8, 'Karl Jablonski', 'Male', '305 - 14th Ave. S. Suite 3B', 'Seattle', '98128', 'United States'),
(9, 'Paula Parente', 'Female', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),
(10, 'John Koskitalo', 'Male', 'Torikatu 38', 'Oulu', '90110', 'Finland'),
(39, 'Ann Devon', 'Female', '35 King George', 'London', 'WX3 6FW', 'United Kingdom'),
(38, 'Janine Labrune', 'Female', '67, rue des Cinquante Otages', 'Nantes', '44000', 'Finland'),
(37, 'Kathryn Segal', 'Female', 'Augsburger Strabe 40', 'Ludenscheid Gevelndorf', '58513', 'Germany'),
(36, 'Elizabeth Brown', 'Female', 'Berkeley Gardens 12 Brewery', 'London', 'WX1 6LT', 'United Kingdom'),
(30, 'Trina Davidson', 'Female', '1049 Lockhart Drive', 'Barrie', 'ON L4M 3B1', 'Canada'),
(31, 'Jeff Putnam', 'Male', 'Industrieweg 56', 'Bouvignies', '7803', 'Belgium'),
(32, 'Joyce Rosenberry', 'Female', 'Norra Esplanaden 56', 'HELSINKI', '380', 'Finland'),
(33, 'Ronald Bowne', 'Male', '2343 Shadowmar Drive', 'New Orleans', '70112', 'United States'),
(34, 'Justin Adams', 'Male', '45, rue de Lille', 'ARMENTIERES', '59280', 'France'),
(35, 'Pedro Afonso', 'Male', 'Av. dos Lusiadas, 23', 'Sao Paulo', '05432-043', 'Brazil'),
(100, 'Kathryn Segal', 'Female', 'Augsburger Strabe 40', 'Ludenscheid Gevelndorf', '58513', 'Germany'),
(101, 'Tonia Sayre', 'Female', '84 Haslemere Road', 'ECHT', 'AB32 2DY', 'United Kingdom'),
(102, 'Loretta Harris', 'Female', 'Avenida Boavista 71', 'SANTO AMARO', '4920-111', 'Portugal'),
(103, 'Sean Wong', 'Male', 'Rua Vito Bovino, 240', 'Sao Paulo-SP', '04677-002', 'Brazil'),
(104, 'Frederick Sears', 'Male', 'ul. Marysiuska 64', 'Warszawa', '04-617', 'Poland'),
(105, 'Tammy Cantrell', 'Female', 'Lukiokatu 34', 'HAMEENLINNA', '13250', 'Finland'),
(106, 'Megan Kennedy', 'Female', '1210 Post Farm Road', 'Norcross', '30071', 'United States'),
(107, 'Maria Whittaker', 'Female', 'Spresstrasse 62', 'Bielefeld Milse', '33729', 'Germany'),
(108, 'Dorothy Parker', 'Female', '32 Lairg Road', 'NEWCHURCH', 'HR5 5DR', 'United Kingdom'),
(109, 'Roger Rudolph', 'Male', 'Avenida Julio Saul Dias 78', 'PENAFIEL', '4560-470', 'Portugal'),
(110, 'Karen Metivier', 'Female', 'Rua Guimaraes Passos, 556', 'Sao Luis-MA', '65025-450', 'Brazil'),
(111, 'Charles Hoover', 'Male', 'Al. Tysiaclecia 98', 'Warszawa', '03-851', 'Poland'),
(112, 'Becky Moss', 'Female', 'Laivurinkatu 6', 'MIKKELI', '50120', 'Finland'),
(113, 'Frank Kidd', 'Male', '2491 Carson Street', 'Cincinnati', 'KY 45202', 'United States'),
(114, 'Donna Wilson', 'Female', 'Hallesches Ufer 69', 'Dettingen', '73265', 'Germany'),
(115, 'Lillian Roberson', 'Female', '36 Iolaire Road', 'NEW BARN', 'DA3 3FT', 'United Kingdom');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_customer`
--
ALTER TABLE `tbl_customer`
  ADD PRIMARY KEY (`CustomerID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_customer`
--
ALTER TABLE `tbl_customer`
  MODIFY `CustomerID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=116;



Download


Sunday, 2 December 2018

Ajax Codeigniter Product Filter with Pagination

Ajax Codeigniter Product Filter with Pagination - Part 1



Ajax Codeigniter Product Filter with Pagination - Part 2



Ajax Codeigniter Product Filter with Pagination - Part 3



Ajax Codeigniter Product Filter with Pagination - Part 4




This post is for Codeigniter web developer, because in this post we have make stylish product filter in Codeigniter framework by using Ajax jQuery. In this product filter user can filter product details by different filter which we have make by using Checkbox, and even product can be filter on two price range. For make price range filter here we have use jQuery UI slider plugin. This all operation has been done by using jQuery Ajax with Codeigniter framework.

In this Codeigniter ajax product filter we have also add pagination feature also. So, all product details will not be load on same page but it will be divided into different web page. So, it will reduce web page loading time. Here we have use Codeigniter pagination library for add pagination feature into Codeigniter product filter. If number of product load has been increase by limit to display on webpage, then in Codeigniter product filter it will automatically add pagination link. For go to one page to another page, here also we have use ajax also, so it will load another page product data without refresh of web page. So, this is Codeigniter product filter with pagination link using Ajax.

In most of the ecommerce website all product has been display with differnt product filter with pagination link. There are also for filter product data input type checkbox has been used for filter product data on different condition. Sameway for filter product data between two price, for this here also we have use jQuery UI library slider plugin. When we have slide price, and stop slide then it will fire ajax request for fetch data and display product data on web page which come between two price. These are basic functionality of Codeigniter Ajax Product filter with pagination feature. Below you can find complete source code of this tutorial.




Soruce Code


Database



--
-- Database: `testing`
--

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

--
-- Table structure for table `product`
--

CREATE TABLE `product` (
  `product_id` int(20) NOT NULL,
  `product_name` varchar(120) NOT NULL,
  `product_brand` varchar(100) NOT NULL,
  `product_price` decimal(8,2) NOT NULL,
  `product_ram` char(5) NOT NULL,
  `product_storage` varchar(50) NOT NULL,
  `product_camera` varchar(20) NOT NULL,
  `product_image` varchar(100) NOT NULL,
  `product_quantity` mediumint(5) NOT NULL,
  `product_status` enum('0','1') NOT NULL COMMENT '0-active,1-inactive'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `product`
--

INSERT INTO `product` (`product_id`, `product_name`, `product_brand`, `product_price`, `product_ram`, `product_storage`, `product_camera`, `product_image`, `product_quantity`, `product_status`) VALUES
(1, 'Honor 9 Lite (Sapphire Blue, 64 GB)  (4 GB RAM)', 'Honor', '14499.00', '4', '64', '13', 'image-1.jpeg', 10, '1'),
(2, '\r\nInfinix Hot S3 (Sandstone Black, 32 GB)  (3 GB RAM)', 'Infinix', '8999.00', '3', '32', '13', 'image-2.jpeg', 10, '1'),
(3, 'VIVO V9 Youth (Gold, 32 GB)  (4 GB RAM)', 'VIVO', '16990.00', '4', '32', '16', 'image-3.jpeg', 10, '1'),
(4, 'Moto E4 Plus (Fine Gold, 32 GB)  (3 GB RAM)', 'Moto', '11499.00', '3', '32', '8', 'image-4.jpeg', 10, '1'),
(5, 'Lenovo K8 Plus (Venom Black, 32 GB)  (3 GB RAM)', 'Lenevo', '9999.00', '3', '32', '13', 'image-5.jpg', 10, '1'),
(6, 'Samsung Galaxy On Nxt (Gold, 16 GB)  (3 GB RAM)', 'Samsung', '10990.00', '3', '16', '13', 'image-6.jpeg', 10, '1'),
(7, 'Moto C Plus (Pearl White, 16 GB)  (2 GB RAM)', 'Moto', '7799.00', '2', '16', '8', 'image-7.jpeg', 10, '1'),
(8, 'Panasonic P77 (White, 16 GB)  (1 GB RAM)', 'Panasonic', '5999.00', '1', '16', '8', 'image-8.jpeg', 10, '1'),
(9, 'OPPO F5 (Black, 64 GB)  (6 GB RAM)', 'OPPO', '19990.00', '6', '64', '16', 'image-9.jpeg', 10, '1'),
(10, 'Honor 7A (Gold, 32 GB)  (3 GB RAM)', 'Honor', '8999.00', '3', '32', '13', 'image-10.jpeg', 10, '1'),
(11, 'Asus ZenFone 5Z (Midnight Blue, 64 GB)  (6 GB RAM)', 'Asus', '29999.00', '6', '128', '12', 'image-12.jpeg', 10, '1'),
(12, 'Redmi 5A (Gold, 32 GB)  (3 GB RAM)', 'MI', '5999.00', '3', '32', '13', 'image-12.jpeg', 10, '1'),
(13, 'Intex Indie 5 (Black, 16 GB)  (2 GB RAM)', 'Intex', '4999.00', '2', '16', '8', 'image-13.jpeg', 10, '1'),
(14, 'Google Pixel 2 XL (18:9 Display, 64 GB) White', 'Google', '61990.00', '4', '64', '12', 'image-14.jpeg', 10, '1'),
(15, 'Samsung Galaxy A9', 'Samsung', '36000.00', '8', '128', '24', 'image-15.jpeg', 10, '1'),
(16, 'Lenovo A5', 'Lenovo', '5999.00', '2', '16', '13', 'image-16.jpeg', 10, '1'),
(17, 'Asus Zenfone Lite L1', 'Asus', '5999.00', '2', '16', '13', 'image-17.jpeg', 10, '1'),
(18, 'Lenovo K9', 'Lenovo', '8999.00', '3', '32', '13', 'image-18.jpeg', 10, '1'),
(19, 'Infinix Hot S3x', 'Infinix', '9999.00', '3', '32', '13', 'image-19.jpeg', 10, '1'),
(20, 'Realme 2', 'Realme', '8990.00', '4', '64', '13', 'image-20.jpeg', 10, '1'),
(21, 'Redmi Note 6 Pro', 'Redmi', '13999.00', '4', '64', '20', 'image-21.jpeg', 10, '1'),
(22, 'Realme C1', 'Realme', '7999.00', '2', '16', '15', 'image-22.jpeg', 10, '1'),
(23, 'Vivo V11', 'Vivo', '22900.00', '6', '64', '21', 'image-23.jpeg', 10, '1'),
(24, 'Oppo F9 Pro', 'Oppo', '23990.00', '6', '64', '18', 'image-24.jpg', 10, '1'),
(25, 'Honor 9N', 'Honor', '11999.00', '4', '64', '15', 'image-25.jpg', 10, '1'),
(26, 'Redmi 6A', 'Redmi', '6599.00', '2', '16', '13', 'image-26.jpeg', 10, '1'),
(27, 'InFocus Vision 3', 'InFocus', '7399.00', '2', '16', '13', 'image-27.jpeg', 10, '1'),
(28, 'Vivo Y69', 'Vivo', '11390.00', '3', '32', '16', 'image-28.jpeg', 10, '1'),
(29, 'Honor 7x', 'Honor', '12721.00', '4', '32', '18', 'image-29.jpeg', 10, '1'),
(30, 'Nokia 2.1', 'Nokia', '6580.00', '2', '1', '8', 'image-30.jpeg', 10, '1');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `product`
--
ALTER TABLE `product`
  ADD PRIMARY KEY (`product_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `product`
--
ALTER TABLE `product`
  MODIFY `product_id` int(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;



Controllers - Product_filter.php



<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Product_filter extends CI_Controller {
 
 public function __construct()
 {
  parent::__construct();
  $this->load->model('product_filter_model');
 }

 function index()
 {
  $data['brand_data'] = $this->product_filter_model->fetch_filter_type('product_brand');
  $data['ram_data'] = $this->product_filter_model->fetch_filter_type('product_ram');
  $data['product_storage'] = $this->product_filter_model->fetch_filter_type('product_storage');
  $this->load->view('product_filter', $data);
 }

 function fetch_data()
 {
  sleep(1);
  $minimum_price = $this->input->post('minimum_price');
  $maximum_price = $this->input->post('maximum_price');
  $brand = $this->input->post('brand');
  $ram = $this->input->post('ram');
  $storage = $this->input->post('storage');
  $this->load->library('pagination');
  $config = array();
  $config['base_url'] = '#';
  $config['total_rows'] = $this->product_filter_model->count_all($minimum_price, $maximum_price, $brand, $ram, $storage);
  $config['per_page'] = 8;
  $config['uri_segment'] = 3;
  $config['use_page_numbers'] = TRUE;
  $config['full_tag_open'] = '<ul class="pagination">';
  $config['full_tag_close'] = '</ul>';
  $config['first_tag_open'] = '<li>';
  $config['first_tag_close'] = '</li>';
  $config['last_tag_open'] = '<li>';
  $config['last_tag_close'] = '</li>';
  $config['next_link'] = '&gt;';
  $config['next_tag_open'] = '<li>';
  $config['next_tag_close'] = '</li>';
  $config['prev_link'] = '&lt;';
  $config['prev_tag_open'] = '<li>';
  $config['prev_tag_close'] = '</li>';
  $config['cur_tag_open'] = "<li class='active'><a href='#'>";
  $config['cur_tag_close'] = '</a></li>';
  $config['num_tag_open'] = '<li>';
  $config['num_tag_close'] = '</li>';
  $config['num_links'] = 3;
  $this->pagination->initialize($config);
  $page = $this->uri->segment(3);
  $start = ($page - 1) * $config['per_page'];
  $output = array(
   'pagination_link'  => $this->pagination->create_links(),
   'product_list'   => $this->product_filter_model->fetch_data($config["per_page"], $start, $minimum_price, $maximum_price, $brand, $ram, $storage)
  );
  echo json_encode($output);
 }
  
}
?>


Models - Product_filter_model.php



<?php

class Product_filter_model extends CI_Model
{
 function fetch_filter_type($type)
 {
  $this->db->distinct();
  $this->db->select($type);
  $this->db->from('product');
  $this->db->where('product_status', '1');
  return $this->db->get();
 }

 function make_query($minimum_price, $maximum_price, $brand, $ram, $storage)
 {
  $query = "
  SELECT * FROM product 
  WHERE product_status = '1' 
  ";

  if(isset($minimum_price, $maximum_price) && !empty($minimum_price) &&  !empty($maximum_price))
  {
   $query .= "
    AND product_price BETWEEN '".$minimum_price."' AND '".$maximum_price."'
   ";
  }

  if(isset($brand))
  {
   $brand_filter = implode("','", $brand);
   $query .= "
    AND product_brand IN('".$brand_filter."')
   ";
  }

  if(isset($ram))
  {
   $ram_filter = implode("','", $ram);
   $query .= "
    AND product_ram IN('".$ram_filter."')
   ";
  }

  if(isset($storage))
  {
   $storage_filter = implode("','", $storage);
   $query .= "
    AND product_storage IN('".$storage_filter."')
   ";
  }
  return $query;
 }

 function count_all($minimum_price, $maximum_price, $brand, $ram, $storage)
 {
  $query = $this->make_query($minimum_price, $maximum_price, $brand, $ram, $storage);
  $data = $this->db->query($query);
  return $data->num_rows();
 }

 function fetch_data($limit, $start, $minimum_price, $maximum_price, $brand, $ram, $storage)
 {
  $query = $this->make_query($minimum_price, $maximum_price, $brand, $ram, $storage);

  $query .= ' LIMIT '.$start.', ' . $limit;

  $data = $this->db->query($query);

  $output = '';
  if($data->num_rows() > 0)
  {
   foreach($data->result_array() as $row)
   {
    $output .= '
    <div class="col-sm-4 col-lg-3 col-md-3">
     <div style="border:1px solid #ccc; border-radius:5px; padding:16px; margin-bottom:16px; height:450px;">
      <img src="'.base_url().'images/'. $row['product_image'] .'" alt="" class="img-responsive" >
      <p align="center"><strong><a href="#">'. $row['product_name'] .'</a></strong></p>
      <h4 style="text-align:center;" class="text-danger" >'. $row['product_price'] .'</h4>
      <p>Camera : '. $row['product_camera'].' MP<br />
      Brand : '. $row['product_brand'] .' <br />
      RAM : '. $row['product_ram'] .' GB<br />
      Storage : '. $row['product_storage'] .' GB </p>
     </div>
    </div>
    ';
   }
  }
  else
  {
   $output = '<h3>No Data Found</h3>';
  }
  return $output;
 }
}

?>




Views - product_filter.php



<!DOCTYPE html>
<html lang="en">

<head>

    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta name="description" content="">
    <meta name="author" content="">

    <title>Product Filters in Codeigniter using Ajax</title>

    <!-- Bootstrap Core CSS -->
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
 <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.min.js"></script>
 <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
   <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
  <link href = "<?php echo base_url(); ?>asset/jquery-ui.css" rel = "stylesheet">
    <!-- Custom CSS -->
    <link href="<?php echo base_url(); ?>asset/style.css" rel="stylesheet">
</head>

<body>
    <!-- Page Content -->
    <div class="container">
        <div class="row">
            <div class="col-md-3">
    <br />
    <br />
    <br />
    <div class="list-group">
     <h3>Price</h3>
     <input type="hidden" id="hidden_minimum_price" value="0" />
                    <input type="hidden" id="hidden_maximum_price" value="65000" />
                    <p id="price_show">1000 - 65000</p>
                    <div id="price_range"></div>
                </div>    
                <div class="list-group">
     <h3>Brand</h3>
     <?php
                    foreach($brand_data->result_array() as $row)
                    {
                    ?>
                    <div class="list-group-item checkbox">
                        <label><input type="checkbox" class="common_selector brand" value="<?php echo $row['product_brand']; ?>"  > <?php echo $row['product_brand']; ?></label>
                    </div>
                    <?php
                    }
                    ?>
                </div>

    <div class="list-group">
     <h3>RAM</h3>
     <?php
                    foreach($ram_data->result_array() as $row)
                    {
                    ?>
                    <div class="list-group-item checkbox">
                        <label><input type="checkbox" class="common_selector ram" value="<?php echo $row['product_ram']; ?>" > <?php echo $row['product_ram']; ?> GB</label>
                    </div>
                    <?php
                    }
                    ?> 
                </div>
    
    <div class="list-group">
     <h3>Internal Storage</h3>
     <?php
                    foreach($product_storage->result_array() as $row)
                    {
                    ?>
                    <div class="list-group-item checkbox">
                        <label><input type="checkbox" class="common_selector storage" value="<?php echo $row['product_storage']; ?>"  > <?php echo $row['product_storage']; ?> GB</label>
                    </div>
                    <?php
                    }
                    ?> 
                </div>
            </div>

            <div class="col-md-9">
             <h2 align="center">Product Filters in Codeigniter using Ajax</h2>
    <br />
    <div align="center" id="pagination_link">

                </div>
    <br />
    <br />
    <br />
                <div class="row filter_data">

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

    </div>
<style>
#loading
{
 text-align:center; 
 background: url('<?php echo base_url(); ?>asset/loader.gif') no-repeat center; 
 height: 150px;
}
</style>

<script>
$(document).ready(function(){

    filter_data(1);

    function filter_data(page)
    {
        $('.filter_data').html('<div id="loading" style="" ></div>');
        var action = 'fetch_data';
        var minimum_price = $('#hidden_minimum_price').val();
        var maximum_price = $('#hidden_maximum_price').val();
        var brand = get_filter('brand');
        var ram = get_filter('ram');
        var storage = get_filter('storage');
        $.ajax({
            url:"<?php echo base_url(); ?>product_filter/fetch_data/"+page,
            method:"POST",
            dataType:"JSON",
            data:{action:action, minimum_price:minimum_price, maximum_price:maximum_price, brand:brand, ram:ram, storage:storage},
            success:function(data)
            {
                $('.filter_data').html(data.product_list);
                $('#pagination_link').html(data.pagination_link);
            }
        })
    }

    function get_filter(class_name)
    {
        var filter = [];
        $('.'+class_name+':checked').each(function(){
            filter.push($(this).val());
        });
        return filter;
    }

    $(document).on('click', '.pagination li a', function(event){
        event.preventDefault();
        var page = $(this).data('ci-pagination-page');
        filter_data(page);
    });

    $('.common_selector').click(function(){
        filter_data(1);
    });

    $('#price_range').slider({
        range:true,
        min:1000,
        max:65000,
        values:[1000,65000],
        step:500,
        stop:function(event, ui)
        {
            $('#price_show').html(ui.values[0] + ' - ' + ui.values[1]);
            $('#hidden_minimum_price').val(ui.values[0]);
            $('#hidden_maximum_price').val(ui.values[1]);
            filter_data(1);
        }

    });

});
</script>

</body>

</html>

Sunday, 25 November 2018

Image Crop and Save into Database using PHP with Ajax



Do you know How to Store or Insert Crop image into Mysql database with PHP script. If you looking such type of tutorial, then you have come on best post. In this post you can learn How to crop an Image by using Croppie javascript plugin and then after that croped image has been inserted into mysql database by using PHP script with Ajax and Bootstrap modal. In one of our post like Image Crop and Upload into folder using javascript plugin with PHP and Ajax, in that post you can find before upload of image, it has been crop by using javascript Croppie plugin, and after crop of image it will store into folder using Ajax with PHP.

Why we have to store image into Mysql database in place of store image info folder, there is one benefit is that if we have store image into database then it has been load faster than image load from folder. There is one another benifit is that image which are store in database is not cache in browser, while image load from folder has been easily cache in browser. There are some more other benifit for storing image into database. So, Here we have do two operation like first we have crop image by using jQuery plugin, and after crop image has been inserted into mysql database.

Now we have seen whole process of crop image and store into mysql database. When first we have select image, then Croppie plugin has been initialize, and by using this plugin we can zoom image, and as per our requirement we can crop image. Once image has been crop then this plugin has convert crop image into binary string format. By using base64_decode() function we decode binary string into image, and by using file_put_contents() we have create under local folder. Once Image has been created and under store under folder, we ahve convert into binary string format by using file_get_contents() and addslashes() function. Then after run insert query and store into database. Once crop image has been store into database by using unlink() function, it will be remove from local folder also. This is whole process of Image crop and Save into Mysql database by using javascript croppie plugin with PHP and Ajax. Below you can find complete source code of this tutorial.






Source Code


Database



--
-- Database: `testing`
--

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

--
-- Table structure for table `tbl_images`
--

CREATE TABLE `tbl_images` (
  `image_id` int(11) NOT NULL,
  `images` longblob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_images`
--
ALTER TABLE `tbl_images`
  ADD PRIMARY KEY (`image_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_images`
--
ALTER TABLE `tbl_images`
  MODIFY `image_id` int(11) NOT NULL AUTO_INCREMENT;


index.php



<html>  
    <head>  
        <title>Image Crop and Save into Database using PHP with Ajax</title>  
  
  <script src="jquery.min.js"></script>  
  <script src="bootstrap.min.js"></script>
  <script src="croppie.js"></script>
  <link rel="stylesheet" href="bootstrap.min.css" />
  <link rel="stylesheet" href="croppie.css" />
    </head>  
    <body>  
        <div class="container">
          <br />
      <h3 align="center">Image Crop and Save into Database using PHP with Ajax</h3>
      <br />
      <br />
   <div class="panel panel-default">
      <div class="panel-heading">Select Profile Image</div>
      <div class="panel-body" align="center">
       <input type="file" name="insert_image" id="insert_image" accept="image/*" />
       <br />
       <div id="store_image"></div>
      </div>
     </div>
    </div>
    </body>  
</html>

<div id="insertimageModal" class="modal" role="dialog">
 <div class="modal-dialog">
  <div class="modal-content">
      <div class="modal-header">
        <button type="button" class="close" data-dismiss="modal">&times;</button>
        <h4 class="modal-title">Crop & Insert Image</h4>
      </div>
      <div class="modal-body">
        <div class="row">
          <div class="col-md-8 text-center">
            <div id="image_demo" style="width:350px; margin-top:30px"></div>
          </div>
          <div class="col-md-4" style="padding-top:30px;">
        <br />
        <br />
        <br/>
            <button class="btn btn-success crop_image">Crop & Insert Image</button>
          </div>
        </div>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
      </div>
    </div>
  </div>
</div>

<script>  
$(document).ready(function(){

 $image_crop = $('#image_demo').croppie({
    enableExif: true,
    viewport: {
      width:200,
      height:200,
      type:'square' //circle
    },
    boundary:{
      width:300,
      height:300
    }    
  });

  $('#insert_image').on('change', function(){
    var reader = new FileReader();
    reader.onload = function (event) {
      $image_crop.croppie('bind', {
        url: event.target.result
      }).then(function(){
        console.log('jQuery bind complete');
      });
    }
    reader.readAsDataURL(this.files[0]);
    $('#insertimageModal').modal('show');
  });

  $('.crop_image').click(function(event){
    $image_crop.croppie('result', {
      type: 'canvas',
      size: 'viewport'
    }).then(function(response){
      $.ajax({
        url:'insert.php',
        type:'POST',
        data:{"image":response},
        success:function(data){
          $('#insertimageModal').modal('hide');
          load_images();
          alert(data);
        }
      })
    });
  });

  load_images();

  function load_images()
  {
    $.ajax({
      url:"fetch_images.php",
      success:function(data)
      {
        $('#store_image').html(data);
      }
    })
  }

});  
</script>





database_connection.php



<?php

//database_connection.php

$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");

?>


insert.php



<?php

//insert.php

if(isset($_POST["image"]))
{
 include('database_connection.php');

 $data = $_POST["image"];

 $image_array_1 = explode(";", $data);

 $image_array_2 = explode(",", $image_array_1[1]);

 $data = base64_decode($image_array_2[1]);

 $imageName = time() . '.png';

 file_put_contents($imageName, $data);

 $image_file = addslashes(file_get_contents($imageName));

 $query = "INSERT INTO tbl_images(images) VALUES ('".$image_file."')";

 $statement = $connect->prepare($query);

 if($statement->execute())
 {
  echo 'Image save into database';
  unlink($imageName);
 }

}

?>


fetch_images.php



<?php

//fetch_images.php

include('database_connection.php');

$query = "SELECT * FROM tbl_images ORDER BY image_id DESC";

$statement = $connect->prepare($query);

$output = '<div class="row">';

if($statement->execute())
{
 $result = $statement->fetchAll();

 foreach($result as $row)
 {
  $output .= '
  <div class="col-md-2" style="margin-bottom:16px;">
   <img src="data:image/png;base64,'.base64_encode($row['images']).'" class="img-thumbnail" />
  </div>
  ';
 }
}

$output .= '</div>';

echo $output;

?>

Thursday, 22 November 2018

How to Delete Multiple Records using Checkbox in Codeigniter



This post covered topic on Codeigniter tutorial for beginner step by step, and in this post we have shared simple feature like How to Delete multiple data from Mysql table using Checkbox in Codeigniter framework with Ajax. For do this operation we will use jQuery Ajax and HTML Check boxes input fields for delete multiple rows of mysql data in Codeigniter. Mainly we have always put delete button at individual row for delete single data. But if we have large amount of data, from that data we want to remove many unwanted rows of data at the same time. Then at that time this functionality will helpful. This is because if we have remove single data one by one then it will take much more time, and by using this feature we can delete bulk data at the same time.

If we have use Codeigniter framework for our web development, then we will make any large application by using this framework. So, if application is large, then in that application data will be bulk of amount. So, If we have made this functionality in our application then it will reduce our work time, and it will increase efficiency of our web application. For make this feature we have use jQuery, Ajax and HTML check boxes. HTML check boxes used for select multiple row of data by selecting checkbox. Ajax used for send and received request to server. By using Ajax with Codeigniter we can delete multiple or bulk of mysql data without refresh of web page. jQuery is used for give animation effect at the time of delete of multiple data.

For discuss deleting of multiple data in Codeigniter using Checkbox with Ajax, here we have first fetch data from mysql database with delete button and checkbox selection. When used checked checkbox, then that row background color and font color will be change. For multiple delete operation we have define one button for delete action. Once we have click on delete button, by using jQuery code first we have store id of data in one local variable, and then after send ajax request to Codeigniter controller with array of id data store in local variable. At server side it will delete multiple records one by one, and on client side, user can see deleting of multiple data with animated effect. Below you can find complete source code of How to Delete Multiple Records using Checkbox in Codeigniter.








Source Code


Step 1 - Database


Run following sql script in your phpMyAdmin, this script will make tbl_employee in your database.


--
-- Database: `testing`
--

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

--
-- Table structure for table `tbl_employee`
--

CREATE TABLE `tbl_employee` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `address` text NOT NULL,
  `gender` varchar(10) NOT NULL,
  `designation` varchar(100) NOT NULL,
  `age` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_employee`
--

INSERT INTO `tbl_employee` (`id`, `name`, `address`, `gender`, `designation`, `age`) VALUES
(5, 'Clara Gilliam', '63 Woodridge Lane\r\nMemphis, TN 38138', 'Female', 'Programmer', 24),
(6, 'Barbra K. Hurley', '1241 Canis Heights Drive\r\nLos Angeles, CA 90017', 'Female', 'Service technician', 26),
(7, 'Antonio J. Forbes', '403 Snyder Avenue\r\nCharlotte, NC 28208', 'Male', 'Faller', 32),
(8, 'Charles D. Horst', '1636 Walnut Hill Drive\r\nCincinnati, OH 45202', 'Male', 'Financial investigator', 29),
(174, 'Martha B. Tomlinson', '4005 Bird Spring Lane, Houston, TX 77002', 'Female', 'Systems programmer', 38),
(162, 'Jarrod D. Jones', '3827 Bingamon Road, Garfield Heights, OH 44125', 'Male', 'Manpower development advisor', 64),
(177, 'Patricia L. Scott', '1584 Dennison Street\r\nModesto, CA 95354', 'Female', 'Urban and regional planner', 54),
(181, 'Kimberly J. Ellis', '4905 Holt Street\r\nFort Lauderdale, FL 33301', 'Male', 'Dressing room attendant', 24),
(183, 'Steve John', '108, Vile Parle, CL', 'Male', 'Software Engineer', 29),
(186, 'Louis C. Charmis', '1462 Juniper Drive\r\nBreckenridge, MI 48612', 'Male', 'Mental health counselor', 40),
(190, 'Michael Cobb', '2409 Patterson Fork Road, Westchester, IL 60154', 'Female', 'Personal secretary', 36);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_employee`
--
ALTER TABLE `tbl_employee`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_employee`
--
ALTER TABLE `tbl_employee`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=197;


Step 2 - Multiple_delete.php(Controllers)


Once database is ready, next step we have to create controller in our Codeigniter application. So, here we have create Multiple_delete.php controller in application/controllers folder.

In this controller class we have two method like index() and delete_all() method. index() method is used for fetch data from mysql database and display on web page, while delete_all() method is used for received ajax request for delte multiple data.


<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Multiple_delete extends CI_Controller {
 
 public function __construct()
 {
  parent::__construct();
  $this->load->model('multiple_delete_model');
 }

 function index()
 {
  $data['employee_data'] = $this->multiple_delete_model->fetch_data();
  $this->load->view('multiple_delete', $data);
 }

 function delete_all()
 {
  if($this->input->post('checkbox_value'))
  {
   $id = $this->input->post('checkbox_value');
   for($count = 0; $count < count($id); $count++)
   {
    $this->multiple_delete_model->delete($id[$count]);
   }
  }
 }
  
}
?>


Step 3 - Multiple_delete_model.php (Models)


Generally, In Codeigniter models file is used for do any database operation. So, here also we have make Multiple_delete_model.php model file under application/models folder for database operation. In this file also there are two method like fetch_data() for fetch data from employee table and delete() method for delete data from mysql database operation.


<?php

class Multiple_delete_model extends CI_Model
{
 function fetch_data()
 {
  $this->db->select("*");
  $this->db->from("tbl_employee");
  $this->db->order_by('id', 'desc');
  return $this->db->get();
 }

 function delete($id)
 {
  $this->db->where('id', $id);
  $this->db->delete('tbl_employee');
 }
}

?>





Step 4 - multiple_delete.php (Views)


In Codeigniter Views file is used for display output on webpage and received any request for client. Here we have make multiple_delete.php view file under application/views folder. This view file first display employee table data in html table formate. And from this view file user can give request to delete multiple records using Ajax.


<html>
<head>
    <title>Delete Multiple Data using Checkboxs in Codeigniter 3 with Ajax</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.1.0/jquery.min.js"></script>
</head>
<body>
<div class="container">
 <br /><br /><br />
 <h3 align="center">Delete Multiple Data using Checkboxs in Codeigniter 3 with Ajax</h3><br />
 
 <div class="table-responsive">
  <table class="table table-bordered">
   <thead>
    <tr>
     <th width="5%"><button type="button" name="delete_all" id="delete_all" class="btn btn-danger btn-xs">Delete</button></th>
     <th width="20%">Name</th>
     <th width="38%">Address</th>
     <th width="7%">Gender</th>
     <th width="25%">Designation</th>
     <th width="5%">Age</th>
    </tr>
   </thead>
   <tbody>
   <?php
   foreach($employee_data->result() as $row)
   {
    echo '
    <tr>
     <td><input type="checkbox" class="delete_checkbox" value="'.$row->id.'" /></td>
     <td>'.$row->name.'</td>
     <td>'.$row->address.'</td>
     <td>'.$row->gender.'</td>
     <td>'.$row->designation.'</td>
     <td>'.$row->age.'</td>
    </tr>
    ';
   }
   ?>
   </tbody>
  </table>
    </div>
</body>
</html>
<style>
.removeRow
{
 background-color: #FF0000;
    color:#FFFFFF;
}
</style>
<script>
$(document).ready(function(){
 
 $('.delete_checkbox').click(function(){
  if($(this).is(':checked'))
  {
   $(this).closest('tr').addClass('removeRow');
  }
  else
  {
   $(this).closest('tr').removeClass('removeRow');
  }
 });

 $('#delete_all').click(function(){
  var checkbox = $('.delete_checkbox:checked');
  if(checkbox.length > 0)
  {
   var checkbox_value = [];
   $(checkbox).each(function(){
    checkbox_value.push($(this).val());
   });
   $.ajax({
    url:"<?php echo base_url(); ?>multiple_delete/delete_all",
    method:"POST",
    data:{checkbox_value:checkbox_value},
    success:function()
    {
     $('.removeRow').fadeOut(1500);
    }
   })
  }
  else
  {
   alert('Select atleast one records');
  }
 });

});
</script>


Once you have follow video tutorial and above source code, then you can make Codeigniter application in which you can delete multiple records using checkbox selection with Ajax jQuery and Mysql.

Saturday, 17 November 2018

Laravel Searching Column Sorting with Pagination using Ajax

Laravel Sorting with Pagination using Ajax




Laravel Searching with Sorting with Pagination using Ajax




If you are beginner level in Laravel, and if you want to learn How to use Ajax with Laravel, then you have come on right place because in this post you can find How to Live data search with Sort table column data in ascending or descending order and Pagination in Laravel framework by using Ajax.  We have always try to publish something new post which will help you to learn something new. So, here we have publish new article on Laravel with Ajax and make simple mysql table data live searching, table column sorting data in Ascending or descending order with pagination link. This is amazing tutorial of Laravel tutorial series with Ajax and here we have make search filter with sortable table and pagination link. Which is one part of any crud application. So, by using this tutorial you can make simple application in which can live search mysql data, sort tabular data and pagination link in Laravel framework using Ajax. In short we will do column sorting with Live data search in Laravel using Ajax.

We all know by using Data sorting and searching is important for find quick and perfect data in a very short time. Here we will take a live example of data sorting in ascending or descending order with pagination. Here we will not only implement tabular data sorting but also we will also make pagination link with column sorting in Laravel using Ajax. We will discuss step by step this ascending or descending column sorting of data with pagination using Ajax and Laravel. This tutorial will show you how to create data sorting with pagination operation without refresh of web page with Laravel jQuery and Ajax.





Step 1 : Instal Laravel Application


Here we have start from scratch. So, If you have not install Laravel application, then you have to open command prompt, and run composert command, and then after you can run below command this command will download Laravel application, and install at define destination.


composer create-project --prefer-dist laravel/laravel student_crud


Step 2 - Database Connection


Once Laravel application has been installed successfully, then first you have to make database connection. For make database connection you have to .env file and define following database configuration.


DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=test
DB_USERNAME=root
DB_PASSWORD=


Step 3 - Create PaginationController (Controller)


After making database connection, we have to make one PaginationController file under app/Http/Controllers/PaginationController.php. This controller will handle http request for display data on web page with data sorting and pagination request.


<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use DB;

class PaginationController extends Controller
{
    function index()
    {
     $data = DB::table('post')->orderBy('id', 'asc')->paginate(5);
     return view('pagination', compact('data'));
    }

    function fetch_data(Request $request)
    {
     if($request->ajax())
     {
      $sort_by = $request->get('sortby');
      $sort_type = $request->get('sorttype');
            $query = $request->get('query');
            $query = str_replace(" ", "%", $query);
      $data = DB::table('post')
                    ->where('id', 'like', '%'.$query.'%')
                    ->orWhere('post_title', 'like', '%'.$query.'%')
                    ->orWhere('post_description', 'like', '%'.$query.'%')
                    ->orderBy($sort_by, $sort_type)
                    ->paginate(5);
      return view('pagination_data', compact('data'))->render();
     }
    }
}
?>


Step 4 - Create View file


For display data on web page, in Laravel we have to create view file in resources/views folder. In this folder we have to create two view files like pagination.blade.php and pagination_data.blade.php.

In pagination.blade.php you can find complete html jQuery and ajax code for data sorting and pagination. In this file we have include pagination_data.blade.php file data by using @include statement. Below you can find source code of both view files.




resources/views/pagination.blade.php

<!DOCTYPE html>
<html>
 <head>
  <title>Laravel Live Data Search with Sorting & Pagination using Ajax</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.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.7/js/bootstrap.min.js"></script>
 </head>
 <body>
  <br />
  <div class="container">
   <h3 align="center">Laravel Live Data Search with Sorting & Pagination using Ajax</h3><br />
   <div class="row">
    <div class="col-md-9">

    </div>
    <div class="col-md-3">
     <div class="form-group">
      <input type="text" name="serach" id="serach" class="form-control" />
     </div>
    </div>
   </div>
   <div class="table-responsive">
    <table class="table table-striped table-bordered">
     <thead>
      <tr>
       <th width="5%" class="sorting" data-sorting_type="asc" data-column_name="id" style="cursor: pointer">ID <span id="id_icon"></span></th>
       <th width="38%" class="sorting" data-sorting_type="asc" data-column_name="post_title" style="cursor: pointer">Title <span id="post_title_icon"></span></th>
       <th width="57%">Description</th>
      </tr>
     </thead>
     <tbody>
      @include('pagination_data')
     </tbody>
    </table>
    <input type="hidden" name="hidden_page" id="hidden_page" value="1" />
    <input type="hidden" name="hidden_column_name" id="hidden_column_name" value="id" />
    <input type="hidden" name="hidden_sort_type" id="hidden_sort_type" value="asc" />
   </div>
  </div>
 </body>
</html>

<script>
$(document).ready(function(){

 function clear_icon()
 {
  $('#id_icon').html('');
  $('#post_title_icon').html('');
 }

 function fetch_data(page, sort_type, sort_by, query)
 {
  $.ajax({
   url:"/pagination/fetch_data?page="+page+"&sortby="+sort_by+"&sorttype="+sort_type+"&query="+query,
   success:function(data)
   {
    $('tbody').html('');
    $('tbody').html(data);
   }
  })
 }

 $(document).on('keyup', '#serach', function(){
  var query = $('#serach').val();
  var column_name = $('#hidden_column_name').val();
  var sort_type = $('#hidden_sort_type').val();
  var page = $('#hidden_page').val();
  fetch_data(page, sort_type, column_name, query);
 });

 $(document).on('click', '.sorting', function(){
  var column_name = $(this).data('column_name');
  var order_type = $(this).data('sorting_type');
  var reverse_order = '';
  if(order_type == 'asc')
  {
   $(this).data('sorting_type', 'desc');
   reverse_order = 'desc';
   clear_icon();
   $('#'+column_name+'_icon').html('<span class="glyphicon glyphicon-triangle-bottom"></span>');
  }
  if(order_type == 'desc')
  {
   $(this).data('sorting_type', 'asc');
   reverse_order = 'asc';
   clear_icon
   $('#'+column_name+'_icon').html('<span class="glyphicon glyphicon-triangle-top"></span>');
  }
  $('#hidden_column_name').val(column_name);
  $('#hidden_sort_type').val(reverse_order);
  var page = $('#hidden_page').val();
  var query = $('#serach').val();
  fetch_data(page, reverse_order, column_name, query);
 });

 $(document).on('click', '.pagination a', function(event){
  event.preventDefault();
  var page = $(this).attr('href').split('page=')[1];
  $('#hidden_page').val(page);
  var column_name = $('#hidden_column_name').val();
  var sort_type = $('#hidden_sort_type').val();

  var query = $('#serach').val();

  $('li').removeClass('active');
        $(this).parent().addClass('active');
  fetch_data(page, sort_type, column_name, query);
 });

});
</script>


resources/views/pagination_data.blade.php

                                               @foreach($data as $row)
      <tr>
       <td>{{ $row->id}}</td>
       <td>{{ $row->post_title }}</td>
       <td>{{ $row->post_description }}</td>
      </tr>
      @endforeach
      <tr>
       <td colspan="3" align="center">
        {!! $data->links() !!}
       </td>
      </tr>


Step 5 - Set Route


Lastly we have to set route for controller and method which we have make under controller. For this we have to go to routes/web.php file and write following code for set route.


<?php

Route::get('/pagination', 'PaginationController@index');

Route::get('/pagination/fetch_data', 'PaginationController@fetch_data');

?>


Once you have completely follow this step then you can make data sorting and pagination in Laravel with Ajax. For run laravel application, you have to run following command.


php artisan serve


After run this command, then you will receive message like Laravel server has been start with this link - http://localhost:8000/. For run this application you have to type following link in your browser.


http://localhost:8000/pagination