我使用服务器端数据表(和CodeIgniter)来显示数据。
我不能直接从数据库中删除条目(项目的指导方针，不幸的是)，所以我正在使用deleted- flags, DB表中的一列称为'deleted' (tinyint(1)，因为MySQL自动将其从BOOLEAN更改为)。
对于服务器端处理，我使用https://github.com/blake-nouribekian/codeigniter-datatables/blob/master/data.php提供的脚本，并进行了一些小更改
// Columns use for SELECT part of the query. These names are not being escaped, so subqueries etc are possible.
$aColumns = array('events.id AS id', 'title', "FROM_UNIXTIME(start, '%d.%m.%Y') AS start", "FROM_UNIXTIME(start, '%H:%i') AS start_time", '(SELECT COUNT(*) FROM event_bookings WHERE id_event = events.id AND confirmed IS NOT NULL) AS participants', 'max_participants');
// Column names for WHERE clause (important for stuff like subqueries).
$aColumnNames = array('id', 'title', "start", "start", '(SELECT COUNT(*) FROM event_bookings WHERE id_event = events.id AND confirmed IS NOT NULL)', 'max_participants');
// Column for deleted flag in the DB.
$deleted_column = 'deleted';
// DB table to use
$sTable = 'events';
// no deleted entries
$this->db->where(Event_Model::$dbDeletedFlag, 0);
最初，数据显示正确，没有显示标记为已删除的条目。然而，只要我在搜索字段中输入一些东西，它就会找到所有与"……"匹配的条目。像%输入%"。我已经疯了$this->db->where(Event_Model::$dbDeletedFlag, 0);命令，把它放在每一条可能的线上，仍然没有改善。它显示标记为已删除的条目。当使用搜索字段时，where命令不知何故不起作用。
有什么建议来解决这个问题吗?
编辑:感谢到目前为止的答案。我把整个函数贴在这里:
public function server_processing() {
// EDIT THIS
// -----
// Columns use for SELECT part of the query. These names are not being escaped, so subqueries etc are possible.
$aColumns = array('events.id AS id', 'title', "FROM_UNIXTIME(start, '%d.%m.%Y') AS start", "FROM_UNIXTIME(start, '%H:%i') AS start_time", '(SELECT COUNT(*) FROM event_bookings WHERE id_event = events.id AND confirmed IS NOT NULL) AS participants', 'max_participants');
// Column names for WHERE clause (important for stuff like subqueries).
$aColumnNames = array('id', 'title', "start", "start", '(SELECT COUNT(*) FROM event_bookings WHERE id_event = events.id AND confirmed IS NOT NULL)', 'max_participants');
// Column for deleted flag in the DB.
$deleted_column = 'deleted';
// DB table to use
$sTable = 'events';
// no deleted entries
$this->db->where(Event_Model::$dbDeletedFlag, 0);
// -------
$iDisplayStart = $this->input->get_post('iDisplayStart', true);
$iDisplayLength = $this->input->get_post('iDisplayLength', true);
$iSortCol_0 = $this->input->get_post('iSortCol_0', true);
$iSortingCols = $this->input->get_post('iSortingCols', true);
$sSearch = $this->input->get_post('sSearch', true);
$sEcho = $this->input->get_post('sEcho', true);
// Paging
if (isset($iDisplayStart) && $iDisplayLength != '-1') {
$this->db->limit($this->db->escape_str($iDisplayLength), $this->db->escape_str($iDisplayStart));
}
// Ordering
if (isset($iSortCol_0)) {
for ($i = 0; $i < intval($iSortingCols); $i++) {
$iSortCol = $this->input->get_post('iSortCol_' . $i, true);
$bSortable = $this->input->get_post('bSortable_' . intval($iSortCol), true);
$sSortDir = $this->input->get_post('sSortDir_' . $i, true);
if ($bSortable == 'true') {
$this->db->order_by($aColumnNames[intval($this->db->escape_str($iSortCol))], $this->db->escape_str($sSortDir));
}
}
}
/*
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
if (isset($sSearch) && !empty($sSearch)) {
for ($i = 0; $i < count($aColumns); $i++) {
$bSearchable = $this->input->get_post('bSearchable_' . $i, true);
// Individual column filtering
if (isset($bSearchable) && $bSearchable == 'true') {
$this->db->or_like($aColumnNames[$i], $this->db->escape_like_str($sSearch));
}
}
}
// Select Data
$this->db->select('SQL_CALC_FOUND_ROWS ' . str_replace(' , ', ' ', implode(', ', $aColumns)), false);
$rResult = $this->db->get($sTable);
// Data set length after filtering
$this->db->select('FOUND_ROWS() AS found_rows');
$iFilteredTotal = $this->db->get()->row()->found_rows;
// Total data set length
$iTotal = $this->db->count_all($sTable);
// Output
$output = array(
'sEcho' => intval($sEcho),
'iTotalRecords' => $iTotal,
'iTotalDisplayRecords' => $iFilteredTotal,
'aaData' => array()
);
$output['aaData'] = array();
foreach ($rResult->result_array() as $aRow) {
// EDIT THIS
// --------
$row = array(
'<a class="detail_link link" href="javascript:void(0);" data-event-id="' . $aRow['id'] . '">' . $aRow['title'] . '</a>',
$aRow['start'],
$aRow['start_time'],
$aRow['participants'] . ' / ' . $aRow['max_participants']
);
if ($this->auth->access(Auth::Admin, true, false)) {
$row[] = '<img class="edit_link" data-event-id="' . $aRow['id'] . '" src="' . base_url() . 'img/icons/glyphicons_150_edit.png"> <img class="delete_link" data-event-id="' . $aRow['id'] . '" src="' . base_url() . 'img/icons/glyphicons_192_circle_remove.png">';
}
// --------
$output['aaData'][] = $row;
}
echo json_encode($output);
}
谢谢你的努力!
编辑2
我认为，问题是，生成的sql语句看起来像这样，当搜索字段被使用:
SELECT fields FROM table WHERE deleted = 0 OR field LIKE %input%
而它应该是:
SELECT fields FROM table WHERE field LIKE %input% AND deleted = 0
我真的不知道，如何活动记录建立SQL语句，以解决这个问题
根据您的评论，问题似乎在于活动记录如何建立WHERE条款的LIKE片段。
if (isset($sSearch) && !empty($sSearch)) {
// Build up the SQL manually. First, initialize SQL string.
// This clause will be ANDed to the other WHERE clauses;
// Use parentheses to isolate the OR in the inner clauses
$sql = "("; //
for ($i = 0; $i < count($aColumns); $i++) {
$bSearchable = $this->input->get_post('bSearchable_' . $i, true);
// Individual column filtering
if (isset($bSearchable) && $bSearchable == 'true') {
//$this->db->or_like($aColumnNames[$i], $this->db->escape_like_str($sSearch));
$sql_tag = ($i==0) ? "" : " OR "; // don't need OR before the first field
$sql .= $sql_tag . $aColumnNames[$i] . " LIKE " . $this->db->escape_like_str($sSearch);
}
}
$sql .= ")"; // close parentheses
$this->db->where($sql); // this will add to the WHERE clause using AND
}
我要做的是手动构建LIKE子句。您需要允许对多个字段进行搜索。如果我的细节设置正确，你的最终查询应该是这样的:
SELECT fields FROM table WHERE deleted = 0
AND (field1 LIKE '%input%' OR field2 LIKE '%input%' OR field3 LIKE '%input%')
假设您有三个字段要搜索