Friday, 16 October 2020

How to Convert CSV to JSON in PHP


This is simple and short tutorial on How do we convert CSV file data into JSON format in PHP. In this world of web programming, sometimes we have to make feature like fetch data from CSV file and insert that CSV file data into Mysql database then at that time we have need to convert CSV to JSON. The main purpose of this tutorial is to learn how can we easy way to convert CSV data or file to JSON object using PHP script.

Advantages of CSV


  1. CSV files can be opened or edited by text editors like notepad.
  2. In data-warehouse, CSV follows a reasonably flat, simple schema.
  3. Any programing language to parse CSV data is trivial, generating it's extremely easy.
  4. CSV is safe and may clearly differentiate between the numeric values and text. CSV doesn't manipulate data and stores it as-is.
  5. In CSV, you write column headers just one occasion where as in Excel, you've got to possess a start tag and end tag for every column in each row.
  6. Importing CSV files are often much faster, and it also consumes less memory.
  7. It's easy to programmatically manipulate CSV since, after all, they're simple text files.
  8. CSV are often opened with any text editor in Windows like notepad, MS Excel, Microsoft Works 9, etc.

Advantages of JSON


  1. JSON is a portable that means it can be parse and write are available in many programming languages like JavaScript.
  2. By using JSON we can online transfer large data from source to destination in the form of array or objects.
  3. IN JSON we can transfer data in plain text or unformatted data like comma speratted or delimited data.
  4. JSON is better that XML, this is because it has more sufficient space than XML data structure.
  5. JSON has no tag name and it has structure which is nested braces instead of verbose tags.
  6. In exchange of data JSON is faster than XML.


So here we have seen benefits of CSV and JSON data and both are mainly used for data exchange. But if you have work on any web services than JSON data is mostly used for transmit or exchange of data on the web. So if you have data in CSV file and you have to exchange that data then you must convert that data into JSON format and you can easily exchange data online.

In PHP language there many functions are available for parse or read data from CSV file and then convert that data into PHP array and lastly for convert data into JSON then we can use json_encode() function.



Sample Input CSV Data



Company Name,Address,City,State,ZIP/PIN CODE,Country,Website,Industry,First Name,Last Name,Phone Number,Designation,Email
Cougar Investment,3570 Thunder Road,Mountain View,CA,94041,USA,dealtechs.com,Animal care and service worker,Devin,Taylor,650-564-1816,Animal care and service worker,DevinRTaylor@armyspy.com
Robert Hall,1729 Lyndon Street,Slatington,PA,18080,USA,toksalgida.com,Media,Ruby,Gibson,610-767-5251,News anchor,RubyBGibson@armyspy.com



index.php



<?php

$error = '';

if(isset($_POST["upload_file"]))
{
  if($_FILES['file']['name'])
  {
    $file_array = explode(".", $_FILES['file']['name']);

    $file_name = $file_array[0];

    $extension = end($file_array);

    if($extension == 'csv')
    {
      $column_name = array();

      $final_data = array();

      $file_data = file_get_contents($_FILES['file']['tmp_name']);

      $data_array = array_map("str_getcsv", explode("\n", $file_data));

      $labels = array_shift($data_array);

      foreach($labels as $label)
      {
        $column_name[] = $label;
      }

      $count = count($data_array) - 1;

      for($j = 0; $j < $count; $j++)
      {
        $data = array_combine($column_name, $data_array[$j]);

        $final_data[$j] = $data;
      }

      header('Content-disposition: attachment; filename='.$file_name.'.json');

      header('Content-type: application/json');

      echo json_encode($final_data);

      exit;
    }
    else
    {
      $error = 'Only <b>.csv</b> file allowed';
    }
  }
  else
  {
    $error = 'Please Select CSV File';
  }
}

?>

<!DOCTYPE html>
<html>
  	<head>
    	<title>Convert CSV to JSON using PHP</title>
    	<meta name="viewport" content="width=device-width, initial-scale=1.0">
      <script src="http://code.jquery.com/jquery.js"></script>
    	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  	</head>
  	<body>
  		<div class="container">
  			<br />
  			<br />
    		<h1 align="center">Convert CSV to JSON using PHP</h1>
    		<br />
    		<div class="panel panel-default">
          <div class="panel-heading">
            <h3 class="panel-title">Select CSV File</h3>
          </div>
          <div class="panel-body">
            <?php
            if($error != '')
            {
              echo '<div class="alert alert-danger">'.$error.'</div>';
            }
            ?>
            <form method="post" enctype="multipart/form-data">
              <div class="col-md-6" align="right">Select File</div>
              <div class="col-md-6">
                <input type="file" name="file" />
              </div>
              <br /><br /><br />
              <div class="col-md-12" align="center">
                <input type="submit" name="upload_file" class="btn btn-primary" value="Upload" />
              </div>
            </form>
          </div>
        </div>
    	</div>
    	
  	</body>
</html>



Output JSON Data



