www.webdeveloper.com
Results 1 to 5 of 5

Thread: [RESOLVED] Group results by most tagged

  1. #1
    Join Date
    Aug 2012
    Location
    UK
    Posts
    10

    resolved [RESOLVED] Group results by most tagged

    Hello

    I am struggling to make a query work in the intended manner, the logic is for some reason not working in my head.

    This is what I have:

    Table: uploads
    Fields: uid, ufile

    Table: tags
    Fields: tid, tname

    Table: tags_uploads
    Fields: tuid, tid, uid

    A user uploads a file, the file name is stored in uploads.ufile.
    The user can then tag these uploads.
    A script checks against existing tags to see if one already exists, but if it doesn't then it makes a new tag, and the name of that tag is stored in tags.tname.
    Then the tag is linked to the upload using tags_upload, where tags_uploads.tid refers to tags.tid, and tags_uploads.uid refers to uploads.uid.

    I want to bring back a list of results like so:

    Grouped by image - I don't want to display the same image twice.
    Ordered by popular tags.

    So for example:

    A user uploads 10 images, but tags 3 of those images as 'black and white', 2 of them as 'red' and 1 as 'blue', I want the user to see the 3 'black and white' images first, followed by the 2 'red' images, then the 'blue' one and then whatever else follows.

    But the query I am using is not working, because it is just sorting by the number of times that particular image has been tagged, and not the popularity of the tag that has been attached to the upload.

    This is what I have:

    Code:
    if($viewRes=$mysqli->query("SELECT uploads.ufile,COUNT(tags_uploads.tid) AS tidcount FROM uploads INNER JOIN tags_uploads ON uploads.uid = tags_uploads.uid GROUP BY uploads.ufile ORDER BY tidcount DESC")){
    Feel free to laugh, but know that I will learn from whatever you will teach me

  2. #2
    Join Date
    Aug 2012
    Location
    UK
    Posts
    10
    Something like this is closer to where I want to be:

    Code:
    SELECT uploads.ufile,tags_uploads.tid FROM uploads INNER JOIN tags_uploads ON uploads.uid = tags_uploads.uid ORDER BY tags_uploads.tid
    It returns the following:

    Code:
    Array ( [ufile] => 1351795216_php515e.jpeg [tid] => 1 ) 
    Array ( [ufile] => 1351795217_php5181.jpeg [tid] => 2 ) 
    Array ( [ufile] => 1351795216_php517f.jpeg [tid] => 3 ) 
    Array ( [ufile] => 1351795216_php515d.jpeg [tid] => 4 ) 
    Array ( [ufile] => 1351795216_php515f.jpeg [tid] => 5 ) 
    Array ( [ufile] => 1351795218_php5195.jpeg [tid] => 6 ) 
    Array ( [ufile] => 1351795218_php5194.jpeg [tid] => 6 ) 
    Array ( [ufile] => 1351795217_php5193.jpeg [tid] => 6 ) 
    Array ( [ufile] => 1351795217_php5192.jpeg [tid] => 6 ) 
    Array ( [ufile] => 1351795217_php5191.jpeg [tid] => 6 ) 
    Array ( [ufile] => 1351795217_php5180.jpeg [tid] => 6 ) 
    Array ( [ufile] => 1351795217_php5181.jpeg [tid] => 7 ) 
    Array ( [ufile] => 1351795216_php515e.jpeg [tid] => 8 ) 
    Array ( [ufile] => 1351795216_php517f.jpeg [tid] => 9 ) 
    Array ( [ufile] => 1351795216_php515d.jpeg [tid] => 10 ) 
    Array ( [ufile] => 1351795217_php5180.jpeg [tid] => 11 ) 
    Array ( [ufile] => 1351795216_php515f.jpeg [tid] => 12 ) 
    Array ( [ufile] => 1351795216_php515e.jpeg [tid] => 13 ) 
    Array ( [ufile] => 1351795216_php517f.jpeg [tid] => 13 ) 
    Array ( [ufile] => 1351795216_php517f.jpeg [tid] => 14 ) 
    Array ( [ufile] => 1351795216_php515d.jpeg [tid] => 15 ) 
    Array ( [ufile] => 1351795217_php5191.jpeg [tid] => 16 ) 
    Array ( [ufile] => 1351795217_php5192.jpeg [tid] => 17 ) 
    Array ( [ufile] => 1351795217_php5193.jpeg [tid] => 18 ) 
    Array ( [ufile] => 1351795218_php5194.jpeg [tid] => 19 )
    As you can see, tag 6 is used the most. I would like to order by the number of times something is tagged in a descending order, so that 6 would be at the top, followed by what looks like 13 in this example.

  3. #3
    Join Date
    Sep 2011
    Posts
    59
    try that
    Code:
    SELECT uploads.ufile,tags_uploads.tid, t.TagsNum 
    FROM uploads 
    INNER JOIN tags_uploads 
    ON uploads.uid = tags_uploads.uid 
    inner join (select distinct tags_uploads.tid, COUNT(*) as TagsNum 
    			from tags_uploads 
    			group by tid) as t
    on 	tags_uploads.tid=t.tid	
    ORDER BY t.TagsNum

  4. #4
    Join Date
    Aug 2012
    Location
    UK
    Posts
    10
    Thanks for your response.

    I am going to test this shortly and will get back to you.

  5. #5
    Join Date
    Aug 2012
    Location
    UK
    Posts
    10
    Thanks, that works perfectly.

    I'm still learning a lot about SQL. I thought I was good at it. Looks like I need to go re-read some material and practice some more.

    Many thanks for providing your help on this.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles