<?php
include 'connect.php';
try {
$where =" 1=1 ";
$order_by="mName";
$rows=25;
$current=1;
$limit_l=($current * $rows) - ($rows);
$limit_h=$limit_lower + $rows ;
//Handles Sort querystring sent from Bootgrid
if (isset($_REQUEST['sort']) && is_array($_REQUEST['sort']) )
{
$order_by="";
foreach($_REQUEST['sort'] as $key=> $value)
$order_by.=" $key $value";
}
//Handles search querystring sent from Bootgrid
if (isset($_REQUEST['searchPhrase']) )
{
$search=trim($_REQUEST['searchPhrase']);
$where.= " AND ( title LIKE '".$search."%' OR call_on LIKE '".$search."%' OR mName LIKE '".$search."%' OR start LIKE '".$search."%') ";
}
//Handles determines where in the paging count this result set falls in
if (isset($_REQUEST['rowCount']) )
$rows=$_REQUEST['rowCount'];
//calculate the low and high limits for the SQL LIMIT x,y clause
if (isset($_REQUEST['current']) )
{
$current=$_REQUEST['current'];
$limit_l=($current * $rows) - ($rows);
$limit_h=$rows ;
}
if ($rows==-1)
$limit=""; //no limit
else
$limit=" LIMIT $limit_l,$limit_h ";
//NOTE: No security here please beef this up using a prepared statement - as is this is prone to SQL injection.
$sql="SELECT
tbl_machine.mName,
tbl_logs.`start`,
tbl_material.call_on,
tbl_material.title
FROM
tbl_logs
LEFT JOIN tbl_material ON tbl_material.logs_id = tbl_logs.Id
LEFT JOIN tbl_machine ON tbl_logs.machine_id = tbl_machine.id
WHERE $where ORDER BY $order_by $limit";
$stmt=$db->prepare($sql);
$stmt->execute();
$results_array=$stmt->fetchAll(PDO::FETCH_ASSOC);
$json=json_encode( $results_array );
$nRows=$db->query("SELECT count(*) FROM tbl_logs WHERE $where")->fetchColumn();
header('Content-Type: application/json'); //tell the broswer JSON is coming
if (isset($_REQUEST['rowCount']) ) //Means we're using bootgrid library
echo "{ \"current\": $current, \"rowCount\":$rows, \"rows\": ".$json.", \"total\": $nRows }";
else
echo $json; //Just plain vanillat JSON output
exit;
}
catch(PDOException $e) {
echo 'SQL PDO ERROR: ' . $e->getMessage();
}
?>
Tag : PHP, MySQL, HTML/CSS, JavaScript, Ajax, jQuery