Wednesday, 16 August 2017

Display Online Users using PHP Mysql with Ajax Jquery

How to Show Live Online User using PHP with Ajax Jquery - 1





How to Show Live Online User using PHP with Ajax Jquery - 2



It is very important for any website to find out who are currently login into our syste or who are online. Because it will increase the value of website into another user who has online to system. This type of feature most we have seen in social media or forum site. So in this post we have discuss topic like display user information who are currently login into our website by using simple PHP Script Mysql database with Ajax JQuery. If you are using internet and you have already seen on many website in which we can seen the number of online users. So displaying online user details may create nice image on the new visitors of websites.

For create this type of feature there are following steps to create this type of feature.

1. Store User Login Information in table
2. Update Last Activity of Login user into his login information
3. Fetch Online User data from table

Store User Login Information in table


First when user has login into our system then we have to save his login information like his id, login time into our login details database. When login into our system by entering correct login details then after we have create one SESSION variable login id in which we have store the id of login details id based on this id we can update his last activity table column data with current time stamp.

Update Last Activity of Login user into his login information


After storing user login information in table and generate one unique login id and store into SESSION variable, so we can access this variable value into accros our system. So when user logged into system after this we will update current time stamp into his last activity table column into login details based on the unique value of login id get from SESSION variable. For update time stamp data on every three second we have use Ajax with JQuery. For update last activity data we have make one jquery function and in that function we have use Ajax request which send request to server for update login user last activity data and by using JQuery setInterval method we have called this function on every three seconds. So this way we have update login user last activity data with current time stamp data by using Ajax JQuer with PHP Sever script.

Fetch Online User data from table


Now We have already discuss how logged in user data insert into login details details table and how we have update that login used login details every three seconds, now we have move to master user login. This master user can view who is currently logged into our website. So for this we have make one jquery function and in this function we have use Ajax request which send request to PHP server script for fetch data from login details table and here we have condition like it fetch only that data whose last activity table column data is current time stamp data minus five second. That means it only fetch that user data whose last activity time stamp data less than of current time stamp minus five. So this query will return only that user data who currently online with our system or use our system. After fetching this data we have use setInterval() method, by using this method we have called this jquery function on every three second. So on every three second it will display live online user data on web page.

So this is simple system in which we have discuss topic like how to display online information by using PHP PDO with Ajax JQuery and Mysql.




Source Code


database_connection.php



<?php
//database_connection.php
$connect = new PDO('mysql:host=localhost;dbname=testing2', 'root', '');
session_start();
?>


login.php



<?php
//login.php
include('database_connection.php');
if(isset($_SESSION["type"]))
{
 header("location: index.php");
}
$message = '';

if(isset($_POST["login"]))
{
 if(empty($_POST["user_email"]) || empty($_POST["user_password"]))
 {
  $message = "<label>Both Fields are required</label>";
 }
 else
 {
  $query = "
  SELECT * FROM user_details 
  WHERE user_email = :user_email
  ";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    'user_email' => $_POST["user_email"]
   )
  );
  $count = $statement->rowCount();
  if($count > 0)
  {
   $result = $statement->fetchAll();
   foreach($result as $row)
   {
    if(password_verify($_POST["user_password"], $row["user_password"]))
    {
     $insert_query = "
     INSERT INTO login_details (
      user_id, last_activity) VALUES (
      :user_id, :last_activity)
     ";
     $statement = $connect->prepare($insert_query);
     $statement->execute(
      array(
       'user_id'  => $row["user_id"],
       'last_activity' => date("Y-m-d H:i:s", STRTOTIME(date('h:i:sa')))
      )
     );
     $login_id = $connect->lastInsertId();
     if(!empty($login_id))
     {
      $_SESSION["type"] = $row["user_type"];
      $_SESSION["login_id"] = $login_id;
      header("location: index.php");
     }
    }
    else
    {
     $message = "<label>Wrong Password</label>";
    }
   }
  }
  else
  {
   $message = "<label>Wrong Email Address</labe>";
  }
 }
}


?>

<!DOCTYPE html>
<html>
 <head>
  <title>How Display Users Online using PHP with 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">
   <h2 align="center">How Display Users Online using PHP with Ajax JQuery</h2>
   <br />
   <div class="panel panel-default">
    <div class="panel-heading">Login</div>
    <div class="panel-body">
     <form method="post">
      <span class="text-danger"><?php echo $message; ?></span>
      <div class="form-group">
       <label>User Email</label>
       <input type="text" name="user_email" class="form-control" />
      </div>
      <div class="form-group">
       <label>Password</label>
       <input type="password" name="user_password" class="form-control" />
      </div>
      <div class="form-group">
       <input type="submit" name="login" value="Login" class="btn btn-info" />
      </div>
     </form>
    </div>
   </div>
  </div>
 </body>
</html>


index.php



<?php
//index.php
include('database_connection.php');

if(!isset($_SESSION["type"]))
{
 header("location: login.php");
}

?>
<!DOCTYPE html>
<html>
 <head>
  <title>How Display Users Online using PHP with 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">
   <h2 align="center">How Display Users Online using PHP with Ajax JQuery</h2>
   <br />
   <div align="right">
    <a href="logout.php">Logout</a>
   </div>
   <br />
   <?php
   if($_SESSION["type"] =="user")
   {
    echo '<div align="center"><h2>Hi... Welcome User</h2></div>';
   }
   else
   {
   ?>
   <div class="panel panel-default">
    <div class="panel-heading">Online User Details</div>
    <div id="user_login_status" class="panel-body">

    </div>
   </div>
   <?php
   }
   ?>
  </div>
 </body>
</html>

<script>
$(document).ready(function(){
<?php
if($_SESSION["type"] == "user")
{
?>
function update_user_activity()
{
 var action = 'update_time';
 $.ajax({
  url:"action.php",
  method:"POST",
  data:{action:action},
  success:function(data)
  {

  }
 });
}
setInterval(function(){ 
 update_user_activity();
}, 3000);


<?php
}
else
{
?>
fetch_user_login_data();
setInterval(function(){
 fetch_user_login_data();
}, 3000);
function fetch_user_login_data()
{
 var action = "fetch_data";
 $.ajax({
  url:"action.php",
  method:"POST",
  data:{action:action},
  success:function(data)
  {
   $('#user_login_status').html(data);
  }
 });
}
<?php
}
?>

});
</script>


action.php



<?php
//action.php
include('database_connection.php');
if(isset($_POST["action"]))
{
 if($_POST["action"] == "update_time")
 {
  $query = "
  UPDATE login_details 
  SET last_activity = :last_activity 
  WHERE login_details_id = :login_details_id
  ";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    'last_activity'  => date("Y-m-d H:i:s", STRTOTIME(date('h:i:sa'))),
    'login_details_id' => $_SESSION["login_id"]
   )
  );
 }
 if($_POST["action"] == "fetch_data")
 {
  $output = '';
  $query = "
  SELECT login_details.user_id, user_details.user_email, user_details.user_image FROM login_details 
  INNER JOIN user_details 
  ON user_details.user_id = login_details.user_id 
  WHERE last_activity > DATE_SUB(NOW(), INTERVAL 5 SECOND) 
  AND user_details.user_type = 'user'
  ";
  $statement = $connect->prepare($query);
  $statement->execute();
  $result = $statement->fetchAll();
  $count = $statement->rowCount();
  $output .= '
  <div class="table-responsive">
   <div align="right">
    '.$count.' Users Online
   </div>
   <table class="table table-bordered table-striped">
    <tr>
     <th>No.</th>
     <th>Email ID</th>
     <th>Image</th>
    </tr>
  ';

  $i = 0;
  foreach($result as $row)
  {
   $i = $i + 1;
   $output .= '
   <tr> 
    <td>'.$i.'</td>
    <td>'.$row["user_email"].'</td>
    <td><img src="images/'.$row["user_image"].'" class="img-thumbnail" width="50" /></td>
   </tr>
   ';
  }
  $output .= '</table></div>';
  echo $output;
 }
}



