What would be the equation form to pull out the results for a field for the last 7 days?
let’s say i would like to know what amount of data a user took over that period. The table field in the users table is known as downloaded for obvious reasons.
ive tried the current format
SUM(downloaded / (NOW() - added) / 604800)) AS weekly but with no luck.
Well, you can sum something and then use the date to divide by. Makes no sense at all.
You can add all the downloaded data where the date is less than one week. You did not show us your table info. If you have a table that tracks downloads, and the date downloaded is kept in the table, you can just get that total with SELECT SUM(downloaded) WHERE download_date >= DATE(NOW()) - INTERVAL 7 DAY; Or something loosely.
But, if you just add the total downloaded each download, you have no way to track it by time at all.
How does your table keep track of downloads? Normally, you would have a table that tracks what the user downloaded, the date/time it was downloaded and the user_id to know who downloaded it. Then, you can do reports based on date and get totals with a simple query. If you want to just keep the running total for a week, you would need to keep the total for each day and then sum all the days. Once a new day comes along, the 7 day total would be accurate back to 7 days. Either way would work.
Not sure if that helps, but, hope so…
I dont have what you said. My downloaded field is int format.
The equation i’ve did in the SUM is the equation i already have in php to show that, and it works and i’m trying to recreate it for mysql so i can sort a table by daily, 7 days and 1 month downloaded
Mariusd, You can SUM a total of any field. But, if the data is just an integer (INT) it is just a number.
This means you can sum it all you want to get totals. BUT, again, if you want to use dates I showed you
how to use that in the WHERE clause. BUT, again, you need a DATE field to compare against.
You did not show us your table structure, so we have no idea what is in the table you want to run queries
against. If it is a table listing each download and the date it was downloaded, you can then use the query
that I gave you and just adjust it for 1 day, 7 day, 1 month and it will work. But, you need to explain the
way you store the date for each download and the name of the table and we can write the query for you.
Hope you understand…
My code that actually runs ok is this
while ($arr = mysqli_fetch_assoc($res)) {
$day = round((TIME_NOW - $arr[‘added’]) / 86400);
… some code here …
}
and the print code is
(yes it prints correctly for daily usage)
im just trying to implement this into the query itself so i can pull it out lets say AS daily
so when im trying to print it to just use
Again, you did not show us your table’s data structure. I will guess…
added DATETIME ( Guessing this is the date-time that a user downloaded some file )
downloaded INT ( Guessing this is the amount dowloaded by a user on the “added” date-time )
If so, this query will get all the three totals for you…
SELECT SUM(downloaded) AS total_one_day FROM your-table-name WHERE download_date >= DATE(NOW()) - INTERVAL 1 DAY;
SELECT SUM(downloaded) AS total_seven_day FROM your-table-name WHERE download_date >= DATE(NOW()) - INTERVAL 7 DAY;
SELECT SUM(downloaded) AS total_month FROM your-table-name WHERE download_date >= DATE(NOW()) - INTERVAL 1 MONTH;
This is ONE query with three sub-queries in it. It should return the three totals you asked for. BUT, this is set for all users as you did NOT show us code. If you want the data for all users, you would need to alter the query to include grouping by user ID…
That as far as I can help you without knowing the data table structure or at least seeing how your current query is set up. You never ever do these types of decodes in an array of results from another query. Waste of time. Just add these three queries into your original query and just add these three totals to the $res and then you have them ready to go for each user. Don’t try to do it with the resulting array. Will not work.