stats by # of days inputted

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

stats by # of days inputted

Post 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?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: stats by # of days inputted

Post 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.
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Re: stats by # of days inputted

Post by psychotomus »

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

Post 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());
                }
 
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Re: stats by # of days inputted

Post 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);
                    }
                }
 
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Re: stats by # of days inputted

Post by psychotomus »

nobody knows?
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Re: stats by # of days inputted

Post by psychotomus »

;[
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Re: stats by # of days inputted

Post by psychotomus »

is this thing that complicated?
Post Reply