?>


logout.php



<?php
//logout.php
session_start();
session_destroy();
header("location:login.php");
?>


Database



--
-- Database: `testing2`
--

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

--
-- Table structure for table `login_details`
--

CREATE TABLE IF NOT EXISTS `login_details` (
  `login_details_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `last_activity` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `user_details`
--

CREATE TABLE IF NOT EXISTS `user_details` (
  `user_id` int(11) NOT NULL,
  `user_email` varchar(200) NOT NULL,
  `user_password` varchar(200) NOT NULL,
  `user_type` enum('master','user') NOT NULL,
  `user_image` varchar(150) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `user_details`
--

INSERT INTO `user_details` (`user_id`, `user_email`, `user_password`, `user_type`, `user_image`) VALUES
(1, 'john_smith@gmail.com', '$2y$10$cHpf3TzonURXDENRiRF0de1ycSfnM4NJ27sdwyUCf5L.sewDlkCBe', 'master', ''),
(2, 'dona_huber@gmail.com', '$2y$10$lcLYyNeK1adgzYcBplv45uuXHFuFyWYThnj3nB2SZ/LbQvtWSoGjO', 'user', 'dona_huber.jpg'),
(3, 'roy_hise@gmail.com', '$2y$10$XlyVI9an5B6rHW3SS9vQpesJssKJxzMQYPbSaR7dnpWjDI5fpxJSS', 'user', 'roy_hise.jpg'),
(4, 'peter_goad@gmail.com', '$2y$10$n1B.FdHNwufTkmzp/pNqc.EiwjB8quQ1tBCEC7nkaldI5pS.et04e', 'user', 'peter_goad.jpg'),
(5, 'sarah_thomas@gmail.com', '$2y$10$s57SErOPlgkIZf1lxzlX3.hMt8LSSKaYig5rusxghDm7LW8RtQc/W', 'user', 'sarah_thomas.jpg'),
(6, 'edna_william@gmail.com', '$2y$10$mfMXnH.TCmg5tlYRhqjxu.ILly8s9.qsLKOpyxgUl6h1fZt6x/B5C', 'user', 'edna_william.jpg');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `login_details`
--
ALTER TABLE `login_details`
  ADD PRIMARY KEY (`login_details_id`);

--
-- Indexes for table `user_details`
--
ALTER TABLE `user_details`
  ADD PRIMARY KEY (`user_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `login_details`
--
ALTER TABLE `login_details`
  MODIFY `login_details_id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `user_details`
--
ALTER TABLE `user_details`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=7;

Friday, 11 August 2017

How to Make Google Line Chart by using PHP JSON Data



This is one more post on Google Graph or Chart API, in which we will discuss one more Google Chart type. So Here we will discuss Google Line Chart and how to dynamic make Google Line Chart by using JSON data. In this blog we will check how to create Google API Line Chart by using PHP Array and convert into JSON and data get from Mysql table. We will populating dynamic Data using PHP server side script and by uisng Google Line Chart API we will load that data and make simple dynamic Line Chart.

We all know Google has provide Google Chart API which is one type of tools and it is very powerful javascript based tool and it is very simple to use for web production and it is free to use. And it provide rich chart gallery to display our dynamic data on interactive charts. For discussion here we have use Google Line chart with JSON Data, we will use JSON data to make Line chart which display data in Line chart format on web page.

For discuss topic how to load JSON data and make Line chart, so we have used Sensors temperature data which we have stored in Mysql database table. So first we want to fetch data from that table so we have PHP script for fetch data from Table. After fetching data we have convert that data into PHP Array format which provide by Google to use in our PHP Code. Then after we have convert that array into JSON format because is a datatype which we can use in Javascript and Whole Google chart API based on javascript so we have converted into JSON format. Lastly we have load Google Chart API and load that data into Google chart DataTable method and set property of chart as per our requirement. So here we have make Google Line chart with JSON data get from Mysql by using PHP script. Below of this post you can find source code of this post, so try this code in your local computer.




Source Code


index.php



<?php
//index.php
$connect = mysqli_connect("localhost", "root", "", "testing");
$query = '
SELECT sensors_temperature_data, 
UNIX_TIMESTAMP(CONCAT_WS(" ", sensors_data_date, sensors_data_time)) AS datetime 
FROM tbl_sensors_data 
ORDER BY sensors_data_date DESC, sensors_data_time DESC
';
$result = mysqli_query($connect, $query);
$rows = array();
$table = array();

$table['cols'] = array(
 array(
  'label' => 'Date Time', 
  'type' => 'datetime'
 ),
 array(
  'label' => 'Temperature (°C)', 
  'type' => 'number'
 )
);

while($row = mysqli_fetch_array($result))
{
 $sub_array = array();
 $datetime = explode(".", $row["datetime"]);
 $sub_array[] =  array(
      "v" => 'Date(' . $datetime[0] . '000)'
     );
 $sub_array[] =  array(
      "v" => $row["sensors_temperature_data"]
     );
 $rows[] =  array(
     "c" => $sub_array
    );
}
$table['rows'] = $rows;
$jsonTable = json_encode($table);

?>


<html>
 <head>
  <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
  <script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
  <script type="text/javascript">
   google.charts.load('current', {'packages':['corechart']});
   google.charts.setOnLoadCallback(drawChart);
   function drawChart()
   {
    var data = new google.visualization.DataTable(<?php echo $jsonTable; ?>);

    var options = {
     title:'Sensors Data',
     legend:{position:'bottom'},
     chartArea:{width:'95%', height:'65%'}
    };

    var chart = new google.visualization.LineChart(document.getElementById('line_chart'));

    chart.draw(data, options);
   }
  </script>
  <style>
  .page-wrapper
  {
   width:1000px;
   margin:0 auto;
  }
  </style>
 </head>  
 <body>
  <div class="page-wrapper">
   <br />
   <h2 align="center">Display Google Line Chart with JSON PHP & Mysql</h2>
   <div id="line_chart" style="width: 100%; height: 500px"></div>
  </div>
 </body>
</html>


Database



--
-- Database: `testing`
--

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

--
-- Table structure for table `tbl_sensors_data`
--

CREATE TABLE IF NOT EXISTS `tbl_sensors_data` (
  `sensors_data_id` int(11) NOT NULL,
  `sensors_temperature_data` varchar(30) NOT NULL,
  `sensors_data_date` date NOT NULL,
  `sensors_data_time` time NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=484 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_sensors_data`
--

INSERT INTO `tbl_sensors_data` (`sensors_data_id`, `sensors_temperature_data`, `sensors_data_date`, `sensors_data_time`) VALUES
(339, '28', '2017-08-08', '10:00:00'),
(340, '26', '2017-08-07', '10:10:00'),
(341, '36', '2017-08-06', '10:20:00'),
(342, '31', '2017-08-05', '10:30:00'),
(343, '30', '2017-08-04', '10:40:00'),
(344, '27', '2017-08-03', '10:50:00'),
(345, '28', '2017-08-02', '11:00:00'),
(346, '25', '2017-08-01', '11:10:00'),
(347, '27', '2017-07-31', '11:20:00'),
(348, '27', '2017-07-30', '11:30:00'),
(349, '36', '2017-07-29', '11:40:00'),
(350, '30', '2017-07-28', '11:50:00'),
(351, '39', '2017-07-27', '12:00:00'),
(352, '36', '2017-07-26', '12:10:00'),
(353, '32', '2017-07-25', '12:20:00'),
(354, '33', '2017-08-09', '12:30:00'),
(355, '40', '2017-08-09', '12:40:00'),
(356, '33', '2017-08-09', '12:50:00'),
(357, '25', '2017-08-09', '13:00:00'),
(358, '33', '2017-08-09', '13:10:00'),
(359, '26', '2017-08-09', '13:20:00'),
(360, '38', '2017-07-24', '13:30:00'),
(361, '33', '2017-07-23', '13:40:00'),
(362, '37', '2017-07-22', '13:50:00'),
(363, '35', '2017-07-21', '14:00:00'),
(364, '39', '2017-07-20', '14:10:00'),
(365, '26', '2017-07-19', '14:20:00'),
(366, '29', '2017-07-18', '14:30:00'),
(367, '40', '2017-07-17', '14:40:00'),
(368, '40', '2017-07-16', '14:50:00'),
(369, '37', '2017-07-15', '15:00:00'),
(370, '28', '2017-07-14', '15:10:00'),
(371, '26', '2017-07-13', '15:20:00'),
(372, '32', '2017-07-12', '15:30:00'),
(373, '34', '2017-07-11', '15:40:00'),
(374, '31', '2017-07-10', '15:50:00'),
(375, '34', '2017-07-09', '16:00:00'),
(376, '37', '2017-07-08', '16:10:00'),
(377, '31', '2017-07-07', '16:20:00'),
(378, '36', '2017-07-06', '16:30:00'),
(379, '40', '2017-07-05', '16:40:00'),
(380, '27', '2017-07-04', '16:50:00'),
(381, '26', '2017-07-03', '17:00:00'),
(382, '38', '2017-07-02', '17:10:00'),
(383, '39', '2017-07-01', '17:20:00'),
(384, '33', '2017-06-30', '17:30:00'),
(385, '31', '2017-06-29', '17:40:00'),
(386, '38', '2017-06-28', '17:50:00'),
(387, '26', '2017-06-27', '18:00:00'),
(388, '32', '2017-06-26', '18:10:00'),
(389, '30', '2017-06-25', '18:20:00'),
(390, '27', '2017-06-24', '18:30:00'),
(391, '29', '2017-06-23', '18:40:00'),
(392, '39', '2017-06-22', '18:50:00'),
(393, '40', '2017-06-21', '19:00:00'),
(394, '39', '2017-06-20', '19:10:00'),
(395, '38', '2017-06-19', '19:20:00'),
(396, '25', '2017-06-18', '19:30:00'),
(397, '28', '2017-06-17', '19:40:00'),
(398, '37', '2017-06-16', '19:50:00'),
(399, '40', '2017-06-15', '20:00:00'),
(400, '40', '2017-06-14', '20:10:00'),
(401, '40', '2017-06-13', '20:20:00'),
(402, '25', '2017-06-12', '20:30:00'),
(403, '32', '2017-06-11', '20:40:00'),
(404, '34', '2017-06-10', '20:50:00'),
(405, '32', '2017-06-09', '21:00:00'),
(406, '25', '2017-06-08', '21:10:00'),
(407, '31', '2017-06-07', '21:20:00'),
(408, '39', '2017-06-06', '21:30:00'),
(409, '37', '2017-06-05', '21:40:00'),
(410, '30', '2017-06-04', '21:50:00'),
(411, '26', '2017-06-03', '22:00:00'),
(412, '38', '2017-06-02', '22:10:00'),
(413, '28', '2017-06-01', '22:20:00'),
(414, '40', '2017-05-31', '22:30:00'),
(415, '31', '2017-05-30', '22:40:00'),
(416, '34', '2017-05-29', '22:50:00'),
(417, '37', '2017-05-28', '23:00:00'),
(418, '33', '2017-05-27', '23:10:00'),
(419, '25', '2017-05-26', '23:20:00'),
(420, '27', '2017-05-25', '23:30:00'),
(421, '35', '2017-05-24', '23:40:00'),
(422, '30', '2017-05-23', '23:50:00'),
(423, '25', '2017-05-22', '00:00:00'),
(424, '35', '2017-05-21', '00:10:00'),
(425, '29', '2017-05-20', '00:20:00'),
(426, '38', '2017-05-19', '00:30:00'),
(427, '36', '2017-05-18', '00:40:00'),
(428, '32', '2017-05-17', '00:50:00'),
(429, '35', '2017-05-16', '01:00:00'),
(430, '35', '2017-05-15', '01:10:00'),
(431, '32', '2017-05-14', '01:20:00'),
(432, '35', '2017-05-13', '01:30:00'),
(433, '36', '2017-05-12', '01:40:00'),
(434, '39', '2017-05-11', '01:50:00'),
(435, '28', '2017-05-10', '02:00:00'),
(436, '28', '2017-05-09', '02:10:00'),
(437, '40', '2017-05-08', '02:20:00'),
(438, '35', '2017-05-07', '02:30:00'),
(439, '26', '2017-05-06', '02:40:00'),
(440, '36', '2017-05-05', '02:50:00'),
(441, '25', '2017-05-04', '03:00:00'),
(442, '28', '2017-05-03', '03:10:00'),
(443, '34', '2017-05-02', '03:20:00'),
(444, '28', '2017-05-01', '03:30:00'),
(445, '27', '2017-04-30', '03:40:00'),
(446, '25', '2017-04-29', '03:50:00'),
(447, '37', '2017-04-28', '04:00:00'),
(448, '39', '2017-04-27', '04:10:00'),
(449, '33', '2017-04-26', '04:20:00'),
(450, '38', '2017-04-25', '04:30:00'),
(451, '25', '2017-04-24', '04:40:00'),
(452, '28', '2017-04-23', '04:50:00'),
(453, '27', '2017-04-22', '05:00:00'),
(454, '26', '2017-04-21', '05:10:00'),
(455, '38', '2017-04-20', '05:20:00'),
(456, '32', '2017-04-19', '05:30:00'),
(457, '39', '2017-04-18', '05:40:00'),
(458, '33', '2017-04-17', '05:50:00'),
(459, '39', '2017-04-16', '06:00:00'),
(460, '34', '2017-04-15', '06:10:00'),
(461, '28', '2017-04-14', '06:20:00'),
(462, '31', '2017-04-13', '06:30:00'),
(463, '28', '2017-04-12', '06:40:00'),
(464, '40', '2017-04-11', '06:50:00'),
(465, '29', '2017-04-10', '07:00:00'),
(466, '32', '2017-04-09', '07:10:00'),
(467, '27', '2017-04-08', '07:20:00'),
(468, '28', '2017-04-07', '07:30:00'),
(469, '26', '2017-04-06', '07:40:00'),
(470, '29', '2017-04-05', '07:50:00'),
(471, '40', '2017-04-04', '08:00:00'),
(472, '26', '2017-04-03', '08:10:00'),
(473, '32', '2017-04-02', '08:20:00'),
(474, '34', '2017-04-01', '08:30:00'),
(475, '29', '2017-03-31', '08:40:00'),
(476, '35', '2017-03-30', '08:50:00'),
(477, '34', '2017-03-29', '09:00:00'),
(478, '26', '2017-03-28', '09:10:00'),
(479, '33', '2017-03-27', '09:20:00'),
(480, '27', '2017-03-26', '09:30:00'),
(481, '39', '2017-03-25', '09:40:00'),
(482, '34', '2017-03-24', '09:50:00'),
(483, '30', '2017-03-23', '10:00:00');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_sensors_data`
--
ALTER TABLE `tbl_sensors_data`
  ADD PRIMARY KEY (`sensors_data_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_sensors_data`
--
ALTER TABLE `tbl_sensors_data`
  MODIFY `sensors_data_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=484;


Saturday, 5 August 2017

Load Last N Days Data into Datatables using PHP Ajax



In this blog we have made discussion on topic like How to load last dynamic days data into Datatables by using PHP script with Ajax Jquery. Most of the programmers are used Jquery Datatables plugin for display data in tabular format on web page. Because it is light weight and it provide lots of feature like search data, sort table column, pagination and many more other features. So for example now we want to load last 30 days data from mysql table by using PHP script and display under Jquery Datatables plugin. Because this type of feature is not found under this plugin. For use functionality of this feature we want to write some extra code at front end and back end.

Here we have use HTML select box for list number of days. So user can select days for get last particular days data. When user select days from select box then we will first destroy Datatable by using destroy() method and then after we have again called Datatables Ajax request with value of days select from drop down list box. So here we have send value of selected days to server via Ajax request. On PHP server script we have append query with condition like fetch data between both date. In condition first we want to get start date, so for get start date we have minus selected days from current date. So this ways we have get start date and for end date we can get from CURDATE() mysql function. So this ways we can get start date and end date for fetch data between this two date. So this ways we have append query for fetch last particular data from Mysql table and it will load data without refresh of web page.

If we have use date datatype in table then this feature will helpful to fetch last N days data from table by using PHP code with Ajax JQuery. By using this functionality with Jquery Datatables plugin then we can easily get last particular days data and load into table. This feature will increase the value of your code and add extra functionality into Datatables features. So I hope you have understand this topic which we have describe in this post. Below this you can find source code of this post.




Source Code


index.php



<html>
 <head>
  <title>Display Last N Days Data into Datatables 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>
  
 </head>
 <body>
  <div class="container">
   <h2 align="center">Display Last N Days Data into Datatables using PHP Ajax</h2>
   <br />
   <div class="row">
    <div class="col-md-2">
     <select name="days_filter" id="days_filter" class="form-control">
      <option value="">Select Days</option>
      <option value="180">In 180 Days</option>
      <option value="90">In 90 Days</option>
      <option value="60">In 60 Days</option>
                     <option value="30">In 30 Days</option>
     </select>
    </div>
    <div style="clear:both"></div>
    <br />
    <div class="table-responsive">
     <table id="order_data" class="table table-bordered table-striped">
      <thead>
       <tr>
        <th>Customer Name</th>
        <th>Product Name</th>
        <th>Order Value</th>
        <th>Order Date</th>
       </tr>
      </thead>
     </table>
    </div>
   </div>
  </div>
 </body>
</html>



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

 function load_data(is_days)
 {
  var dataTable = $('#order_data').DataTable({
   "processing":true,
   "serverSide":true,
   "order":[],
   "ajax":{
    url:"fetch.php",
    type:"POST",
    data:{is_days:is_days}
   }
  });
 }

 $(document).on('change', '#days_filter', function(){
  var no_of_days = $(this).val();
  $('#order_data').DataTable().destroy();
  if(no_of_days != '')
  {
   load_data(no_of_days);
  }
  else
  {
   load_data();
  }
 });
 
});
</script>


