Member Avatar for phplover

Hi,

I have developed a download / upload script that also counts the downloads of each downloaded file.

For statistics i currently got total files and total overall downloads but would like to extend this so it can say how many downloads there has been in a week, month, year.

I know i would need to probably setup a cron job to reset those stats each month but my dilema is what is the best way and most efficent way of creating such a script that i could implement with my own?

I did a Google search to see if i could find a tutorial to see how other developers do such a task but could not find anything.

If anyone could recommend the best way for me to do this please reply. My stores all the data about the file in a MySql database and files are stored in a folder not MySQL.

Thank you
PHPLOVER

  • 3 Contributors
  • 6 Replies
  • 191 Views
  • 15 Hours Discussion Span
  • Latest Post Latest Post by phplover

Dani AI

Generated

Two practical, production-ready patterns solve “downloads last week/month/year.” ’ simple counter+cron is easy and works for small sites, but it loses history and can be brittle under concurrent traffic. ’s AWStats is useful for server-log analysis, but it doesn’t integrate per-file counts into an app DB. Below are two robust alternatives and operational tips.

Option A — full timestamped log (most flexible). Record one row per download with a UTC timestamp and optional user_id/ip. This makes ad‑hoc queries trivial and preserves history for audits or trend graphs.

CREATE TABLE download_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  file_id INT NOT NULL,
  downloaded_at DATETIME NOT NULL,
  user_id INT NULL,
  ip VARCHAR(45),
  INDEX(file_id, downloaded_at)
);

Weekly count example:

SELECT COUNT(*) FROM download_log
WHERE file_id = ? AND downloaded_at >= UTC_TIMESTAMP() - INTERVAL 7 DAY;

Option B — daily aggregates (best for scale). Maintain one row per (file_id, day) and increment it atomically. Queries for week/month are SUM() over the date range. This bounds table size and gives fast reads.

CREATE TABLE daily_downloads (
  file_id INT NOT NULL,
  day DATE NOT NULL,
  downloads INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY(file_id, day)
);
-- atomic increment
INSERT INTO daily_downloads (file_id, day, downloads) VALUES (?, CURDATE(), 1)
ON DUPLICATE KEY UPDATE downloads = downloads + 1;

Operational notes: store times in UTC, index columns used in ranges, use prepared statements or PDO for safe atomic updates, and prefer server-side file delivery (X-Sendfile / X-Accel-Redirect) so PHP only logs then hands off the transfer. For very high write rates, increment in Redis and flush to MySQL periodically. For unique-user downloads store user_id and use COUNT(DISTINCT user_id) or maintain a separate daily-unique set. Cron-based zeroing is simple but discards history; rolling aggregates or daily summary rows are generally safer and more useful long term.

Recommended Answers

All 6 Replies

Member Avatar for rch1231

There are several free stats apps out there that you can download and install. Awstats is a good place to start.

Member Avatar for phplover

Hi,

I don't mean that, it's something i want to add to the script, like There was 20 downloads this week, 150 downloads this month.


Thanks
PHPLOVER

Member Avatar for FlashCreations

Assuming that the downloads are routed through a PHP script, it should be simple enough to record user download data. The devil is in the database and how you store this information. Here's my recommendation:
You only need one table to store download information along with hit counts. I recommend three columns for tracking download numbers: A total downloads column, a weekly downloads column, and a monthly downloads column. When a user requests a file download, simply increment all three fields for that file by one.
You stats are simply a MySQL query away:

//Get total downloads
mysql_query("SELECT downloads FROM files WHERE id='".$id."'");

//Get weekly downloads
mysql_query("SELECT weeklyDownloads FROM files WHERE id='".$id."'");

//Get monthly downloads
mysql_query("SELECT monthlyDownloads FROM files WHERE id='".$id."'");

*Depending on what you name your columns

You will need CRON jobs to run once every Sunday and once at the end of every month. The weekly script should contain something along the lines of:

//Set all the files to a 0 weekly download count for the new week
mysql_query("UPDATE files SET weeklyDownloads=0");

As you can assume the monthly CRON will look something like this:

//Set all the files to a 0 monthly download count for the new month
mysql_query("UPDATE files SET monthlyDownloads=0");

**These all assume that you have already connected to MySQL and a DB, and perform the necessary mysql_fetch_array() 's after if the query requires it.

Cheers,
PhpMyCoder

commented: Fantastic post made in helping me :) +1
Member Avatar for phplover

Thanks PhpMyCoder, Superb example and very simple now you have told me the logic, thank you very much :)

added to your reputation, thanks

PHPLOVER

Edited by phplover because: n/a
Member Avatar for FlashCreations

Thanks man! If you need any help implementing it, shoot me a PM or post on the PHP forum.

Cheers,
PhpMyCoder :)

Member Avatar for phplover

Thanks for your kindess :)

I know how to code it thanks, just could not think of the logic behind it.

PhpLover

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.