PunBB Convert - OK except for 1 important field - need SQL suggestion

[ryanknoll]

SMF Version: SMF 1.1.1
Hi.  The SMF 1.1.1 - PunBB converter worked great.  The only problem is it did not convert the Guest's name if they posted as guest (not logged in). 

::::::: explanation of punbbposts table:::::::
poster = name of poster
posterid = all guests are assigned id of 1
id = unique id for message

So, from the punbbposts table, I'm trying to select  poster and id where posterid =1, and insert poster into the smf_messages table where id = ID_MSG and posterid = ID_MEMBER.  Does that make sense?

It's been 10 years since I've written SQL and I just can't seem to get the syntax write.  I would appreciate any guidance. Thanks in advance....and maybe this can be added to the punBB conversion script.

[JayBachatero]

So you saying that the posts are showing as made from Guests and not the actual name of the member?

[ryanknoll]

So you saying that the posts are showing as made from Guests and not the actual name of the member?

All the posts from punBB are showing up, and the registered member's names associated with the posts converted perfectly....But, the names of non-members did not convert.  The guest names are in the punbb database, whether it be "Anonymous" or the name the guest typed in before submitting the forum message, and it looks like posterName in smfmessage is used to store the names of anonymous posters. So, in punBB a post was written by non-member "John Smith", but in SMF it shows up as "Guest".

So my goal is to get the 'poster' names from punBB table inserted into 'posterName' in smfmessages.

reference
punBB's punbbposts tabel = SMF's smfmessages table
poster = posterName
id = ID_MSG
posterid = ID_MEMBER

[JayBachatero]

When you run this query, what does the posterName say for guests posts?

Make sure to change {$to_prefix} and {$from_prefix} to the correct prefix.

Code Select Expand


SELECT
p.id AS ID_MSG, p.topic_id AS ID_TOPIC, t.forum_id AS ID_BOARD,
p.posted AS posterTime, p.poster_id AS ID_MEMBER,
SUBSTRING(t.subject, 1, 255) AS subject,
SUBSTRING(IFNULL(u.username, p.poster), 1, 255) AS posterName,
SUBSTRING(p.poster_ip, 1, 255) AS posterIP,
SUBSTRING(IFNULL(u.email, poster_email), 1, 255) AS posterEmail,
IF(p.hide_smilies = 0, 1, 0) AS smileysEnabled,
SUBSTRING(REPLACE(p.message, '<br>', '<br />'), 1, 65534) AS body
FROM ({$from_prefix}posts AS p, {$from_prefix}topics AS t)
LEFT JOIN {$from_prefix}users AS u ON (u.id = p.poster_id)
WHERE t.id = p.topic_id;


[ryanknoll]

Ugh, I received an error:

Error

SQL query: Documentation

SELECT hxxp:p.id [nonactive] AS ID_MSG, p.topic_id AS ID_TOPIC, hxxp:t.forum [nonactive]_id AS ID_BOARD, p.posted AS posterTime, p.poster_id AS ID_MEMBER, SUBSTRING( t.subject, 1, 255 ) AS subject, SUBSTRING( IFNULL( u.username, p.poster ) , 1, 255 ) AS posterName, SUBSTRING( p.poster_ip, 1, 255 ) AS posterIP, SUBSTRING( IFNULL( hxxp:u.email [nonactive], poster_email ) , 1, 255 ) AS posterEmail, IF( p.hide_smilies =0, 1, 0 ) AS smileysEnabled, SUBSTRING( REPLACE( p.message, '<br>', '<br />' ) , 1, 65534 ) AS body
FROM (
{$punbb}posts AS p, {$smf_}topics AS t
)
LEFT JOIN {$punbb}users AS u ON ( hxxp:u.id [nonactive] = p.poster_id )
WHERE hxxp:t.id [nonactive] = p.topic_id
LIMIT 0 , 230

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '} posts AS p , { $smf_ } topics AS t )  LEFT JOIN { $punbb } users AS u ON ( u .' at line 1

[JayBachatero]

You need to change {$from_prefix} to somethins like punbb_

[ryanknoll]

Thanks Jay.  It executed but I didn't notice any data changes.  This post, for example, still doesn't have the poster's name copied over from punbb:

hxxp:www.franchisepundit.com/forums/index.php/topic,140.0.html [nonactive]

My table prefixes are "punbb" and "smf_".

Below was my query

Showing rows 0 - 29 (763 total, Query took 0.0246 sec)

SQL query: SELECT hxxp:p.id [nonactive] AS ID_MSG, p.topic_id AS ID_TOPIC, hxxp:t.forum [nonactive]_id AS ID_BOARD, p.posted AS posterTime, p.poster_id AS ID_MEMBER, SUBSTRING( t.subject, 1, 255 ) AS subject, SUBSTRING( IFNULL( u.username, p.poster ) , 1, 255 ) AS posterName, SUBSTRING( p.poster_ip, 1, 255 ) AS posterIP, SUBSTRING( IFNULL( hxxp:u.email [nonactive], poster_email ) , 1, 255 ) AS posterEmail, IF( p.hide_smilies =0, 1, 0 ) AS smileysEnabled, SUBSTRING( REPLACE( p.message, '<br>', '<br />' ) , 1, 65534 ) AS body
FROM (
punbbposts AS p, punbbtopics AS t
)
LEFT JOIN punbbusers AS u ON ( hxxp:u.id [nonactive] = p.poster_id )
WHERE hxxp:t.id [nonactive] = p.topic_id
LIMIT 0 , 30;

[JayBachatero]

Yes I know.  It's not supposed to.  I wanted to see what value was returned for posterName.  Is it possible for me to get access to phpMyAdmin?

[ryanknoll]

PM with details sent.  thanks jay.

[JayBachatero]

I'll look into it later on and see what's going on.

[JayBachatero]

ryanknoll I looked into it but punBB doesn't store no information for guests.  It just stores "Guests" for name and email.  So you can't really change this.

[ryanknoll]

ryanknoll I looked into it but punBB doesn't store no information for guests.  It just stores "Guests" for name and email.  So you can't really change this.

In table 'punbbposts', the 'poster' column is the name of the person who posted the message.  "poster' is what I'm trying to copy over to smf.  In punbb, 'poster' is either automatically their username if they were logged in, or it is the name typed in upon posting the message (it is a required field for non-member posting).  So the data is stored in the database.

[JayBachatero]

Ok I got it.  Do you want to convert your board again?   

[ryanknoll]

sure.  i've only had a few posts which i can type in manually.  i'll just use a different table prefix.

[JayBachatero]

Try using the converter in this topic: punBB 1.2.5

Please continue further discussion in the other topic.