Merging Two User Admins

[nowordneeded]

My gallery is located here: http://tamara-braun.com/gallery and is running the current stable version of 1.5.46.

I've looked all over the forum and can't find the topic I need help with. Here's my issue: This gallery is the very first I ever put up using Coppermine. It was begun back in 2006 and when I set it up I used one username I'll call "A" and kept that for at least five to six years. During that time I uploaded a lot of images. I added another admin user we'll call "B". I want to merge both admins into one so the gallery has all images uploaded by the same admin name. I've tried going in through phpmyadmin and was able to change the one name, but I really would like to not have two admins. How would I do that?

Thanks in advance.

[phill104]

Each user is given an ID number, stored in the table CPG15x_users in the column user_id. You would need to work out the user ID of the second admin user, the one you do not want, and the ID of the one you want to keep.

Next would be to go through all the tables that refer to the second ID, and change them to the first. Without checking I would guess that CPG15x_pictures you would need to change the column owner_id to match the correct user_id number. You would need to do the same in some other tables too, I am guessing _categories , _albums, _comments and _favpics but there may be others. I cannot check right now as I only have a mobile device for internet access this week.

All the changes could be made with sql queries, but I am sure you knew that. Once all is done you can remove the second admin user.

[nowordneeded]

So if I wanted to change tamaraobscura to tamarabraunonline, I would have to go through and change all instances of tamaraobscura to tamarabraunonline? Fortunately there are only the uploads because I set up my gallery so as not to allow signups due to spam. I've only found the one instance in the users section.

[phill104]

Not quite, you would change the user id, this is just a number. So for instance, if the admin user is 1 and the second admin is user id 7, you would need to change all those 7s to 1s. This can be done with an SQL query and would need to be performed on each of the columns I mention above. I will not have access to a PC for a week as I am working away from home, so maybe someone else can advise on the syntax.

[nowordneeded]

I think I might have to wait to find out how to do this. I'm not sure of myself doing anything too intricate on mysql. I only have two users that are 1 (tamarabraunonline) the one I want to use, but 298 is tamaraobscura, the one I want to convert to the first.

[Αndré]

This should do the trick:

Code Select Expand

UPDATE cpg15x_albums SET owner = 1 WHERE owner = 238;
UPDATE cpg15x_categories SET owner_id = 1 WHERE owner_id = 238;
UPDATE cpg15x_pictures SET owner_id = 1 WHERE owner_id = 238;


[nowordneeded]

I wish it did. I keep getting an error. The open line in the Sql window is:

Code Select Expand

SELECT * FROM `cpg143_users` WHERE 1

And when I put in the command I get this:

Code Select Expand

SQL query:

UPDATE `cpg143_users` SET `user_id`=[value-1],`user_group`=[value-2],`user_active`=[value-3],`user_name`=[value-4],`user_password`=[value-5],`user_lastvisit`=[value-6],`user_regdate`=[value-7],`user_group_list`=[value-8],`user_email`=[value-9],`user_profile1`=[value-10],`user_profile2`=[value-11],`user_profile3`=[value-12],`user_profile4`=[value-13],`user_profile5`=[value-14],`user_profile6`=[value-15],`user_actkey`=[value-16],`auto_subscribe_post`=[value-17],`auto_subscribe_comment`=[value-18],`avatar_url`=[value-19],`user_pmsmail`=[value-20],`enable_admin_email`=[value-21],`user_language`=[value-22],`user_email_valid`=[value-23] WHERE 1

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[value-1],`user_group`=[value-2],`user_active`=[value-3],`user_name`=[value-4],`' at line 1

[gmc]

I wish it did. I keep getting an error. The open line in the Sql window is:

Code Select Expand

SELECT * FROM `cpg143_users` WHERE 1

And when I put in the command I get this:

Code Select Expand

SQL query:

UPDATE `cpg143_users` SET `user_id`=[value-1],`user_group`=[value-2],`user_active`=[value-3],`user_name`=[value-4],`user_password`=[value-5],`user_lastvisit`=[value-6],`user_regdate`=[value-7],`user_group_list`=[value-8],`user_email`=[value-9],`user_profile1`=[value-10],`user_profile2`=[value-11],`user_profile3`=[value-12],`user_profile4`=[value-13],`user_profile5`=[value-14],`user_profile6`=[value-15],`user_actkey`=[value-16],`auto_subscribe_post`=[value-17],`auto_subscribe_comment`=[value-18],`avatar_url`=[value-19],`user_pmsmail`=[value-20],`enable_admin_email`=[value-21],`user_language`=[value-22],`user_email_valid`=[value-23] WHERE 1

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[value-1],`user_group`=[value-2],`user_active`=[value-3],`user_name`=[value-4],`' at line 1

Fortunately the default command they inserted failed - or you would have wiped out your CPG 'users' table...
You didn't issue the commands Αndré provided.

If you are using phpMyAdmin - click on the SQL tab - delete the default query that comes up - and copy/paste the commands Αndré gave you.

[nowordneeded]

I ran it again like you said, and this was the error:

Code Select Expand

SQL query:

UPDATE cpg15x_albums SET owner = 1 WHERE owner = 238;

MySQL said: Documentation
#1146 - Table '1484137466.cpg15x_albums' doesn't exist

Here is a screenshot of what I got when I ran it. Fortunately there are no other users by my two, the original I created and the other one I want to replace it with. I haven't opened my galleries up for comments because of one I had where someone spammed me with over 1000+ comments including porn. I learned my lesson on that.

[gmc]

I ran it again like you said, and this was the error:

Code Select Expand

SQL query:

UPDATE cpg15x_albums SET owner = 1 WHERE owner = 238;

MySQL said: Documentation
#1146 - Table '1484137466.cpg15x_albums' doesn't exist

Here is a screenshot of what I got when I ran it. Fortunately there are no other users by my two, the original I created and the other one I want to replace it with. I haven't opened my galleries up for comments because of one I had where someone spammed me with over 1000+ comments including porn. I learned my lesson on that.

OK... Αndré's SQL assumed you had the default table prefix of cpg15x... or would adjust it yourself. It looks like from previous post your tables are using the prefix cpg143 (likely originally installed at the 1.4 level)
Confirm that either in phpMyAdmin or your cpg config.inc.php member - then issue:

Code Select Expand


UPDATE cpg143_albums SET owner = 1 WHERE owner = 238;
UPDATE cpg143_categories SET owner_id = 1 WHERE owner_id = 238;
UPDATE cpg143_pictures SET owner_id = 1 WHERE owner_id = 238;


[nowordneeded]

I had tried to replace the CPG version in the code Andre gave me, but nothing happened. I executed the command you gave me and I get this:

Code Select Expand

# MySQL returned an empty result set (i.e. zero rows).

Yes, the gallery was installed in 2006.

So let me understand where the command is executed. I go into the USERS database, then execute it, or do I do it in the MSQ in the main section? Because the above error I received happened in the USERS section.

[Αndré]

Try

Code Select Expand

UPDATE cpg143_albums SET owner = 1 WHERE owner = 298;
UPDATE cpg143_categories SET owner_id = 1 WHERE owner_id = 298;
UPDATE cpg143_pictures SET owner_id = 1 WHERE owner_id = 298;

Sorry for the typo.

[nowordneeded]

Success!!! I thank you all for your help and appreciate your time.