If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

[RESOLVED] Group results by most tagged

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

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.