<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.6000.16705" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>I posted on the d.o forum yesterday about putting
together a sql query for a block in which users are listed according to the
total pageviews their content had received. I got one or two suggestions but
nothing concrete so waded in to try and get it done myself. I've come up against
both sql problems and theming issues too.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>The two relevant tables are node and node_counter,
so if we take some example data:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2>node</FONT></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New"
size=2>nid uid</FONT></DIV>
<DIV><FONT face="Courier New" size=2>1
1</FONT></DIV>
<DIV><FONT face="Courier New" size=2>2
2</FONT></DIV>
<DIV><FONT face="Courier New" size=2>3
1</FONT></DIV>
<DIV><FONT face="Courier New" size=2>4
1</FONT></DIV>
<DIV><FONT face="Courier New" size=2>5
2</FONT></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2>node_counter</FONT></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2>nid
totalcount</FONT></DIV>
<DIV><FONT face="Courier New" size=2>1
15</FONT></DIV>
<DIV><FONT face="Courier New" size=2>2
22</FONT></DIV>
<DIV><FONT face="Courier New" size=2>3
9</FONT></DIV>
<DIV><FONT face="Courier New" size=2>4
17</FONT></DIV>
<DIV><FONT face="Courier New" size=2>5
6</FONT></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2>so I'm looking for a block that gives
me:</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face="Courier New" size=2>USER
PAGEVIEWS</FONT></DIV>
<DIV><FONT face="Courier New" size=2>John (uid 1)
41</FONT></DIV>
<DIV><FONT face="Courier New" size=2>Ned (uid 2)
28</FONT></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2>so far, I have this:</FONT></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New"
size=2><table><tr><td>Title</td><td>Pageviews</td></tr><BR><?php<BR>$uid
= $account->uid;<BR>$query= "SELECT node.title, node.nid,
node_counter.totalcount FROM node INNER JOIN node_counter ON node.nid =
node_counter.nid WHERE uid = $uid ORDER BY totalcount DESC LIMIT 5";<BR>$results
= db_query($query);<BR>while($output = db_fetch_object($results)){<BR>print
"<tr><td>";<BR>print "<a
href=\"/node/$output->nid\">";<BR>print $output->title;<BR>print
"</a>";<BR>print "</td><td>";<BR>print
$output->totalcount;<BR>print
"</td></tr>";<BR>}<BR>?><BR></table></FONT></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>which gives me the top 5 viewed pages for each user
on their main /user page. It's very handy and I think I'll use it. Can someone
give me a pointer to go the next step towards my initial aim, the total of a
user's pageviews and then a list of the top X users on a site based on
pageviews. I know I need to use SUM in the sql statement but I don't seem to be
able to get the syntax right.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>My other question regards the theming of the
output. You can see from my code that I've manually built the links...is there
an easier way to do it. I've used "$output .= node_title_list($result);" before
but that was just for a list of node titles, not for a table with 2-3 cols in
it. Is there another theme function for what I'm trying to do?</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>thanks in advance for any hints you can
give.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Neil</FONT></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV></BODY></HTML>