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
Getting top 10
Moderator: General Moderators
Code: Select all
SELECT * FROM `projects` WHERE `date` BETWEEN '2006-02-01' AND '2006-02-28 ORDER BY `hits` ASC LIMIT 10its 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.
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.
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
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
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
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
something along the lines of
you will need to tweak the table/field names though.
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- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
modified...
few examples of month()
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 10Code: 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)