Display latest topics on non-vb pages

[ChristianAlfredsson]

Do you have a sql-question that selects latest X posts in all open topics.

Want to do a nice Joomla and Wordpress plugin to display latest.

[glennrocksvb]

I recommend just using the Node API similar to what you did for login to fetch the latest posts instead of doing a direct SQL query to the database.

[ChristianAlfredsson]

Can you give me a hint how?

[glennrocksvb]

Correction on what I said. You should use the Search API not the Node API.

Call the get getInitialResults Search API method and pass a Search JSON data (same JSON you would use to display Latest Topics in Search Module). You could also specify the number of result items per page to return and page number.

[ChristianAlfredsson]

I have tried but don't understand. Can you explain more?

[glennrocksvb]

Check for sample usage of getInitialResults() function at /core/vb/api/follow.php inside the getFollowingUserActivities() function. Inside that function, there is a call to getInitialResults() function.

[ChristianAlfredsson]

Sorry but still lost

[ChristianAlfredsson]

Is there a guide how to use vbulletin for beginners? With examples?

[kolifan]

We use this code on our website. It brings headings of messages of a forum to the homepage

<?php

class ForumParser
{
    private $forumIds = [15, 263];
    private $ctChannel = 23;
    private $ctText = 22;
    private $prefix = 'https://oursite/forum/';
    private $prefixPage = 'https://oursite/forum/channel/';
    private $count = 5;

    public function get()
    {
        $this->loadChannels($this->forumIds);
        $this->loadThreads();
        $this->loadPosts();
        $this->sortPosts();
        $this->loadUsers();
        $result = [];
        foreach ($this->posts as $post) {
            $p = $this->fillPost($post);
            if ($p !== null) {
                $result[] = $p;
            }
        }
        return $result;
    }

    private function loadChannels($ids)
    {
        $pattern = '
            SELECT
                `nodeid`,`parentid`,`title`,`urlident`
            FROM
                `vbc_node`
            WHERE
                `nodeid` IN ('.implode(',', $ids).')
        ';
        foreach (DB::query2base1($pattern, null, DB::fAssoc) as $row) {
            $this->channels[$row['nodeid']] = [
                'title' => $row['title'],
                'url' => $this->prefixPage.$row['urlident'],
            ];
        }
    }

    private function loadChildren($ids, $level = 5)
    {
        if ($level <= 0) {
            return;
        }
        $pattern = '
            SELECT
                `nodeid`,`parentid`,`title`,`urlident`
            FROM
                `vbc_node`
            WHERE
                `parentid` IN ('.implode(',', $ids).')
                AND `contenttypeid`='.$this->ctChannel.'
                AND `showpublished`
        ';
        $idsN = [];
        foreach (DB::query2base1($pattern, null, DB::fAssoc) as $row) {
            $id = $row['nodeid'];
            $prefix = isset($this->channels[$row['parentid']]) ? $this->channels[$row['parentid']]['url'].'/' : $this->prefixPage;
            $this->channels[$id] = [
                'title' => $row['title'],
                'url' => $prefix.$row['urlident'],
            ];
            $idsN[] = $id;
        }
        if (!empty($idsN)) {
            $this->loadChildren($idsN, $level - 1);
        }
    }

    private function loadThreads()
    {
        $pattern = '
            SELECT
                *
            FROM
                `vbc_node`
            WHERE
                `parentid` IN ('.implode(',', array_keys($this->channels)).')
                AND `contenttypeid`='.$this->ctText.'
                AND `showpublished`
            ORDER BY
                `lastcontent` DESC
            LIMIT '.$this->count.'
        ';
        foreach (DB::query2base1($pattern, null, DB::fAssoc) as $row) {
            $this->threads[$row['nodeid']] = $row;
            $this->posts[] = $row;
            if ($row['totalcount'] > 0) {
                $this->threadsP[] = $row['nodeid'];
            }
        }
    }

    private function loadPosts()
    {
        if (empty($this->threadsP)) {
            return;
        }
        $pattern = '
            SELECT
                *
            FROM
                `vbc_node`
            WHERE
                `parentid` IN ('.implode(',', $this->threadsP).')
                AND `contenttypeid`='.$this->ctText.'
                AND `showpublished`
            ORDER BY
                `lastcontent` DESC
            LIMIT '.$this->count.'
        ';
        foreach (DB::query2base1($pattern, null, DB::fAssoc) as $row) {
            $this->posts[] = $row;
        }
    }

    private function sortPosts()
    {
        usort($this->posts, function ($a, $b) {
            if ($a['publishdate'] > $b['publishdate']) {
                return -1;
            }
            return 1;
        });
        $this->posts = array_slice($this->posts, 0, $this->count);
    }

