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

Download this: https://vborg.vbsupport.ru/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.

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.




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

Try this:

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!

found it!

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