INSERT AFTER and increase values below

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
SidewinderX
Forum Contributor
Posts: 407
Joined: Fri Jul 16, 2004 9:04 pm
Location: NY

INSERT AFTER and increase values below

Post 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?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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.
(#10850)
SidewinderX
Forum Contributor
Posts: 407
Joined: Fri Jul 16, 2004 9:04 pm
Location: NY

Post by SidewinderX »

Thats what I thought, but I figured I would ask as I don't want to reinvent the wheel.
sunilbhatia79
Forum Newbie
Posts: 24
Joined: Sun Nov 11, 2007 9:37 pm
Location: Mumbai, India

Post 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.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

You could take a look at how other ORM packages handle it - acts_as_list does exactly this in Rails.
SidewinderX
Forum Contributor
Posts: 407
Joined: Fri Jul 16, 2004 9:04 pm
Location: NY

Post 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.*
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

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