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

[Sadiq6210]

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")

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")

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]

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:

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]

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:

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:




Even in website, all comments is unreadable



As I said, in xenforo importer script we need only to use this

'message' => $this->_convertToUtf8($category['pagetext'], true),

But I am not able to do it using SQL query

 

[Jake Bunce]

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.