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

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?
 

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

Active 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
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:

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.
 
Top