fetch.php



<?php
//fetch.php
$connect = mysqli_connect("localhost", "root", "", "testing");
$column = array("order_customer_name", "order_item", "order_value", "order_date");
$query = "
 SELECT * FROM tbl_order WHERE 
";

if(isset($_POST["is_days"]))
{
 $query .= "order_date BETWEEN CURDATE() - INTERVAL ".$_POST["is_days"]." DAY AND CURDATE() AND ";
}

if(isset($_POST["search"]["value"]))
{
 $query .= '(order_customer_name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR order_item LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR order_value LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR order_date LIKE "%'.$_POST["search"]["value"].'%") ';
}

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

$query1 = '';

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

$number_filter_row = mysqli_num_rows(mysqli_query($connect, $query));

$result = mysqli_query($connect, $query . $query1);

$data = array();

while($row = mysqli_fetch_array($result))
{
 $sub_array = array();
 $sub_array[] = $row["order_customer_name"];
 $sub_array[] = $row["order_item"];
 $sub_array[] = $row["order_value"];
 $sub_array[] = $row["order_date"];
 $data[] = $sub_array;
}

function get_all_data($connect)
{
 $query = "SELECT * FROM tbl_order";
 $result = mysqli_query($connect, $query);
 return mysqli_num_rows($result);
}

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