[
  {
    "Company Name": "Cougar Investment",
    "Address": "3570 Thunder Road",
    "City": "Mountain View",
    "State": "CA",
    "ZIP/PIN CODE": "94041",
    "Country": "USA",
    "Website": "dealtechs.com",
    "Industry": "Animal care and service worker",
    "First Name": "Devin",
    "Last Name": "Taylor",
    "Phone Number": "650-564-1816",
    "Designation": "Animal care and service worker",
    "Email": "DevinRTaylor@armyspy.com"
  },
  {
    "Company Name": "Robert Hall",
    "Address": "1729 Lyndon Street",
    "City": "Slatington",
    "State": "PA",
    "ZIP/PIN CODE": "18080",
    "Country": "USA",
    "Website": "toksalgida.com",
    "Industry": "Media",
    "First Name": "Ruby",
    "Last Name": "Gibson",
    "Phone Number": "610-767-5251",
    "Designation": "News anchor",
    "Email": "RubyBGibson@armyspy.com"
  }
]



Tuesday, 13 October 2020

How to Implement Google reCaptcha in Codeigniter



This post will covered How to add Google re Captcha in Codeigniter application or in another word How to validate Codeigniter Form data by using Google reCaptcha validation. This is simple post on Integration of Google reCaptcha in PHP Codeigniter framework. In this tutorial, we will step by step describe you implementation of Google reCaptcha in Codeingniter application.

Now in your mind one question will arise why need to Add Google reCaptcha in our Form. So by using this Google reCaptcha, we can reduce form spam entry and it will stop storing spam data in our web application.

What is reCaptcha?


reCaptcha is one of the free services which has been provided by Google and it will protect our online web application from the spam data and abuse. It has been used some advanced risk analysis techniques which will be tells that particular user is humans or bots which has fill form data. So, it is mainly used for reduce spamming in our website.

What are benifits of using reCaptcha


There are several benifits of Google reCaptcha which you can find below.

  • It will protect our website registration form from spamming registration.
  • It will prevent from spam comments
  • It will secured our Online Shopping
  • It will protect our Email Account

So there are many other benifits of adding Google reCaptcha in our Codeigniter website. But now below you can find step by step process for integrate Google recaptcha in Codeigniter framework.




1. Get Google reCaptcha Site Key and Secret Key


In first step we want to get the Google recaptcha Site key and Secret key. So first we have to login into our Google account and then after https://www.google.com/recaptcha/admin/create and register your domain name in which website you want to add. So in below you image you can find data like label, reCAPTCHA type and domain name field. After filling this details, you have to click on submit button.



Once you have click on submit button, then you can get Google reCaptcha site key and secret key which you can see in below image. So you have to copy both key and store in safe place. We will use both key in Codeigniter for integrate Google reCaptcha with Codeigniter framework.



2. Define Base Url in Codeigniter


Before starting coding in Codeigniter framework, we have to first define base url of Codeigniter application. For define base url, we have to open application/config/ config.php and under that file you have define base url at $config['base_url'] in this variable.


$config['base_url'] = 'http://localhost/tutorial/codeigniter/';


3. Make Database Connection


For making MySql Database connection in Codeigniter framework, we have to open application/config/database.php file and under this file we have to define Mysql Database configuration.


$active_group = 'default';
$query_builder = TRUE;

$db['default'] = array(
	'dsn'	=> '',
	'hostname' => 'localhost',
	'username' => 'root',
	'password' => '',
	'database' => 'testing',
	'dbdriver' => 'mysqli',
	'dbprefix' => '',
	'pconnect' => FALSE,
	'db_debug' => (ENVIRONMENT !== 'production'),
	'cache_on' => FALSE,
	'cachedir' => '',
	'char_set' => 'utf8',
	'dbcollat' => 'utf8_general_ci',
	'swap_pre' => '',
	'encrypt' => FALSE,
	'compress' => FALSE,
	'stricton' => FALSE,
	'failover' => array(),
	'save_queries' => TRUE
);


4. Make Table in Mysql Database


After making Mysql database connection in Codeigniter framework, next we want to create table in Mysql database. So for create table in Mysql database, we have to run following SQL script in phpMyAdmin and it will make sample_data table will create in Mysql database.


--
-- Table structure for table `sample_data`
--

