Getting top 10

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
tamiya
Forum Newbie
Posts: 14
Joined: Mon Feb 06, 2006 7:19 am

Getting top 10

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
tamiya
Forum Newbie
Posts: 14
Joined: Mon Feb 06, 2006 7:19 am

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
tamiya
Forum Newbie
Posts: 14
Joined: Mon Feb 06, 2006 7:19 am

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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)
Post Reply