asked on

Obtaining top number of downloads from db...

In hindsight I may have set up my database in an odd way but it's too late for now. It is set up with the following fields:

section|ip|date|file

So what I am trying to do now is create something that will list the top 10 downloaded files for example. How can I do this since there are no actual numbers? I thought maybe using COUNT or GROUP BY but am unsure.

a.marsh

So there is a record in the table for each time a file is downloaded?

Ant

a.marsh

Let's build this up bit by bit. :o)

Firstly let's get the total downloads for each file:

select file, count(file) as total_downloads from table_name group by file order by total_downloads desc;

Try that and make sure it works.

:o)

Ant

ASKER CERTIFIED SOLUTION

a.marsh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

a.marsh

Just make sure you change the "table_name" part as you didn't specify the name of the table.

:o)

Ant

treyjeff

ASKER

Yes an example of the data is:

Table name is downloads, fields are (section,ip,date, file)

YaBB 61.11.236.67 17 May 2001 01:43:17 PM ./downloads/YaBB1Fcgi.zip
YaBB 212.163.219.62 17 May 2001 01:43:47 PM ./downloads/YaBB1F.zip
YaBB 212.163.219.62 17 May 2001 01:44:11 PM ./downloads/Y1Gold_Beta7_pl.zip
YaBB 63.42.144.99 17 May 2001 01:45:15 PM ./downloads/YaBB1Fcgi.zip
YaBB 24.190.88.15 17 May 2001 01:49:41 PM ./downloads/YaBB1Fcgi.zip

treyjeff

ASKER

I got an error with the query above when I entered it through PHPmyAdmin. I changed the table name as well. Where is total_downloads coming from?

treyjeff

ASKER

Query I entered:

select file, count(file) as total_downloads from downloads group by file order by total_downloads desc
limit 10;

treyjeff

ASKER

PHP file:

<?php

### Page Setup ###
$title = "Xnull Downloads Report";
$nav1 = "YaBB";
$nav2 = "downloads";

$mysql_link = mysql_connect("localhost", "xnull_downloadsu", "downloadspwd");
if (!$mysql_link) {
print "ERROR: Can not connect to database\n";
exit;
}
mysql_select_db(xnull_downloads, $mysql_link);

$SQL = "select file, count(file) as total_downloads from downloads group by file order by

total_downloads desc limit 10;";
$result= mysql_query($SQL, $mysql_link) or die(mysql_error());
include("http://yabb.xnull.com/header.php);

echo <<<EOT
<td width="75%">
The following page produces reports based on download counts for Xnull. 
Top 10 Downloads 
$result
</td>

EOT;
include("footer.php");

?>

a.marsh

mmmmmm...........try:

select file, count(*) as total_downloads from table_name group by file order by total_downloads desc limit 10;

total_downloads is just the alias to the count column.

What version of MySQL are you using?

Ant

treyjeff

ASKER

I may be doing something wrong too...Simply printing $result, will it show whatever was grabbed?

treyjeff

ASKER

Ok, looks like I was doing something wrong :)

Now $result returns: Resource id #2

a.marsh

mmmmmm....change:

echo <<<EOT
<td width="75%">
The following page produces reports based on download counts for Xnull. 
Top 10 Downloads 
$result
</td>

EOT;

to:

echo("<td width=\"75%\">The following page produces reports based on download counts for Xnull. Top 10 Downloads ");

while(($row = mysql_fetch_object($result))){
echo("" . $row->file . "(" . $row->total_downloads . ")");
}

echo("</td>");

Ant

treyjeff

ASKER

Oh ok! Now I see how it works. So I need to reference the array as well? (row->file)

Check it out at http://yabbdev.xnull.com/downloads_report.php

Is there anyway to trim off the extra ./downloads/ ?

treyjeff

ASKER

a.marsh

Change:

echo("" . $row->file . "(" . $row->total_downloads . ")");

to:

echo("" . substr(strrchr(($row->file), "/"), 1) . "(" . $row->total_downloads . ")");

:o)

You've got a VERY good amount of help for your 10 points you know!! EE recommend a minimum of 50 points for an easy question!

;o)

Ant

treyjeff

ASKER

I know :) A lot of my free points are still tied up in deleted questions...Sorry :(

Thanks for all the help though!

a.marsh

Glad to help. :o)

Ant