echo json_encode($output);



?>



--
-- Database: `testing`
--

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

--
-- Table structure for table `tbl_order`
--

CREATE TABLE IF NOT EXISTS `tbl_order` (
  `order_id` int(11) NOT NULL,
  `order_customer_name` varchar(255) NOT NULL,
  `order_item` varchar(255) NOT NULL,
  `order_value` double(12,2) NOT NULL,
  `order_date` date NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_order`
--

INSERT INTO `tbl_order` (`order_id`, `order_customer_name`, `order_item`, `order_value`, `order_date`) VALUES
(1, 'David E. Gary', 'Shuttering Plywood', 1500.00, '2017-07-14'),
(2, 'Eddie M. Douglas', 'Aluminium Heavy Windows', 2000.00, '2017-06-08'),
(3, 'Oscar D. Scoggins', 'Plaster Of Paris', 150.00, '2017-05-29'),
(4, 'Clara C. Kulik', 'Spin Driller Machine', 350.00, '2017-06-30'),
(5, 'Christopher M. Victory', 'Shopping Trolley', 100.00, '2017-08-01'),
(6, 'Jessica G. Fischer', 'CCTV Camera', 800.00, '2017-04-02'),
(7, 'Roger R. White', 'Truck Tires', 2000.00, '2017-03-28'),
(8, 'Susan C. Richardson', 'Glass Block', 200.00, '2017-02-04'),
(9, 'David C. Jury', 'Casing Pipes', 500.00, '2017-07-27'),
(10, 'Lori C. Skinner', 'Glass PVC Rubber', 1800.00, '2017-05-30'),
(11, 'Shawn S. Derosa', 'Sony HTXT1 2.1-Channel TV', 180.00, '2017-06-03'),
(12, 'Karen A. McGee', 'Over-the-Ear Stereo Headphones ', 25.00, '2017-05-01'),
(13, 'Kristine B. McGraw', 'Tristar 10" Round Copper Chef Pan with Glass Lid', 20.00, '2017-07-30'),
(14, 'Gary M. Porter', 'ROBO 3D R1 Plus 3D Printer', 600.00, '2017-05-02'),
(15, 'Sarah D. Hunter', 'Westinghouse Select Kitchen Appliances', 35.00, '2017-06-29'),
(16, 'Diane J. Thomas', 'SanDisk Ultra 32GB microSDHC', 12.00, '2017-04-05'),
(17, 'Helena J. Quillen', 'TaoTronics Dimmable Outdoor String Lights', 16.00, '2017-05-04'),
(18, 'Arlette G. Nathan', 'TaoTronics Bluetooth in-Ear Headphones', 25.00, '2017-06-03'),
(19, 'Ronald S. Vallejo', 'Scotchgard Fabric Protector, 10-Ounce, 2-Pack', 20.00, '2017-05-03'),
(20, 'Felicia L. Sorensen', 'Anker 24W Dual USB Wall Charger with Foldable Plug', 12.00, '2017-07-04');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_order`
--
ALTER TABLE `tbl_order`
  ADD PRIMARY KEY (`order_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_order`
--
ALTER TABLE `tbl_order`
  MODIFY `order_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=21;

Wednesday, 2 August 2017

Dynamic Product Slider using Bootstrap Carousel with PHP



If you have looking for web tutorial on How to Create Dynamic Product or Image Slider by using PHP then you have come here for right post in which we have discus topic like How to developed dynamic slider by using Twitter Bootstrap Carousel with PHP Script. Usually We have put Image Slider or Product slider on our website home page and in this we have display our online store hot product and from that customer can go to that particular product page. So it this slider make dynamically then on every day we can display different product images in slider on daily basis.

So here we have make this simple web tutorial in which we have create dynamic image slider by using Twitter Bootstrap with PHP script and Mysql database. For implement product slider we have use Bootstrap Carousel plugin, by using this plugin we can easily make simple image slider. But here we want to make image slider dynamically so we have use PHP simple script. In this script it will fetch banner data from Mysql table and make dynamic code as per Bootstrap Carousel. So, If we want to display other images in slider then just we want to change table data and that php script will make slider as data available in table and that will display that particular image in slider.

This is simple script which create Dynamic Image slider by using PHP Mysql with Bootstrap Carousel. In most of the website we can see Image slider is place on home page. So if we can make this slider dynamically then we can easily change image from slider and can display different images in slider every day. This simple tutorial and from this post you can learn how to create bootstrap carousel slider with PHP and Mysql



Source Code



<?php
//index.php
$connect = mysqli_connect("localhost", "root", "", "testing");
function make_query($connect)
{
 $query = "SELECT * FROM banner ORDER BY banner_id ASC";
 $result = mysqli_query($connect, $query);
 return $result;
}

function make_slide_indicators($connect)
{
 $output = ''; 
 $count = 0;
 $result = make_query($connect);
 while($row = mysqli_fetch_array($result))
 {
  if($count == 0)
  {
   $output .= '
   <li data-target="#dynamic_slide_show" data-slide-to="'.$count.'" class="active"></li>
   ';
  }
  else
  {
   $output .= '
   <li data-target="#dynamic_slide_show" data-slide-to="'.$count.'"></li>
   ';
  }
  $count = $count + 1;
 }
 return $output;
}

function make_slides($connect)
{
 $output = '';
 $count = 0;
 $result = make_query($connect);
 while($row = mysqli_fetch_array($result))
 {
  if($count == 0)
  {
   $output .= '<div class="item active">';
  }
  else
  {
   $output .= '<div class="item">';
  }
  $output .= '
   <img src="banner/'.$row["banner_image"].'" alt="'.$row["banner_title"].'" />
   <div class="carousel-caption">
    <h3>'.$row["banner_title"].'</h3>
   </div>
  </div>
  ';
  $count = $count + 1;
 }
 return $output;
}

?>
<!DOCTYPE html>
<html>
 <head>
  <title>How to Make Dynamic Bootstrap Carousel with PHP</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">
   <h2 align="center">How to Make Dynamic Bootstrap Carousel with PHP</h2>
   <br />
   <div id="dynamic_slide_show" class="carousel slide" data-ride="carousel">
    <ol class="carousel-indicators">
    <?php echo make_slide_indicators($connect); ?>
    </ol>

    <div class="carousel-inner">
     <?php echo make_slides($connect); ?>
    </div>
    <a class="left carousel-control" href="#dynamic_slide_show" data-slide="prev">
     <span class="glyphicon glyphicon-chevron-left"></span>
     <span class="sr-only">Previous</span>
    </a>

    <a class="right carousel-control" href="#dynamic_slide_show" data-slide="next">
     <span class="glyphicon glyphicon-chevron-right"></span>
     <span class="sr-only">Next</span>
    </a>

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


Database



--
-- Database: `testing`
--

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

--
-- Table structure for table `banner`
--

CREATE TABLE IF NOT EXISTS `banner` (
  `banner_id` int(11) NOT NULL,
  `banner_title` varchar(200) NOT NULL,
  `banner_image` varchar(200) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `banner`
--

INSERT INTO `banner` (`banner_id`, `banner_title`, `banner_image`) VALUES
(1, 'Banner 1', 'banner-1.jpg'),
(2, 'Banner 2', 'banner-2.jpg'),
(3, 'Banner 3', 'banner-3.jpg'),
(4, 'Banner 4', 'banner-4.jpg'),
(5, 'Banner 5', 'banner-5.jpg');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `banner`
--
ALTER TABLE `banner`
  ADD PRIMARY KEY (`banner_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `banner`
--
ALTER TABLE `banner`
  MODIFY `banner_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=6;

Saturday, 29 July 2017

Update Mysql Database through Upload CSV File using PHP



In this post We have discuss topic like how to upload CSV file for update mysql table data by using PHP script. We have received to many request on make tutorial on how to update mysql data by uploading CSV file by simple PHP script. In one of our previous tutorial we have already learn how to insert data into mysql table by uploading CSV file using PHP code. But now here topic is something different, here we do not want to insert data but we want to update existing inserted data by uploading CSV file using simple PHP code.

This type of feature mainly used if you have manage online store or online restaurant booking system in which you want to update data on daily basis. For example, suppose you have run online store and on your website home page want to display daily different product then at that time if you have build this type of feature for your web application. By using this feature you can easily update existing data and that data you can display on your web application. So this way you can use this feature for display different data on your web application.

In simple terms by uploading file you can edit large amount of data in single click event. For developed this type of simple application we just want knowledge of PHP script filesystem function. Here we have used fopen() and fclose() function for open file for data from particular file. After this we have use fgetcsv() function, by using this function we can get comma separated values data in array format. After getting CSV data in array format, then after we have make simple mysql update data query for edit existing data in database. This way we can update existing data of Mysql table by using CSV file using simple PHP script.



Source Code



<?php
//index.php
$connect = mysqli_connect("localhost", "root", "", "testing");
$message = '';

if(isset($_POST["upload"]))
{
 if($_FILES['product_file']['name'])
 {
  $filename = explode(".", $_FILES['product_file']['name']);
  if(end($filename) == "csv")
  {
   $handle = fopen($_FILES['product_file']['tmp_name'], "r");
   while($data = fgetcsv($handle))
   {
    $product_id = mysqli_real_escape_string($connect, $data[0]);
    $product_category = mysqli_real_escape_string($connect, $data[1]);  
                $product_name = mysqli_real_escape_string($connect, $data[2]);
    $product_price = mysqli_real_escape_string($connect, $data[3]);
    $query = "
     UPDATE daily_product 
     SET product_category = '$product_category', 
     product_name = '$product_name', 
     product_price = '$product_price' 
     WHERE product_id = '$product_id'
    ";
    mysqli_query($connect, $query);
   }
   fclose($handle);
   header("location: index.php?updation=1");
  }
  else
  {
   $message = '<label class="text-danger">Please Select CSV File only</label>';
  }
 }
 else
 {
  $message = '<label class="text-danger">Please Select File</label>';
 }
}

if(isset($_GET["updation"]))
{
 $message = '<label class="text-success">Product Updation Done</label>';
}

$query = "SELECT * FROM daily_product";
$result = mysqli_query($connect, $query);
?>
<!DOCTYPE html>
<html>
 <head>
  <title>Update Mysql Database through Upload CSV File using PHP</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">
   <h2 align="center">Update Mysql Database through Upload CSV File using PHP</a></h2>
   <br />
   <form method="post" enctype='multipart/form-data'>
    <p><label>Please Select File(Only CSV Formate)</label>
    <input type="file" name="product_file" /></p>
    <br />
    <input type="submit" name="upload" class="btn btn-info" value="Upload" />
   </form>
   <br />
   <?php echo $message; ?>
   <h3 align="center">Deals of the Day</h3>
   <br />
   <div class="table-responsive">
    <table class="table table-bordered table-striped">
     <tr>
      <th>Category</th>
      <th>Product Name</th>
      <th>Product Price</th>
     </tr>
     <?php
     while($row = mysqli_fetch_array($result))
     {
      echo '
      <tr>
       <td>'.$row["product_category"].'</td>
       <td>'.$row["product_name"].'</td>
       <td>'.$row["product_price"].'</td>
      </tr>
      ';
     }
     ?>
    </table>
   </div>
  </div>
 </body>
</html>



Database



CREATE TABLE IF NOT EXISTS `daily_product` (
  `daily_product_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `product_category` varchar(250) NOT NULL,
  `product_name` varchar(250) NOT NULL,
  `product_price` double(10,2) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `daily_product`
--

INSERT INTO `daily_product` (`daily_product_id`, `product_id`, `product_category`, `product_name`, `product_price`) VALUES
(1, 101, 'Home and Garden', 'Fashionoma Hobby Toolkit', 199.00),
(2, 102, 'Electronics', 'Samsung EVO 32 GB', 749.00),
(3, 103, 'Fashion', 'Nova NHT Trimmer', 349.00),
(4, 104, 'Mobiles', 'Cases and Safeguard', 299.00),
(5, 105, 'Fashions', 'Womens Bag', 180.00),
(6, 106, 'Home and Garden', 'Insects Repellents', 99.00),
(7, 107, 'Home and Garden', 'Pressure Cookers and pans', 499.00),
(8, 108, 'Electronis', 'Mitashi TV', 6999.00),
(9, 109, 'Home and Garden', 'Water Gas Graysers', 2499.00),
(10, 110, 'Office', 'HP Deskjet Ink Advantage', 3999.00);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `daily_product`
--
ALTER TABLE `daily_product`
  ADD PRIMARY KEY (`daily_product_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `daily_product`
--
ALTER TABLE `daily_product`
  MODIFY `daily_product_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=11;

Tuesday, 25 July 2017

PHP Filesystem with Ajax JQuery

1. List Folder from Directory


2. Create Folder or Directory



3. Change or Rename Folder or Directory Name



4. Upload File in Folder or Directory


5. List Files from Folder or Directory



6. Remove or Delete Files From Folder or Directory



7. Remove or Delete Folder or Directory



8. Change File Name Inside Folder



9. How to Get Folder or Directory Size



There are received many request on made tutorial on PHP Filesystem, so in this post we have discuss PHP Filesystem with Ajax JQuery. In this post we will discuss different function of PHP Filesystem function how can we used particular function as per our requirement. PHP Filesystem mainly deal with files or folder or directory. If you are looking for web tutorial on PHP filesystem then you have to follow this post in which we have used this function with Ajax Jquery. So we can perform all operation without refresh of web page.

For discuss this tutorial we will first list all folder from directory by using PHP glob() function. By using this function we can get all list of files or folder now we want to get only directory then we have used array_filter(). So by using both function we can filter only folder from directory and display on web page. After this we want to create new folder dynamically, so we have use mkdir() PHP Filesystem function. By using this function we can create new folder in directory. After create new folder now we want to change folder name so we have use rename() function for change particular folder or directory name dynamically. Then after we want to upload file in particular folder, so we have use move_uploaded_file() function for upload files in particular folder. After uploading files in particular folder, now we want to list files from particular folder, show we have use scandir() function, by using this function we have list files from particular folder. After list files from particular folder now we want to remove files from folder or directory so we have use unlink() function. This function will remove files from particular folder. And lastly we want to remove folder or directory dynamically so we have use rmdir() function. By using this function we can remove folder or directory but this function will remove only blank folder or directory, so first we want to remove all files from folder, then we can use this folder or directory.

Here we have not only use PHP script but also we have use Ajax JQuery and Bootstrap modal. We can do this all operation without refresh of web page. For front end we have use Ajax JQuery and Bootstrap modal, we have send request to server by using Ajax and it will send particular operation request to server and at server side we have use different PHP Filesystem function for do different operation like list folder or directory, create new folder or directory, rename particular folder or directory, upload files in folder or directory, list files from folder or directory, remove files from folder and delete or remove folder or directory. This all operation we can do without refresh of web page because we have use Ajax Jquery with PHP script. Below you can find complete source code and above you can get all video of this PHP filesystem video series.




Source Code


index.php



<!DOCTYPE html>
<html>
 <head>
  <title>PHP Filesystem with 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 /><br />
  <div class="container">
   <h2 align="center">PHP Filesystem with Ajax JQuery</a></h2>
   <br />
   <div align="right">
    <button type="button" name="create_folder" id="create_folder" class="btn btn-success">Create</button>
   </div>
   <br />
   <div class="table-responsive" id="folder_table">
    
   </div>
  </div>
 </body>
</html>

<div id="folderModal" class="modal fade" 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"><span id="change_title">Create Folder</span></h4>
   </div>
   <div class="modal-body">
    <p>Enter Folder Name
    <input type="text" name="folder_name" id="folder_name" class="form-control" /></p>
    <br />
    <input type="hidden" name="action" id="action" />
    <input type="hidden" name="old_name" id="old_name" />
    <input type="button" name="folder_button" id="folder_button" class="btn btn-info" value="Create" />
    
   </div>
   <div class="modal-footer">
    <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
   </div>
  </div>
 </div>
</div>
<div id="uploadModal" class="modal fade" 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">Upload File</h4>
   </div>
   <div class="modal-body">
    <form method="post" id="upload_form" enctype='multipart/form-data'>
     <p>Select Image
     <input type="file" name="upload_file" /></p>
     <br />
     <input type="hidden" name="hidden_folder_name" id="hidden_folder_name" />
     <input type="submit" name="upload_button" class="btn btn-info" value="Upload" />
    </form>
   </div>
   <div class="modal-footer">
    <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
   </div>
  </div>
 </div>
</div>

<div id="filelistModal" class="modal fade" 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">File List</h4>
   </div>
   <div class="modal-body" id="file_list">
    
   </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(){
 
 load_folder_list();
 
 function load_folder_list()
 {
  var action = "fetch";
  $.ajax({
   url:"action.php",
   method:"POST",
   data:{action:action},
   success:function(data)
   {
    $('#folder_table').html(data);
   }
  });
 }
 
 $(document).on('click', '#create_folder', function(){
  $('#action').val("create");
  $('#folder_name').val('');
  $('#folder_button').val('Create');
  $('#folderModal').modal('show');
  $('#old_name').val('');
  $('#change_title').text("Create Folder");
 });
 
 $(document).on('click', '#folder_button', function(){
  var folder_name = $('#folder_name').val();
  var old_name = $('#old_name').val();
  var action = $('#action').val();
  if(folder_name != '')
  {
   $.ajax({
    url:"action.php",
    method:"POST",
    data:{folder_name:folder_name, old_name:old_name, action:action},
    success:function(data)
    {
     $('#folderModal').modal('hide');
     load_folder_list();
     alert(data);
    }
   });
  }
  else
  {
   alert("Enter Folder Name");
  }
 });
 
 $(document).on("click", ".update", function(){
  var folder_name = $(this).data("name");
  $('#old_name').val(folder_name);
  $('#folder_name').val(folder_name);
  $('#action').val("change");
  $('#folderModal').modal("show");
  $('#folder_button').val('Update');
  $('#change_title').text("Change Folder Name");
 });
 
 $(document).on("click", ".delete", function(){
  var folder_name = $(this).data("name");
  var action = "delete";
  if(confirm("Are you sure you want to remove it?"))
  {
   $.ajax({
    url:"action.php",
    method:"POST",
    data:{folder_name:folder_name, action:action},
    success:function(data)
    {
     load_folder_list();
     alert(data);
    }
   });
  }
 });
 
 $(document).on('click', '.upload', function(){
  var folder_name = $(this).data("name");
  $('#hidden_folder_name').val(folder_name);
  $('#uploadModal').modal('show');
 });
 
 $('#upload_form').on('submit', function(){
  $.ajax({
   url:"upload.php",
   method:"POST",
   data: new FormData(this),
   contentType: false,
   cache: false,
   processData:false,
   success: function(data)
   { 
    load_folder_list();
    alert(data);
   }
  });
 });
 
 $(document).on('click', '.view_files', function(){
  var folder_name = $(this).data("name");
  var action = "fetch_files";
  $.ajax({
   url:"action.php",
   method:"POST",
   data:{action:action, folder_name:folder_name},
   success:function(data)
   {
    $('#file_list').html(data);
    $('#filelistModal').modal('show');
   }
  });
 });
 
 $(document).on('click', '.remove_file', function(){
  var path = $(this).attr("id");
  var action = "remove_file";
  if(confirm("Are you sure you want to remove this file?"))
  {
   $.ajax({
    url:"action.php",
    method:"POST",
    data:{path:path, action:action},
    success:function(data)
    {
     alert(data);
     $('#filelistModal').modal('hide');
     load_folder_list();
    }
   });
  }
 });

$(document).on('blur', '.change_file_name', function(){
  var folder_name = $(this).data("folder_name");
  var old_file_name = $(this).data("file_name");
  var new_file_name = $(this).text();
  var action = "change_file_name";
  $.ajax({
   url:"action.php",
   method:"POST",
   data:{folder_name:folder_name, old_file_name:old_file_name, new_file_name:new_file_name, action:action},
   success:function(data)
   {
    alert(data);
   }
  });
 });
 
});
</script>


action.php



<?php

function format_folder_size($size)
{
 if ($size >= 1073741824)
 {
  $size = number_format($size / 1073741824, 2) . ' GB';
 }
    elseif ($size >= 1048576)
    {
        $size = number_format($size / 1048576, 2) . ' MB';
    }
    elseif ($size >= 1024)
    {
        $size = number_format($size / 1024, 2) . ' KB';
    }
    elseif ($size > 1)
    {
        $size = $size . ' bytes';
    }
    elseif ($size == 1)
    {
        $size = $size . ' byte';
    }
    else
    {
        $size = '0 bytes';
    }
 return $size;
}

function get_folder_size($folder_name)
{
 $total_size = 0;
 $file_data = scandir($folder_name);
 foreach($file_data as $file)
 {
  if($file === '.' or $file === '..')
  {
   continue;
  }
  else
  {
   $path = $folder_name . '/' . $file;
   $total_size = $total_size + filesize($path);
  }
 }
 return format_folder_size($total_size);
}

if(isset($_POST["action"]))
{
 if($_POST["action"] == "fetch")
 {
  $folder = array_filter(glob('*'), 'is_dir');
  
  $output = '
  <table class="table table-bordered table-striped">
   <tr>
    <th>Folder Name</th>
    <th>Total File</th>
    <th>Size</th>
    <th>Update</th>
    <th>Delete</th>
    <th>Upload File</th>
    <th>View Uploaded File</th>
   </tr>
   ';
  if(count($folder) > 0)
  {
   foreach($folder as $name)
   {
    $output .= '
     <tr>
      <td>'.$name.'</td>
      <td>'.(count(scandir($name)) - 2).'</td>
      <td>'.get_folder_size($name).'</td>
      <td><button type="button" name="update" data-name="'.$name.'" class="update btn btn-warning btn-xs">Update</button></td>
      <td><button type="button" name="delete" data-name="'.$name.'" class="delete btn btn-danger btn-xs">Delete</button></td>
      <td><button type="button" name="upload" data-name="'.$name.'" class="upload btn btn-info btn-xs">Upload File</button></td>
      <td><button type="button" name="view_files" data-name="'.$name.'" class="view_files btn btn-default btn-xs">View Files</button></td>
     </tr>';
   }
  }
  else
  {
   $output .= '
    <tr>
     <td colspan="6">No Folder Found</td>
    </tr>
   ';
  }
  $output .= '</table>';
  echo $output;
 }
 
 if($_POST["action"] == "create")
 {
  if(!file_exists($_POST["folder_name"])) 
  {
   mkdir($_POST["folder_name"], 0777, true);
   echo 'Folder Created';
  }
  else
  {
   echo 'Folder Already Created';
  }
 }
 if($_POST["action"] == "change")
 {
  if(!file_exists($_POST["folder_name"]))
  {
   rename($_POST["old_name"], $_POST["folder_name"]);
   echo 'Folder Name Change';
  }
  else
  {
   echo 'Folder Already Created';
  }
 }
 
 if($_POST["action"] == "delete")
 {
  $files = scandir($_POST["folder_name"]);
  foreach($files as $file)
  {
   if($file === '.' or $file === '..')
   {
    continue;
   }
   else
   {
    unlink($_POST["folder_name"] . '/' . $file);
   }
  }
  if(rmdir($_POST["folder_name"]))
  {
   echo 'Folder Deleted';
  }
 }
 
 if($_POST["action"] == "fetch_files")
 {
  $file_data = scandir($_POST["folder_name"]);
  $output = '
  <table class="table table-bordered table-striped">
   <tr>
    <th>Image</th>
    <th>File Name</th>
    <th>Delete</th>
   </tr>
  ';
  
  foreach($file_data as $file)
  {
   if($file === '.' or $file === '..')
   {
    continue;
   }
   else
   {
    $path = $_POST["folder_name"] . '/' . $file;
    $output .= '
    <tr>
     <td><img src="'.$path.'" class="img-thumbnail" height="50" width="50" /></td>
     <td contenteditable="true" data-folder_name="'.$_POST["folder_name"].'"  data-file_name = "'.$file.'" class="change_file_name">'.$file.'</td>
     <td><button name="remove_file" class="remove_file btn btn-danger btn-xs" id="'.$path.'">Remove</button></td>
    </tr>
    ';
   }
  }
  $output .='</table>';
  echo $output;
 }
 
 if($_POST["action"] == "remove_file")
 {
  if(file_exists($_POST["path"]))
  {
   unlink($_POST["path"]);
   echo 'File Deleted';
  }
 }
 
 if($_POST["action"] == "change_file_name")
 {
  $old_name = $_POST["folder_name"] . '/' . $_POST["old_file_name"];
  $new_name = $_POST["folder_name"] . '/' . $_POST["new_file_name"];
  if(rename($old_name, $new_name))
  {
   echo 'File name change successfully';
  }
  else
  {
   echo 'There is an error';
  }
 }
}
?>


upload.php



<?php
if($_FILES["upload_file"]["name"] != '')
{
 $data = explode(".", $_FILES["upload_file"]["name"]);
 $extension = $data[1];
 $allowed_extension = array("jpg", "png", "gif");
 if(in_array($extension, $allowed_extension))
 {
  $new_file_name = rand() . '.' . $extension;
  $path = $_POST["hidden_folder_name"] . '/' . $new_file_name;
  if(move_uploaded_file($_FILES["upload_file"]["tmp_name"], $path))
  {
   echo 'Image Uploaded';
  }
  else
  {
   echo 'There is some error';
  }
 }
 else
 {
  echo 'Invalid Image File';
 }
}
else
{
 echo 'Please Select Image';
}
?>

Thursday, 13 July 2017

Datatables Individual column searching using PHP Ajax Jquery



This is one more post on Jquery Datatables plugin. In this post we have made discussion on filter or searching individual data in Datatable by using PHP script with JQuery Ajax. By using this feature we can search or event you can also say filter specific column data in Datatable. There are many in build functionality are available with this Jquery plugin. But if you want to make this type of advance functionality then you have to write some code at server side for search or filter of specific column data.

We all know Jquery Datatable plugin is widely used for display our web application data on web page in tabular format because it has many rich features are available for ready to use which are very usefull according to needs. So with those feature here we can add one more features we can use with this plugi. Because searching or filtering of data according to particular column then it is also a one type of useful feature for our wep application. If you have large amount of data and from that data you want to filter data according to data of particular column then at that time this type of functionality will reduce your work and increase output of your web application.

For develop this feature we do want to write some many line of code but you have to pass only value of particular column into Ajax Datatable method and by this method we have pass value to server side. So at server side PHP script we have just append query where condition regarding the filter or searching of individual column. So when it will pass any data to server script then it will produce output according to value of that data. But if it has pass blank value then it will send all data from Database. So for develop filter or search data according to particular column data by using PHP script with Ajax JQuery, then you have to follow below source code.




Source Code


index.php



<?php 
$connect = mysqli_connect("localhost", "root", "", "testing1");
$query = "SELECT * FROM category ORDER BY category_name ASC";
$result = mysqli_query($connect, $query);
?>
<html>
 <head>
  <title>Datatables Individual column searching using PHP Ajax Jquery</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>
  
 </head>
 <body>
  <div class="container">
   <h1 align="center">Datatables Individual column searching using PHP Ajax Jquery</h1>
   <br />
   
   <div class="table-responsive">
    <table id="product_data" class="table table-bordered table-striped">
     <thead>
      <tr>
       <th>Sr. No.</th>
       <th>Product Name</th>
       <th>
        <select name="category" id="category" class="form-control">
         <option value="">Category Search</option>
         <?php 
         while($row = mysqli_fetch_array($result))
         {
          echo '<option value="'.$row["category_id"].'">'.$row["category_name"].'</option>';
         }
         ?>
        </select>
       </th>
       <th>Product Price</th>
      </tr>
     </thead>
    </table>
   </div>
  </div>
 </body>
</html>



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

 function load_data(is_category)
 {
  var dataTable = $('#product_data').DataTable({
   "processing":true,
   "serverSide":true,
   "order":[],
   "ajax":{
    url:"fetch.php",
    type:"POST",
    data:{is_category:is_category}
   },
   "columnDefs":[
    {
     "targets":[2],
     "orderable":false,
    },
   ],
  });
 }

 $(document).on('change', '#category', function(){
  var category = $(this).val();
  $('#product_data').DataTable().destroy();
  if(category != '')
  {
   load_data(category);
  }
  else
  {
   load_data();
  }
 });
});
</script>


