Rebuild Thread and Forum Tables from Post Table

[Zylantex]

I am trying to migrate from phpBB 3.1.9 to vBulletin and the process has been somewhat successful. I first migrated to vB 3.8.9 then upgraded to vB 4.2.2
However there are outstanding issues that I am sure others have encountered and overcome and I would be grateful for any help you can offer.

My primary concern is that all threads are lacking dates. Naturally this means the latest posts in forums are also dateless with the result that all forums show no recent content. Zero dates all show as 31 Dec 1969 11:00pm. If I expand the forum search to show threads from the beginning all threads show up. Albeit with the wrong date and wrong number of replies/views. The number of post per page parameter is also being ignored but that might resolve itself if the previously mentioned issues are fixed.

The good news every post has a date and valid thread so it must be possible to rebuild the thread and forum information from them.

I have almost zero SQL experience so the chances of me re-writing the tables without help is almost nil.

I have tried to step through the post table and rewrite the thread table but without knowledge of how to use indexes properly I'm picking up the wrong values.

Basically what is required is a block of code to rewrite chunks of the thread and forum tables from the post table. Does an utility such as this exist already?
How such an glaring gap in data could have arisen is beyond me!

The concerned fields in the tables are as follows:

Table: post
Field: postid - contains valid data
Field: threadid - contains valid data
Field: username : contains valid data
Field: userid - contains valid data
Field: dateline - contains valid data

Table: thread
Field: firstpostid - all zeroes
Field: lastpostid - all zeroes
Field: lastpost - all zeroes - should be a date I believe
Field: forumid - contains valid data
Field: dateline - contains valid data

Table: forum
Field: lastpost - all zeroes
Field: lastpostid - all zeroes
Field: lastthreadid - contains valid data
Field: lastposter - all zeroes
Field: lastposterid - all zeroes

Mod - I raised this issue in a zombie thread but I think it needs a thread of its own as the scope of the issues is more widespread than I previously thought.

[Dave]

Try the following. (Best to try on a backup first to make sure it works well)

Update firstpostid in thread:
UPDATE thread SET firstpostid = (SELECT postid FROM post WHERE threadid = thread.threadid ORDER BY postid ASC LIMIT 0,1)

Update lastpostid in thread:
UPDATE thread SET firstpostid = (SELECT postid FROM post WHERE threadid = thread.threadid ORDER BY postid DESCLIMIT 0,1)

Update date in thread:
UPDATE thread SET lastpost = (SELECT dateline FROM post WHERE threadid = thread.threadid ORDER BY postid DESC LIMIT 0,1)

Update lastpost in forum:
UPDATE forum SET lastpost = (SELECT b.dateline FROM thread AS a INNER JOIN post AS b ON a.threadid = b.threadid WHERE a.forumid = forum.forumid ORDER BY b.postid DESC LIMIT 0,1)

Update lastpostid in forum:
UPDATE forum SET lastpostid = (SELECT b.postid FROM thread AS a INNER JOIN post AS b ON a.threadid = b.threadid WHERE a.forumid = forum.forumid ORDER BY b.postid DESC LIMIT 0,1)

Update lastposter in forum:
UPDATE forum SET lastposter = (SELECT b.username FROM thread AS a INNER JOIN post AS b ON a.threadid = b.threadid WHERE a.forumid = forum.forumid ORDER BY b.postid DESC LIMIT 0,1)

Update lastposterid in forum:
UPDATE forum SET lastposterid = (SELECT b.userid FROM thread AS a INNER JOIN post AS b ON a.threadid = b.threadid WHERE a.forumid = forum.forumid ORDER BY b.postid DESC LIMIT 0,1)

[Zylantex]

Hi Dave,

That is amazing. I'm genuinely overwhelmed at your kindness. Thank you.

--------------- Added 1475677791 at 1475677791 ---------------

I added a little, I'm just putting it all together here in case it's useful to anyone else.


These all work perfectly - DO NOT EDIT

UPDATE thread SET firstpostid = (SELECT postid FROM post WHERE threadid = thread.threadid ORDER BY postid ASC LIMIT 0,1)

UPDATE thread SET lastpostid = (SELECT postid FROM post WHERE threadid = thread.threadid ORDER BY postid DESC LIMIT 0,1)

UPDATE thread SET lastpost = (SELECT dateline FROM post WHERE threadid = thread.threadid ORDER BY postid DESC LIMIT 0,1)

UPDATE thread SET lastposterid = (SELECT userid FROM post WHERE threadid = thread.threadid ORDER BY postid DESC LIMIT 0,1)

UPDATE thread SET lastposter = (SELECT postusername FROM post WHERE threadid = thread.threadid ORDER BY postid DESC LIMIT 0,1)

UPDATE thread SET replycount = ((SELECT COUNT(threadid) AS count FROM post WHERE threadid = thread.threadid ORDER BY postid ASC LIMIT 0,1) - 1 )

UPDATE forum SET lastpost = (SELECT b.dateline FROM thread AS a INNER JOIN post AS b ON a.threadid = b.threadid WHERE a.forumid = forum.forumid ORDER BY b.postid DESC LIMIT 0,1)

UPDATE forum SET lastpostid = (SELECT b.postid FROM thread AS a INNER JOIN post AS b ON a.threadid = b.threadid WHERE a.forumid = forum.forumid ORDER BY b.postid DESC LIMIT 0,1)

UPDATE forum SET lastpostid = (SELECT b.postid FROM thread AS a INNER JOIN post AS b ON a.threadid = b.threadid WHERE a.forumid = forum.forumid ORDER BY b.postid DESC LIMIT 0,1)

UPDATE forum SET lastpostid = (SELECT b.postid FROM thread AS a INNER JOIN post AS b ON a.threadid = b.threadid WHERE a.forumid = forum.forumid ORDER BY b.postid DESC LIMIT 0,1)



I just need to find out how to reset the number of replies and I'm pretty much there.

I think the lack of number of replies is what is causing the "display number of posts per page" to fall over. Zero replies means no need to calculate pages.