Query to split thread by post date

[Newtonhead1]

Greetings everyone,

I manage several mid sized forums with established threads that need to be regularly split to keep them to manageable size. Manually selecting hundreds of posts to be move is a time consuming job, so I'm looking to speed up the process.

I would sincerely appreciate if someone could provide a mysql query that would allow me to move posts from one thread to another by post date range.

In other words, I would like to run a query wherein I would enter the source and target thread id's and a start and end date range for the posts to be moved.

FYI, In case it makes a difference, I'm currently in vb 3.0.6, although I'm planning the upgrade to the latest version as soon as we finish documenting all of our telpmate mods.

Thanks,

Newtonhead1

[Eikinskjaldi]

OK, well lets assume you have start_date and end_date in sql format, for example

start_date = '2007-08-07'

the query would then be

update post set threadid=new_thread_id where threadid=old_thread_id
and dateline between (unix_timestamp(start_date), unix_timestamp(end_date))

Of course, given that the thread table has all sorts of co-dependencies related to lastpost and first post, I have absolutely no idea how the posts will view in the new thread.

If I was going to do this, and first I'd have to wrap my head around the concept of a thread needing to be of manageable size, I'd be doing it in code with the data managers.

EDIT: I am going off 3.5+ database structure. I have no idea if 3.0.x has the same tables and or fields.

[Newtonhead1]

Hi Eikinskjaldi,

I sincerely appreciate your input. Due to my obvious concern to get the syntax of the query exactly correct, would the following be in correct format?

update post set threadid=NEW thread id where threadid=OLD thread id and dateline between (unix_timestamp(start_date), unix_timestamp(end_date))

Sample:

update post set threadid=123456 where threadid=654321 and dateline between (unix_timestamp(2006-01-01), unix_timestamp(2006-12-31))

Am I missing any apostrophes or commas in the above? For example...

update post set threadid='123456' where threadid='654321' and dateline between (unix_timestamp('2006-01-01'), unix_timestamp('2006-12-31'))

Am I missing any formatting requirements, especially in the date formats?

FYI, I am planning to test this on a test forum before I run it on my production forum.

Thanks,

Newtonhead1

[Eikinskjaldi]

THis is correct

Originally Posted by Newtonhead1

update post set threadid=123456 where threadid=654321 and dateline between (unix_timestamp('2006-01-01'), unix_timestamp('2006-12-31'))

assuming new threadid is 123456 and old thread id is 654321

[Newtonhead1]

Hi Eikinskjaldi,

Of course, I have updated my example query.

update post set threadid=NEW thread id where threadid=OLD thread id and dateline between (unix_timestamp('start_date'), unix_timestamp('end_date'))

Thanks,

Newtonhead1

[Eikinskjaldi]

Right, except the dates are quoted, as per y previous post.

[Newtonhead1]

Greetings everyone,

I ran the following query:

update post set threadid=5355 where threadid=3558 and dateline between (unix_timestamp('2006-01-01'), unix_timestamp('2006-01-31'))

Upon which vB returned the following error:

error number: 1064
error desc: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Any ideas as to what's not correct in the query?

Thanks,

Newntonhead1

[Eikinskjaldi]

Originally Posted by Newtonhead1

Greetings everyone,

I ran the following query:

update post set threadid=5355 where threadid=3558 and dateline between (unix_timestamp('2006-01-01'), unix_timestamp('2006-01-31'))

Upon which vB returned the following error:

error number: 1064
error desc: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Any ideas as to what's not correct in the query?

Thanks,

Newntonhead1

Sorry, my bad. The correct syntax is
update post set threadid=5355 where threadid=3558 and dateline between unix_timestamp('2006-01-01') AND unix_timestamp('2006-01-31')

[Newtonhead1]

Greetings Everyone,

Eikinskjaldi, the revised query worked perfectly and has saved me dozens of hours. Many thanks.

FYI: After running the script I obviously needed to update thread info to tighten up several dependencies.

Okay, now I've got to ask for one further modification. Can anyone please provide the modifications to the script that will only change the thread ID on posts that DO NOT have a file attachment?

Just so everybody doesn't think I'm crazy, the reason I need this is because my forum has a number of Photo Gallery threads wherein members post numerous comments about the photos, and thus I get a lot of complaints that said Photo Gallery threads are being diluted with the non-photo posts. The easiest solution is to periodically move all the comments (posts without attachments) to a "Photo Gallery Comments" thread.

Thanks,

NewtonHead

[Eikinskjaldi]

I think posts with attachments have the attach field of the post table set to the number of attachments

update post set threadid=5355 where threadid=3558 and attach=0 and dateline between unix_timestamp('2006-01-01') AND unix_timestamp('2006-01-31')

Note that that attach field is not indexed, so the query is not optimised. Perhaps you should consider indexing it.