SQL Query Replace Text Issue

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • BentoSan
    New Member
    • Aug 2009
    • 11

    SQL Query Replace Text Issue

    ! SOLVED !


    I am having an issue on my forum where i had a problem with an outdated youtube embed plugin which has caused an issue when i updated my forum.

    I have about 1200+ posts with youtube links looking like this now : http://www.youtube.com/watch?v=http:...?v=A5Yj6ImVc1k

    when they should look like this:


    I tried this SQL update code but it hasnt worked for me:

    UPDATE post SET pagetext = REPLACE(pagetext, 'http://www.youtube.com/watch?v=http', 'http')

    Any help that can be assisted would be GREATLY appreciated !

    Edit1:
    I also tried this with no luck:

    UPDATE
    post
    SET
    pagetext = replace(pagetext,'http://www.youtube.com/watch?v=http', 'http')
    WHERE
    pagetext LIKE '%http://www.youtube.com/watch?v=http%'

    Edit2: heres an example of an affected post : http://www.djtechtools.com/forum/sho...2&postcount=10

    Edit3: Yes my member # does have access permissions in config.php
    Last edited by BentoSan; Fri 2 Apr '10, 1:02pm.
  • BentoSan
    New Member
    • Aug 2009
    • 11

    #2
    24 hour bump action !

    Comment

    • borbole
      Senior Member
      • Feb 2010
      • 3074
      • 4.0.0

      #3
      Try my mod, it does just that. It replaces text as well.



      But if you want to run the query manually, then you should use this query instead of the one that you have been trying to use above:

      Code:
      UPDATE post SET pagetext = replace(pagetext,  'http://www.youtube.com/watch?v=http', 'http');
      If your db tables have a prefix, you should include that as well in the query.

      Let me know how it will go.
      Last edited by borbole; Fri 2 Apr '10, 6:25am.

      Comment

      • BentoSan
        New Member
        • Aug 2009
        • 11

        #4
        Thanks for the help !

        However the SQL you suggested returned an error

        I input this:

        Code:
         UPDATE post SET pagetext = replace(pagetext, ‘http://www.youtube.com/watch?v=http’, ‘http’);
        An error occurred while attempting to execute your query. The following information was returned.
        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 '‘http://www.youtube.com/watch?v=http’, ‘http’)' at line 1
        The plugin you linked is for version 4.0.2 of Vbul and i am using 3.8.5 - i have looked for a plugin that would work for 3.8.5 but i couldnt find one that worked with my current version (i did find ones for v4 and earlier versions of v3 however)

        As far as i know the db tables do not have a prefix, we haven't done anything fancy with our SQL database on the forum - the vbul install was pretty strait forward.

        Comment

        • borbole
          Senior Member
          • Feb 2010
          • 3074
          • 4.0.0

          #5
          Sorry about that, the single quotes were not placed right.Try the following query. It works 100% as I just tried it at my test forum. I also update my previous post with the quotes fixed.

          Code:
          UPDATE post SET pagetext = replace(pagetext, 'http://www.youtube.com/watch?v=http', 'http');

          Comment

          • BentoSan
            New Member
            • Aug 2009
            • 11

            #6
            Hm didn't work for me, perhaps whoever originally setup the forums (it wasnt me) put a prefix like you mentioned before. Not quite sure how to check that but ill get to the bottom of this !

            Thanks for your help !

            Comment

            • BentoSan
              New Member
              • Aug 2009
              • 11

              #7
              Hmm actually in our config php file it says this

              $config['Database']['tableprefix'] = '';
              So i dont think we have a prefix, very weird !

              I do know the name or the database is called vbulletin though

              $config['Database']['dbname'] = 'vbulletin';
              Oh i completely forgot to mention, the first time i did a

              UPDATE post SET pagetext = replace(pagetext, 'http://www.youtube.com/watch?v=http', 'http')
              It edited a ton of entries, but when i went to look on the forums to see i these posts were actually edited, they were not.

              Edit: Lol ! ok i am a complete retard, i did not optimize the post table after editing it ! it seems ok now !

              Thanks for your help
              Last edited by BentoSan; Fri 2 Apr '10, 11:35am.

              Comment

              • borbole
                Senior Member
                • Feb 2010
                • 3074
                • 4.0.0

                #8
                Originally posted by BentoSan
                Hmm actually in our config php file it says this



                So i dont think we have a prefix, very weird !

                I do know the name or the database is called vbulletin though



                Oh i completely forgot to mention, the first time i did a



                It edited a ton of entries, but when i went to look on the forums to see i these posts were actually edited, they were not.

                Lol ! ok i am a complete retard, i did npt optimize the post table after editing it ! it seems ok now !

                Thanks for your help
                You are welcome. Glad to have been of help.

                Comment

                • Basketmen
                  Senior Member
                  • Oct 2006
                  • 195
                  • 3.6.x

                  #9
                  Originally posted by borbole
                  Sorry about that, the single quotes were not placed right.Try the following query. It works 100% as I just tried it at my test forum. I also update my previous post with the quotes fixed.

                  Code:
                  UPDATE post SET pagetext = replace(pagetext, 'http://www.youtube.com/watch?v=http', 'http');

                  Hi guys, i am sorry replying old thread

                  my forum url is changed, from with www ( http://www.domain.com ), to with out www ( http://domain.com )


                  i had try this

                  UPDATE post SET pagetext = replace(pagetext, 'www.domain.com', 'domain.com');

                  or

                  UPDATE post SET pagetext = replace(pagetext, 'http://www.domain.com', 'http://domain.com');



                  but its not works, its just says Affected Rows: 0 (3.0977s)


                  please help, how to change it in post table, there are a lot of them in post table
                  Build community is not easy, its a very long game

                  Comment

                  • BirdOPrey5
                    Senior Member
                    • Jul 2008
                    • 9613
                    • 5.6.3

                    #10
                    1) Are you sure there are any local links in your posts? I tried the query on my demo forum and it worked.

                    2) You should be using an htaccess to redirect www URLs to the non-WWW address anyway so the link doesn't matter.

                    Comment

                    Related Topics

                    Collapse

                    Working...