Understanding the Database - SQL Question

[FirstDivision]

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:

Code Select Expand


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):

Code Select Expand


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

[Nibbler]

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.

[FirstDivision]

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