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?