Welcome to Geeklog Sunday, June 16 2019 @ 12:34 pm EDT

Geeklog Forums

Most Popular Article Block?


Status: offline

DubiousChrisJ

Forum User
Regular Poster
Registered: 10/05/05
Posts: 114
hopeful
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.

Status: offline

machinari

Forum User
Full Member
Registered: 22/03/04
Posts: 1512
this is the query from stats.php that gets the top 10 stories:
PHP 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");

 
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:
PHP 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

Status: offline

DubiousChrisJ

Forum User
Regular Poster
Registered: 10/05/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.

Status: offline

machinari

Forum User
Full Member
Registered: 22/03/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...
PHP Formatted Code
function phpblock_mostpopular()
{
    global $_TABLES;
    //the rest of your code
}
 

Status: offline

DubiousChrisJ

Forum User
Regular Poster
Registered: 10/05/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.

PHP 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.

Status: offline

machinari

Forum User
Full Member
Registered: 22/03/04
Posts: 1512
I modified it a bit for you... tested this one and it works
PHP 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.

Status: offline

DubiousChrisJ

Forum User
Regular Poster
Registered: 10/05/05
Posts: 114
Thank you thank you thank you!

It works perfectly.
Luhme summa dat GL.

All times are EDT. The time is now 12:34 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