Previous Thread
Next Thread
Print Thread
Rate Thread
Joined: Jun 2002
Posts: 303
Enthusiast
Enthusiast
Joined: Jun 2002
Posts: 303
I would like to see if anyone is interested in giving me a quote for a script to port data from another message board over to UBBT. Please take a look at https://ubbdev.com/threads/php/...iew=expanded&sb=5&o=&fpart=1 for a look at the db structure of the one porting from.

I wanted to do it myself, but my knowledge of php is just not good enough nor do I have the time.

Thanks for looking this over.

Sponsored Links
Joined: Jun 2002
Posts: 375
Enthusiast
Enthusiast
Offline
Joined: Jun 2002
Posts: 375
Is that all of the data you would want converted to UBBT, or are there other tables needing to be rolled into the UBBT database upon board conversion?

Joined: Jun 2002
Posts: 303
Enthusiast
Enthusiast
Joined: Jun 2002
Posts: 303
There are other tables for the message board itself, such as the board setup, but they are not applicable to threads. There are approximately 5500 or so posts. The following is a quick summary of what each field in the table is.

[]

id - autoincrements - post number
board - the board # it belongs to, which in this case all are 1
thread - thread number
parent - 0 if thread starter, else id. Let me double check this
author -
email -
subject -
body -
datestamp timestamp(14) -
ip -
host -
vipflag - not applicable in threads
link - URL which is appended at the end of the body with <br>
linkname - description of the above named URL
image - URL of a picture to be displayed in post. appended to end of body with <br>
locked -
sticky -
email_replies - notifies poster of replies by email
hide_email - hides or shows email in post
counter - number of views. This is different than threads in that each post has an individual post view count opposed to a thread count like threads. Not that big of deal if it not included.




I can send a portion of the dump if you need a closer look on how it all gels. Thanks for looking into it.

Mark

Joined: May 1999
Posts: 1,715
Addict
Addict
Joined: May 1999
Posts: 1,715
This shouldn't be too hard to do, it seems mostly to be a matter of transferring the data into the threads db. The post counts could be merge into one thread count I guess. It would also be easiest to add the link and image into the body of the post, since you would have to alter the threads db otherwise (and hack som of the scripts).
Another question is what to do with the author and email, should users be created for each poster, or should all posts be added as anonymous posts and the email put into the body (or removed).

I created another import script some time ago, which I think is working. It could be used as a base for your conversion, since the part that adds to threads will basicly be the same. You can find that script here.

Joined: Jun 2002
Posts: 303
Enthusiast
Enthusiast
Joined: Jun 2002
Posts: 303
I've been using PostNuke, which I will be deleting eventually once I get some things worked out. There are 218 users which include the Anonymous and myself as Fishtails.

My message board is not directly connected to the user base, in that the message board did not use anything from that table. However, the majority of the posters would post a message using the same name they were signed up with, and then the script set a cookie.

Anyway, what would be great would be for the conversion script to first populate the UBBT users with my postnuke users, with the exception of Anonymous and Fishtails of course since I would have had those when I first did the install. All users would be in I guess the group -4- the normal users. Just the bare minimum necesary for UBBT is all I need. The users can update their profiles and settings later. ( Actually, I guess I could do this part fairly easy with phpmyadmin, but I need to know what fields in table users are an absolute necesity in UBBT, and if there are any other tables which may need something. )

Then, when the script begins with populating the db with messages it would check the author against the UBBT users and assign it accordingly. Any post which does not match a user would then be assigned 'guest' or anonymous. One thing to note, my forums will be set up to allow nonregistered guests to post with a username of their choice if it is not a registered name. So, with that in mind could these unmatched authors stay intact?

Thanks.

Sponsored Links
Joined: May 1999
Posts: 1,715
Addict
Addict
Joined: May 1999
Posts: 1,715
Importing users from postnuke shouldn't be that hard either, the only problem I can think of straight away is that the password might be encrypted in a different way.
You should add username, password, email at least and it is probably good to set the same default values as adduser.php. But you will get that if you use the adduser code from that script to add all the users so that shouldn't be a problem.

