I got this php class from the internet which deal with mysql database:
<?php
class SSP {
static function data_output($columns, $data, $isJoin = false) {
$out = array();
for ($i = 0, $ien = count($data); $i < $ien; $i++) {
$row = array();
for ($j = 0, $jen = count($columns); $j < $jen; $j++) {
$column = $columns[$j];
// Is there a formatter?
if (isset($column['formatter'])) {
$row[$column['dt']] = ($isJoin) ? $column['formatter']($data[$i][$column['field']], $data[$i]) : $column['formatter']($data[$i][$column['db']], $data[$i]);
} else {
$row[$column['dt']] = ($isJoin) ? $data[$i][$columns[$j]['field']] : $data[$i][$columns[$j]['db']];
}
}
$out[] = $row;
}
return $out;
}
static function limit($request, $columns) {
$limit = '';
if (isset($request['start']) && $request['length'] != -1) {
$limit = "LIMIT " . intval($request['start']) . ", " . intval($request['length']);
}
return $limit;
}
static function order($request, $columns, $isJoin = false) {
$order = '';
if (isset($request['order']) && count($request['order'])) {
$orderBy = array();
$dtColumns = SSP::pluck($columns, 'dt');
for ($i = 0, $ien = count($request['order']); $i < $ien; $i++) {
// Convert the column index into the column data property
$columnIdx = intval($request['order'][$i]['column']);
$requestColumn = $request['columns'][$columnIdx];
$columnIdx = array_search($requestColumn['data'], $dtColumns);
$column = $columns[$columnIdx];
if ($requestColumn['orderable'] == 'true') {
$dir = $request['order'][$i]['dir'] === 'asc' ?
'ASC' :
'DESC';
$orderBy[] = ($isJoin) ? $column['db'] . ' ' . $dir : '`' . $column['db'] . '` ' . $dir;
}
}
$order = 'ORDER BY ' . implode(', ', $orderBy);
}
return $order;
}
static function filter($request, $columns, &$bindings, $isJoin = false) {
$globalSearch = array();
$columnSearch = array();
$dtColumns = SSP::pluck($columns, 'dt');
if (isset($request['search']) && $request['search']['value'] != '') {
$str = $request['search']['value'];
for ($i = 0, $ien = count($request['columns']); $i < $ien; $i++) {
$requestColumn = $request['columns'][$i];
$columnIdx = array_search($requestColumn['data'], $dtColumns);
$column = $columns[$columnIdx];
if ($requestColumn['searchable'] == 'true') {
$binding = SSP::bind($bindings, '%' . $str . '%', PDO::PARAM_STR);
$globalSearch[] = ($isJoin) ? $column['db'] . " LIKE " . $binding : "`" . $column['db'] . "` LIKE " . $binding;
}
}
}
// Individual column filtering
for ($i = 0, $ien = count($request['columns']); $i < $ien; $i++) {
$requestColumn = $request['columns'][$i];
$columnIdx = array_search($requestColumn['data'], $dtColumns);
$column = $columns[$columnIdx];
$str = $requestColumn['search']['value'];
if ($requestColumn['searchable'] == 'true' &&
$str != '') {
$binding = SSP::bind($bindings, '%' . $str . '%', PDO::PARAM_STR);
$columnSearch[] = ($isJoin) ? $column['db'] . " LIKE " . $binding : "`" . $column['db'] . "` LIKE " . $binding;
}
}
// Combine the filters into a single string
$where = '';
if (count($globalSearch)) {
$where = '(' . implode(' OR ', $globalSearch) . ')';
}
if (count($columnSearch)) {
$where = $where === '' ?
implode(' AND ', $columnSearch) :
$where . ' AND ' . implode(' AND ', $columnSearch);
}
if ($where !== '') {
$where = 'WHERE ' . $where;
}
return $where;
}
static function simple($request, $sql_details, $table, $primaryKey, $columns, $joinQuery = NULL, $extraWhere = '', $groupBy = '', $varDeclare = '') {
$bindings = array();
$db = SSP::sql_connect($sql_details);
// Build the SQL query string from the request
$limit = SSP::limit($request, $columns);
$order = SSP::order($request, $columns, $joinQuery);
$where = SSP::filter($request, $columns, $bindings, $joinQuery);
// IF Extra where set then set and prepare query
if ($extraWhere) {
$extraWhere = ($where) ? ' AND ' . $extraWhere : ' WHERE ' . $extraWhere;
}
// Main query to actually get the data
if ($joinQuery) {
$col = SSP::pluck($columns, 'db', $joinQuery);
$query = "$varDeclare " .
"SELECT SQL_CALC_FOUND_ROWS " . implode(", ", $col) . "
$joinQuery
$where
$extraWhere
$groupBy
$order
$limit";
} else {
$query = "SELECT SQL_CALC_FOUND_ROWS `" . implode(", ", SSP::pluck($columns, 'db')) . "`
FROM `$table`
$where
$extraWhere
$groupBy
$order
$limit";
}
$data = SSP::sql_exec($db, $bindings, $query);
// Data set length after filtering
$resFilterLength = SSP::sql_exec($db, "SELECT FOUND_ROWS()"
);
$recordsFiltered = $resFilterLength[0][0];
// Total data set length
$resTotalLength = SSP::sql_exec($db, "SELECT COUNT(`{$primaryKey}`)
FROM `$table`"
);
$recordsTotal = $resTotalLength[0][0];
/*
* Output
*/
return array(
"draw" => intval($request['draw']),
"recordsTotal" => intval($recordsTotal),
"recordsFiltered" => intval($recordsFiltered),
"data" => SSP::data_output($columns, $data, $joinQuery)
);
}
static function sql_connect($sql_details) {
try {
$db = @new PDO(
"mysql:host={$sql_details['host']};dbname={$sql_details['db']}", $sql_details['user'], $sql_details['pass'], array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
);
$db->query("SET NAMES 'utf8'");
} catch (PDOException $e) {
SSP::fatal(
"An error occurred while connecting to the database. " .
"The error reported by the server was: " . $e->getMessage()
);
}
return $db;
}
static function sql_exec($db, $bindings, $sql = null) {
// Argument shifting
if ($sql === null) {
$sql = $bindings;
}
$stmt = $db->prepare($sql);
//echo $sql;
// Bind parameters
if (is_array($bindings)) {
for ($i = 0, $ien = count($bindings); $i < $ien; $i++) {
$binding = $bindings[$i];
$stmt->bindValue($binding['key'], $binding['val'], $binding['type']);
}
}
// Execute
try {
$stmt->execute();
} catch (PDOException $e) {
SSP::fatal("An SQL error occurred: " . $e->getMessage());
}
// Return all
return $stmt->fetchAll();
}
static function fatal($msg) {
echo json_encode(array(
"error" => $msg
));
exit(0);
}
static function bind(&$a, $val, $type) {
$key = ':binding_' . count($a);
$a[] = array(
'key' => $key,
'val' => $val,
'type' => $type
);
return $key;
}
static function pluck($a, $prop, $isJoin = false) {
$out = array();
for ($i = 0, $len = count($a); $i < $len; $i++) {
$out[] = ($isJoin && isset($a[$i]['as'])) ? $a[$i][$prop] . ' AS ' . $a[$i]['as'] : $a[$i][$prop];
}
return $out;
}
}
Mysql query original format which I haved tested on phpmyadmin without any problem:
SET @gender = NULL;
SET @agefrom = NULL;
SET @ageto = NULL;
SET @mobile = NULL;
SET @country = NULL;
SET @state = NULL;
SET @email = TRUE;
SELECT B.BUYER_ID AS ID,
B.BUYER_NAME AS NAME,
(SELECT GR.GENDER FROM MOB_GENDER_REF GR WHERE B.BUYER_GENDER = GR.GENDER_ID) AS GENDER,
(TRUNCATE(DATEDIFF(CURDATE(), B.BUYER_DOB)/365.25,0)) AS AGE,
B.BUYER_MOBILE_NO AS MOBILE_NO,
(SELECT CR.COUNTRY_NAME FROM MOB_COUNTRY_REF AS CR WHERE CR.COUNTRY_ID = B.COUNTRY ) AS COUNTRY,
(SELECT CSR.STATE_NAME FROM MOB_COUNTRY_STATE_REF AS CSR WHERE CSR.STATE_ID = B.STATE AND CSR.COUNTRY_ID = B.COUNTRY) AS STATE,
B.BUYER_EMAIL AS EMAIL
FROM MOB_BUYER B
WHERE (
IF((@gender IS NULL) || (@gender = "") || (@gender < 0 && @gender > B.BUYER_GENDER), (B.BUYER_GENDER LIKE '%'), (B.BUYER_GENDER = @gender))
AND
IF((@agefrom IS NULL) || (@agefrom = "") || (@agefrom < 0 && @agefrom > (TRUNCATE(DATEDIFF(CURDATE(), B.BUYER_DOB)/365.25,0))), (TRUNCATE(DATEDIFF(CURDATE(), B.BUYER_DOB)/365.25,0)) LIKE '%', (TRUNCATE(DATEDIFF(CURDATE(), B.BUYER_DOB)/365.25,0)) >= @agefrom)
AND
IF((@ageto IS NULL) || (@ageto = "") || (@ageto < 0 && @ageto > (TRUNCATE(DATEDIFF(CURDATE(), B.BUYER_DOB)/365.25,0))), (TRUNCATE(DATEDIFF(CURDATE(), B.BUYER_DOB)/365.25,0)) LIKE '%', (TRUNCATE(DATEDIFF(CURDATE(), B.BUYER_DOB)/365.25,0)) <= @ageto)
AND
IF((@mobile IS NULL) || (@mobile = "") && (@mobile != TRUE && @mobile != FALSE), (B.BUYER_MOBILE_NO LIKE '%'), IF(@mobile = TRUE, (CHAR_LENGTH(B.BUYER_MOBILE_NO) >= @mobile && CAST(B.BUYER_MOBILE_NO AS UNSIGNED) > 0), (B.BUYER_MOBILE_NO = 0)))
AND
IF((@country IS NULL) || (@country = ""), (B.COUNTRY LIKE '%'), FIND_IN_SET(B.COUNTRY, @country))
AND
IF((@state IS NULL ) || (@state = ""), (B.STATE LIKE '%'), FIND_IN_SET(B.STATE, @state))
AND
IF((@email IS NULL) || (@email = "") && (@email != TRUE && @email != FALSE), (B.BUYER_EMAIL LIKE '%'), IF(@email = TRUE, (CHAR_LENGTH(B.BUYER_EMAIL) >= @email), (B.BUYER_EMAIL = "" OR B.BUYER_EMAIL IS NULL)))
)
and this is the php source code which I used to execute the sql query:
<?php
$table = 'MOB_BUYER';
$primaryKey = 'BUYER_ID';
$columns = array(
array('db' => '`B`.`BUYER_ID`', 'dt' => 'BUYER_ID', 'field' => 'ID', 'as' => 'ID'),
array('db' => '`B`.`BUYER_NAME`', 'dt' => 'BUYER_NAME', 'field' => 'NAME', 'as' => 'NAME'),
array('db' => '(SELECT `GR`.`GENDER` FROM `MOB_GENDER_REF` AS `GR` WHERE `B`.`BUYER_GENDER` = `GR`.`GENDER_ID`)', 'dt' => 'GENDER', 'field' => 'GENDER', 'as' => 'GENDER'),
array('db' => '(TRUNCATE(DATEDIFF(CURDATE(), `B`.`BUYER_DOB`)/365.25,0))', 'dt' => 'AGE', 'field' => 'AGE', 'as' => 'AGE'),
array('db' => '`B`.`BUYER_MOBILE_NO`', 'dt' => 'MOBILE_NO', 'field' => 'MOBILE_NO', 'as' => 'MOBILE_NO'),
array('db' => '(SELECT `CR`.`COUNTRY_NAME` FROM `MOB_COUNTRY_REF` AS `CR` WHERE `CR`.`COUNTRY_ID` = `B`.`COUNTRY`)', 'dt' => 'COUNTRY', 'field' => 'COUNTRY', 'as' => 'COUNTRY'),
array('db' => '(SELECT `CSR`.`STATE_NAME` FROM `MOB_COUNTRY_STATE_REF` AS `CSR` WHERE `CSR`.`STATE_ID` = `B`.`STATE` AND `CSR`.`COUNTRY_ID` = `B`.`COUNTRY`)', 'dt' => 'STATE', 'field' => 'STATE', 'as' => 'STATE'),
array('db' => '`B`.`BUYER_EMAIL`', 'dt' => 'EMAIL', 'field' => 'EMAIL', 'as' => 'EMAIL')
);
// SQL server connection information
$sql_details = array(
'user' => 'user',
'pass' => 'password',
'db' => 'test',
'host' => '127.0.0.1'
);
require('ssp.class.php' );
$joinQuery = "FROM `MOB_BUYER` AS `B`";
$extraWhere = "("
. "IF((@gender IS NULL) || (@gender = '') || (@gender < 0 && @gender > `B`.`BUYER_GENDER`), (`B`.`BUYER_GENDER` LIKE '%'), (`B`.`BUYER_GENDER` = @gender)) "
. "AND "
. "IF((@agefrom IS NULL) || (@agefrom = '') || (@agefrom < 0 && @agefrom > (TRUNCATE(DATEDIFF(CURDATE(), `B`.`BUYER_DOB`)/365.25,0))), (TRUNCATE(DATEDIFF(CURDATE(), `B`.`BUYER_DOB`)/365.25,0)) LIKE '%', (TRUNCATE(DATEDIFF(CURDATE(), `B`.`BUYER_DOB`)/365.25,0)) >= @agefrom) "
. "AND "
. "IF((@ageto IS NULL) || (@ageto = '') || (@ageto < 0 && @ageto > (TRUNCATE(DATEDIFF(CURDATE(), `B`.`BUYER_DOB`)/365.25,0))), (TRUNCATE(DATEDIFF(CURDATE(), `B`.`BUYER_DOB`)/365.25,0)) LIKE '%', (TRUNCATE(DATEDIFF(CURDATE(), `B`.`BUYER_DOB`)/365.25,0)) <= @ageto) "
. "AND "
. "IF((@mobile IS NULL) || (@mobile = '') && (@mobile != TRUE && @mobile != FALSE), (`B`.`BUYER_MOBILE_NO` LIKE '%'), IF(@mobile = TRUE, (CHAR_LENGTH(`B`.`BUYER_MOBILE_NO`) >= @mobile && CAST(`B`.`BUYER_MOBILE_NO` AS UNSIGNED) > 0), (`B`.`BUYER_MOBILE_NO` = 0))) "
. "AND "
. "IF((@country IS NULL) || (@country = ''), (`B`.`COUNTRY` LIKE '%'), FIND_IN_SET(`B`.`COUNTRY`, @country)) "
. "AND "
. "IF((@state IS NULL ) || (@state = ''), (`B`.`STATE` LIKE '%'), FIND_IN_SET(`B`.`STATE`, @state)) "
. "AND "
. "IF((@email IS NULL) || (@email = '') && (@email != TRUE && @email != FALSE), (`B`.`BUYER_EMAIL` LIKE '%'), IF(@email = TRUE, (CHAR_LENGTH(`B`.`BUYER_EMAIL`) >= @email), (`B`.`BUYER_EMAIL` = '' OR `B`.`BUYER_EMAIL` IS NULL)))"
. ")";
$groupBy = "";
$varDeclare = "SET @gender = NULL; "
. "SET @agefrom = NULL; "
. "SET @ageto = NULL; "
. "SET @mobile = NULL; "
. "SET @country = NULL; "
. "SET @state = NULL; "
. "SET @email = NULL;";
echo json_encode (
SSP::simple($_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere, $groupBy, $varDeclare)
);
?>
When I execute the php source it return blank page....
anyone mind lend a helping hand?
Thanks
:)
Aucun commentaire:
Enregistrer un commentaire