Page 1 of 1

Getting top 10

Posted: Tue Feb 07, 2006 2:41 am
by tamiya
How would i go about getting the top 10 entries for a certain month?
eg. there are a hundred projects in my database (msql) and i would like to see which 10 were viewed the most during the last month...

any help would be appreciated

thx

Posted: Tue Feb 07, 2006 3:04 am
by JayBird

Code: Select all

SELECT * FROM `projects` WHERE `date` BETWEEN '2006-02-01' AND '2006-02-28 ORDER BY `hits` ASC LIMIT 10
Moved to databases

Posted: Tue Feb 07, 2006 3:19 am
by tamiya
its not so simple....

im getting the data from a log table, where there are no specific counters for each project...

what happens when a person views a project, is that it just gets logged to this table, by client, contact, project reference, action and date. im just trying to find a rather short solution to this problem, because im working on a deadline, and writing extensive pieces of code will really be pushing it. (there's 15 reports, all displaying data for the last 6 mths...) so i basically need to write 15 diff pieces of it.

Posted: Tue Feb 07, 2006 3:23 am
by JayBird
Well, i gave you one answer to your lacklustre question.

If you want an answer that specifically fits you needs, then you really need to go into more depth when asking your questions.

If in doubt, read this - viewtopic.php?t=8815

Posted: Tue Feb 07, 2006 3:44 am
by tamiya
look, im relatively new to php, in my firts job after graduating not so long ago. i studied c#, not php and SQL Server2000, not mysql, so im still learning to adjust. i've got all these ideas in my head on what to do, but i find i very difficult to express them. thats why i ask for help.

sorry if the questions arent always clear, but i forget that people dont see the rest of the code as i do...


now, if i may ask?
if i were to use an array, to populate 10 spaces for the highest 10 entries, how would i go about it to get the one with the most at the top. remember that the tables are just log tables, and not a table which increments a certain project's counter column everytime it gets viewed.
the table looks like follows:
id,client,contact,projref,action,datetime
1,zzz000,001,ndn0001,viewed project,2005-01-01
2,zzz000,001,ndn5511,viewed project,2005-01-03
3,zzz010,012,ndn0001,viewed project,2005-01-03
4,zzz010,012, ,submit search ,2005-01-03

now i need to find out, where action='viewed project' which projref had the most 'viewed projects' during a certain period, andlist the highest 10 in a display table

i hope this is a bit more clear

Posted: Tue Feb 07, 2006 10:28 am
by Weirdan
something along the lines of

Code: Select all

select 
  project.name, count(*) as views
from
  project
inner join
  log
on
  log.project_id = project.id and log.action = 'viewed project'
group by
  project.id
order by 
  views desc
limit 10
you will need to tweak the table/field names though.

Posted: Tue Feb 07, 2006 5:10 pm
by raghavan20
modified...

Code: Select all

select
  project.name, count(*) as views
from
  project
inner join
  log
on
  log.project_id = project.id and log.action = 'viewed project'
where month(`datetime`) = $monthIndex /* provide month as number here */
group by
  project.id
order by
  views desc
limit 10
few examples of month()

Code: Select all

mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

mysql> select month(curdate());
+------------------+
| month(curdate()) |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

mysql> select month(sysdate());
+------------------+
| month(sysdate()) |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)