As long as the names are equal in the posts as in postnuke all should be able to be imported for them. The others can be imported as if they have been written anomyously with the name of their choice as you describe.

c0bra #310393 09/24/2002 5:51 PM
Joined: Jun 2002
Posts: 303
Enthusiast
Enthusiast
Joined: Jun 2002
Posts: 303
I have successfully written a script to import my postnuke member data into threads, with the exception of the date. I tested it on my home pc and just used the current date for registration purposes. The registration date in the postnuke mysql db is in this format - Nov 10, 2000

Is there a way to reformat this to use threads format?

$date = time(); // this is my current time entered into my db
$row[user_regdate] // this is the array of users registration date.

One thing to note. When I install threads, it will obviously register me with the current date. Will registration dates prior to this have any adverse effect on the threads scripts? This question would also be posed for the messages (discussed below) as well.

I'm making good headway on writing my forum messages conversion script as well, however, my date posted for messages in my forums that I want converted take on this format - 20010307235235
I took a look at the script which processes the post and it looks like it uses the mysql function NOW() when inserting to the db. How do I reformat this for threads?

Thanks for any help.

#310394 09/24/2002 6:20 PM
Joined: May 1999
Posts: 1,715
Addict
Addict
Joined: May 1999
Posts: 1,715
You'll have to manually parse the two different date formats and put the information into this php function:
mktime ( int hour, int minute, int second, int month, int day, int year [, int is_dst])

You will then get the unix timestamp which is how dates are stored in threads.

