Add topics through SQL import?

Converting from other board software? Good decision! Need help? Have a question about a convertor? Wish to offer a convertor package? Post here.
Post Reply
Shohreh
Registered User
Posts: 48
Joined: Tue Jun 17, 2014 11:32 am

Add topics through SQL import?

Post by Shohreh »

Hello,

To avoid losing archives before turning it off, I'd like to import the contents of a blog into an existing phpBB3.3 forum so as to only keep a single application on the server.

What tables (phpbb_topics + phpbb_posts?) should I edit, and within those, what columns must be included — and what can be left empty?

Using the same username for all blog posts is fine.

Once imported, the blog will resume as new articles in the forum, and the blog will be removed.

Thank you.
User avatar
warmweer
Jr. Extension Validator
Posts: 9496
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Bel ... gium
Contact:

Re: Add topics through SQL import?

Post by warmweer »

How many blog entries are we talking about?
I suspect it will be easier just to copy/paste and edit a bit here and there, rather than creating SQLs to import into phpBB.
Spelling is freeware, which means you can use it for free.
On the other hand, it is not open source, which means you cannot change it or publish it in a modified form.


Time flies like an arrow, but fruit flies like a banana.
Shohreh
Registered User
Posts: 48
Joined: Tue Jun 17, 2014 11:32 am

Re: Add topics through SQL import?

Post by Shohreh »

There are 6,660 articles.
Shohreh
Registered User
Posts: 48
Joined: Tue Jun 17, 2014 11:32 am

Re: Add topics through SQL import?

Post by Shohreh »

Using the following, I added a new post to an existing topic… but it doesn't show in the forum.

Code: Select all

INSERT INTO `phpbb_posts` (topic_id,forum_id,post_time,post_username,post_text) VALUES (2,4,1674484430,"DonaldDuck","<t>Inserted text</t>");
Should I fill every column that has no default value?

Code: Select all

MariaDB [phpbb]> describe phpbb_topics;
+---------------------------+-----------------------+------+-----+---------+----------------+
| Field                     | Type                  | Null | Key | Default | Extra          |
+---------------------------+-----------------------+------+-----+---------+----------------+
| topic_id                  | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| forum_id                  | mediumint(8) unsigned | NO   | MUL | 0       |                |
| icon_id                   | mediumint(8) unsigned | NO   |     | 0       |                |
| topic_attachment          | tinyint(1) unsigned   | NO   |     | 0       |                |
| topic_reported            | tinyint(1) unsigned   | NO   |     | 0       |                |
| topic_title               | varchar(255)          | NO   |     |         |                |
| topic_poster              | int(10) unsigned      | NO   |     | 0       |                |
| topic_time                | int(11) unsigned      | NO   |     | 0       |                |
| topic_time_limit          | int(11) unsigned      | NO   |     | 0       |                |
| topic_views               | mediumint(8) unsigned | NO   |     | 0       |                |
| topic_status              | tinyint(3)            | NO   |     | 0       |                |
| topic_type                | tinyint(3)            | NO   |     | 0       |                |
| topic_first_post_id       | int(10) unsigned      | NO   |     | 0       |                |
| topic_first_poster_name   | varchar(255)          | NO   |     |         |                |
| topic_first_poster_colour | varchar(6)            | NO   |     |         |                |
| topic_last_post_id        | int(10) unsigned      | NO   |     | 0       |                |
| topic_last_poster_id      | int(10) unsigned      | NO   |     | 0       |                |
| topic_last_poster_name    | varchar(255)          | NO   |     |         |                |
| topic_last_poster_colour  | varchar(6)            | NO   |     |         |                |
| topic_last_post_subject   | varchar(255)          | NO   |     |         |                |
| topic_last_post_time      | int(11) unsigned      | NO   | MUL | 0       |                |
| topic_last_view_time      | int(11) unsigned      | NO   |     | 0       |                |
| topic_moved_id            | int(10) unsigned      | NO   |     | 0       |                |
| topic_bumped              | tinyint(1) unsigned   | NO   |     | 0       |                |
| topic_bumper              | mediumint(8) unsigned | NO   |     | 0       |                |
| poll_title                | varchar(255)          | NO   |     |         |                |
| poll_start                | int(11) unsigned      | NO   |     | 0       |                |
| poll_length               | int(11) unsigned      | NO   |     | 0       |                |
| poll_max_options          | tinyint(4)            | NO   |     | 1       |                |
| poll_last_vote            | int(11) unsigned      | NO   |     | 0       |                |
| poll_vote_change          | tinyint(1) unsigned   | NO   |     | 0       |                |
| topic_visibility          | tinyint(3)            | NO   | MUL | 0       |                |
| topic_delete_time         | int(11) unsigned      | NO   |     | 0       |                |
| topic_delete_reason       | varchar(255)          | NO   |     |         |                |
| topic_delete_user         | int(10) unsigned      | NO   |     | 0       |                |
| topic_posts_approved      | mediumint(8) unsigned | NO   |     | 0       |                |
| topic_posts_unapproved    | mediumint(8) unsigned | NO   |     | 0       |                |
| topic_posts_softdeleted   | mediumint(8) unsigned | NO   |     | 0       |                |
+---------------------------+-----------------------+------+-----+---------+----------------+

