Forum Preview

06/25/05 12:41am

Status: offline

DubiousChrisJ

Forum User

Regular Poster

Registered: 05/10/05

Posts: 114

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.

06/25/05 01:35am

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:

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");

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:

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

06/25/05 01:56am

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.

06/25/05 02:40am

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

Text Formatted Code
function phpblock_mostpopular()

{

    global $_TABLES;

    //the rest of your code

}

06/25/05 11:44am

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.

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.

06/25/05 02:18pm

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

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.