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

#1
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?
 

Jake Bunce

XenForo moderator
Staff member
#2
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
 
#3
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
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: