Page 1 of 1

INSERT AFTER and increase values below

Posted: Wed Nov 14, 2007 9:05 pm
by SidewinderX
I've created a very simple ranking system - it allows a user to enter a rank title, and its weight (the lower the weight the more 'powerful' the rank). I have my database setup as follows:

Code: Select all

id               title               weight
Currently, the administrator can enter a multiple ranks with the same weight - now fixing this problem is trivial. What I want is a little different. Assum my table contains some data as follows:

Code: Select all

id               title               weight
1                Colonel                1
3                 Major                 2
6               Sergeant                3
7               Private                 4
But the administrator comes back and realizes he also wants to add Corporal whose weight is less than a Seargant but greater than a Private. Now I'm assuming there is some sort of "INSERT AFTER" syntax that I would use to...insert after sergeant, but if the administrator entered (assuming the correct query):

title: "Corporal "
weight: 4

The table would then look like this?

Code: Select all

id               title               weight
1                Colonel                1
3                 Major                 2
6               Sergeant                3
8               Corporal                4
7               Private                 4

With Corporal and Private having the same weight. Is there an easy to insert after "x" and bump every weight up by one below the new insertion? Or do I have to do the insertion, then make a loop - looping through all the elements below x and 'manually' increase each weight?

Posted: Wed Nov 14, 2007 9:55 pm
by Christopher
Databases don't really have the positional quality you are asking for. Record have no real internal order -- you give them with the ORDER BY clause of the SELECT statement. With so few ranks you could just select all the records, modify the values that need changing and then update the 'weight' in the records that have changed.

You could also increase by 1 the value of all records greater than or equal to the weight of the record you want to insert and then insert the new record.

Posted: Wed Nov 14, 2007 10:08 pm
by SidewinderX
Thats what I thought, but I figured I would ask as I don't want to reinvent the wheel.

Posted: Thu Nov 15, 2007 12:19 am
by sunilbhatia79
I think this post will help you to ORDER BY in a predefined order:
http://www.sunilb.com/mysql/mysql-tutor ... ined-order

Hope this helps.

Posted: Thu Nov 15, 2007 12:23 am
by Kieran Huggins
You could take a look at how other ORM packages handle it - acts_as_list does exactly this in Rails.

Posted: Thu Nov 15, 2007 2:27 am
by SidewinderX
How is this?

Code: Select all

addRankAt("Corporal", 4);
function addRankAt($rank, $weight) {
	$result = mysql_query("SELECT * FROM `#_ct_member_ranks` WHERE `weight` >= '$weight' ORDER BY `weight` DESC");
	while($row = mysql_fetch_assoc($result)){
		$id = $row['id'];
		$newWeight =  $row['weight'] + 1;
		mysql_query("UPDATE `#_ct_member_ranks` SET `weight` = '$newWeight' WHERE `id` = '$id'");
	}
	mysql_query("INSERT INTO `#_ct_member_ranks` VALUES ('', '$rank', '$weight')");
}
Doesn't seem very efficient -- O(n) with 3 queries...
*Looking up "ORM packages" now.*

Posted: Thu Nov 15, 2007 10:22 am
by Kieran Huggins
That's likely what you'll find in an ORM package, there are only so many ways to skin this particular cat.

It's still worth a look, mind you.