Announcement

Collapse
No announcement yet.

SQL Query Replace Text Issue

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

  • 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:
    http://www.youtube.com/watch?v=A5Yj6ImVc1k

    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, 2:02pm.

  • #2
    24 hour bump action !

    Comment


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

      http://www.vbulletin.org/forum/showthread.php?t=239234

      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, 7:25am.

      Comment


      • #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


        • #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


          • #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


            • #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, 12:35pm.

              Comment


              • #8
                Originally posted by BentoSan View Post
                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


                • #9
                  Originally posted by borbole View Post
                  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


                  • #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

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