stats by # of days inputted
Moderator: General Moderators
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
stats by # of days inputted
how can I show stats from past say 3 days, 7 days, ect like most downloaded in past 3 days, most rated in past 3 days.
I have my themes table with these stats as overall.
whats the best approach?
I know I'll probally have to create a new table called stats that is something like this
stats_table: day, theme_id, downloads, rated, viewed, demo_played
but that would only store per day. how can I get stats from past few days inputted?
I have my themes table with these stats as overall.
whats the best approach?
I know I'll probally have to create a new table called stats that is something like this
stats_table: day, theme_id, downloads, rated, viewed, demo_played
but that would only store per day. how can I get stats from past few days inputted?
Re: stats by # of days inputted
You need to use a range selection and group the data in your SQL. For example:
That assumes that the `day` column is a date type.
If you need the theme data as well (like the name) use a LEFT JOIN to the themes table to get it.
Code: Select all
SELECT SUM(`downloads`) AS total_downloads, SUM(`rating`) AS total_rating, SUM(`viewed`) AS total_viewed
FROM `theme_stats`
WHERE 1
AND `day` BETWEEN DATE('2008-07-11') AND DATE('2008-07-14')
GROUP BY `theme_id`
ORDER BY total_downloads
If you need the theme data as well (like the name) use a LEFT JOIN to the themes table to get it.
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
Re: stats by # of days inputted
wow. didnt know such a thing existed in mySQL ;]
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
Re: stats by # of days inputted
how do I use left join?
this is what I got so far. my field is "demos"
this is what I got so far. my field is "demos"
Code: Select all
if(!empty($_GET['day']))
{
$result = mysql_query("SELECT * FROM settings") or die(mysql_error());
$setting = mysql_fetch_object($result);
$end_date = $setting->day;
$start_date = $setting->day - $_GET['day'];
$result = mysql_query("SELECT SUM(`demos`) AS demos WHERE 1 AND `day` BETWEEN $start_date AND $end_date GROUP BY `theme_id` ORDER BY demos LIMIT $start,12") or die(mysql_error());
}
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
Re: stats by # of days inputted
heres what i got now
http://www.wpskins.org/wordpress-templa ... .php?day=3
everything is blank...
http://www.wpskins.org/wordpress-templa ... .php?day=3
everything is blank...
Code: Select all
if (!empty($_GET["start"]))
{
$start = mysql_real_escape_string($_GET['start']);
}
else
{
$start = 0;
}
if(!empty($_GET['day']))
{
$day1 = $_GET['day'];
$result = mysql_query("SELECT * FROM settings") or die(mysql_error());
$setting = mysql_fetch_object($result);
$end_date = $setting->day;
$start_date = $setting->day - $_GET['day'];
$result = mysql_query("SELECT SUM(`downloads`) AS downloads, SUM(`votes`) AS votes, SUM(`views`) AS views
FROM theme_stats_by_day WHERE 1
AND `day` BETWEEN $start_date AND $end_date
GROUP BY `theme_id`
ORDER BY downloads
LIMIT $start,12") or die(mysql_error());
$result2 = mysql_query("SELECT SUM(`downloads`) AS downloads, SUM(`votes`) AS votes, SUM(`views`) AS views
FROM theme_stats_by_day WHERE 1
AND `day` BETWEEN $start_date AND $end_date
GROUP BY `theme_id`
ORDER BY downloads") or die(mysql_error());
$num_results = mysql_num_rows($result2);
}
else
{
$day1 = 0;
$result = mysql_query("SELECT * FROM themes ORDER BY downloads DESC LIMIT $start,12") or die(mysql_error());
$result2 = mysql_query("SELECT * FROM themes ORDER BY downloads") or die(mysql_error());
$num_results = mysql_num_rows($result2);
}
while($themes = mysql_fetch_object($result))
{
if(!empty($_GET['day']))
{
phpEntry($themes->theme_id, $themes->name, $themes->username, $themes->downloads, $themes->views, $themes->demos, $themes->vote_overall, $themes->votes, $themes->screen_url);
}
else
{
phpEntry($themes->id, $themes->name, $themes->username, $themes->downloads, $themes->views, $themes->demos, $themes->vote_overall, $themes->votes, $themes->screen_url);
}
}
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
Re: stats by # of days inputted
nobody knows?
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
-
psychotomus
- Forum Contributor
- Posts: 487
- Joined: Fri Jul 11, 2003 1:59 am
Re: stats by # of days inputted
is this thing that complicated?