Hi,
I've been playing around with the coppermine database and keep running up against the same issue, I'm not sure how to tie a picture to a user.
I do see a column called owner_id in the picture table, but this contains a lot of values (zeros) that don't tie to a valid user_id in the users table. Most of these pictures appear to belong to me (and so should have a owner_id of 1), but there are some that do not....I think these might be batches that I did for other users?
Anyway, zero is definitely not a user_id, so the only way I've been able to get a list of user name, album count, picture count, is with the following (kinda wierd) query:
SELECT
cuser.user_name,
COUNT(DISTINCT album.aid) AS AlbumCount,
COUNT(DISTINCT picture.pid) AS PictureCount
FROM
cmine_albums AS album
INNER JOIN cmine_pictures AS picture ON picture.aid = album.aid
INNER JOIN cmine_users AS cuser ON cuser.user_id = (album.category - 10000)
WHERE
album.category > 10000
GROUP BY
user_name;
That results in (actual user names removed):
user_name AlbumCount PictureCount
removed 19 332
removed 1 10
removed 6 57
removed 1 15
removed 1 7
removed 2 18
removed 4 34
Doing a subtraction in the join feels a little wierd, so is this a data issue, or am I missing something else?
Thanks,
-Andrew
Those with an owner_id of 0 are either anonymous uploads or were added via batch add in previous versions of Coppermine where the user_id was not recorded.
Hi Nibbler,
Thanks for the info, I kinda had a feeling that was the case.
So I could update the zero fields to 1 (my user ID) and them be able to join on owner_id (but still only where category is greater than 10000)?
Is the 10000 limit a standard on all coppermine installations? It looks like it's a constant in some of the code I looked at, so that tells me that it might be different for some installations?
Thanks again,
Andrew