MariaDB [phpbb]> describe phpbb_posts;
+--------------------+-----------------------+------+-----+---------+----------------+
| Field              | Type                  | Null | Key | Default | Extra          |
+--------------------+-----------------------+------+-----+---------+----------------+
| post_id            | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| topic_id           | int(10) unsigned      | NO   | MUL | 0       |                |
| forum_id           | mediumint(8) unsigned | NO   | MUL | 0       |                |
| poster_id          | int(10) unsigned      | NO   | MUL | 0       |                |
| icon_id            | mediumint(8) unsigned | NO   |     | 0       |                |
| poster_ip          | varchar(40)           | NO   | MUL |         |                |
| post_time          | int(11) unsigned      | NO   |     | 0       |                |
| post_reported      | tinyint(1) unsigned   | NO   |     | 0       |                |
| enable_bbcode      | tinyint(1) unsigned   | NO   |     | 1       |                |
| enable_smilies     | tinyint(1) unsigned   | NO   |     | 1       |                |
| enable_magic_url   | tinyint(1) unsigned   | NO   |     | 1       |                |
| enable_sig         | tinyint(1) unsigned   | NO   |     | 1       |                |
| post_username      | varchar(255)          | NO   | MUL |         |                |
| post_subject       | varchar(255)          | NO   |     |         |                |
| post_text          | mediumtext            | NO   |     | NULL    |                |
| post_checksum      | varchar(32)           | NO   |     |         |                |
| post_attachment    | tinyint(1) unsigned   | NO   |     | 0       |                |
| bbcode_bitfield    | varchar(255)          | NO   |     |         |                |
| bbcode_uid         | varchar(8)            | NO   |     |         |                |
| post_postcount     | tinyint(1) unsigned   | NO   |     | 1       |                |
| post_edit_time     | int(11) unsigned      | NO   |     | 0       |                |
| post_edit_reason   | varchar(255)          | NO   |     |         |                |
| post_edit_user     | int(10) unsigned      | NO   |     | 0       |                |
| post_edit_count    | smallint(4) unsigned  | NO   |     | 0       |                |
| post_edit_locked   | tinyint(1) unsigned   | NO   |     | 0       |                |
| post_visibility    | tinyint(3)            | NO   | MUL | 0       |                |
| post_delete_time   | int(11) unsigned      | NO   |     | 0       |                |
| post_delete_reason | varchar(255)          | NO   |     |         |                |
| post_delete_user   | int(10) unsigned      | NO   |     | 0       |                |
+--------------------+-----------------------+------+-----+---------+----------------+
User avatar
janus_zonstraal
Registered User
Posts: 5929
Joined: Sat Aug 30, 2014 1:30 pm

Re: Add topics through SQL import?

Post by janus_zonstraal »

You have to put it also in a couple of other tables
topics and users, therefor we already suggest
I suspect it will be easier just to copy/paste and edit a bit here and there, rather than creating SQLs to import into phpBB.
Sorry! My English is bat ;) !!!
Shohreh
Registered User
Posts: 48
Joined: Tue Jun 17, 2014 11:32 am

Re: Add topics through SQL import?

Post by Shohreh »

I know, but I've never done web automation, so wanted to make sure it couldn't be done through SQL instead.

--
Edit: FWIW, here are the tables that were affected after adding a new topic:

Code: Select all

INSERT INTO `phpbb_config`
INSERT INTO `phpbb_forums`
INSERT INTO `phpbb_posts`
INSERT INTO `phpbb_search_wordlist`
INSERT INTO `phpbb_search_wordmatch`
INSERT INTO `phpbb_sessions`
INSERT INTO `phpbb_topics`
INSERT INTO `phpbb_topics_posted`
INSERT INTO `phpbb_topics_track`
INSERT INTO `phpbb_users`
Post Reply

Return to “[3.3.x] Convertors”