Welcome Guest, Not a member yet? Register   Sign In
Sort order of record items
#1

I'm having a db table with this structure
Code:
table: Items
id
category_id
title
sortorder

In 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"><class="fa fa-chevron-down"></i></a
                    <
a href="' . site_url('app/orderitems/' . $item->id. '/' . $item->category_id . '/up') . '" class="button tiny disabled"><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');
    } 
So the order of the items should change by clicking either chevron-down or chevron-up button-links. How can I target a specific item to move up or down on the item list ?
Reply
#2

see order_by
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#3

(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
Reply
#4

(This post was last modified: 06-17-2016, 04:18 AM by ivantcholakov. Edit Reason: a typo )

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.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB