<!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>OK, I kept working away and am now
<STRONG>very</STRONG> close:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial
size=2><table><tr><td>User</td><td>Pageviews</td></tr><BR><?php<BR>$query=
"SELECT node.nid, node.uid, SUM( node_counter.totalcount ) AS
TotalPageviews<BR>FROM node<BR>JOIN node_counter ON node.nid =
node_counter.nid<BR>GROUP BY node.uid<BR>ORDER BY `TotalPageviews` DESC LIMIT
5";<BR>$results = db_query($query);<BR>while($output =
db_fetch_object($results)){<BR>print "<tr><td>";<BR>print "<a
href=\"/user/$output->uid\">";<BR>print $output->uid;<BR>print
"</a>";<BR>print "</td><td>";<BR>print
$output->TotalPageviews;<BR>print
"</td></tr>";<BR>}<BR>?><BR></table></FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>This gives me the following:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2>User
Pageviews</FONT></DIV>
<DIV><FONT face="Courier New" size=2>2
349</FONT></DIV>
<DIV><FONT face="Courier New" size=2>7
298</FONT></DIV>
<DIV><FONT face="Courier New" size=2>1
187</FONT></DIV>
<DIV><FONT face="Courier New" size=2>5
183</FONT></DIV>
<DIV><FONT face="Courier New" size=2>8
136</FONT></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>How do I get the user names to show instead of the
uid's? Is that going to be another JOIN with user table or can I get them from
the UID in a simpler way?</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Neil</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=neil@esl-lounge.com href="mailto:neil@esl-lounge.com">Neil:
esl-lounge.com</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A title=support@drupal.org
href="mailto:support@drupal.org">support@drupal.org</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Wednesday, September 10, 2008 12:12
PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> [support] couple of issues with
an sql query and theming a db query</DIV>
<DIV><BR></DIV>
<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>
<P>
<HR>
<P></P>-- <BR>[ Drupal support list | http://lists.drupal.org/
]</BLOCKQUOTE></BODY></HTML>