fetch.php



<?php
//fetch.php
$connect = mysqli_connect("localhost", "root", "", "testing1");
$column = array("product.id", "product.name", "category.category_name", "product.price");
$query = "
 SELECT * FROM product 
 INNER JOIN category 
 ON category.category_id = product.category 
";
$query .= " WHERE ";
if(isset($_POST["is_category"]))
{
 $query .= "product.category = '".$_POST["is_category"]."' AND ";
}
if(isset($_POST["search"]["value"]))
{
 $query .= '(product.id LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR product.name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR category.category_name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR product.price LIKE "%'.$_POST["search"]["value"].'%") ';
}

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

$query1 = '';

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

$number_filter_row = mysqli_num_rows(mysqli_query($connect, $query));

$result = mysqli_query($connect, $query . $query1);

$data = array();

while($row = mysqli_fetch_array($result))
{
 $sub_array = array();
 $sub_array[] = $row["id"];
 $sub_array[] = $row["name"];
 $sub_array[] = $row["category_name"];
 $sub_array[] = $row["price"];
 $data[] = $sub_array;
}

function get_all_data($connect)
{
 $query = "SELECT * FROM product";
 $result = mysqli_query($connect, $query);
 return mysqli_num_rows($result);
}

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

echo json_encode($output);

?>


