Anonymous Anonymous - 1 year ago 94
PHP Question

Grouping and sorting on numerous MySQL tables with PHP

I am trying to output a series of connected records over multiple tables in DESC order based on the number of occurrences a record makes in the downloads table.

Resource Data (resource_data):

----------------------------------------
| ID | Contributor ID | Title |
----------------------------------------
| 1 | 111 | Resource A |
| 2 | 222 | Resource B |
| 3 | 333 | Resource C |
| 4 | 222 | Resource D |
| 5 | 111 | Resource E |
----------------------------------------


Contributor Data (contributor_data):

----------------------
| ID | User Name |
----------------------
| 111 | User X |
| 222 | User Y |
| 333 | User Z |
----------------------


Download Data (download_data):

---------------------
| ID | Resource ID |
---------------------
| 1 | 4 |
| 2 | 1 |
| 3 | 4 |
| 4 | 3 |
| 5 | 4 |
| 6 | 4 |
| 7 | 2 |
| 8 | 5 |
| 9 | 4 |
| 10 | 5 |
---------------------


When sorted, the data should come out something like (not the formatting, just the content):

4 222 Resource D User Y (5 records)
5 111 Resource E User X (3 records)
2 222 Resource B User Y (2 records)
3 333 Resource C User Z (1 record)
1 111 Resource A User X (1 record)


So far I have this code, but it's only putting out one record (there should be many, limited to 4 based on the query).

SELECT * FROM resource_data, contributor_data, download_data
WHERE resource_data.contributor_id=contributor_data.id
AND download_data.resource_id=resource_data.id
ORDER BY count(download_data.resource_id) DESC LIMIT 4

Answer

What you want is a GROUP BY clause to group those resource id records from the download_data table like this...

SELECT contributor_data.id, resource_data.id, resource_data.title,
       contributor_data.username, COUNT(download_data.id) AS records
FROM resource_data
JOIN contributor_data
     ON resource_data.contributor_id = contributor_data.id
JOIN download_data
     ON resource_data.id = download_data.resource_id
GROUP BY download_data.resource_id
ORDER BY records DESC;

This would give you the desired result because you can now alias the COUNT result based on the GROUP BY (since ORDER BY takes place after the grouping is done).

So your final result ends up looking more like what you want...

+------+------+------------+----------+---------+
| id   | id   | title      | username | records |
+------+------+------------+----------+---------+
|  222 |    4 | Resource D | User Y   |       5 |
|  111 |    5 | Resource E | User X   |       2 |
|  222 |    2 | Resource B | User Y   |       1 |
|  333 |    3 | Resource C | User Z   |       1 |
|  111 |    1 | Resource A | User X   |       1 |
+------+------+------------+----------+---------+

Here's an SQLfiddle of the same...