SQL to edit post.pagetext

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • 27iMac
    New Member
    • May 2012
    • 20
    • 4.2.X

    SQL to edit post.pagetext

    Hi

    First of all I hope this is posted in the appropriate forum. I have been searching for ages and cannot find something already discussed to help me.

    Like so many folks that have migrated from another forum software phpbb3, I have an issue with getting attachments to appear inline.

    I have walked through the issue I need to overcome and I think it boils down to an sql statement I need to run on the post table.

    phpbb3 inserts attachments inline using (example) [attachment=0]someimagename.jpg[/attachment] where 0=the attachment order within the post - but that seems irrelevent now

    When I import the attachments (successfully) into vBulletin 4.2 using Impex, the code from the phpbb3 post now shows something like [attachment=0:381rc3pg]someimagename.jpg[/attachment:381rc3pg]

    The vBulletin attachment table holds the attachment.filename "someimageattachment.jpg" and also the attachment.attachmentid "3712" and if I manually edit the post to show [ATTACH]3712[/ATTACH] the image appears inline, just how it did on phpbb3.

    The post table for the particular post containing the attachment holds post.pagetext with a story & the string [attachment=0:381rc3pg]someimagename.jpg[/attachment:381rc3pg]

    I believe I need to run a query on the post table to find all instances of [attachment???] & [/attachment???] & replace both with [ATTACH]&[/ATTACH] and secondly find the attachment.attachmentid from the attachment.filename and replace that accordingly where it appears in the post.pagetext.


    Hopefully some knowledgable folks here can advise me as to how I write the query.

    Looking forward to your response.

    Thanks very much
  • Andy
    Senior Member
    • Jan 2002
    • 5886
    • 4.1.x

    #2
    Originally posted by 27iMac
    When I import the attachments (successfully) into vBulletin 4.2 using Impex, the code from the phpbb3 post now shows something like [attachment=0:381rc3pg]someimagename.jpg[/attachment:381rc3pg]

    The vBulletin attachment table holds the attachment.filename "someimageattachment.jpg" and also the attachment.attachmentid "3712" and if I manually edit the post to show [ATTACH]3712[/ATTACH] the image appears inline, just how it did on phpbb3.
    How did you find the attachmentid 3712 ??

    The problem appears to be that the file name someimagename.jpg could have been used thousands of times. How is it associated with a particular attachmentid number?

    Comment

    • 27iMac
      New Member
      • May 2012
      • 20
      • 4.2.X

      #3
      Hi Andy

      in vB attachment table 3712 is attachment.attachmentid and in the same record the attachment.filename is whatever the physical filename actually is & yes the finename could be used time & time again.

      I cannot definitively answer your question but I believe there is a attachment.dateline association.

      Could your very astute observation mean that there is a 3rd dimension involved here & that is to first of all ascertain dulicates and isloate them from the main query. There are just 5,000 attachments.

      Thanks for your interest here.

      Comment

      • Andy
        Senior Member
        • Jan 2002
        • 5886
        • 4.1.x

        #4
        The third component would make sense.

        In the attachment table, the attachment.contentid is the post.postid. See if the attachment.dateline is the same value as the post.dateline.

        Comment

        • 27iMac
          New Member
          • May 2012
          • 20
          • 4.2.X

          #5
          Andy, indeed the attachment.contentid = post.postid but there appears no correlation between datelines in the example I am using perhaps due to post editing by it's author?

          So there is a way to identfy the attachment unique to the post.

          Do you have any suggestions on construction of the query?

          Thanks

          Comment

          • Andy
            Senior Member
            • Jan 2002
            • 5886
            • 4.1.x

            #6
            Oh that must be the third component. The attachment.contentid and attachment.filename would be unique enough to find the attachmentid.

            So here's the logic we need for a script to convert

            [attachment=0:381rc3pg]someimagename.jpg[/attachment:381rc3pg] --> [attach ]3712[/attach ]

            1) check post.pagetext for "[attachment"
            2) if found parse for the someimagename.jpg
            3) get post.postid
            4) find attachment.attachmentid based on post.postid = attachment.contentid = attachment.filename
            5) update pagetext with [attach ]attachment.attachmentid[/attach ]

            Comment

            • 27iMac
              New Member
              • May 2012
              • 20
              • 4.2.X

              #7
              Am I right in thinking that the following could be written as an sql query?

              1) check post.pagetext for "[attachment"
              2) if found parse for the someimagename.jpg
              3) get post.postid
              4) find attachment.attachmentid based on post.postid = attachment.contentid = attachment.filename
              5) update pagetext with [attach ]attachment.attachmentid[/attach ]

              Andy, you seem to have grasped this.

              What do I do now

              Thank you so much for keeping this rolling along.

              Comment

              • Andy
                Senior Member
                • Jan 2002
                • 5886
                • 4.1.x

                #8
                No this would require a script to be written to perform the steps I outlined.

                Comment

                • 27iMac
                  New Member
                  • May 2012
                  • 20
                  • 4.2.X

                  #9
                  Andy, by who ? how do I get thatto happen?

                  Comment

                  • Andy
                    Senior Member
                    • Jan 2002
                    • 5886
                    • 4.1.x

                    #10
                    You can ask for help writing the script over at vbulletin.org. Or you can search for one on Google, there might be something out there already. Or you can pay to have someone write it for you.

                    Comment

                    • 27iMac
                      New Member
                      • May 2012
                      • 20
                      • 4.2.X

                      #11
                      Ok, so there are 3 options, the first is probably why I am on this forum as when I viewed the .org forum there seems to be nowhere to ask for that.

                      I have been searching for weeks and looking for solutions and drawn blanks.

                      What would this sort of job cost? - (pm's welcome)

                      Thank you Andy

                      Comment

                      • Andy
                        Senior Member
                        • Jan 2002
                        • 5886
                        • 4.1.x

                        #12
                        I suggest you start here and request for a quote.

                        This is a discussion forum powered by vBulletin. To find out about vBulletin, go to http://www.vbulletin.com/ .

                        Comment

                        • Lats
                          Senior Member
                          • Mar 2002
                          • 3671

                          #13
                          I ran a google for impex phpbb3 attachments for this site and it retuned quite a few results, such as:




                          Hope that gets you going in the right direction.
                          Lats...

                          Comment

                          • 27iMac
                            New Member
                            • May 2012
                            • 20
                            • 4.2.X

                            #14
                            Originally posted by Lats
                            I ran a google for impex phpbb3 attachments for this site and it retuned quite a few results, such as:




                            Hope that gets you going in the right direction.
                            Thanks for your interest Lats but I believe I have been through that thread (and many many others ) and arrived at the broth on the top / wood for the trees situation.

                            I had previously raised a ticcket with vBulletin and they helpfully ran through the import routine with my source & target db's & confirmed that I was doing the right things and also confirmed that Impex would need an amendment.

                            The status of my data after import is acceptable, except for the given & non showstopping password issue, & the attachment issue covered above in this thread.

                            Cheers

                            Comment

                            • Andy
                              Senior Member
                              • Jan 2002
                              • 5886
                              • 4.1.x

                              #15
                              Hi 27iMac,

                              It was good working with you and I'm glad the scripts I wrote were able to solve the attachment problems converting your phpbb forum to vbulletin.

                              Comment

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