Welcome to Geeklog, Anonymous Saturday, November 30 2024 @ 06:31 pm EST
Geeklog Forums
Most Popular Article Block?
I searched, and did not find any other posts on this, so maybe I am just the lone nut job, but I am trying to figure out a way to make a 'Most Popular Article' block that pulls from the top ten viewed article list from the Site Stats page. I want it to be viewable by everyone (including anonymous). Any suggestions?
Luhme summa dat GL.
Luhme summa dat GL.
17
13
Quote
Status: Banned
machinari
Forum User
Full Member
Registered: 03/22/04
Posts: 1512
this is the query from stats.php that gets the top 10 stories:
change LIMIT 10 to LIMIT 1 and you have the most popular story. Throw the output into a block or staticpage. If you want to return the story as well, make it look like this:
that should get you started
Text Formatted Code
$result = DB_query("SELECT sid,title,hits FROM {$_TABLES["stories"]} WHERE (draft_flag = 0) AND (date <= NOW()) AND (Hits > 0)" . COM_getPermSQL ('AND') . " ORDER BY Hits desc LIMIT 10");Text Formatted Code
$result = DB_query("SELECT sid,title,introtext,bodytext,hits FROM {$_TABLES["stories"]} WHERE (draft_flag = 0) AND (date <= NOW()) AND (Hits > 0)" . COM_getPermSQL ('AND') . " ORDER BY Hits desc LIMIT 1");that should get you started
15
15
Quote
Status: offline
DubiousChrisJ
Forum User
Regular Poster
Registered: 05/10/05
Posts: 114
Well, I will be the first to admit I am no guru...
here's the error:
Sat Jun 25 01:48:39 2005 - 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (draft_flag = 0) AND (date 0) ORDER. SQL in question: SELECT sid,title,hits FROM WHERE (draft_flag = 0) AND (date 0) ORDER BY Hits desc LIMIT 10
Any suggestions?
Luhme summa dat GL.
here's the error:
Sat Jun 25 01:48:39 2005 - 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (draft_flag = 0) AND (date 0) ORDER. SQL in question: SELECT sid,title,hits FROM WHERE (draft_flag = 0) AND (date 0) ORDER BY Hits desc LIMIT 10
Any suggestions?
Luhme summa dat GL.
14
12
Quote
Status: Banned
machinari
Forum User
Full Member
Registered: 03/22/04
Posts: 1512
the error is due to the script not specifying a table to query
make sure that your function (i assume your putting this into a function) makes $_TABLES global...
{
global $_TABLES;
//the rest of your code
}
make sure that your function (i assume your putting this into a function) makes $_TABLES global...
Text Formatted Code
function phpblock_mostpopular(){
global $_TABLES;
//the rest of your code
}
14
19
Quote
Status: offline
DubiousChrisJ
Forum User
Regular Poster
Registered: 05/10/05
Posts: 114
I'll be the first to admit I am not much of a PHP writer...more like a fairly effective code editor. Here is the code I tried to cobble together for the most Popular Article function, and as is so often the case, another SQL error was generated. Please forgive my retardedness.
global $_TABLES;
$sql = DB_query("SELECT sid,title,hits FROM {$_TABLES["stories"]} WHERE (draft_flag = 0) AND (date <= NOW())
AND (Hits > 0)" . COM_getPermSQL ('AND') . " ORDER BY Hits desc LIMIT 10");
$result = DB_query($sql);
$nrows = DB_numRows( $result );
if( $nrows > 0 )
{
$string = '';
for( $i = 0; $i < $nrows; $i++ )
{
$A = DB_fetchArray( $result );
{
$poplist = COM_makeList( $popular, 'list-popular-stories' );
$string .= $poplist . '<br>';
}
}
$popular = array();
}
$popular[] = '<a href="' . COM_buildUrl( $_CONF['site_url']
. '/article.php?story=' . $A['sid'] ) . '">' . $A['title']
. '</a> (' . $A['comments'] . ')';
if( !empty( $popular ))
{
$poplist = COM_makeList( $popular, 'list-popular-stories' );
$string .= $poplist;
DB_query( "UPDATE {$_TABLES['blocks']} SET content = '$string' WHERE name = 'popular_stories'" );
}
}
Any help would be greatly appreciated.
Luhme summa dat GL.
Text Formatted Code
function phpblock_mostPopular () {global $_TABLES;
$sql = DB_query("SELECT sid,title,hits FROM {$_TABLES["stories"]} WHERE (draft_flag = 0) AND (date <= NOW())
AND (Hits > 0)" . COM_getPermSQL ('AND') . " ORDER BY Hits desc LIMIT 10");
$result = DB_query($sql);
$nrows = DB_numRows( $result );
if( $nrows > 0 )
{
$string = '';
for( $i = 0; $i < $nrows; $i++ )
{
$A = DB_fetchArray( $result );
{
$poplist = COM_makeList( $popular, 'list-popular-stories' );
$string .= $poplist . '<br>';
}
}
$popular = array();
}
$popular[] = '<a href="' . COM_buildUrl( $_CONF['site_url']
. '/article.php?story=' . $A['sid'] ) . '">' . $A['title']
. '</a> (' . $A['comments'] . ')';
if( !empty( $popular ))
{
$poplist = COM_makeList( $popular, 'list-popular-stories' );
$string .= $poplist;
DB_query( "UPDATE {$_TABLES['blocks']} SET content = '$string' WHERE name = 'popular_stories'" );
}
}
Any help would be greatly appreciated.
Luhme summa dat GL.
19
13
Quote
Status: Banned
machinari
Forum User
Full Member
Registered: 03/22/04
Posts: 1512
I modified it a bit for you... tested this one and it works
{
global $_TABLES;
$result = DB_query("SELECT sid,title,hits FROM {$_TABLES['stories']}
WHERE (draft_flag = 0) AND (date <= NOW()) AND (hits > 0)"
. COM_getPermSQL ('AND') . " ORDER BY hits desc LIMIT 10");
$nrows = DB_numRows($result);
if( $nrows > 0 ){
$string = '';
$popular = array();
for( $i = 0; $i < $nrows; $i++ ){
$A = DB_fetchArray( $result );
$string .= $poplist . '<br>';
$popular[] = '<a href="' . COM_buildUrl( $_CONF['site_url']
. '/article.php?story=' . $A['sid'] ) . '">' . $A['title']
. '</a> (' . $A['hits'] . ')';
}
if( !empty( $popular )){
$poplist = COM_makeList( $popular, 'list-popular-stories' );
}
}
return $poplist;
}
notice that this function doesn't update the content column in gl_blocks. it functions as a PHP block and so returns the list. That means the block you create must be a php block, not a normal block.
Text Formatted Code
function phpblock_mostPopular(){
global $_TABLES;
$result = DB_query("SELECT sid,title,hits FROM {$_TABLES['stories']}
WHERE (draft_flag = 0) AND (date <= NOW()) AND (hits > 0)"
. COM_getPermSQL ('AND') . " ORDER BY hits desc LIMIT 10");
$nrows = DB_numRows($result);
if( $nrows > 0 ){
$string = '';
$popular = array();
for( $i = 0; $i < $nrows; $i++ ){
$A = DB_fetchArray( $result );
$string .= $poplist . '<br>';
$popular[] = '<a href="' . COM_buildUrl( $_CONF['site_url']
. '/article.php?story=' . $A['sid'] ) . '">' . $A['title']
. '</a> (' . $A['hits'] . ')';
}
if( !empty( $popular )){
$poplist = COM_makeList( $popular, 'list-popular-stories' );
}
}
return $poplist;
}
notice that this function doesn't update the content column in gl_blocks. it functions as a PHP block and so returns the list. That means the block you create must be a php block, not a normal block.
17
15
Quote
Status: offline
DubiousChrisJ
Forum User
Regular Poster
Registered: 05/10/05
Posts: 114
Thank you thank you thank you!
It works perfectly.
Luhme summa dat GL.
It works perfectly.
Luhme summa dat GL.
18
12
Quote
All times are EST. The time is now 06:31 pm.
- Normal Topic
- Sticky Topic
- Locked Topic
- New Post
- Sticky Topic W/ New Post
- Locked Topic W/ New Post
- View Anonymous Posts
- Able to post
- Filtered HTML Allowed
- Censored Content