Database



--
-- Database: `testing1`
--

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

--
-- Table structure for table `category`
--

CREATE TABLE IF NOT EXISTS `category` (
  `category_id` int(11) NOT NULL,
  `category_name` varchar(250) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `category`
--

INSERT INTO `category` (`category_id`, `category_name`) VALUES
(1, 'Mobiles'),
(2, 'Computers'),
(3, 'Clothing'),
(4, 'Beauty Item'),
(5, 'Sports Item'),
(6, 'Toys Item'),
(7, 'Books'),
(8, 'Entertainment Item');

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

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

CREATE TABLE IF NOT EXISTS `product` (
  `id` int(11) NOT NULL,
  `category` int(11) NOT NULL,
  `name` varchar(250) NOT NULL,
  `price` double(10,2) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=latin1;

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

INSERT INTO `product` (`id`, `category`, `name`, `price`) VALUES
(1, 1, 'Save on BLU Advance 5.5 HD', 74.99),
(2, 2, 'Dell Inspiron 15.6" Gaming Laptop', 860.00),
(3, 3, 'Women''s Slim Sleeveless', 69.00),
(4, 4, 'Andis 1875-Watt Fold-N-Go Ionic Hair Dryer', 17.00),
(5, 5, 'GM Ripple Cricket Grip, Set Of 3', 66.00),
(6, 6, 'Barbie Fashions and Accessories', 12.00),
(7, 7, 'The Ministry of Utmost Happiness', 6.00),
(8, 8, 'The Great Gatsby (3D)', 8.00),
(9, 1, 'iVooMi Me 1+', 49.00),
(10, 2, 'Apple MacBook Air MQD32HN/A 13.3-inch Laptop 2017', 896.00),
(11, 3, 'Balenzia Premium Mercerised Cotton Loafer Socks', 5.00),
(12, 4, 'Organix Mantra Lemon Cold Pressed Essential Oil', 4.50),
(13, 5, 'SpeedArm Cricket Ball Thrower', 15.00),
(14, 6, 'Mattel Bounce Off Game, Multi Color', 10.00),
(15, 7, 'Seven Days With Her Boss', 5.00),
(16, 8, 'Supernatural Season 1-9 DVD', 22.00),
(17, 1, 'InFocus Turbo 5', 189.00),
(18, 2, 'HP 15-bg008AU 15.6-inch Laptop , Jack Black', 350.00),
(19, 3, 'Seven Rocks Men''s V-Neck Cotton Tshirt', 12.00),
(20, 4, 'Exel Elixir Sublime Antioxidant Serum Cream', 55.00),
(21, 5, 'Gray Nicolls Bat Repair Kit', 9.00),
(22, 6, 'Think Fun Rush Hour, Multi Color', 22.00),
(23, 7, 'Pregnancy Notes: Before, During & After', 5.00),
(24, 8, 'Sherlock Season - 4', 15.00),
(25, 1, 'Vivo Y53', 105.00),
(26, 2, 'Dell Inspiron 15-3567 15.6-inch Laptop', 356.00),
(27, 3, 'Fastrack Sport Sunglasses (Black) (P222GR1)', 14.00),
(28, 4, 'Exel Lotion with stabilized Tea Tree Oil', 28.00),
(29, 5, 'Burn Vinyl Hexagonal Dumbbell', 45.00),
(30, 6, 'Cup Cake Surprise Princess', 8.00),
(31, 7, 'Word Power Made Easy', 2.00),
(32, 8, 'Star Wars: The Force Awakens', 5.00),
(33, 1, 'Lenovo Vibe K5 (Gold, VoLTE update)', 65.00),
(34, 2, 'Lenovo 110 -15ACL 15.6-inch Laptop , Black', 225.00),
(35, 3, 'Zacharias Ankle Socks Pack of 12 Pair', 5.00),
(36, 4, 'Exel SUNSCREEN Broad Spectrum UVA & UVB', 26.00),
(37, 5, 'Burn 500124 Inter Lock Mat (Black)', 24.00),
(38, 6, 'Toyshine Devis Boy 9', 10.00),
(39, 7, 'Think and Grow Rich', 2.50),
(40, 8, 'The Jungle Book', 10.00);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `category`
--
ALTER TABLE `category`
  ADD PRIMARY KEY (`category_id`);

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

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `category`
--
ALTER TABLE `category`
  MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=9;
--
-- AUTO_INCREMENT for table `product`
--
ALTER TABLE `product`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=41;