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.
Want to do a nice Joomla and Wordpress plugin to display latest.
Helpful? Donate. Thanks! |
Fast VPS Host for vBulletin: A2 Hosting & IONOS |
Helpful? Donate. Thanks! |
Fast VPS Host for vBulletin: A2 Hosting & IONOS |
Helpful? Donate. Thanks! |
Fast VPS Host for vBulletin: A2 Hosting & IONOS |
<?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 = [];
}
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); }
PHP Warning: count(): Parameter must be an array or an object that implements Countable in /home/zoopet/forum/api/json.php on line 4
$thread_returns['totalRecords'] = count($thread_returns['results']);
There is 1 user viewing this forum topic.
Comment