CREATE TABLE `sample_data` (
  `id` int(10) NOT NULL,
  `first_name` varchar(250) NOT NULL,
  `last_name` varchar(250) NOT NULL,
  `age` varchar(30) NOT NULL,
  `gender` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `sample_data`
--
ALTER TABLE `sample_data`
  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;



5. Create Controller in Codeigniter


Codeigniter is a MVC model based framework and under this framework Controllers class has been used for handle http request. So for handle Google reCaptcha validation here we have create application/controllers/Captcha.php file and under this class file we have make following method.

__construct() - This magic method will execute code when object of this class has been created and it will load session library and captcha model class under this class. So we can use both class mehtod under this class.

index() - This is root method of this class and it will display views/captcha.php file html content on web page.

validate() - This method has been received form data from client side and under this method firs it has been check g-recaptcha-response variable value has been set or not. If this variable value is set then only it will execute other code. This variable has been generated by Google reCaptcha site key. And then after for check at server side it has use Secret key and send request reCaptcha api for get response regarding user has give proper answer or not. If from reCaptcha api success variable value has been received that means user has pass google recaptcha validation and then after data will be inserted into Mysql database. So here without Google reCaptcha validation data will not be insert into our system.

application/controllers/Captcha.php

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

class Captcha extends CI_Controller {
	
	public function __construct()
	{
		parent::__construct();
		$this->load->library('session');
		$this->load->model('captcha_model');
	}

	function index()
	{
		$this->load->view('captcha');
	}

	function validate()
	{
		$captcha_response = trim($this->input->post('g-recaptcha-response'));

		if($captcha_response != '')
		{
			$keySecret = '6LefzNYZAAAAABWAiYy2_X2OiBSZkXdT7K-OoaKW';

			$check = array(
				'secret'		=>	$keySecret,
				'response'		=>	$this->input->post('g-recaptcha-response')
			);

			$startProcess = curl_init();

			curl_setopt($startProcess, CURLOPT_URL, "https://www.google.com/recaptcha/api/siteverify");

			curl_setopt($startProcess, CURLOPT_POST, true);

			curl_setopt($startProcess, CURLOPT_POSTFIELDS, http_build_query($check));

			curl_setopt($startProcess, CURLOPT_SSL_VERIFYPEER, false);

			curl_setopt($startProcess, CURLOPT_RETURNTRANSFER, true);

			$receiveData = curl_exec($startProcess);

			$finalResponse = json_decode($receiveData, true);

			if($finalResponse['success'])
			{
				$storeData = array(
					'first_name'	=>	$this->input->post('first_name'),
					'last_name'		=>	$this->input->post('last_name'),
					'age'			=>	$this->input->post('age'),
					'gender'		=>	$this->input->post('gender')
				);

				$this->captcha_model->insert($storeData);

				$this->session->set_flashdata('success_message', 'Data Stored Successfully');

				redirect('captcha');
			}
			else
			{
				$this->session->set_flashdata('message', 'Validation Fail Try Again');
				redirect('captcha');
			}
		}
		else
		{
			$this->session->set_flashdata('message', 'Validation Fail Try Again');

			redirect('captcha');
		}
	}

}

?>


6. Create Models class in Codeigniter


In Codeigniter models class has been used for perform database related operation and models chass has been created in application/models directory. In this tutorial, we have create Captcha_models.php class file and under this file we have make insert() method which has been used for insert data into Mysql table.

application/models/Captcha_models.php

<?php

class Captcha_model extends CI_Model
{
	function insert($data)
	{
		$this->db->insert('sample_data', $data);
	}
}


7. Create Views file in Codeigniter


Views file has been used for display HTML output in brwoser under this Codeigniter framework and here we have create application/views/captcha.php file. Under this file we have make one html form for get data from user table and for create Google reCaptcha under this form we have create one division tag with class=g-recaptcha and at under this tag we have add data-sitekey and under this attirbute value we have to define Google reCaptcha site key. So by define this site key it will generate Google reCaptcha wiget under this form.

application/views/captcha.php

<html>
<head>
    <title>How to Implement Google reCaptcha in Codeigniter</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>
    <script src='https://www.google.com/recaptcha/api.js'></script>
</head>
<body>
    <div class="container box">
        <br />
        <h2 align="center"><b>How to Implement Google reCaptcha in Codeigniter</b></h2>
        <br />
        <div class="panel panel-default">
            <div class="panel-heading">Fill Form Data</div>
            <div class="panel-body">
                <?php
                if($this->session->flashdata('message'))
                {
                ?>
                    <div class="alert alert-danger">
                        <?php
                        echo $this->session->flashdata('message');
                        ?>
                    </div>
                <?php
                }

                if($this->session->flashdata('success_message'))
                {
                ?>
                    <div class="alert alert-success">
                        <?php
                        echo $this->session->flashdata('success_message');
                        ?>
                    </div>
                <?php
                }
                ?>
                <form method="post" action="<?php echo base_url(); ?>captcha/validate">
                    <div class="form-group">
                        <label>First Name</label>
                        <input type="text" name="first_name" class="form-control" />
                    </div>
                    <div class="form-group">
                        <label>Last Name</label>
                        <input type="text" name="last_name" class="form-control" />
                    </div>
                    <div class="form-group">
                        <label>Age</label>
                        <input type="text" name="age" class="form-control" />
                    </div>
                    <div class="form-group">
                        <label>Gender</label>
                        <input type="text" name="gender" class="form-control" />
                    </div>
                    <div class="form-group">
                        <div class="g-recaptcha" data-sitekey="6LefzNYZAAAAAIeoFhVYj3T5BoCEo1Yja5DvCRxP"></div>
                    </div>
                    <div class="form-group">
                        <input type="submit" name="send" class="btn btn-success" value="Send" />
                    </div>
                </form>
            </div>
        </div>        
    </div>
</body>
</html>


So, this is complete step by step process for integrate Google reCaptcha under this Codeigniter application and reduce spam entry in your website. This is because without pass Google reCaptcha validation, data will not be entered into our database. So it will reduce spam attack in our website. If you have any query then you can ask in comment box we will reply on your comment regarding your query.

Monday, 5 October 2020

Show Hide jQuery DataTables Column with Server-side Processing with PHP Ajax


We have published many tutorial on jQuery DataTables with PHP Server-side processing using Ajax. But there are some viewers has requested us to publish tutorial on How can we show and hide jQuery DataTables columns with not loosing server-side process using Ajax with PHP. So for solve this problem, we have publish this tutorial, in which we will first load Mysql table dynamic data in jQuery DataTables plugin with server-side processing of searching, pagination of data using Ajax and then after we will make one select box with selection of multiple option with column name. So when we have select any column name name, then that column data will be hide from web page and when we have disselect any column name then that column data will be display on web page. So this show and hide column feature we will make for jQuery DataTable.

In this tutorial, for build show and hide DataTable column, we have use PHP script for backend operation and for front-end operation here we have use Ajax, jQuery DataTable plugin, Bootstrap library and Bootstrap select plugin. We can easily hide simple HTML table column by using CSS nth-child selector, but with DataTables it is not working properly. But if you search on DataTables site then it has already provide method for show or hide column. So by using that method we have make solution for dynamically show or hide jQuery DataTable column with PHP server-side processing using Ajax.

So here mainly we have use two main jQuery plugin for make show hide DataTable column tutorial, one is jQuery DataTable and another one is Bootstrap select plugin.

jQuery DataTable Plugin


jQuery DataTable plugin is a javascript library which has convert simple HTML table into multiple functional table with different feature like data search, pagination, sorting of data etc. This plugin has been used for load dynamic data in tabular format on web page. By using this plugin user can easily filter data and it will automatically make pagination without writing any line of code. So in this tutorial, we want to learn how to show or hide DataTable column dynamically.

Bootstrap Select Plugin


In this post we have use Bootstrap select plugin for select multiple column name for show or hide DataTable column dynamically. By using this plugin, we can convert simple select element into stylish select element with different feature like multi selection of option, search option text and much more. So here we want to make tutorial on show or hide multiple DataTable column dynamically, for this we have use this plugin under this tutorial for select multiple option at the same time for hide or show multiple DataTable column with server-side processing.


Here you can find complete source code for how to use Bootstrap select plugin with jQuery DataTable for make show or hide jQuery DataTable column dynamically. Here you can find how to load dynamic mysql table data in jQuery DataTable plugin and then after we will make simple select box and then after convert that select box into stylish select box by using Bootstrap select plugin. So when we have select column name then that column data will be hide from web page and when we have disselect selected column name then that column data will be visible on web page. So by using this feature we can display only that DataTable column data, which is required and we can hide othere unrequired DataTable column data from web page. Below you can find complete source code of show hide DataTable column data dynamically with server-side processing using PHP with Ajax.




Source Code



--
-- Database: `testing`
--

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

--
-- Table structure for table `customer_table`
--

CREATE TABLE `customer_table` (
  `customer_id` int(11) NOT NULL,
  `customer_first_name` varchar(200) NOT NULL,
  `customer_last_name` varchar(200) NOT NULL,
  `customer_email` varchar(300) NOT NULL,
  `customer_gender` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `customer_table`
--
ALTER TABLE `customer_table`
  ADD PRIMARY KEY (`customer_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `customer_table`
--
ALTER TABLE `customer_table`
  MODIFY `customer_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;


index.php



<html>
	<head>
		<title>How to Dynamically Display Hide DataTables Column with PHP Ajax</title>
		<!-- JS, Popper.js, and jQuery -->
		<script  src="https://code.jquery.com/jquery-3.5.1.js" integrity="sha256-QWo7LDvxbWT2tbbQ97B53yJnYU3WhH/C8ycbRAkjPDc=" crossorigin="anonymous"></script>
		<!-- CSS only -->
		<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" integrity="sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z" crossorigin="anonymous">

		
		<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js" integrity="sha384-9/reFTGAW83EW2RDu2S0VKaIzap3H66lZH81PoYlFhbGU+6BZp6G7niu735Sk7lN" crossorigin="anonymous"></script>
		<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js" integrity="sha384-B4gt1jrGC7Jh4AgTPSdUtOBvfO8shuf57BaghqFfPlYxofvL8/KUEfYiJOMMV+rV" crossorigin="anonymous"></script>
		<script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>
		<script src="https://cdn.datatables.net/1.10.22/js/dataTables.bootstrap4.min.js"></script>  
		<link rel="stylesheet" href="https://cdn.datatables.net/1.10.22/css/dataTables.bootstrap4.min.css" />
		<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-select@1.13.14/dist/css/bootstrap-select.min.css">
  		<script src="https://cdn.jsdelivr.net/npm/bootstrap-select@1.13.14/dist/js/bootstrap-select.min.js"></script>
	</head>
	<body>
		<div class="container">
			<br />
			<h1 align="center" class="text-primary"><b>How to Dynamically Display Hide DataTables Column with PHP Ajax</b></h1>
			<br />
			<div class="card">
				<div class="card-header">
					<div class="row">
						<div class="col-lg-9">Sample Data</div>
						<div class="col-lg-3">
							<select name="column_name" id="column_name" class="form-control selectpicker" multiple>
								<option value="0">Customer ID</option>
						      	<option value="1">Customer First Name</option>
						      	<option value="2">Customer Last Name</option>
						      	<option value="3">Customer Email</option>
						      	<option value="4">Customer Gender</option>
							</select>
						</div>
					</div>
				</div>
				<div class="card-body">
					<div class="table-responsive">
						<table id="sample_data" class="table table-bordered table-striped">
							<thead>
								<tr>
									<th>Customer ID</th>
									<th>Customer First Name</th>
									<th>Customer Last Name</th>
									<th>Customer Email</th>
									<th>Customer Gender</th>
								</tr>
							</thead>
						</table>
					</div>
				</div>
			</div>
		</div>
		<br />
		<br />
	</body>
</html>

<script type="text/javascript" language="javascript">

$(document).ready(function(){
	
	var dataTable = $('#sample_data').DataTable({
		"processing" : true,
		"serverSide" : true,
		"order" : [],
		"ajax" : {
			url:"fetch.php",
			type:"POST"
		}
	});
	
	$('#column_name').selectpicker();

	$('#column_name').change(function(){

		var all_column = ["0", "1", "2", "3", "4"];

		var remove_column = $('#column_name').val();

		var remaining_column = all_column.filter(function(obj) { return remove_column.indexOf(obj) == -1; });

		dataTable.columns(remove_column).visible(false);

		dataTable.columns(remaining_column).visible(true);

	});

});	
</script>


fetch.php



<?php

//fetch.php

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

$column = array("customer_id", "customer_first_name", "customer_last_name", "customer_email", "customer_gender");

$query = "SELECT * FROM customer_table ";

if(isset($_POST["search"]["value"]))
{
	$query .= '
	WHERE customer_id LIKE "%'.$_POST["search"]["value"].'%" 
	OR customer_first_name LIKE "%'.$_POST["search"]["value"].'%" 
	OR customer_last_name LIKE "%'.$_POST["search"]["value"].'%" 
	OR customer_email LIKE "%'.$_POST["search"]["value"].'%" 
	OR customer_gender LIKE "%'.$_POST["search"]["value"].'%" 
	';
}

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

$query1 = '';

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

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

$statement->execute();

$number_filter_row = $statement->rowCount();

$result = $connect->query($query . $query1);

$data = array();

foreach($result as $row)
{
	$sub_array = array();
	$sub_array[] = $row['customer_id'];
	$sub_array[] = $row['customer_first_name'];
	$sub_array[] = $row['customer_last_name'];
	$sub_array[] = $row['customer_email'];
	$sub_array[] = $row['customer_gender'];
	$data[] = $sub_array;
}

function count_all_data($connect)
{
	$query = "SELECT * FROM customer_table";

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

	$statement->execute();

	return $statement->rowCount();
}

$output = array(
	"draw"		=>	intval($_POST["draw"]),
	"recordsTotal"	=>	count_all_data($connect),
	"recordsFiltered"	=>	$number_filter_row,
	"data"	=>	$data
);

echo json_encode($output);

?>



Friday, 2 October 2020

How to Make Editable DataTable in PHP using X-Editable Plugin



Hey Guys, Are you looking for solution of Creating Live Inline Editing in jQuery DataTable with PHP Server-side processing using Ajax, then you have come on the best place because we have make this tutorial in which we have use X-Editable jquery plugin with DataTable for converting simple DataTable into Live editable DataTable, so we can update any cell data on web page without going into another page. In this post, you can find how can we implement jQuery X-editable plugin with jQuery DataTable plugin with PHP Server-side processing for making live inline editing DataTable data without refreshing of web page. Below you can find simple description of both plugin.

jQuery DataTable Plugin


jQuery DataTable plugin is a javascript library for converting simple HTML table into very advance level using different feature like pagination, Instant Search, multi column ordering or sorting etc. So by using this jQuery DataTable plugin we can load data in tabular format. This plugin is also work with bootstrap library so by using this plugin we can also get the benifits of Bootstrap library and it is very easy to integrate in our new project or exisiting project. And by using jQuery plugin we can easily perform client side processing of data and server side processing of data without refresh of web page.

jQuery X-Editable Plugin


By using jQuery X-Editable plugin we can create editable elements on web page. So by using this plugin we can convert simple HTML table into Inline Live HTML table that means we can edit the content of HTML table cells. This plugin is compatible with Bootstrap library, jQuery UI library or even you can also use with simple jQuery also without using Bootstrap or jQuery UI library. And by using this library we can edit table cells data with both Popup and Inline modes also. By using this plugin we can edit table cells data and it will send ajax request to server for perform update database operation.




Now we want to use both plugin with each other and we want to convert simple DataTable into inline editable DataTable using X-editable plugin and we want to also perform update data operation also and convert simple DataTable into Inline live table cells editable DataTable with update database operation. Below you can find complete source code for How to make editable DataTable in PHP using X-Editable jQuery plugin.



Source Code


database_connection.php



<?php

//database_connection.php

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

?>


index.php



<html>
	<head>
		<title>In-Place Editing in DataTable with X-Editable using PHP 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://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>
		<link href="https://cdnjs.cloudflare.com/ajax/libs/x-editable/1.5.1/bootstrap3-editable/css/bootstrap-editable.css" rel="stylesheet">
  		<script src="https://cdnjs.cloudflare.com/ajax/libs/x-editable/1.5.1/bootstrap3-editable/js/bootstrap-editable.js"></script>
	</head>
	<body>
		<div class="container">
			<h3 align="center">In-Place Editing in DataTable with X-Editable using PHP Ajax</h3>
			<br />
			<div class="panel panel-default">
				<div class="panel-heading">Sample Data</div>
				<div class="panel-body">
					<div class="table-responsive">
						<table id="sample_data" class="table table-bordered table-striped">
							<thead>
								<tr>
									<th>ID</th>
									<th>First Name</th>
									<th>Last Name</th>
									<th>Gender</th>
								</tr>
							</thead>
						</table>
					</div>
				</div>
			</div>
		</div>
		<br />
		<br />
	</body>
</html>

<script type="text/javascript" language="javascript">

$(document).ready(function(){
	var dataTable = $('#sample_data').DataTable({
		"processing": true,
		"serverSide": true,
		"order":[],
		"ajax":{
			url:"fetch.php",
			type:"POST",
		},
		createdRow:function(row, data, rowIndex)
		{
			$.each($('td', row), function(colIndex){
				if(colIndex == 1)
				{
					$(this).attr('data-name', 'first_name');
					$(this).attr('class', 'first_name');
					$(this).attr('data-type', 'text');
					$(this).attr('data-pk', data[0]);
				}
				if(colIndex == 2)
				{
					$(this).attr('data-name', 'last_name');
					$(this).attr('class', 'last_name');
					$(this).attr('data-type', 'text');
					$(this).attr('data-pk', data[0]);
				}
				if(colIndex == 3)
				{
					$(this).attr('data-name', 'gender');
					$(this).attr('class', 'gender');
					$(this).attr('data-type', 'select');
					$(this).attr('data-pk', data[0]);
				}
			});
		}
	});

	$('#sample_data').editable({
		container:'body',
		selector:'td.first_name',
		url:'update.php',
		title:'First Name',
		type:'POST',
		validate:function(value){
			if($.trim(value) == '')
			{
				return 'This field is required';
			}
		}
	});

	$('#sample_data').editable({
		container:'body',
		selector:'td.last_name',
		url:'update.php',
		title:'Last Name',
		type:'POST',
		validate:function(value){
			if($.trim(value) == '')
			{
				return 'This field is required';
			}
		}
	});

	$('#sample_data').editable({
		container:'body',
		selector:'td.gender',
		url:'update.php',
		title:'Gender',
		type:'POST',
		datatype:'json',
		source:[{value: "Male", text: "Male"}, {value: "Female", text: "Female"}],
		validate:function(value){
			if($.trim(value) == '')
			{
				return 'This field is required';
			}
		}
	});
});	
</script>


fetch.php



<?php

//fetch.php

include('database_connection.php');

$column = array("id", "first_name", "last_name", "gender");

$query = "SELECT * FROM tbl_sample ";

if(isset($_POST["search"]["value"]))
{
	$query .= '
	WHERE first_name LIKE "%'.$_POST["search"]["value"].'%" 
	OR last_name LIKE "%'.$_POST["search"]["value"].'%" 
	OR gender LIKE "%'.$_POST["search"]["value"].'%" 
	';
}

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

$query1 = '';

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

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

$statement->execute();

$number_filter_row = $statement->rowCount();

$result = $connect->query($query . $query1);

$data = array();

foreach($result as $row)
{
	$sub_array = array();
	$sub_array[] = $row['id'];
	$sub_array[] = $row['first_name'];
	$sub_array[] = $row['last_name'];
	$sub_array[] = $row['gender'];
	$data[] = $sub_array;
}

function count_all_data($connect)
{
	$query = "SELECT * FROM tbl_sample";

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

	$statement->execute();

	return $statement->rowCount();
}

$output = array(
	'draw'		=>	intval($_POST['draw']),
	'recordsTotal'	=>	count_all_data($connect),
	'recordsFiltered'	=>	$number_filter_row,
	'data'		=>	$data
);

echo json_encode($output);

?>


update.php



<?php

//update.php

include('database_connection.php');

$query = "
UPDATE tbl_sample 
SET ".$_POST["name"]." = '".$_POST["value"]."' 
WHERE id = '".$_POST["pk"]."'
";

$connect->query($query);

?>

Thursday, 17 September 2020

Laravel 8 Search with Pagination using Livewire


Hi, In this post, we will show you how to make search with pagination application using Livewire in Laravel 8 framework. In previous our Laravel Livewire tutorial, we have make single page CRUD application. Now we want to make application in which we can load data with pagination link and with search filter by using Livewire package in Laravel 8. So here we will step by step learn how to make pagination with Mysql database using Livewire package in Laravel 8 framework.

If you are looking for how to make Livewire search with pagination tutorial from scratch in Laravel 8 framework, then in this post we have share tutorial on how to create Search with Pagination Livewire app in Laravel 8 framework. So this tutorial will help you to learn How to make Laravel 8 Livewire search with pagination app step by step and how to implement live data search functionality with pagination by using Livewire in your Laravel 8 project.

Follow following steps for implement livewire search with pagination in Laravel 8.

  • 1 - Install Laravel 8
  • 2 - Make Database connection
  • 3 - Create table with fake data
  • 4 - Install Livewire package with component
  • 5 - Generate Laravel Pagination Templates
  • 6 - Run Laravel 8 Development Server

Laravel 8 Search with Pagination using Livewire


1 - Install Laravel 8


In first steps we want to download Laravel 8 framework. For this we want to go command prompt and in command prompt go to directory in which we want to download and install Laravel 8 framework. After this run following command.


composer create-project --prefer-dist laravel/laravel livewire-search-pagination

Above command will make livewire-search-pagination directory and under this directory it will download Laravel 8 framework.

2 - Make Database connection


In second steps we want to make Mysql database connection. So in Laravel 8 framework for make database connection, we have to open .env file and under this file we have to define mysql database configuration details.


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

The above configuration will make mysql database connection with testing database in your Laravel 8 framework.





3 - Create table with fake data


After making database connection, now in third steps we want to generate fake data. So for generate fake data here we will use Laravel default models class which is User.php and it has been stored in app/Models/User.php this path. So first we want to make table in our testing database. For this we have to go command prompt and run following command.


php artisan migrate

The above command will export user table defination to mysql database and create users table in mysql database. Now we want to generate fake data, so for generate fake data, here we will use Laravel Database seeder class which has been stored under database/seeders/DatabaseSeeder.php path. So we have open this file and define models class at header of this class and write code for generate fake data which you can find below.

database/seeders/DatabaseSeeder.php

<?php

namespace Database\Seeders;

use Illuminate\Database\Seeder;
use App\Models\User;

class DatabaseSeeder extends Seeder
{
    /**
     * Seed the application's database.
     *
     * @return void
     */
    public function run()
    {
        // User::factory(10)->create();
        User::factory(500)->create();
    }
}


After this we have again go to command prompt and run following command.


php artisan db:seed

This command will execute run() method of DatabaseSeeder.php class and it will generate fake 500 data in users table. So this way we can generate fake data in Laravel 8 framework.

4 - Install Livewire package with component


Now in fourth steps, we want to install Livewire package in Laravel 8 framework. For this, we have go to command prompt and run following command.


composer require livewire/livewire

This command will install Livewire package in Laravel 8 framework. Next we want to make Livewire component. So for this also we have go to command prompt and write following command.


php artisan make:livewire filter

This command will generate two file, one is Filter.php class at app/Http/Livewire directory and filter.blade.php views file at views/livewire directory. This both file will be used for create Livewire Search with pagination application.

So, first we have open app/Http/Livewire/Filter.php file and under this file, we have add use Livewire\WithPagination; and use App\Models\User; at the header of this class and then after Under this class, we have define public $searchTerm; for handle search query request. Under this class render method, which is root method of this class, we have define code for search data and create pagination links.

app/Http/Livewire/Filter.php

<?php

namespace App\Http\Livewire;

use Livewire\Component;
use Livewire\WithPagination;
use App\Models\User;

class Filter extends Component
{
	use WithPagination;

	public $searchTerm;

    public function render()
    {
    	$query = '%'.$this->searchTerm.'%';

    	return view('livewire.filter', [
    		'users'		=>	User::where(function($sub_query){
    							$sub_query->where('name', 'like', '%'.$this->searchTerm.'%')
    									  ->orWhere('email', 'like', '%'.$this->searchTerm.'%');
    						})->paginate(10)
    	]);
    }
}


After this, we have go to views/livewire/filter.blade.php file and under this file, we have create one html table and fill that table with data and below that table we have also make pagination links also.

views/livewire/filter.blade.php

<div>
    <div class="container">
	    <div class="row">
	        <div class="col-md-12">	            
	            <input type="text"  class="form-control" placeholder="Search" wire:model="searchTerm" />
	            <table class="table table-bordered" style="margin: 10px 0 10px 0;">
	                <tr>
	                    <th>Name</th>
	                    <th>Email</th>
	                </tr>
	                @foreach($users as $user)
	                <tr>
	                    <td>
	                        {{ $user->name }}
	                    </td>
	                    <td>
	                        {{ $user->email }}
	                    </td>
	                </tr>
	                @endforeach
	            </table>
	            {{ $users->links() }}
	        </div>
	    </div>
	</div>
</div>


Next we have go to resources/views/welcome.blade.php file and under this file, we want to include source of resources/views/livewire/filter.blade.php file. So for this, we have write @livewire('filter') and then after we want to include livewire scripts, so for this, we have write @livewireScripts. By using this tag, we can included livewire javascript file under this welcome.blade.php file.

resources/views/welcome.blade.php

<!DOCTYPE html>
<html>
    <head>
        <title></title>
        <!-- Fonts -->
        <link href="https://fonts.googleapis.com/css?family=Nunito:200,600" rel="stylesheet">
 
        <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.1/css/bootstrap.min.css">
    </head>
     
<body>
    <div class="container mt-4">
        <div class="card">
            <div class="card-header">
                <b>Search & Pagination with Livewire in Laravel 8</b>
            </div>
            <div class="card-body">
                @livewire('filter')
            </div>
        </div>
    </div>
</body>

@livewireScripts

</html>

The above process will make Livewire Search with pagination application using default Livewire pagination templates but you want to use Bootstrap 4 pagination templates you have to follow 5thstep which you can find below.

5 - Generate Laravel Pagination Templates


For implement Bootstrap 4 pagination templates under this Laravel Livewire pagination templates, so first want to generate different Laravel pagination templates. So for this we have go to command prompt and run following command.


php artisan vendor:publish

After run this command it will ask for which vendor class want be publish, so we have to enter 16 which is Laravel-Pagination and then after it will generate different Laravel pagination templates at resources/views/vendor/pagination directory which you can find in below image.


Laravel 8 Search with Pagination using Livewire


So from that list of templates, we have to open resources/views/vendor/pagination/bootstrap-4.blade.php file and copy whole file code and paste into resources/views/livewire/livewire-pagination.blade.php file. After this, we want to removed all of the href="url" links and put href="#" so page will not be reload when we have click on pagination links. After this, we have to add wire:click attribute in each link so our file code will be look like below file.

resources/views/vendor/pagination/bootstrap-4.blade.php

@if ($paginator->hasPages())
    <nav>
        <ul class="pagination">
            {{-- Previous Page Link --}}
            @if ($paginator->onFirstPage())
                <li class="page-item disabled" aria-disabled="true" aria-label="@lang('pagination.previous')">
                    <span class="page-link" aria-hidden="true">&lsaquo;</span>
                </li>
            @else
                <li class="page-item">
                    <a class="page-link" href="#" wire:click="setPage('{{ $paginator->previousPageUrl() }}')" rel="prev" aria-label="@lang('pagination.previous')">&lsaquo;</a>
                </li>
            @endif

            {{-- Pagination Elements --}}
            @foreach ($elements as $element)
                {{-- "Three Dots" Separator --}}
                @if (is_string($element))
                    <li class="page-item disabled" aria-disabled="true"><span class="page-link">{{ $element }}</span></li>
                @endif

                {{-- Array Of Links --}}
                @if (is_array($element))
                    @foreach ($element as $page => $url)
                        @if ($page == $paginator->currentPage())
                            <li class="page-item active" aria-current="page"><span class="page-link">{{ $page }}</span></li>
                        @else
                            <li class="page-item"><a class="page-link" href="#" wire:click="setPage('{{ $url }}')">{{ $page }}</a></li>
                        @endif
                    @endforeach
                @endif
            @endforeach

            {{-- Next Page Link --}}
            @if ($paginator->hasMorePages())
                <li class="page-item">
                    <a class="page-link" href="#" wire:click="setPage('{{ $paginator->nextPageUrl() }}')" rel="next" aria-label="@lang('pagination.next')">&rsaquo;</a>
                </li>
            @else
                <li class="page-item disabled" aria-disabled="true" aria-label="@lang('pagination.next')">
                    <span class="page-link" aria-hidden="true">&rsaquo;</span>
                </li>
            @endif
        </ul>
    </nav>
@endif


Then after we have to open resources/views/livewire/filter.blade.php file and put {{ $users->links('livewire.livewire-pagination') }} in place of {{ $users->links() }} code which you can seen below.

resources/views/livewire/filter.blade.php

<div>
    <div class="container">
	    <div class="row">
	        <div class="col-md-12">	            
	            <input type="text"  class="form-control" placeholder="Search" wire:model="searchTerm" />
	            <table class="table table-bordered" style="margin: 10px 0 10px 0;">
	                <tr>
	                    <th>Name</th>
	                    <th>Email</th>
	                </tr>
	                @foreach($users as $user)
	                <tr>
	                    <td>
	                        {{ $user->name }}
	                    </td>
	                    <td>
	                        {{ $user->email }}
	                    </td>
	                </tr>
	                @endforeach
	            </table>
	            {{ $users->links('livewire.livewire-pagination') }}
	        </div>
	    </div>
	</div>
</div>


Next we have go add setPage($url) method in app/Http/Livewire/Filter.php which you can seen below source code.

app/Http/Livewire/Filter.php

<?php

namespace App\Http\Livewire;

use Livewire\Component;
use Livewire\WithPagination;
use Illuminate\Pagination\Paginator;
use App\Models\User;

class Filter extends Component
{
	use WithPagination;

	public $searchTerm;
    public $currentPage = 1;

    public function render()
    {
    	$query = '%'.$this->searchTerm.'%';

    	return view('livewire.filter', [
    		'users'		=>	User::where(function($sub_query){
    							$sub_query->where('name', 'like', '%'.$this->searchTerm.'%')
    									  ->orWhere('email', 'like', '%'.$this->searchTerm.'%');
    						})->paginate(10)
    	]);
    }

    public function setPage($url)
    {
        $this->currentPage = explode('page=', $url)[1];
        Paginator::currentPageResolver(function(){
            return $this->currentPage;
        });
    }
}



6 - Run Laravel 8 Development Server


So, All are sets now we are ready for view output in browser. So first we want to start Laravel server. So we have go to command prompt and run following command.


php artisan serve

After run this command it will start Laravel development server and provide us base url of our Laravel application.


http://127.0.0.1:8000/

So, for view Laravel 8 Livewire Search with Pagination application, we have to type above base url in browser and we check output in browser. So, this is complete step by step process for make Livewire Search with Pagination in Laravel 8 tutorial with implementing Bootstrap 4 pagination templates in place of default Livewire pagination templates. If you have any query regarding this tutorial you can make comment in below comment box and then after we will solve your query by replying on your comment.