public function queryBasedConstruct($query, $queryParams = array(), $wdtParameters = array(), $init_read = false)
{
global $wdt_var1, $wdt_var2, $wdt_var3, $wpdb;
// Sanitizing query
$query = wpdatatables_sanitize_query($query);
$query = str_replace('`', '', $query);
// Placeholders
if (strpos($query, '%CURRENT_USER_ID%') !== false) {
$wdt_cur_user_id = isset($_POST['current_user_placeholder']) ? $_POST['current_user_placeholder'] : get_current_user_id();
$query = str_replace('%CURRENT_USER_ID%', $wdt_cur_user_id, $query);
}
if (strpos($query, '%WPDB%') !== false) {
$query = str_replace('%WPDB%', $wpdb->prefix, $query);
}
// Shortcode VAR1
if (strpos($query, '%VAR1%') !== false) {
$query = str_replace('%VAR1%', $wdt_var1, $query);
}
// Shortcode VAR2
if (strpos($query, '%VAR2%') !== false) {
$query = str_replace('%VAR2%', $wdt_var2, $query);
}
// Shortcode VAR3
if (strpos($query, '%VAR3%') !== false) {
$query = str_replace('%VAR3%', $wdt_var3, $query);
}
// Adding limits if necessary
if (!empty($wdtParameters['limit']) && strpos(strtolower($query), 'limit') === false) {
$query .= ' LIMIT ' . $wdtParameters['limit'];
}
// Server-side requests
if ($this->serverSide()) {
$query = apply_filters('wpdatatables_filter_query_before_limit', $query, $this->getWpId());
if (!isset($_GET['sEcho'])) {
$query .= ' LIMIT ' . $this->getDisplayLength();
} else {
// Server-side params
$limit = '';
$orderby = '';
$search = '';
$aColumns = array_keys($wdtParameters['column_titles']);
if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1') {
$limit = "LIMIT " . addslashes($_GET['iDisplayStart']) . ", " . addslashes($_GET['iDisplayLength']);
}
// Adding sort parameters for AJAX if necessary
if (isset($_GET['iSortCol_0'])) {
$orderby = "ORDER BY ";
for ($i = 0; $i < intval($_GET['iSortingCols']); $i++) {
if ($_GET['bSortable_' . intval($_GET['iSortCol_' . $i])] == "true") {
$orderby .= '`' . $aColumns[intval($_GET['iSortCol_' . $i])] . "`\n " . addslashes($_GET['sSortDir_' . $i]) . ", ";
}
}
$orderby = substr_replace($orderby, "", -2);
if ($orderby == "ORDER BY") {
$orderby = "";
}
}
// filtering
if ($_GET['sSearch'] != "") {
$search = " (";
for ($i = 0; $i < count($aColumns); $i++) {
$search .= '`' . $aColumns[$i] . "` LIKE '%" . addslashes($_GET['sSearch']) . "%' OR ";
}
$search = substr_replace($search, "", -3);
$search .= ')';
}
/* Individual column filtering */
for ($i = 0; $i < count($aColumns); $i++) {
if ($_GET['bSearchable_' . $i] == "true" && $_GET['sSearch_' . $i] != '' && $_GET['sSearch_' . $i] != '~') {
if (!empty($search)) {
$search .= ' AND ';
}
switch ($wdtParameters['filter_types'][$aColumns[$i]]) {
case 'number':
$search .= '`' . $aColumns[$i] . "` = " . $_GET['sSearch_' . $i] . " ";
break;
case 'number-range':
list($left, $right) = explode('~', $_GET['sSearch_' . $i]);
if ($left !== '') {
$left = (double) $left;
$search .= '`' . $aColumns[$i] . "` >= {$left} ";
}
if ($right !== '') {
$right = (double) $right;
if (!empty($search) && $left !== '') {
$search .= ' AND ';
}
$search .= '`' . $aColumns[$i] . "` <= {$right} ";
}
break;
case 'date-range':
list($left, $right) = explode('~', $_GET['sSearch_' . $i]);
$date_format = str_replace('m', '%m', get_option('wdtDateFormat'));
$date_format = str_replace('M', '%M', $date_format);
$date_format = str_replace('Y', '%Y', $date_format);
$date_format = str_replace('y', '%y', $date_format);
$date_format = str_replace('d', '%d', $date_format);
if ($left && $right) {
$search .= '`' . $aColumns[$i] . "` BETWEEN STR_TO_DATE('{$left}', '{$date_format}') AND STR_TO_DATE('{$right}', '{$date_format}') ";
} elseif ($left) {
$search .= '`' . $aColumns[$i] . "` >= STR_TO_DATE('{$left}', '{$date_format}') ";
} elseif ($right) {
$search .= '`' . $aColumns[$i] . "` <= STR_TO_DATE('{$right}', '{$date_format}') ";
}
break;
case 'select':
$search .= '`' . $aColumns[$i] . "` = '" . addslashes($_GET['sSearch_' . $i]) . "' ";
break;
case 'checkbox':
$checkboxSearches = explode('|', $_GET['sSearch_' . $i]);
$j = 0;
$search .= " (";
foreach ($checkboxSearches as $checkboxSearch) {
// Trim regex parts
$checkboxSearch = substr($checkboxSearch, 1, -1);
if ($j > 0) {
$search .= " OR ";
}
$search .= '`' . $aColumns[$i] . "` = '" . addslashes($checkboxSearch) . "' ";
$j++;
}
$search .= ") ";
break;
case 'text':
default:
$search .= '`' . $aColumns[$i] . "` LIKE '%" . addslashes($_GET['sSearch_' . $i]) . "%' ";
}
}
}
}
}
// Add the filtering by user ID column, if requested
if ($this->_onlyOwnRows) {
$userIdColumnCondition = '`' . $this->_userIdColumn . '` = ' . get_current_user_id();
$whereInsertIndex = count($query);
// Detect where to insert the string
if (false !== stripos($query, 'WHERE')) {
// If WHERE is already present in the query
$query = substr_replace($query, ' ' . $userIdColumnCondition . ' AND', stripos($query, 'WHERE') + 5, 0);
} else {
// If WHERE is not present
if (false !== stripos($query, 'LIMIT')) {
// If LIMIT is present
$query = substr_replace($query, ' WHERE ' . $userIdColumnCondition . ' ', stripos($query, 'LIMIT'), 0);
} else {
$query .= " WHERE " . $userIdColumnCondition;
}
}
}
// The serverside return scenario
if (isset($_GET['action']) && $_GET['action'] == 'get_wdtable') {
/**
* 1. Forming the query
*/
$query = preg_replace('/SELECT /i', 'SELECT SQL_CALC_FOUND_ROWS ', $query, 1);
if ($search) {
if (stripos($query, 'WHERE')) {
$query = substr_replace($query, ' ' . $search . ' AND', stripos($query, 'WHERE') + 5, 0);
} else {
$query .= ' WHERE ' . $search;
}
}
$query .= ' ' . $orderby;
$query .= ' ' . $limit;
$query = apply_filters('wpdatatables_filter_mysql_query', $query, $this->getWpId());
/**
* 2. Executing the queries
*/
// The main query
// Prepare query - replace all duplicated spaces, newlines, etc.
$query = preg_replace('!\\s+!', ' ', $query);
if (get_option('wdtUseSeparateCon')) {
$main_res_dataRows = $this->_db->getAssoc($query, $queryParams);
} else {
// querying using the WP driver otherwise
$main_res_dataRows = $wpdb->get_results($query, ARRAY_A);
}
// result length after filtering
if (get_option('wdtUseSeparateCon')) {
$res_length = $this->_db->getField('SELECT FOUND_ROWS()');
} else {
// querying using the WP driver otherwise
$res_length = $wpdb->get_row('SELECT FOUND_ROWS()', ARRAY_A);
$res_length = $res_length['FOUND_ROWS()'];
}
// total data length
// get the table name
$table_title = substr($query, strpos(strtolower($query), 'from') + 5);
$table_title = substr($table_title, 0, strpos($table_title, ' '));
$table_title = trim($table_title);
if (get_option('wdtUseSeparateCon')) {
$total_length_query = 'SELECT COUNT(*) FROM ' . $table_title;
// If "Only own rows" options is defined, do not count other user's rows
if (isset($userIdColumnCondition)) {
$total_length_query .= ' WHERE ' . $userIdColumnCondition;
}
$total_length = $this->_db->getField($total_length_query);
} else {
// querying using the WP driver otherwise
$total_length_query = 'SELECT COUNT(*) as cnt_total FROM ' . $table_title;
// If "Only own rows" options is defined, do not count other user's rows
if (isset($userIdColumnCondition)) {
$total_length_query .= ' WHERE ' . $userIdColumnCondition;
}
$total_length = $wpdb->get_row($total_length_query, ARRAY_A);
$total_length = $total_length['cnt_total'];
}
/**
* 3. Forming the output
*/
// base array
$output = array("sEcho" => intval($_GET['sEcho']), "iTotalRecords" => $total_length, "iTotalDisplayRecords" => $res_length, "aaData" => array());
// create the supplementary array of column objects
// which we will use for formatting
$col_objs = array();
foreach ($wdtParameters['data_types'] as $dataColumn_key => $dataColumn_type) {
$col_objs[$dataColumn_key] = WDTColumn::generateColumn($dataColumn_type, array('title' => $wdtParameters['column_titles'][$dataColumn_key]));
$col_objs[$dataColumn_key]->setInputType($wdtParameters['input_types'][$dataColumn_key]);
}
// reformat output array and reorder as user wanted
if (!empty($main_res_dataRows)) {
foreach ($main_res_dataRows as $res_row) {
$row = array();
foreach ($wdtParameters['column_order'] as $dataColumn_key) {
$row[] = $col_objs[$dataColumn_key]->returnCellValue($res_row[$dataColumn_key]);
unset($cell);
}
$output['aaData'][] = $row;
}
}
/**
* 4. Returning the result
*/
return json_encode($output);
} else {
// Getting the query result
// getting by own SQL driver if the user wanted a separate connection
if (get_option('wdtUseSeparateCon')) {
$query = apply_filters('wpdatatables_filter_mysql_query', $query, $this->getWpId());
$res_dataRows = $this->_db->getAssoc($query, $queryParams);
$mysql_error = $this->_db->getLastError();
} else {
// querying using the WP driver otherwise
$query = apply_filters('wpdatatables_filter_mysql_query', $query, $this->getWpId());
$res_dataRows = $wpdb->get_results($query, ARRAY_A);
$mysql_error = $wpdb->last_error;
}
// If this is the table initialization from WP-admin, and no data is returned, throw an exception
if ($init_read && empty($res_dataRows)) {
$msg = __('No data fetched! ', 'wpdatatables');
$msg .= '<br/>' . __('Rendered query: ', 'wpdatatables') . '<strong>' . $query . '</strong><br/>';
if (!empty($mysql_error)) {
$msg .= __(' MySQL said: ', 'wpdatatables') . $mysql_error;
}
throw new Exception($msg);
}
// Sending the array to arrayBasedConstruct
return $this->arrayBasedConstruct($res_dataRows, $wdtParameters);
}
}
public function queryBasedConstruct($query, $queryParams = array(), $wdtParameters = array())
{
// checking if the table is existing in cache
// and setting the flag if it does
if (self::$mc) {
$this->_cacheHash = 'bbq_' . md5($query);
if (@self::$mc->get($this->_cacheHash)) {
$this->_fromCache = $this->_cacheHash;
return true;
}
}
// Sanitizing query
$query = wpdatatables_sanitize_query($query);
// Placeholders
if (strpos($query, '%CURRENT_USER_ID%') !== false) {
$query = str_replace('%CURRENT_USER_ID%', get_current_user_id(), $query);
}
// Shortcode VAR1
if (strpos($query, '%VAR1%') !== false) {
$query = str_replace('%VAR1%', $wdt_var1, $query);
}
// Shortcode VAR2
if (strpos($query, '%VAR2%') !== false) {
$query = str_replace('%VAR2%', $wdt_var2, $query);
}
// Shortcode VAR3
if (strpos($query, '%VAR3%') !== false) {
$query = str_replace('%VAR3%', $wdt_var3, $query);
}
// Adding limits if necessary
if (!empty($wdtParameters['limit']) && strpos(strtolower($query), 'limit') === false) {
$query .= ' LIMIT ' . $wdtParameters['limit'];
}
// Server-side requests
if ($this->serverSide()) {
if (!isset($_GET['sEcho'])) {
$query .= ' LIMIT ' . $this->getDisplayLength();
} else {
// Server-side params
$limit = '';
$orderby = '';
$search = '';
$aColumns = array_keys($wdtParameters['column_titles']);
if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1') {
$limit = "LIMIT " . addslashes($_GET['iDisplayStart']) . ", " . addslashes($_GET['iDisplayLength']);
}
// Adding sort parameters for AJAX if necessary
if (isset($_GET['iSortCol_0'])) {
$orderby = "ORDER BY ";
for ($i = 0; $i < intval($_GET['iSortingCols']); $i++) {
if ($_GET['bSortable_' . intval($_GET['iSortCol_' . $i])] == "true") {
$orderby .= $aColumns[intval($_GET['iSortCol_' . $i])] . "\n " . addslashes($_GET['sSortDir_' . $i]) . ", ";
}
}
$orderby = substr_replace($orderby, "", -2);
if ($orderby == "ORDER BY") {
$orderby = "";
}
}
// filtering
if ($_GET['sSearch'] != "") {
$search = " (";
for ($i = 0; $i < count($aColumns); $i++) {
$search .= '`' . $aColumns[$i] . "` LIKE '%" . addslashes($_GET['sSearch']) . "%' OR ";
}
$search = substr_replace($search, "", -3);
$search .= ')';
}
/* Individual column filtering */
for ($i = 0; $i < count($aColumns); $i++) {
if ($_GET['bSearchable_' . $i] == "true" && $_GET['sSearch_' . $i] != '' && $_GET['sSearch_' . $i] != '~') {
if (!empty($search)) {
$search .= ' AND ';
}
switch ($wdtParameters['filter_types'][$aColumns[$i]]) {
case 'number':
$search .= $aColumns[$i] . " = " . $_GET['sSearch_' . $i] . " ";
break;
case 'number-range':
list($left, $right) = explode('~', $_GET['sSearch_' . $i]);
if ($left) {
$search .= $aColumns[$i] . " >= {$left} ";
}
if ($right) {
if (!empty($search)) {
$search .= ' AND ';
}
$search .= $aColumns[$i] . " <= {$right} ";
}
break;
case 'date-range':
list($left, $right) = explode('~', $_GET['sSearch_' . $i]);
$date_format = str_replace('m', '%m', get_option('wdtDateFormat'));
$date_format = str_replace('Y', '%Y', $date_format);
$date_format = str_replace('y', '%y', $date_format);
$date_format = str_replace('d', '%d', $date_format);
if ($left && $right) {
$search .= $aColumns[$i] . " BETWEEN STR_TO_DATE('{$left}', '{$date_format}') AND STR_TO_DATE('{$right}', '{$date_format}') ";
} elseif ($left) {
$search .= $aColumns[$i] . " >= STR_TO_DATE('{$left}', '{$date_format}') ";
} elseif ($right) {
$search .= $aColumns[$i] . " <= STR_TO_DATE('{$right}', '{$date_format}') ";
}
break;
case 'select':
$search .= $aColumns[$i] . " = '" . addslashes($_GET['sSearch_' . $i]) . "' ";
break;
case 'checkbox':
$checkboxSearches = explode('|', $_GET['sSearch_' . $i]);
$j = 0;
$search .= " (";
foreach ($checkboxSearches as $checkboxSearch) {
if ($j > 0) {
$search .= " OR ";
}
$search .= $aColumns[$i] . " LIKE '%" . addslashes($checkboxSearch) . "%' ";
$j++;
}
$search .= ") ";
break;
case 'text':
default:
$search .= $aColumns[$i] . " LIKE '%" . addslashes($_GET['sSearch_' . $i]) . "%' ";
}
}
}
}
}
// The serverside return scenario
if (isset($_GET['action']) && $_GET['action'] == 'get_wdtable') {
/**
* 1. Forming the query
*/
$query = str_ireplace('SELECT ', 'SELECT SQL_CALC_FOUND_ROWS ', $query);
if ($search) {
if (strpos($query, 'WHERE')) {
$query .= ' AND ' . $search;
} else {
$query .= ' WHERE ' . $search;
}
}
$query .= ' ' . $orderby;
$query .= ' ' . $limit;
$query = apply_filters('wpdatatables_filter_mysql_query', $query, $this->getWpId());
/**
* 2. Executing the queries
*/
// The main query
if (get_option('wdtUseSeparateCon')) {
$main_res_dataRows = $this->_db->getAssoc($query, $queryParams);
} else {
global $wpdb;
// querying using the WP driver otherwise
$main_res_dataRows = $wpdb->get_results($query, ARRAY_A);
}
// result length after filtering
if (get_option('wdtUseSeparateCon')) {
$res_length = $this->_db->getField('SELECT FOUND_ROWS()');
} else {
global $wpdb;
// querying using the WP driver otherwise
$res_length = $wpdb->get_row('SELECT FOUND_ROWS()', ARRAY_A);
$res_length = $res_length['FOUND_ROWS()'];
}
// total data length
// get the table name
$table_title = substr($query, strpos(strtolower($query), 'from') + 5);
$table_title = substr($table_title, 0, strpos($table_title, ' '));
$table_title = trim($table_title);
if (get_option('wdtUseSeparateCon')) {
$total_length = $this->_db->getField('SELECT COUNT(' . $aColumns[0] . ') FROM ' . $table_title);
} else {
global $wpdb;
// querying using the WP driver otherwise
$total_length = $wpdb->get_row('SELECT COUNT(' . $aColumns[0] . ') as cnt_total FROM ' . $table_title, ARRAY_A);
$total_length = $total_length['cnt_total'];
}
/**
* 3. Forming the output
*/
// base array
$output = array("sEcho" => intval($_GET['sEcho']), "iTotalRecords" => $total_length, "iTotalDisplayRecords" => $res_length, "aaData" => array());
// create the supplementary array of column objects
// which we will use for formatting
$col_objs = array();
foreach ($wdtParameters['data_types'] as $dataColumn_key => $dataColumn_type) {
$col_objs[$dataColumn_key] = WDTColumn::generateColumn($dataColumn_type, array('title' => $wdtParameters['column_titles'][$dataColumn_key]));
$col_objs[$dataColumn_key]->setInputType($wdtParameters['input_types'][$dataColumn_key]);
}
// reformat output array and reorder as user wanted
if (!empty($main_res_dataRows)) {
foreach ($main_res_dataRows as $res_row) {
$row = array();
foreach ($wdtParameters['column_order'] as $dataColumn_key) {
$row[] = $col_objs[$dataColumn_key]->returnCellValue($res_row[$dataColumn_key]);
unset($cell);
}
$output['aaData'][] = $row;
}
}
/**
* 4. Returning the result
*/
return json_encode($output);
} else {
// Getting the query result
// getting by own SQL driver if the user wanted a separate connection
if (get_option('wdtUseSeparateCon')) {
$query = apply_filters('wpdatatables_filter_mysql_query', $query, $this->getWpId());
$res_dataRows = $this->_db->getAssoc($query, $queryParams);
} else {
global $wpdb;
// querying using the WP driver otherwise
$query = apply_filters('wpdatatables_filter_mysql_query', $query, $this->getWpId());
$res_dataRows = $wpdb->get_results($query, ARRAY_A);
}
// Sending the array to arrayBasedConstruct
return $this->arrayBasedConstruct($res_dataRows, $wdtParameters);
}
}