vBulletin Mods

The Official vBulletin Modifications Site
https://www.vbulletin.org/forum/showthread.php?t=328729

VB4 queries converted to VB5 ?
by svoeric
24 Apr 2021 19:52

I had 2 queries I ran against the db in VB4 to pull most recent posts (in a non-vb page), and all posts from a specific forum.

Wondering if someone can help me convert these to VB5.6 -- I've not quite got the vb5 DB format figured out yet


this one grabs stuff from my FYI thread --

SELECT thread.threadid, thread.title, thread.dateline, post.threadid,thread.firstpostid,
thread.lastpost, thread.visible, thread.lastposter, thread.lastposterid,
post.threadid, post.pagetext
FROM " . $TABLE_PREFIX . "thread AS thread
LEFT JOIN " . $TABLE_PREFIX . "post AS post ON (post.postid = thread.firstpostid)
WHERE thread.forumid =56
AND thread.visible = 1
GROUP BY thread.threadid
ORDER BY thread.lastpost DESC
LIMIT 0, " . $postret . "
");



this one shows most recent posts --

SELECT thread.threadid, thread.title, thread.dateline,
thread.lastpost, thread.visible, thread.lastposter, thread.lastposterid,
forum.forumid, forum.title as forumtitle, user.userid, usergroup.opentag, usergroup.closetag
FROM " . $TABLE_PREFIX . "thread AS thread
INNER JOIN " . $TABLE_PREFIX . "forum AS forum ON(forum.forumid = thread.forumid)
LEFT JOIN " . $TABLE_PREFIX . "user AS user ON (thread.lastposterid = user.userid)
LEFT JOIN " . $TABLE_PREFIX . "usergroup AS usergroup ON (usergroup.usergroupid = user.usergroupid)
WHERE thread.forumid NOT IN($excforum)
AND thread.visible = 1
GROUP BY thread.threadid
ORDER BY thread.lastpost DESC
LIMIT 0, " . $postret . "
");

Thanks Really appreciate any help

Seven Skins 24 Apr 2021 20:34

Download this: https://www.vbulletin.org/forum/showthread.php?t=328344

Look in this folder: Yilmaz - Hotbar vB5\core\packages\yilmazhotbar\db\mysql

You get pretty good idea what queries to use.

svoeric 26 Apr 2021 02:41

grabbed this query, It's a ton of help, but the group by statements are causing some issues... if I remove the group by, it works.



Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.



any thoughts on this ?

get this when I run With the group-by in.
failed : Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'DB_bb.node.iconid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Seven Skins 27 Apr 2021 22:32

Try this:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


svoeric 28 Apr 2021 19:44

That did the trick, Thanks a ton.

one more question -- (maybe should go to new thread, IDK...)

in VB4 -- I would build a link to the thread (plus show-latest), which was easy, as you just passed showthread.php the threadID, and poof, piece of cake.

in 5, seems you need /forum/forum-name/child-name/node-id to build the direct link URL

I'm not seeing this info directly in the node table, what table do I need to join to get it ?
OR, is there a short-cut to have VB build the url with just the node-id ?

Thanks again for the help!

svoeric 30 Apr 2021 02:13

found it!

if you direct url to yoursite.com/forum/node/nodeID
takes you right to the thread, err node :)


All times are GMT. The time now is 12:41.

Powered by vBulletin® Version 3.8.14
Copyright © 2021, MH Sub I, LLC dba vBulletin. All Rights Reserved. vBulletin® is a registered trademark of MH Sub I, LLC
Copyright ©2001 - , vbulletin.org. All rights reserved.