    private function loadUsers()
    {
        $ids = [];
        foreach ($this->posts as $post) {
            $ids[$post['userid']] = true;
        }
        if (empty($ids)) {
            return;
        }
        $pattern = '
            SELECT
                `u`.*,`u`.`userid`,`u`.`username`,`a`.`filename`
            FROM
                `vbc_user` AS `u`
            LEFT JOIN
                `vbc_customavatar` AS `a`
                ON `a`.`userid`=`u`.`userid`
            WHERE
                `u`.`userid` IN ('.implode(',', array_keys($ids)).')';
        foreach (DB::query2base1($pattern, null, DB::fAssoc) as $row) {
            if ($row['filename']) {
                $row['avatar_src'] = $this->prefix.'core/customavatars/'.$row['filename'];
            } else {
                $row['avatar_src'] = $this->prefix.'core/images/default/default_avatar_large.png';
            }
            $this->users[$row['userid']] = $row;
        }
    }

    private function fillPost($post)
    {
        $id = $post['nodeid'];
        if (isset($this->threads[$id])) {
            $thread = $post;
            $suffix = '';
        } elseif (isset($this->threads[$post['parentid']])) {
            $thread = $this->threads[$post['parentid']];
            $suffix = '?p='.$post['nodeid'].'#post'.$post['nodeid'];
        } else {
            return null;
        }
        if (isset($this->channels[$thread['parentid']])) {
            $forum = $this->channels[$thread['parentid']];
        } else {
            return null;
        }
        if (isset($this->users[$post['userid']])) {
            $user = $this->users[$post['userid']];
        } else {
            return null;
        }
        return [
            'thread_title' => $thread['title'],
            'thread_url' => $forum['url'].'/'.$thread['nodeid'].'-'.$thread['urlident'].$suffix,
            'forum_title' => $forum['title'],
            'forum_url' => $forum['url'],
            'date' => date('Y-m-d H:i:s', $post['publishdate']),
            'ts' => $post['publishdate'],
            'user_name' => $user['username'],
            'user_url' => $this->prefix.'member/'.$user['userid'].'-'.mb_strtolower($user['username'], 'UTF-8'),
            'avatar_src' => $user['avatar_src'],
        ];
    }

    private $channels = [];

    private $threads = [];

    private $threadsP = [];

    private $posts = [];

    private $users = [];
}

[ChristianAlfredsson]

Is it possibel to get your class DB also?

[beergeek]

Here's how I ended up doing this -- I was (*shockingly*) able to get the API to work. I created a simple file that initialized the API and as @glenrocksvb suggested, uses the search json parameters to pull posts. I greatly simplified the returning JSON because it caused problems occasionally (failing in the json_encode step for some reason).

if (isset($_GET['days'])) {
  $num_days = (int)$_GET['days'];
} else {
  $num_days = 1;
}

if (isset($_GET['wanted'])) {
  $channel_id = 23;
} else {
  $channel_id = 21;
}

// Path to the vBulletin 5 folder. 
// '.' indicates current directory where the .htaccess file resides.
$vbpath = '..';
define('CSRF_PROTECTION', false);
require_once($vbpath . '/includes/vb5/autoloader.php');
vB5_Autoloader::register($vbpath);
vB5_Frontend_Application::init('config.php');

$api = Api_InterfaceAbstract::instance();

$search_json = '{"channel":["'.$channel_id.'"],"starter_only":1,"date":{"from":"'.$num_days.'"},"sort":{"lastcontent":"desc"},"view":"","exclude_type":["vBForum_PrivateMessage"]}';

$threads = vB_Api::instanceInternal('search')->getInitialResults($search_json, 30, 1);      

$i = 0;
$thread_returns = array();

foreach ($threads['results'] as $tid => $thread_details) {
  $i++;
  $thread_returns['results'][$i] = array(
    'title'         => $thread_details['content']['title'],
    'publishdate'   => $thread_details['content']['publishdate'],
    'channeltitle'  => $thread_details['content']['channeltitle']
  );
  $thread_returns['results'][$i]['postlink'] = vB5_Route::buildUrl(
    $thread_details['routeid'] . '|fullurl',
    array(
      'nodeid' => $thread_details['nodeid'],
      'title' => $thread_details['title']
    )
  );
}
$thread_returns['totalRecords'] = count($thread_returns['results']);
$thread_returns['searchJSON'] = $search_json;
$thread_returns['days'] = $num_days;

if ($thread_returns['totalRecords'] > 0) {
  header('Content-Type: application/json');
  echo json_encode($thread_returns);
}

Put this on my VB5 forum, under a new directory at the root -- then called that from a script on my WP site to parse the JSON and make a list of links. You can, of course, modify the array elements returned to fit what you need.

Cheers,
Jeff

[ChristianAlfredsson]

Tried it and get this error and no JSON

Link: https://www.zoopet.com/forum/api/json.php

PHP Warning:  count(): Parameter must be an array or an object that implements Countable in /home/zoopet/forum/api/json.php on line 4 

This is the line in the code

$thread_returns['totalRecords'] = count($thread_returns['results']); 

[beergeek]

That's just a warning -- and I guess it's not bulletproof code. But nothing is returned -- meaning nothing matched your search parameters / json ($search_json)

[ChristianAlfredsson]

Thank you! Of course. I will change it and report back!

[ChristianAlfredsson]

Great. It is now working for me too. Can't thank you enough!