Delete Duplicate Posts

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • legionofangels2
    Senior Member
    • Feb 2007
    • 701

    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.
  • Wayne Luke
    vBulletin Technical Support Lead
    • Aug 2000
    • 73981

    #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

    • Rik Brown
      Senior Member
      • May 2005
      • 345
      • 4.2.X

      #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

      • MikeF
        Senior Member
        • Jan 2006
        • 172

        #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

        • Jose Amaral Rego
          Senior Member
          • Feb 2005
          • 11058
          • 1.1.x

          #5
          Originally posted by MikeF
          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

          • Rik Brown
            Senior Member
            • May 2005
            • 345
            • 4.2.X

            #6
            Originally posted by MikeF
            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

            • MikeF
              Senior Member
              • Jan 2006
              • 172

              #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

              • LuisMontemayor
                New Member
                • Oct 2009
                • 18
                • 3.8.x

                #8
                Thanks! this was of great help!

                Comment

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