CODEWITHSUNDEEP
mysqlselectcountunique
Reidmere
Reidmere

Reputation: 76

MySQL: showing totals

I am trying to figure out how to have PHP check and print 2 different functions.

Both of these questions are referring to table called "remix". The first, and more important problem at the minute, is I would like to know how to show how many DIFFERENT values are under "author", as to compile the amount of total authors registered. I need to know not only how to most efficiently use COUNT on returning UNIQUE names under "author", but how to show it inline with the total number of rows, which are currently numbered.

The second question would be asking how I would be able to set up a top 3 artists, based on how many times their name occurs in a list. This also would show on the same page as the above code.

Here is my current code:

require 'remix/archive/connect.php';
mysql_select_db($remix);
$recentsong = mysql_query("SELECT ID,song,author,filename FROM remix ORDER by ID desc limit 1;");
$row = mysql_fetch_array($recentsong);

echo'
<TABLE BORDER=1><TR><TD WIDTH=500>

Currently '.$row['ID'].' Remixes by **(want total artists here)** artists.<BR>

Most recent song: <A HREF=remix/archive/'.$row['filename'].'>'.$row['song'].'</A> by <FONT COLOR=white>'.$row['author'].'</FONT>

So as you can see, I have it currently set up to show the most recent song (not the most efficient way), but want the other things in there, such as at least the top contributor, but don't know if I would be able to put it all in one php block, break it, or be able to do it all within one quarry call, with the right code.

Thanks for any help!

Upvotes: 1

Views: 124

Answers (1)

Olivier Coilland
Olivier Coilland

Reputation: 3096

I'm not sure I really understood everything in your question but we'll work this through together :p

I've created an SQLFiddle to work on some test data: http://sqlfiddle.com/#!2/9b613/1/0.
Note the INDEX on the author field, it will assure good performance :)

In order to know how to show how many DIFFERENT values are under "author" you can use:

SELECT COUNT(DISTINCT author) as TOTAL_AUTHORS
FROM remix;

In order to know the total number of rows, which are currently numbered you can use:

SELECT COUNT(*) as TOTAL_SONGS
FROM remix;

And you can combine both in a single query:

SELECT
    COUNT(DISTINCT author) as TOTAL_AUTHORS,
    COUNT(*) as TOTAL_SONGS
FROM remix;

To the top 3 subject now. This query will give you the 3 authors with the greatest number of songs, first one on top:

SELECT
    author,
    COUNT(*) as AUTHOR_SONGS
FROM remix
GROUP BY author
ORDER BY AUTHOR_SONGS DESC
LIMIT 3;

Let me know if this answer is incomplete and have fun with SQL !

Edit1: Well, just rewrite your PHP code in:

(...)
$recentsong = mysql_query("SELECT COUNT(DISTINCT author) as TOTAL_AUTHORS, COUNT(*) as TOTAL_SONGS FROM remix;");
$row = mysql_fetch_array($recentsong);
(...)
Currently '.$row['TOTAL_SONGS'].' Remixes by '.$row['TOTAL_AUTHORS'].' artists.<BR>
(...)

For the top3 part, use another mysql_query and create your table on the fly :)

Upvotes: 1

Related Questions