Welcome to Geeklog, Anonymous Saturday, April 20 2024 @ 01:05 am EDT
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.
8
5
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
8
8
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.
9
5
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
}
8
11
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.
10
6
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.
11
7
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.
11
5
Quote
All times are EDT. The time is now 01:05 am.
- 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