+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Sort order of record items (/showthread.php?tid=65484)
Sort order of record items - Lykos22 - 06-16-2016
I'm having a db table with this structure
Code:
table: Items
id
category_id
title
sortorderIn my view I'm showing a list of all items like this:
PHP Code:
foreach ($items as $item) {
<tr>
<td><a href="' . site_url('app/item/' . $item->id. '/' . $item->category_id) . '">' . $item->title. '</a></td>
<td><a href="' . site_url('app/orderitems/' . $item->id. '/' . $item->category_id . '/down') . '" class="button tiny"><i class="fa fa-chevron-down"></i></a>
<a href="' . site_url('app/orderitems/' . $item->id. '/' . $item->category_id . '/up') . '" class="button tiny disabled"><i class="fa fa-chevron-up"></i></a></td>
<td><a href="' . site_url('app/edititem/' . $item->id . '/' . $item->category_id) . '" class="button tiny">Edit</a></td>
<td><a href="' . site_url('app/deleteitem/' . $item->id) . '" class="button tiny">Delete</a></td>
</tr>
}
this is the function for sorting-ordering my items
PHP Code:
// controller
public function orderItem($id, $category_id, $mode = 'down') {
$this->load->model('item_m');
$this->item_m->order($id, $category_id, $mode);
redirect('app/items/' . $category_id); // redirect back ti listing page
}
and this is my order function in item_m
PHP Code:
public function order($id, $category_id, $mode = 'down') {
$this->db->set(array(
'sortorder' => ????,
));
$this->db->where(array(
'id' => (int) $id,
'category_id' => (int) $category_id,
));
$this->db->update('items');
}
RE: Sort order of record items - InsiteFX - 06-16-2016
see order_by
RE: Sort order of record items - Lykos22 - 06-16-2016
(06-16-2016, 10:52 AM)InsiteFX Wrote: see order_by
No, I'm afraid you didn't understand, my problem is not on fetching the items from the database (I 'm already using order_by when quering items), but how to update the sortorder of each item when I click the up pr down links
RE: Sort order of record items - ivantcholakov - 06-16-2016
Code:
<?php defined('BASEPATH') OR exit('No direct script access allowed.');
/**
* @author Ivan Tcholakov <[email protected]>, 2014
* @license The MIT License, http://opensource.org/licenses/MIT
*/
if (!function_exists('display_order_up')) {
function display_order_up($table, $id, $param = null) {
if (is_object($table)) {
$table = $table->table();
}
$table = ci()->db->escape_str($table);
$sql = "SELECT
t1.id,
t1.display_order,
t2.display_order AS display_order_2
FROM
".$table." AS t1,
".$table." AS t2
WHERE
t2.id='".ci()->db->escape_str($id)."' AND
t1.display_order < t2.display_order";
if (isset($param)) {
$sql .= " AND ".$param;
}
$sql .= " ORDER BY
t1.display_order DESC
LIMIT
1";
$row = ci()->db
->query($sql)
->row_array();
if (!empty($row)) {
$id_1 = $row['id'];
$display_order_1 = $row['display_order'];
$id_2 = $id;
$display_order_2 = $row['display_order_2'];
ci()->db
->set('display_order', $display_order_1)
->where('id', $id_2)
->update($table);
ci()->db
->set('display_order', $display_order_2)
->where('id', $id_1)
->update($table);
}
}
}
if (!function_exists('display_order_down')) {
function display_order_down($table, $id, $param = null) {
if (is_object($table)) {
$table = $table->table();
}
$table = ci()->db->escape_str($table);
$sql = "SELECT
t1.id,
t1.display_order,
t2.display_order AS display_order_2
FROM
".$table." AS t1,
".$table." AS t2
WHERE
t2.id='".ci()->db->escape_str($id)."' AND
t1.display_order > t2.display_order";
if (isset($param)) {
$sql .= " AND ".$param;
}
$sql .= " ORDER BY
t1.display_order ASC
LIMIT
1";
$row = ci()->db
->query($sql)
->row_array();
if (!empty($row)) {
$id_1 = $row['id'];
$display_order_1 = $row['display_order'];
$id_2 = $id;
$display_order_2 = $row['display_order_2'];
ci()->db
->set('display_order', $display_order_1)
->where('id', $id_2)
->update($table);
ci()->db
->set('display_order', $display_order_2)
->where('id', $id_1)
->update($table);
}
}
}You would need to adapt this code, ci() means get_instance(). Also, when you create a new item assign an initial value for its 'display_order' field, for example:
Code:
// Rewrite this using the pure CodeIgniter's query builder.
$this->products->update($id, array('display_order' =>
(int) $this->products
->select('MAX(display_order)')
->with_deleted()
->as_value()
->first()
+ 10
));Possible extras you could think about:
1. After "Move Up" and "Move Down" update the table using AJAX. Reloading the whole page is quite annoying.
2. Adding a button for drag and drop sorting also would be nice (AJAX), but this is harder.