1 Topic by Mr Puto (edited by Mr Puto 2006-03-11 23:33)

  • Registered: 2005-07-15
  • Posts: 47

Topic: Updating Group ID Query

I already posted this a while back, but maybe I wasn't clear enough. Ok so I have a Junior Administrators mod im doing atm, and its like 90% complete except for this part:
I need to insert a group to group ID to 2..so I need to move every other group id up 1 in order for this to work. So what would be a proper query?

I already know how to make the new group, but I first need to move the other groups upward.

  • Gizzmo
  • Senior Member
  • Offline
  • From: Earth, Milkyway Galaxy
  • Registered: 2004-01-27
  • Posts: 762

Re: Updating Group ID Query

do you have php myadmin?

  • Registered: 2005-07-15
  • Posts: 47

Re: Updating Group ID Query

yes i do, but see im making a mod for public release. and I want people to just click "install" on install_mod.php and it will do it for them.

  • Registered: 2005-07-15
  • Posts: 47

Re: Updating Group ID Query

YEAH STILL NEED HELP

Anyone? -.-

  • From: Leuven, Belgium
  • Registered: 2005-06-14
  • Posts: 2,653

Re: Updating Group ID Query

Something like this maybe? You should try it out...

$db->query("UPDATE ".$db->prefix."groups SET Id=Id+1");

6 Reply by Mr Puto (edited by Mr Puto 2006-03-12 21:43)

  • Registered: 2005-07-15
  • Posts: 47

Re: Updating Group ID Query

EDIT: nah, I tried this:

 UPDATE groups SET g_id = g_id +1

and it gave me this error:

#1062 - Duplicate entry '2' for key 1
  • From: Leuven, Belgium
  • Registered: 2005-06-14
  • Posts: 2,653

Re: Updating Group ID Query

Ok, so that doesn't work... tongue Odd tho tongue

  • Reines
  • Reines
  • Senior Member
  • Offline
  • Registered: 2004-10-07
  • Posts: 304

Re: Updating Group ID Query

elbekko wrote:

Something like this maybe? You should try it out...

$db->query("UPDATE ".$db->prefix."groups SET Id=Id+1");

Doesn't work because group_id is a key, meaning there cannot be more than 1 row with the same entry. The query will try to start with group_id 1, and make it 2. There is already a 2, so it throws an Error.

Reines's Website

  • Registered: 2005-07-15
  • Posts: 47

Re: Updating Group ID Query

Reines wrote:
elbekko wrote:

Something like this maybe? You should try it out...

$db->query("UPDATE ".$db->prefix."groups SET Id=Id+1");

Doesn't work because group_id is a key, meaning there cannot be more than 1 row with the same entry. The query will try to start with group_id 1, and make it 2. There is already a 2, so it throws an Error.

Thats what I thought, and couldn't you make it so it starts at the last group and moves them all up one, except for the admin group?

  • From: Leuven, Belgium
  • Registered: 2005-06-14
  • Posts: 2,653

Re: Updating Group ID Query

Indeed... Well, you could always try removing the key, doing this operation, and then resetting the key...

  • Registered: 2005-07-15
  • Posts: 47

Re: Updating Group ID Query

What a pain in the ass...

12 Reply by Reines

  • Reines
  • Reines
  • Senior Member
  • Offline
  • Registered: 2004-10-07
  • Posts: 304

Re: Updating Group ID Query

Is there a way to control the order of an UPDATE query? I've never heard of such a thing, but it would be handy for this kind of situation.

Reines's Website

13 Reply by Smartys (edited by Smartys 2006-03-12 23:52)

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Updating Group ID Query

mysql> update foo_groups set g_id=g_id+1 order by g_id desc;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

I have a couple extra groups, which is why it's 6 instead of 4 wink
But that's the query (although you also have to set the auto_increment value properly afterwards, or the next time you try to add a group you'll get an error: easiest way to do that is to try to insert a new row in the groups table after updating, but don't catch any errors it causes: just let the script continue to run)

  • Registered: 2005-07-15
  • Posts: 47

Re: Updating Group ID Query

Thats perfect. Thanks you Reines and Smartys!