The lesson for today is… how to sort records sequentially on a numeric column and provide the ability to move records up and down in the existing sort order.
There are 2 functions I use for manual record sorting to change the display order in admin/user areas. The following example is based on a “pages” table and the commands are located in a model. For this example, the table has the following fields: page_id (int), page_sort (smallint), page_title (varchar), and page_content(text).
function move_page($id = 0, $dir = 'up')
{
$this->db->select('page_id, page_sort');
$this->db->where('page_id', $id);
$query = $this->db->get('pages', 1);
$row = $query->row();
if (empty($row->page_id)) return FALSE;
$new_sort = ($dir == 'up') ? $row->page_sort - 1 : $row->page_sort + 1;
$this->db->set('page_sort', $row->page_sort);
$this->db->where('page_sort', $new_sort);
$this->db->update('pages');
$this->db->set('page_sort', $new_sort);
$this->db->where('page_id', $id);
$this->db->update('pages');
$this->update_sort_index();
return TRUE;
}
Originally I was updating each row individually in a PHP while() loop… no seriously! Then it hit me… user-defined mysql variables, duh!
function update_sort_index()
{
$this->db->query('SET @pos=0; UPDATE pages SET page_sort = ( SELECT @pos := @pos +1 ) WHERE 1 ORDER BY page_sort ASC');
}
Unfortunately MySQL slapped me for trying to run that query. I forgot phpMyAdmin automatically splits multiple queries and that’s what I used to test the SET / UPDATE commands before adding the query to my model.
function update_sort_index()
{
$this->db->query('SET @pos=0');
$this->db->query('UPDATE pages SET page_sort = ( SELECT @pos := @pos +1 ) WHERE 1 ORDER BY page_sort ASC');
}
I keep update_sort_index() in it’s own function because it is also called after INSERT and DELETE queries that affect the table.
You’ll notice I use CodeIgniter’s Active Record Class for the majority of the queries. I would have used them in update_sort_index() but the SET command had to be done using query() so I also used it for the subsequent UPDATE command.
I don’t comment a lot of my code because I think it’s pretty straight-forward. So shoot me, lol.
wow..great post. I searched 2 whole days for this. I been working with codeigniter for a few weeks and i’ve hit the point where i need to sort records. Code igniter is awesome but i havent found anything similar to the table->move in joomla which does the job of arranging for you and there are no CI tutorials to this effect.
These two functions will do just that and can be rewritten as some sort of helper?
Great snippet,..keep up the code work.
**new to codeigniter and loving it. o(-_-)o
Question,..when you do an insert, how do you calc the next available sort index on the group?
From your example i was able to create a dynamic arrange model. It has 3 functions.. move, reorder and getnextorder for new inserts. Thanks for the code snippet. Time for bed !!
In my insert function I set the `page_sort` field to either ‘-1’ if I want to make sure it’s first, or ‘999999’ (any large number the field can support) to make sure it ends up being last.
Note: You could always pull the next number by using a MAX()+1 query, but it was not necessary for what I was doing and saved me an extra hit on the database.
Once the INSERT command is processed, I just run update_sort_index() to reorder the sequential index 🙂
As for where I decided to use them, they reside in my ‘pages’ model. If you wanted to make it dynamic, I guess you could put it in a helper. In which case you would need to pass the table name and sort field name to it like below…
function update_sort_index($table, $field)
{
if (empty($table) || empty($field)) return;
$this->db->query('SET @pos=0');
$this->db->query("UPDATE `{$table}` SET `{$field}` = ( SELECT @pos := @pos +1 ) WHERE 1 ORDER BY `{$field}` ASC");
}
I am actually using a ordering column based on type or based on categ_id depending on the table. I am able to rearrange items within different types or different categories using your example and everything works well.
When i delete a record, my ordering sequence of lets say 1,2,3,4,5 now becomes 1,2,4,5 because i deleted 3. How do I reorder with codeigniter so that i now have 1,2,3,4 again. Please note im using a column called ordering to sort
I thought that is what the update sort index was but i had no luck resequencing after a delete..
Let’s see if I’m understanding you right. Are you trying to update the sort of records within a group (eg: only records belonging to a certain category)? If so, just modify the “WHERE 1” clause to something like…
WHERE `grouping_column` = 'some_value'I am using..
function reorder($table,$wherecolumn,$wherevalue){$this->db->query('SET @pos=1');
$this->db->query("UPDATE $table SET ordering = (SELECT @pos := @pos +1) where '$wherecolumn' = '$wherevalue' ORDER BY ordering ASC")
}
What is wrong?
SOLVED
function reorder($table,$wherecolumn,$wherevalue){$this->db->query(‘SET @pos=1′);
$this->db->query(“UPDATE $table SET ordering = (SELECT @pos := @pos +1) where $wherecolumn = ‘$wherevalue’ AND active='enabled' ORDER BY ordering ASC”)
}
I forgot about the status column..active=enabled/disabled.
This works great.
Thanks Robert
Glad to see you figured it out. Not that it’s a requirement, but I always think it’s a good idea to encase your field names in `backticks` as an extra added measure against potential injection threats 😉
function reorder($table, $wherecolumn, $wherevalue) {$this->db->query('SET @pos=1');
$this->db->query("UPDATE `{$table}` SET ordering = (SELECT @pos := @pos +1) where `{$wherecolumn}` = '{$wherevalue}' AND `active` = 'enabled' ORDER BY `ordering` ASC")
}
Quick question… are you using VARCHAR or CHAR for the “active” field? If so, it would be more efficient to use TINYINT or ENUM which will result in a much smaller footprint as your table(s) grow. If the table has the potential to end up with a lot of records, and you are checking that field in the WHERE clause, it would make a huge difference 😀
hi, i’m new to codeigniter and i’m working on a project. i have to create a dynamic drop down menu with values from my database, when a selection is made in the drop down as soon as you click on the submit button a new page has to occur where all the cities associated with the province selected in the drop menu appear, the cities are also in my database .My database consists of an id field, province field and a cities fiield.The drop menu is fine but cant seem to make the cities appear in the next page. your help will be highly appreciated
Have you considered using AJAX requests to repopulate the dropdown when the state changes instead of submitting the form? If the reload is necessary the form needs to either self-target (post to same url) so the ID can be passed or you can set the ID in a session variable and pull it from there on the resulting page.
it’s not working for me.
CodeIgniter Version: 2.1.3
MySQL Version: 5.5.30
MySQL Engine: MyISAM
QUERY:
$query = “INSERT INTO new_table
(
user_id,
cut_off_pay,
total_days,
rate_per_day,
rate_per_hour,
)
(
SELECT
u.id,
@cut_off_pay := (u.current_salary / 2) ,
@total_days := 10,
@rate_per_day := (@cut_off_pay / @total_days),
@rate_per_hour := (@rate_per_day / 8)
FROM attendance a
LEFT JOIN users u
ON a.user_id = u.id
WHERE a.user_id = u.id
GROUP BY a.user_id
);
$this->db->query($query);
The user-defined variables (@cut_off_pay, @total_days, etc..) are not working, it returns 0/NULL values
Take a look at MySQL Manual: User Variables. Unless I can see some actual sample data that is coming from those tables, not much else I can suggest.
Really nice little snippet. Like others, I’d been searching for a compact way of doing this in CI. Thanks for sharing!
I tried many times for @pos := @pos +1 but results me “+2” by each time, not “+1”, it’s very strange. My mysql version 5.0.51, anyone has same problem?
Hard to say without seeing your complete query.