Register Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools
  #1  
Old 24 Apr 2021, 20:52
svoeric svoeric is offline
 
Join Date: May 2008
VB4 queries converted to VB5 ?

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
Reply With Quote
  #2  
Old 24 Apr 2021, 21:34
Seven Skins's Avatar
Seven Skins Seven Skins is offline
 
Join Date: Sep 2008
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.
Reply With Quote
  #3  
Old 26 Apr 2021, 03:41
svoeric svoeric is offline
 
Join Date: May 2008
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
Reply With Quote
  #4  
Old 27 Apr 2021, 23:32
Seven Skins's Avatar
Seven Skins Seven Skins is offline
 
Join Date: Sep 2008
Try this:

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

Reply With Quote
  #5  
Old 28 Apr 2021, 20:44
svoeric svoeric is offline
 
Join Date: May 2008
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!

Last edited by svoeric; 28 Apr 2021 at 21:01.
Reply With Quote
  #6  
Old 30 Apr 2021, 03:13
svoeric svoeric is offline
 
Join Date: May 2008
found it!

if you direct url to yoursite.com/forum/node/nodeID
takes you right to the thread, err node
Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 03:58.

Layout Options | Width: Wide Color: