creating user galleries

[nickfzx]

is there a way to auto create personal galleries for all the members you already have?

or in an ideal world:

is there a way to auto create personal galleries for all members who currently have images in public galleries?


context on why i want to do this:
I run a site with nearly a thousand members.
I have a little over 2000 images in the coppermine gallery...around 1200 of these images are in public galleries and the rest are in personal galleries.

I want to get rid of the public galleries altogether and move all the images that are in them into personal galleries.

The trouble is, is that most users don't have personal galleries so I want to create hundreds of personal galleries.

I am going to manually move all the images over 1 by 1 to the users newly created personal gallery - i intend on keywording them as I do so.

any help on where to start would be very welcome.

[Joachim Müller]

You could come up with a custom script that does the job. Not a trivial thing though, as the script needs to check if a user already has a personal gallery and only create one if not. Then it has to loop through the existing pics (based on ownership data) and move the pics inside public galleries into the personal gallery of the owner. Possible if you code it.

[nickfzx]

i may just create a personal gallery for everyone even if they do already have one...as they may not want their public images put in the same gallery they already have.

I could probably do this with PHPmyAdmin with a sql table insert maybe??

[Nibbler]

Yeah, change the table prefix as appropriate.

Code Select Expand


INSERT INTO cpg_albums(title, category) SELECT CONCAT(user_name, "'s gallery") , 10000 + user_id FROM cpg_users INNER JOIN cpg_pictures ON user_id = owner_id GROUP BY user_id


[nickfzx]

cool thanks Nibbler I will give this a shot on my test install and let you know if I hit any problems.

[nickfzx]

This is the output I got when i ran this:

Inserted rows: 0 (Query took 0.0069 sec)

SQL query: INSERT INTO copper_albums( title, category )
SELECT CONCAT( user_name, "'s gallery" ) , 10000 + user_id
FROM copper_users
INNER JOIN copper_pictures ON user_id = owner_id
GROUP BY user_id

don't get any errors it just says that it inserted 0 rows.  What is CONCAT?  it was lit up red in the output above.

[nickfzx]

sorry i am stupid.

I had to adapt it because I am bridged with punbb.

this is the adaptation for my installation:

Code Select Expand

INSERT INTO copper_albums(title, category) SELECT CONCAT(username, "'s gallery") , 10000 + id FROM users INNER JOIN copper_pictures ON id = owner_id GROUP BY id

worked fine although I am not 100% sure on what this is doing.

It hasn't created galleries for all the users (only a few hundred)...I think it has created them for any user who has uploaded at least 1 images.  Is this correct?

[Nibbler]

Yes, that's what you asked for. If it want it for all users then you can leave out the last join and the group by. You'd need to remove the albums from the first run though.

[nickfzx]

I now know what I specifically want to do...sorry for being indecisive, ( and not sure if it is possible with an sql command, at least not for me with my limited knowledge of mysql (i should really buy a book on MySQL and learn it properly).

I want to create personal albums for all members who have no uploaded images at all.

and

create personal albums for everyone who has one or more images in a public gallery (albums ID's of public galleries are: 3,6,7,8,9,10,11,12,13,14,15,16)


Sorry for flapping around with this and thanks for your help.

[Nibbler]

OK, so

Code Select Expand


INSERT INTO cpg_albums(title, category) SELECT CONCAT(user_name, "'s gallery") , 10000 + user_id FROM cpg_users LEFT JOIN cpg_pictures ON user_id = owner_id WHERE aid IS NULL OR aid IN (3,6,7,8,9,10,11,12,13,14,15,16) GROUP BY user_id


[nickfzx]

works perfectly, you are a hero.