The second date format could be parsed something like this:
$olddate = "20010307235235";
preg_match("/(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})/, $olddate, $matches);
$timestamp = mktime($matches[4], $matches[4], $matches[6], $matches[2], $matches[3], $matches[1]);

Not tested though, and I'm quite tired so I might have overlooked something. The first date format could be parsed in a similar way, but you would need an associative array to get the month number from the month name. And letters are matched with \w instead of \d.

Edit: Doh! This is saved in a mysql date field, since NOW() is used in the query. So you could convert it into timestamp when reading it with the following in your sql query: unix_timestamp(DATE_FIELD)
Hopefully you can use my example to write the parsing for the first date format (10 Sep, 2002).

Last edited by Gardener; 09/24/2002 6:25 PM.
Joined: Jun 2002
Posts: 303
Enthusiast
Enthusiast
Joined: Jun 2002
Posts: 303
I certainly appreciate all your help. I was hoping you might could take a look at my code, if you got a chance.

The script is somewhat working. Though many messages are being imported many are not. Why, I'm not quite sure. Plus they are not showing up in the forum itself event thought they are there. I'm sure it's because I've done something wrong.

The following table fields I didn't know what to do with:
B_Topic
B_Replies

My if statement is screwing up because everybody is being shown as A in the U_Status, so something is wrong there.

I also have to add something to add the number of posts to update the w3t_boards table.

Anyway, I was wondering if you might be able to spot something right of hand. I can't see the trees for the forest.


code:


<?
// author : Mark Summerlin aka fishtails
// cyboards2threads.php imports cyboard messages into UBBthreads
// WARNING!!!!! Backup your database before running this script!
// No warranties are expressed or implied. USE AT YOUR OWN RISK
// this script assumes that you have installed UBBThreads in the same database as your postnuke installation.
// Set variables
$host = "localhost"; // usually localhost
$dbuname = "root"; // db username
$dbpass = ""; // db password
$dbname = "ubbthread"; // name of the database
$board_name = "test"; // this is the name (keyword) of the forum you want entered into
// this assumes that you are putting ALL messages into ONE forum. If you have more than one forum and want
// them entered correctly, you will have to modify this script to do so. Otherwise use the move feature in Threads

// THAT'S ALL OF THE SETTINGS After running this script, DELETE IT FROM YOUR SERVER!

function port_cyboards_posts() {
GLOBAL $host,$dbuname,$dbpass,$dbname,$board_name;

$db_conn = mysql_connect("$host", "$dbuname", "$dbpass");
mysql_select_db("$dbname", $db_conn);

$result = mysql_query("SELECT id, board, thread, parent, author, email, subject, body, UNIX_TIMESTAMP(datestamp) as datestamp, ip, host, vipflag, link, linkname, image, locked, sticky, email_replies, hide_email, counter FROM board_data", $db_conn);

for ($x= 0; $x < mysql_num_rows($result); $x++)
{
$row = mysql_fetch_assoc($result);
$dateposted = $row[datestamp];
$B_Parent = $row[parent];
$B_Board = $board_name;
$B_Number = $row[id];
$B_Main = $row[thread];
$B_Posted = $row[datestamp]; // need to convert the dates here
$B_Last_Post = $row[datestamp]; // another timestamp. is it needed for the conversion?
$B_IP = $row[ip];
$B_Subject = addslashes($row[subject]);
$B_Body = addslashes($row[body]); // need to appens the following to the body $row[link] $row[linkname] $row[image]
$B_Mail = "O";
$B_File = "";
$B_Kept = "";
$B_Status = "O";
$B_Approved = "yes";
$B_Picture = "";
$B_Icon = "book.gif";
$B_UTitle = "";
$B_Counter = 0;
$B_Sticky = "";
$B_Replies = ""; // not sure how to do this one
$B_Poll = "";
$B_Convert = "markup";
$B_Signature = "";
$B_LastEdit = "";
$B_LastEditBy = "";
$B_Rating = "";
$B_Rates = "";
$B_RealRating = "";
echo "<br>$dateposted";
$poster = $row[author];
// see if this is a member
$ismember = mysql_query("SELECT * from w3t_users WHERE U_Username = '$row[author]'");

if ($ismember && (mysql_num_rows($ismember)>0)) // if the author is a member...
{
$memberrow = mysql_fetch_assoc($ismember); // get the member info from the database
$oldtotalposts = $memberrow[U_Totalposts]; // not sure if this is needed, but.....
$totalposts = ($oldtotalposts+1);
$update_result = mysql_query("UPDATE w3t_users SET U_Totalposts=U_Totalposts+1 WHERE U_Username = '$row[author]'"); // update members total posts by adding 1
if ($update_result == TRUE)
{echo "$poster has posted $totalposts messages.<br>";}
else
{echo "The member total posts could not be updated<br>";}
if ($memberrow[U_Status] == Administrator)
{
$B_UStatus = "A";
$B_Color = "#FF0000";
}



$B_Username = addslashes($row[author]); // assign the member name
$B_Reged = "y"; // indicate author is indeed a member
$B_PosterId = $memberrow[U_Number];

}
else
{ // since the author is not a registered member
echo "$poster is not a registered member!<br>";
$B_Username = addslashes($row[author]); // assign the author name
$B_Reged = "n"; // indicate that the author is not a member
$B_PosterId = 1;
}
if ($row[parent] = 0)
{
$B_ParentUser = $row[author];
$B_Topic = 1;
}
else
{
$B_ParentUser = $row[author];
$B_Topic = 0;
}

$post_added = mysql_query("INSERT INTO w3t_posts (B_Board, B_Number, B_Parent, B_Main, B_Posted, B_Last_Post, B_Username, B_IP, B_Subject, B_Body, B_Mail, B_File, B_Kept, B_Status, B_Approved, B_Picture, B_Icon, B_Color, B_Reged, B_UTitle, B_Counter, B_Sticky, B_Replies, B_Poll, B_ParentUser, B_UStatus, B_Topic, B_Convert, B_Signature, B_LastEdit, B_LastEditBy, B_PosterId, B_Rating, B_Rates, B_RealRating)
VALUES ('$B_Board','$B_Number','$B_Parent','$B_Main','$B_Posted','$B_Last_Post','$B_Username','$B_IP','$B_Subject','$B_Body','$B_Mail','$B_File','$B_Kept','$B_Status','$B_Approved','$B_Picture','$B_Icon','$B_Color','$B_Reged','$B_UTitle','$B_Counter','$B_Sticky','$B_Replies','$B_Poll','$B_ParentUser','$B_UStatus','$B_Topic','$B_Convert','$B_Signature','$B_LastEdit','$B_LastEditBy','$B_PosterId','$B_Rating','$B_Rates','$B_RealRating')");

if ($post_added == TRUE)
{echo "Message number $B_Number has been added.<br>";}
else
{echo "Message number $B_Number could not be added.<br>";}

}


mysql_close($db_conn);
echo "<br><br>DONE";

}

port_cyboards_posts();

?>



Joined: May 1999
Posts: 1,715
Addict
Addict
Joined: May 1999
Posts: 1,715
B_Replies is how many approved replies there are in a thread, for each new reply that is made to a thread, B_Replies is increased by one in all posts in the thread. But I believe that only the one in the main post of the thread is used. It is used on the postlist page to show how many replies a thread has.

B_Topic is set to 1 if it is the initial post of a thread, otherwise it is set to 0.

B_Last_Post is the timestamp of the latest post in the thread and is only used on the main post of a thread if I'm not mistaken.

The replies and last post can be updated with the following code, which should be used after any reply is inserted (not main post of a thread):
code:

$query = "
UPDATE w3t_Posts
SET B_Last_Post = '$B_Last_Post',
B_Replies = B_Replies + 1
WHERE B_Main = '$B_Main'
AND B_Board = '$boardname
";
mysql_query($query);



About the if-statement, you need to add "" around Administrator:
if ($memberrow[U_Status] == "Administrator")

I don't really know why the messages won't show up, but it usually has to do with the B_Main being screwed up. In the first post of a thread it has to be the same as the U_Number of that post. In all replies in that thread it has to be the U_Number of the main post. B_Parent also has to be the U_Number of the parent post, or 0 if it is the main post of the thread.
It seems that you read ID, thread number etc from the old database. For this to work you will have to be sure that there aren't any ID:s with the same number already in your threads db, but if it is a clean db it shouldn't matter.
If you run this query and show me the result I might be able to see what might be causing the messages to not show up:
code:

select B_Number, B_Board, B_Subject, B_Username, B_Topic, B_Parent, B_Main, B_Replies from w3t_Posts limit 10



To update the info in the Boards database it might suffice to use the Thread and Post count utility after you've imported all messages.

Sponsored Links
Joined: Jun 2002
Posts: 303
Enthusiast
Enthusiast
Joined: Jun 2002
Posts: 303
I'll have to remember to truncate my posts next time so we don't have to scroll to read.

Anyway, I finally got to sit down last night and work with the code. After 7 hours of trial and error, I've got everything working, thanks to your help. The messages are being displayed now, and the thread and post count utility script works great! The REAL test will come when I upload everything to my live server and run the conversion.

There are a couple more things I'm having difficulty with.

1. The script occasionally times out. Is there something, a routine or function, I can put into the script to stop execution after a certain amount of time with the option to continue where it left off by pressing enter or a continue button? If the script times out before it's done then the db table has to be flushed and the script re-executed.

2. I need to join a couple of table fields to the end of the body of the message. Those fields are $row[link] $row[linkname] and $row[image] . I have tried the concantenation but I'm doing something wrong. The makeup needs to be like this;


code:

$B_Body = addslashes($row[body])
<br><br>
<a href="$row[link]" target="_blank">
$row[linkname]</a>
<br><br>
<img src="$row[image]">



I've tried so many ways I've lost track of what I've done.

3. Some of the messages are not being inserted. They tend to be with the same poster. I figured out some had to do with his username being to long. ( more than 30 varchar ) I fixed those by editing the old db. The others, however, won't insert. I used addslashes() . Do I need to stripslashes() before addslashes() ?

I certainly appreciate all your help. This is a great community, and I'm happy to be a part of it.

#310398 10/02/2002 11:40 AM
Joined: Jun 2002
Posts: 303
Enthusiast
Enthusiast
Joined: Jun 2002
Posts: 303
I figured out #2 and #3, so now I just need to figure a way around the timeouts. Thanks for all your help Gardner. I really appreciate all your help!

#310399 10/02/2002 12:31 PM
Joined: Apr 2002
Posts: 1,768
Addict
Addict
Offline
Joined: Apr 2002
Posts: 1,768
timeouts

Can you run the script from the shell, rather than as a CGI script? That way, you don't have to deal with CGI timeouts.

Also, you may be able to use the following:

// set maximum execution time to specified number of seconds
ini_set('max_execution_time', '300') or die("ini_set\n");

Joined: Jun 2002
Posts: 303
Enthusiast
Enthusiast
Joined: Jun 2002
Posts: 303
I can gain shell access, but I'm not yet comfortable at using it. Does ini_set('max_execution_time', '300') or die("ini_set\n"); go in my script? Also, would this be considered trying to circumvent the server administrators settings at my web host? I would hate to make them mad and pull my account.

If you think this may be a problem, I got some good tips from the PHP forum that should work, just a little more work.

Thanks.

#310401 10/02/2002 4:11 PM
Joined: Apr 2002
Posts: 1,768
Addict
Addict
Offline
Joined: Apr 2002
Posts: 1,768
Using ini_set() is similar to editing php.ini. If you're on a shared server, it would probably be a good idea to check wih the server admin first.

#310402 10/04/2002 10:42 AM
Joined: May 1999
Posts: 1,715
Addict
Addict
Joined: May 1999
Posts: 1,715
I'm glad it seems to be have worked out for you and I'm sorry that I've been away for some time, but I haven't had the opprtunity to go online. If there are any more problems I'm glad to be of further help.

Also, I think it would be a good idea to release the code so that others can use it if they are moving from the same bb system. It might even be of use for others who want to write import scripts for other bb systems too.

c0bra #310403 10/05/2002 7:07 AM
Joined: Jun 2002
Posts: 303
Enthusiast
Enthusiast
Joined: Jun 2002
Posts: 303
I'll be converting my site over the next week. I'm writing a script to import my postnuke news articles as well so everything will gel. I'll release all the scripts seperately, one for post nuke conversion and one for the bbs. I'll need to clean them up and add some config variables due to hard coding for my specific situation. Which forum shall I release them in, and what format (zip, txt, etc) ?

While I'm thinking about it, I've been using 6.03 (from my natca site) for building these scripts. When I'm ready to do the conversion I plan on getting 6.1 for my fish-tails site. Are there any major changes to the db from 6.03 to 6.1 that may affect my scripts?

I also have a script I modified for importing WebBBS flat files into my Cyboards bbs. The original was for importing into phorum, but I never liked phorum so I modified it for Cyboards. It very easily could be modified to import WebBBS into Threads. When I get done converting my site over, if any one is interested, I'll re-write if for threads.

#310404 10/07/2002 5:46 AM
Joined: May 1999
Posts: 1,715
Addict
Addict
Joined: May 1999
Posts: 1,715
You should probably release the scripts in the Beta Modifications forum and it is probably a good idea to zip the php files so that they can retain their file extensions. Also explorer will try to view the php files as html even if they are named .txt which can be quite confusing.

I'm not sure about the differences between 6.0.3 and 6.1 but I don't think they should affect your scripts that much.

Importers are always welcome so if you want to write the WebBBS importer that would be great. I haven't seen anyone that has asked for it here though, but maybe those questions are directed to Infopop and their community site rather than at this developers board.


Link Copied to Clipboard
Donate Today!
Donate via PayPal

Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.

Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
Recommended Hosts
We have personally worked with and recommend the following Web Hosts:
Stable Host
bluehost
InterServer
Visit us on Facebook
Member Spotlight
isaac
isaac
California
Posts: 1,157
Joined: July 2001
Forum Statistics
Forums63
Topics37,573
Posts293,925
Members13,849
Most Online5,166
Sep 15th, 2019
Today's Statistics
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
Top Posters
AllenAyres 21,079
JoshPet 10,369
LK 7,394
Lord Dexter 6,708
Gizmo 5,833
Greg Hard 4,625
Top Posters(30 Days)
Top Likes Received
isaac 82
Gizmo 20
Brett 7
WebGuy 2
Top Likes Received (30 Days)
None yet
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2024 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20221218)