Skip to:
Content
Pages
Categories
Search
Top
Bottom

Import/mapping of custom MySQL forum tables


  • JustMontessori
    Participant

    @justmontessori

    I’ve made the decision to develop my new websites using WordPress. My goal is to eventually re-write my PHP code as a WordPress plugin, but, until then I need to use what’s available, and bbPress seems like a good option. I need to import/map my current tables to work with bbPRess, but not sure how?

    Here is the existing schema along with one parent record, and one child record:

    Forum_1962 (
    Forum mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    Forum_Title varchar(200) NOT NULL DEFAULT ”,
    Forum_Author varchar(100) NOT NULL DEFAULT ”,
    Forum_Author_Email varchar(100) NOT NULL DEFAULT ”,
    Forum_Text text NOT NULL,
    Forum_Parent mediumint(8) unsigned NOT NULL DEFAULT ‘0’,
    Forum_Ancestor mediumint(8) unsigned NOT NULL DEFAULT ‘0’,
    Forum_Category smallint(5) unsigned NOT NULL DEFAULT ‘0’,
    Forum_Approved enum(‘0′,’1’) NOT NULL DEFAULT ‘1’,
    Forum_Entered_by varchar(10) NOT NULL DEFAULT ‘0’,
    Forum_Date_Created datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
    Forum_Last_Changed timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (Forum),
    KEY Forum_Parent (Forum_Parent),
    KEY Forum_Category (Forum_Category),
    KEY Forum_Approved (Forum_Approved),
    FULLTEXT KEY Forum_Title (Forum_Title),
    FULLTEXT KEY Forum_Text (Forum_Text)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

    Parent thread
    =============
    (19261, ‘Can I rent an RV?’, ‘BillB’, ‘bill@web-crafter.net’, ‘I\’d like to give RVing a try, but there\’s no way I\’m going to plunk down thousands of $$ to buy an RV. Is it possible to rent an RV like you\’d a car?’, 0, 19261, 1962, ‘1’, ‘rvcn’, ‘2002-12-12 13:52:29’, ‘2002-12-12 13:53:25’);

    Ancestor thread
    ===============
    (19262, ‘Re: Can I rent an RV?’, ‘Mark’, ‘mark@ittelligent.com’, ‘No problem. Just check your yellow pages for a rental agency in your area. I know there are a few national outfits too. Cruise America and El Monte come to mind off the top of my head.’, 19261, 19261, 1962, ‘1’, ”, ‘2002-12-12 13:59:42’, ‘2002-12-12 13:59:42’);

    You’ll notice that there are fields for both parent and ancestor, which allows for infinite sub-topic threads (those replies to replies under one parent). Also, I the author name and email are also stored in this table, and not tied to a separate user table (so will need to address that). Each MySQL dump is between 1MB and 3MB.

    Need any more information? Please advise.

    My goal is one of two options:

    1. Import/Map existing table dumps in several websites to WordPress and bbPress using above forum schema as source, OR
    2. Convert my existing PHP forum code to be compatible with WordPress and forget about bbPress altogether if I can’t easily map the table (I don’t know WordPress enough to even think about doing this myself, even though the code is simple and fast, and will ne advice on someone who can)

    I am doing this so I can better control the mobile templates and have a more robust way of controlling my websites. I need to do this for about 6 websites, and would like the method to do this myself after the first time.

    Thanks in advance,

    Lisa

Viewing 7 replies - 1 through 7 (of 7 total)

  • Robin W
    Moderator

    @robin-w

    writing an entire forum code for wordpress as a solo project seems to be a significant challenge, so using bbpress and amending that would seem to be a good strategy.

    ok, since you seem to be very php savvy, suggest you go to

    https://codex.bbpress.org/import-forums/

    and simply amend one of the templates there for your forum.

    You may need to extract the users out to a separate table, or you may be able to treat these users as “anonymous users”, I’d suggest first though I’d look at mapping some of the fields for the content only as that should not take much time to get up and running.

    First you want to do this with bbPress 2.6 ‘alpha’ rather than the current 2.5.6 release as there are significant improvements to the importers in the upcoming version.

    You can get your hands on 2.6 alpha via https://wordpress.org/plugins/bbpress/developers/ and it is listed under “Other Versions” as “Development Version”
    Or the direct download link https://downloads.wordpress.org/plugin/bbpress.zip

    Next you want to make a copy of Example.php from the includes/admin/converters folder of the plugin to MyConverter.php and in that file replace any instance of Example with MyConverter (this is case sensitive to be mindful of that)

    Next up start mapping the fields from your schema to the fields in MyConverter.php

    In the following screenshot I’ve mapped drawn lines to the easily concluded fields, any field mappings that you do not use you can simply delete, ideally using as many as you can will ensure the best bbPress experience for your data though.

    Take a stab at doing the above for the rest of your database, topics, replies etc and get back to us.

    If you need a place to host code snippets, use GitHub Gists: http://gist.github.com


    JustMontessori
    Participant

    @justmontessori

    Thank you, Stephen, for taking the time to create a mapping graphic. Much appreciated!

    One question: In my Forum code, the Forum_Parent, which is “0” (zero) identifies it as a thread starter, and because the Forum_Ancestor is not “0” (zero), lists it as a reply, underneath the parent, and multiples are ordered by date DESC. How does bbPress handle this? If I understand the methodology, I’ll do better at the mapping. Do I simply use the Forum_Parent for ALL?

    Hope I am clear in my question…

    Lisa

    Hmmm…. I’m assuming in that case then you have no other parts (or major parts at least) missing from the DB schema you posted in the original post? e.g. No topic_title table listing the topics separately.

    If that is the case you’d now want to ignore those mappings I posted, rather remap them to the the topic and reply sections instead of the forums section.

    This is now where we add some limiting SQL expressions to separate your topics from replies.

    In the Example.php file at the end of the topics section you’ll see:

    
    // Setup any table joins needed for the topic section
    $this->field_map[] = array(
    	'from_tablename'  => 'replies_table',
    	'from_fieldname'  => 'the_topic_id',
    	'join_tablename'  => 'topics_table',
    	'join_type'       => 'INNER',
    	'join_expression' => 'USING replies_table.the_topic_id = topics_table.the_topic_id',
    	'from_expression' => 'WHERE forums_table.the_topic_id = 0',
    	'to_type'         => 'topic'
    );
    

    We’ll modify that to the following:

    
    // Setup any table joins needed for the topic section
    $this->field_map[] = array(
    	'from_tablename'  => 'forum_table',
    	'from_expression' => 'WHERE forums_table.Forum_Parent = 0',
    	'to_type'         => 'topic'
    );
    

    Similarly at the end of the replies section, we change that join expression to:

    
    // Setup any table joins needed for the topic section
    $this->field_map[] = array(
    	'from_tablename'  => 'forum_table',
    	'from_expression' => 'WHERE forums_table.Forum_Parent != 0',
    	'to_type'         => 'reply'
    );
    

    Now assuming I’ve got the logic correct in understanding your Forum_Parent/Forum_Ancestor what equals 0 and what does not equal 0 then we should be good to go 🙂

    So the topics join expression is limiting the queries for your ‘thread starter’ topics, then in the reply section expression we ignore those previously ignored topics already imported and are assuming the remaining entries in the table are replies to those topics. The Forum_Ancestor of each reply should then point to the “parent topic ID’ mapping.

    I think that’s it, one thing you’ll notice is that we also are not actually creating any forums to import the topics into, all the topics will import without being allocated to a forum, this can be bulk updated later, actually we can set a default forum for topics and relies, I’ll get back to you on that bit of code that I can’t remember of the top of my head right this second 😉


    JustMontessori
    Participant

    @justmontessori

    I may be getting in over my abilities now, but will try to digest your last post. Thanks.

      RECORD ONE ABOVE:

    To clarify about Forum_Parent and Forum_Ancestor, notice in the original post, the two records. The first is the parent with a record ID of 19261. In that first record shown, notice the Forum_Parent=0, and the Forum_Ancestor=19261 (since it is the top of the thread).

      RECORD TWO ABOVE:

    The second record is a child of the first (a reply), and you’ll notice it inherits the first records main ID as the Forum_Parent=19261, and is also part of the same family, so Forum_Ancestor=19261 also (If there were a reply to that reply, the Forum_Parent would change, but the Forum_Ancestor would stay the same since it needs to be grouped in the same “family.”

    Not as difficult as I probably made it in the explanation, but nothing changed or was missing from the original post.

    Hope this clarifies a bit more… If not, I apologize.

    Thanks Lisa, it all makes sense, I doubt I made my explanation any better 😉

    I’ll take a closer look a little later but I think I’ve a reasonable solution in mind 🙂


    JustMontessori
    Participant

    @justmontessori

    Hello, Stephen,

    In your last post you indicated a reasonable solution. Was I supposed to provide more info? I know you’re probably busy, but was wondering what that solution was? <nervous smile>

    Also, if you can write a script, perhaps I can pay you? I have several forums just like this to convert and a script I can run for importing would be a great help, especially since I have so many other things to accomplish. Right now I am stuck.

    At your mercy, with gratitude,

    Lisa

Viewing 7 replies - 1 through 7 (of 7 total)
  • You must be logged in to reply to this topic.
Skip to toolbar