1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

XF 1.4 Is this a right method to import the data ?

Discussion in 'Installation, Upgrade, and Import Support' started by sadiq6210, Aug 15, 2015.

  1. sadiq6210

    sadiq6210 Active Member

    Hi,

    I need to import the data from vBulletin addon into xenforo showcase addon
    I don't have importer, therefore I tried to transfer the data from vBulletin database called "vb4final" to xenforo database called "xenforum" from phpMyAdmin using this query:

    Importing items query (from table "adv_links" to "xf_nflj_showcase_item")
    Code:
    INSERT INTO xenforum.xf_nflj_showcase_item(
    item_id,
    item_name,
    description,
    category_id,
    username,
    user_id,
    date_added,
    last_update,
    item_view_count
    )
    SELECT linkid, name, description, catid, username, userid, dateline, lastupdated, views
    FROM vb4final.adv_links
    Importing comments query (from table "adv_links_posts" to "xf_nflj_showcase_comment")
    Code:
    INSERT INTO xenforum.xf_nflj_showcase_comment(
    comment_id_id,
    item_id,
    message,
    user_id,
    username,
    comment_date
    )
    SELECT postid, linkid, pagetext, userid, username, dateline
    FROM vb4final.adv_links_posts
    As a result, all items and comments imported correctly with same ID's and in same location.

    Q1: Is this a right method for importing? I mean why we need to program a long importer if it is possible to easily copy the data from column 1 in database 1 to column 2 in database 2?

    Q2: I am facing a problem after done the importing, vBulletin database was (latin1) and xenforo is (UTF8). Therefore, the text in some column is not showing correctly, how I can solve this?
    In xenforo importer we can use a command like this "'title' => $this->_convertToUtf8($title, true)," but is it possible to convert to UTF8 through SQL manual query?
     
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Q1) That can certainly work. I do direct queries like that for some imports. Usually the reason "why not" is if there is denormalized data that needs to be rebuilt. I am not familiar with that addon so I can't speak to this specific case.

    Q2) You can try CONVERT(). Example using one of your queries:

    Code:
    INSERT INTO xenforum.xf_nflj_showcase_comment(
    comment_id_id,
    item_id,
    message,
    user_id,
    username,
    comment_date
    )
    SELECT postid, linkid, CONVERT(pagetext USING utf8), userid, username, dateline
    FROM vb4final.adv_links_posts
    
     
    sadiq6210 likes this.
  3. sadiq6210

    sadiq6210 Active Member

    Thanks Jake
    The addon can rebuild all the data correctly, so I only need to solve the issue regarding utf8

    I empty the "comments" table then I tried to use your code but same result, all the data imported but still I am facing the same issue with utf8
    The text is showing like this:
    dbe.png



    Even in website, all comments is unreadable
    upload_2015-8-17_12-37-10.png


    As I said, in xenforo importer script we need only to use this
    Code:
    'message' => $this->_convertToUtf8($category['pagetext'], true),
    But I am not able to do it using SQL query
     
    Last edited: Aug 17, 2015
  4. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    You might try latin1, or a binary cast. The 'checked' answer in this thread has an example of both functions, and obviously they can be nested as needed:

    MySQL - Convert latin1 characters on a UTF8 table into UTF8

    The devil is in the details here. It depends on the raw source data and perhaps the connection collation. But using some combination of these functions you can probably find what works.
     

Share This Page