VB4 queries converted to VB5 ?

[svoeric]

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]

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.

[svoeric]

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.

PHP Code:

SELECT node.iconid, node.prefixid, node.nodeid as nodeidsi, node.title as konubasligi, node.votes as begeeni, node.lastcontentauthor as uyeadi, node.lastauthorid as uyeidesi, node.routeid, node.lastcontent, node.created, node.lastcontentid, node.contenttypeid, node.parentid, node.textcount, node.totalcount as reply, icon.iconpath, icon.title as icontitle, closure.parent as forumidesi, closure.depth, forum.title as forumtitle, nodeview.count as viewsx, user.usergroupid, user.username, user.username, text.rawtext, text.previewimage
                FROM node AS node
                LEFT JOIN " . $TABLE_PREFIX . " closure AS closure ON (closure.child = node.nodeid)
                LEFT JOIN " . $TABLE_PREFIX . "user AS user ON (user.userid = node.lastauthorid)    
                LEFT JOIN " . $TABLE_PREFIX . "icon AS icon ON (icon.iconid = node.iconid)
                LEFT JOIN " . $TABLE_PREFIX . "nodeview AS nodeview ON (nodeview.nodeid = node.nodeid)
                LEFT JOIN " . $TABLE_PREFIX . "node AS forum ON (forum.nodeid = closure.parent)
                LEFT JOIN " . $TABLE_PREFIX . "text AS text ON (text.nodeid = closure.child)
                LEFT JOIN " . $TABLE_PREFIX . "channel AS channel ON (channel.nodeid = closure.child)
                LEFT JOIN " . $TABLE_PREFIX . "contenttype AS contenttype ON (contenttype.contenttypeid = node.contenttypeid)
                WHERE  node.parentid !=8 AND node.open !=0 AND closure.depth = 1  AND node.title !='' AND node.inlist = 1
                GROUP BY node.title
                ORDER BY node.created DESC
                LIMIT 14 


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]

Try this:

PHP Code:

SELECT node.iconid, node.prefixid, node.nodeid, node.title, node.votes, node.lastcontentauthor, node.lastauthorid, node.routeid, node.lastcontent, node.created, node.lastcontentid, node.contenttypeid, node.parentid, node.textcount, node.totalcount, icon.iconpath, icon.title, closure.parent, closure.depth, forum.title, nodeview.count, user.usergroupid, user.username, user.username, text.rawtext, text.previewimage 
FROM node AS node
LEFT JOIN closure AS closure ON (closure.child = node.nodeid)
LEFT JOIN user AS user ON (user.userid = node.lastauthorid)    
LEFT JOIN icon AS icon ON (icon.iconid = node.iconid)
LEFT JOIN nodeview AS nodeview ON (nodeview.nodeid = node.nodeid)
LEFT JOIN node AS forum ON (forum.nodeid = closure.parent)
LEFT JOIN text AS text ON (text.nodeid = closure.child)
LEFT JOIN channel AS channel ON (channel.nodeid = closure.child)
LEFT JOIN contenttype AS contenttype ON (contenttype.contenttypeid = node.contenttypeid)
WHERE  node.parentid !=8 AND node.open !=0 AND closure.depth = 1  AND node.title !='' AND node.inlist = 1
GROUP BY node.title, node.nodeid, closure.parent
ORDER BY node.created DESC
LIMIT 5 


[svoeric]

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]

found it!

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