Page 1 of 1
stats by # of days inputted
Posted: Wed Aug 13, 2008 9:16 pm
by psychotomus
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?
Re: stats by # of days inputted
Posted: Thu Aug 14, 2008 3:05 am
by onion2k
You need to use a range selection and group the data in your SQL. For example:
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
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.
Re: stats by # of days inputted
Posted: Thu Aug 14, 2008 3:19 am
by psychotomus
wow. didnt know such a thing existed in mySQL ;]
Re: stats by # of days inputted
Posted: Sun Aug 24, 2008 10:12 pm
by psychotomus
how do I use left join?
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());
}
Re: stats by # of days inputted
Posted: Sun Aug 24, 2008 11:04 pm
by psychotomus
heres what i got now
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);
}
}
Re: stats by # of days inputted
Posted: Mon Aug 25, 2008 7:37 pm
by psychotomus
nobody knows?
Re: stats by # of days inputted
Posted: Tue Aug 26, 2008 7:45 am
by psychotomus
;[
Re: stats by # of days inputted
Posted: Wed Aug 27, 2008 12:58 am
by psychotomus
is this thing that complicated?