Here is my code:
<?
$query = "select count(*) as monthly_views from visitors group by
extract('year', time), extract('month', time) order by monthly_view desc
limit 1";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);
echo $num_results;
echo mysql_error();
?>
When I execute it I get this error:
Warning: Supplied argument is not a valid MySQL result resource in
C:\Inetpub\TecEco_PHP\stats_interface\summary.php on line 75
You have an error in your SQL syntax near ''year', time), extract('month',
time) order by monthly_view desc limit 1' at line 1
I don't know any advanced SQL so can't really debug it.
Could someone tell me what I am doing wrong please?
--
JJ Harrison
[EMAIL PROTECTED]
www.tececo.com
"Richard Lynch" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]...
> >I have been going fine in a Web Stats program until now.
> >
> >How can I figure out which day has had the most records(ie visitors)
> >inserted?
> >
> >There is a time column which has unix timestamp of when the record was
> >inserted.
> >
> >The best I can think of currently is:
> >To use a while loop to repeatedly query the DB.
> >then use an if statement to replace two variables(UNIX timestamp and
number
> >of visitors) if the value returned is higher.
>
> Almost any time you do a while loop to repeatedly query the DB, you've
done
> something wrong. :-)
>
> Either you are missing an SQL function that will do what you want, or you
> just designed the db schema wrong and the application wrong in the first
> place.
>
> Fortunately, in this case, you're just missing an SQL function.
>
> Dig through the manual of your database (you didn't say which one) in the
> Date/Time functions section, and see if you can find one that will extract
> the year and month from a timestamp.
>
> You'll end up writing something not unlike this:
>
> select count(*) as monthly_views
> from visitors
> group by extract('year', whattime), extract('month', whattime)
> order by monthly_view desc
> limit 1
>
> The GROUP BY part is the "magic" -- It will do any "aggregate" function
> (count, average, sum) broken down by whatever fields are listed. In this
> case, I broke it down by year & month.
>
> If you wanted the most popular month over the last five years, it would be
> something not unlike:
>
> select count(*) as monthly_views
> from visitors
> where whattime + '@ 5 years' >= now()
> group by extract('month', whattime)
> order by monthly_view desc
> limit 1
>
> Disclaimers:
> 1. 'extract' is probably not the right function name. You'll have to look
> that up.
> 2. The "whattime + '@ 5 years'" works just nifty in PostgreSQL. You have
to
> type more than that in MySQL, I think... MySQL date arithmetic always
gives
> me a pain.
>
> Always dig for a way to do it in SQL first.
>
> --
> Like Music? http://l-i-e.com/artists.htm
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
