Announcement

Collapse
No announcement yet.

Delete Duplicate Posts

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Delete Duplicate Posts

    Is this possible to do through the ACP or through a query?

    I'm referring specifically to posts that contain the exact same characters even if it's an html embedded code object.

  • #2
    I know of a query if you have multiple duplicate posts in the same thread.

    Code:
    SELECT bad_rows. * FROM post AS bad_rows
    INNER JOIN (
    SELECT threadid, dateline, username, userid, MIN( postid ) AS min_postid FROM post
    GROUP BY threadid, dateline, userid
    HAVING count( * ) >1
    ) AS good_rows ON good_rows.threadid = bad_rows.threadid
    AND good_rows.dateline = bad_rows.dateline
    AND good_rows.userid = bad_rows.userid
    AND good_rows.min_postid <> bad_rows.postid
    However I can't guarantee it will work and you will need to back up your database before running it to ensure data integrity. It won't work if the duplicate post is spread among different threads though.
    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud demonstration site.
    vBulletin 5 API

    Comment


    • #3
      Wayne:

      This works great for us. I knew we had a lot of duplicate posts built up over the years, but I was a little surprised just how many. This cleaned up our database very nicely. We intend to run it on a regular basis from now on.

      Thanks. -- Rik

      Comment


      • #4
        Is there a 3.8 solution for this? This will identify all of the duped posts in mysql or phpmyadmin but it will not actually DELETE the duped posts. Some error appears in phpmyadmin when you try to delete them and I guess you'd have to write an entire custom script to remove the dupes found by this query??

        MySQL said:
        #1054 - Unknown column 'bad_rows.postid' in 'where clause'

        Comment


        • #5
          Originally posted by MikeF View Post
          Is there a 3.8 solution for this? This will identify all of the duped posts in mysql or phpmyadmin but it will not actually DELETE the duped posts. Some error appears in phpmyadmin when you try to delete them and I guess you'd have to write an entire custom script to remove the dupes found by this query??
          Starting your own thread would help, as you have stated that your running vBulletin version 3.8.x. You would need to supply details... eg: 'I use impex to import forum over', 'I have seo running on my forum', 'I am the only one that has this problem' <-hint to solution; Your the admin of your forum an this function does not apply to you.

          Comment


          • #6
            Originally posted by MikeF View Post
            Is there a 3.8 solution for this? This will identify all of the duped posts in mysql or phpmyadmin but it will not actually DELETE the duped posts. Some error appears in phpmyadmin when you try to delete them and I guess you'd have to write an entire custom script to remove the dupes found by this query??
            I'm running 3.8 and use this query every couple of days without any problem.

            As for deleting the rows obtained by the query, we use Navicat (a commercial program) which runs the query and then returns all the rows in an editable table form which looks very much like an Excel spreadsheet. Then we can delete all rows, delete individual rows, or even edit the title of threads, etc. Its a much better program than phpmyadmin for running mysql queries. We've used it for several years. Its great! There is a test-drive version (Google it, its famous) which I believe is fully functional so you can see what I mean.

            Regards. -- Rik

            Comment


            • #7
              Your information has proven to be invaluable to me... can I have your phone number? Can I work for you? Unreal... the problem was solved in a manner of minutes with your solution. Thank you so much. You saved my forum.

              Comment


              • #8
                Thanks! this was of great help!

                Comment

                widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                Working...
                X