StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POServerside Datatables finds entries marked as deleted
primarykey
Id
15656847
data
AcceptedAnswerId
15658763
AnswerCount
1
ClosedDate
CommentCount
5
CommunityOwnedDate
CreationDate
2013-03-27T10:39:57.257
FavoriteCount
0
LastActivityDate
2013-03-27T16:21:38.717
LastEditDate
2013-03-27T16:21:38.717
LastEditorUserId
564353
OwnerUserId
1690821
ParentId
0
PostTypeId
1
Score
1
ViewCount
596
LastEditorDisplayName
text
Body
I am using Server-side Datatables (and CodeIgniter) to show data. I can't directly delete entries from the Database (guideline of the project, unfortunately), so I am working with deleted-Flags, a column in the DB table called 'deleted' (tinyint(1) since MySQL automatically changed it from BOOLEAN to that). For the server side processing I'm using the script provided at <a href="https://github.com/blake-nouribekian/codeigniter-datatables/blob/master/data.php" rel="nofollow">https://github.com/blake-nouribekian/codeigniter-datatables/blob/master/data.php</a>, with some small changes <pre><code>// 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); </code></pre> Initially, the data is shown correct, no entries marked as deleted show up. As soon as I type something into the search field, however, it finds ALL entries that match '... LIKE %input%'. I've gone wild with the $this->db->where(Event_Model::$dbDeletedFlag, 0); command, placing it at every possible line, still no improvement. It shows the entries marked as deleted. The where commands somehow don't work, when the search field is used. Any suggestions how to solve this? EDIT: Thanks for the answers so far. I'm posting the whole function right here: <pre><code>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); } </code></pre> Thank you for your efforts! EDIT 2 I think, the problem is, that the generated SQL-Statement looks like this, when the Search field is used: <pre><code>SELECT fields FROM table WHERE deleted = 0 OR field LIKE %input% </code></pre> Whereas it rather should be: <pre><code>SELECT fields FROM table WHERE field LIKE %input% AND deleted = 0 </code></pre> I just don't know really, how the Active records build the SQL statements, in order to solve this problem
Tags
<php><jquery><codeigniter><activerecord><datatables>
Title
Serverside Datatables finds entries marked as deleted
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USMarc Audet
UserOwnerUserId
1. USPatrick Manser
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POServerside Datatables finds entries marked as deleted
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTApproveEditSuggestion
2. VO
 singulars
 PostPostId
 POServerside Datatables finds entries marked as deleted
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.