Insert old Posts into a new Forum's Posts

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Get Involved
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
LouTuss
Registered User
Posts: 6
Joined: Thu Mar 24, 2011 12:34 pm

Insert old Posts into a new Forum's Posts

Post by LouTuss » Thu Mar 24, 2011 12:48 pm

I hunted for days to find a way to do this and then realised that some php would do it for me.

This will extract and insert posts for a specific user

(in this example, the user was id #50

Scenario:
You have a backup .sql of a forum
You need to retrieve some posts, and re-insert them in your current mysql table

Solution:
Using phpmyadmin or anything of your choosing, create a new temporary mysql database.
Upload and install a temporary phpBB forum in another folder, which will create empty tables in your new temporary mysql database.
Populate the tables using phpmyadmin and uploading the backup .sql

LouTuss
Registered User
Posts: 6
Joined: Thu Mar 24, 2011 12:34 pm

2nd post follows the first one

Post by LouTuss » Thu Mar 24, 2011 12:55 pm

I was going to make it tide by doing several posts
Now the moderator is going to have to merge these okay ?

Ok.

Code: Select all

<?php

error_reporting(E_ALL);

$db = mysql_connect("[DB HOST]", "[DB USER]", "[DB PASSWORD]");
mysql_select_db("[FORUM DB]",$db);

// we assume phpbb_posts
// this is for user #50 but it could be any id

$query = "SELECT * FROM phpbb_posts where poster_id='50'";
$posts_result = mysql_query( $query, $db );
echo "<font face=verdana, arial, helvetica, size=2><br>There are " .mysql_num_rows($posts_result). " posts using Backups for User50<p>";

$posts_array = array();
while($post_row = mysql_fetch_assoc($posts_result)) {
	$post_id = $post_row['post_id'];
	$posts_array[$post_id]["topic_id"] = $post_row["topic_id"];
	$posts_array[$post_id]["forum_id"] = $post_row["forum_id"];
	$posts_array[$post_id]["poster_id"] = $post_row["poster_id"];
	$posts_array[$post_id]["icon_id"] = $post_row["icon_id"];
	$posts_array[$post_id]["poster_ip"] = $post_row["poster_ip"];
	$posts_array[$post_id]["post_time"] = $post_row["post_time"];
	$posts_array[$post_id]["post_approved"] = $post_row["post_approved"];
	$posts_array[$post_id]["post_reported"] = $post_row["post_reported"];
	$posts_array[$post_id]["enable_bbcode"] = $post_row["enable_bbcode"];
	$posts_array[$post_id]["enable_smilies"] = $post_row["enable_smilies"];
	$posts_array[$post_id]["enable_magic_url"] = $post_row["enable_magic_url"];
	$posts_array[$post_id]["enable_sig"] = $post_row["enable_sig"];
	$posts_array[$post_id]["post_username"] = $post_row["post_username"];
	$posts_array[$post_id]["post_subject"] = $post_row["post_subject"];
	$posts_array[$post_id]["post_text"] = $post_row["post_text"];
	$posts_array[$post_id]["post_checksum"] = $post_row["post_checksum"];
	$posts_array[$post_id]["post_attachment"] = $post_row["post_attachment"];
	$posts_array[$post_id]["bbcode_bitfield"] = $post_row["bbcode_bitfield"];
	$posts_array[$post_id]["bbcode_uid"] = $post_row["bbcode_uid"];
	$posts_array[$post_id]["post_postcount"] = $post_row["post_postcount"];
	$posts_array[$post_id]["post_edit_time"] = $post_row["post_edit_time"];
	$posts_array[$post_id]["post_edit_reason"] = $post_row["post_edit_reason"];
	$posts_array[$post_id]["post_edit_user"] = $post_row["post_edit_user"];
	$posts_array[$post_id]["post_edit_count"] = $post_row["post_edit_count"];
	$posts_array[$post_id]["post_edit_locked"] = $post_row["post_edit_locked"];
}
mysql_close($db);

$insert_query = "INSERT INTO phpbb_posts (`topic_id`,`forum_id`,`poster_id`,`icon_id`,`poster_ip`,`post_time`,`post_approved`,`post_reported`,`enable_bbcode`,`enable_smilies`,`enable_magic_url`,`enable_sig`,`post_username`,`post_subject`,`post_text`,`post_checksum`,`post_attachment`,`bbcode_bitfield`,`bbcode_uid`,`post_postcount`,`post_edit_time`,`post_edit_reason`,`post_edit_user`,`post_edit_count`,`post_edit_locked`) VALUES ";

foreach($posts_array as $post_id => $post_data) {
	$insert_post = "(";
	
	$i = 1;
	$count = count($post_data);
	foreach($post_data as $post_key => $post_val) {
		$post_val = str_replace(array('{','}','"'), array('','','\"'), $post_val);
		$insert_post .= '"'.$post_val.'"';
		if($i < $count) {
			$insert_post .= ',';
		}
		$i++;
	}
	
	$insert_post .= "),";
	$insert_query .= $insert_post;
}
$insert_query = substr($insert_query, 0, -1);
$insert_query .= ';';

print_r($insert_query);

// over to the New Forum

$dbnewz = mysql_connect("[DB HOST]", "[DB LOGIN]", "[DB PASSWORD]");
mysql_select_db("[FORUM DB]",$dbnewz);

$querynewz = "SELECT * FROM phpbb_posts";
$resultnewz = mysql_query( $querynewz, $dbnewz );

//INSERT THE POSTS

$insert = mysql_query($insert_query,$dbnewz) or die("$insert_query failed because ".mysql_error()); 

mysql_close($dbnewz);


?>

LouTuss
Registered User
Posts: 6
Joined: Thu Mar 24, 2011 12:34 pm

3rd post follows 2nd post

Post by LouTuss » Thu Mar 24, 2011 1:01 pm

That will update the current posts table

Now using SQLion or any other MySQL access software, go down to the table "phpbb_users" and across to the field `user_posts` after identifying the post_id number

Change the number in 1user_posts`, adding the number of posts that you can see back at the temporary forum database

Destroy the temporary MySQL tables and the temporary forum

Pony99CA
Registered User
Posts: 4783
Joined: Thu Sep 30, 2004 3:13 pm
Location: Hollister, CA
Name: Steve
Contact:

Re: Insert old Posts into a new Forum's Posts

Post by Pony99CA » Thu Mar 24, 2011 4:59 pm

LouTuss wrote: Using phpmyadmin or anything of your choosing, create a new temporary mysql database.
Upload and install a temporary phpBB forum in another folder, which will create empty tables in your new temporary mysql database.
Populate the tables using phpmyadmin and uploading the backup .sql
I don't think that you have to install another copy of phpBB. As the backup file contains all of the necessary information to set up the database, you can just use phpMyAdmin to read the backup file into the new database.

Steve
Silicon Valley Pocket PC (http://www.svpocketpc.com)
Creator of manage_bots and spoof_user (ask me)
Need hosting for a small forum with full cPanel & MySQL access? Contact me or PM me.

LouTuss
Registered User
Posts: 6
Joined: Thu Mar 24, 2011 12:34 pm

Re: Insert old Posts into a new Forum's Posts

Post by LouTuss » Fri Mar 25, 2011 12:11 am

yes ~ it was the fastest way I could think of to create the entire table structure without any mistakes though, and also gave me a spare forum structure to test mods on.

:)

Locked

Return to “[3.0.x] Support Forum”