Y1g Sp1.2 >> SE 1.54 Topic Issue

[LonnieS]

I'm trying real hard to resolve this, and i just need a little insight. I'm not real strong in MySQL yet, but I'm getting there.

This issue begins immediately upon conversion from YaBB 1 Gold SP 1.2 to YaBB SE 1.5.4 (1.5.1). Everything carries over, (except the attachments of course) and there are no errors.

My topics are basically in the right order, but their TOPIC ID's have been reversed. YaBBSE's natural numbering system adds one number increments to each subsequent topic. (i.e. my latest topic has an ID# 109297, the next one will have ID# 109298, etc). Following the conversion, this natural order is reversed - with the latest topic having an ID# 1, and the oldest topic having an ID# 1092...  -Yey as soon as a new topic is created (after the conversion) - the natural order resumes and the latest topic will begin ID numbering at 1092... and up. Please note: I'm not talking about the POSTS or POST ID's (or message ID's as their often referred to). This is specifically with regard to TOPICS, or "Threads".

I need to keep the ID numbering consistent from Y1 G to YabbSE. Somewhere in the conversion scripts this gets reversed, and I'd like to correct it. Can someone PLEASE help me?  I hope I'm not out of line but I'll pay generously $$ if someone will correct this for me. That's how much pressure is riding on this.

Thanks,

Lonnie

[[Unknown]]

CREATE TABLE yabbse_fixed_topics (
 ID_TOPIC int(11) NOT NULL auto_increment,
 ID_BOARD int(11) NOT NULL default '0',
 ID_MEMBER_STARTED int(11) NOT NULL default '0',
 ID_MEMBER_UPDATED int(11) NOT NULL default '0',
 ID_FIRST_MSG int(11) NOT NULL default '0',
 ID_LAST_MSG int(11) NOT NULL default '0',
 ID_POLL int(11) NOT NULL default '-1',
 numReplies int(11) NOT NULL default '0',
 numViews int(11) NOT NULL default '0',
 locked tinyint(4) NOT NULL default '0',
 notifies text,
 isSticky tinyint(4) NOT NULL default '0',
 PRIMARY KEY (ID_TOPIC)
) TYPE=MyISAM
SELECT
   t.ID_TOPIC, t.ID_BOARD, t.ID_MEMBER_STARTED, t.ID_MEMBER_UPDATED, t.ID_FIRST_MSG, t.ID_LAST_MSG, t.ID_POLL,
   t.numReplies, t.numViews, t.locked, t.notifies, t.isSticky
FROM yabbse_topics AS t, yabbse_messages AS m
WHERE m.ID_MSG = t.ID_FIRST_MSG
ORDER m.posterTime ASC;

RENAME TABLE yabbse_topics TO yabbse_broken_topics, yabbse_fixed_topics TO yabbse_topics;

That should solve it...

My wishlist is at http://www.amazon.com/exec/obidos/registry/3TWO4YL24C7P4 .

-[Unknown]

[LonnieS]

Here's the exact error I received in PHPMyAdmin when I ran the rist part of your code:

Code Select Expand

Error

SQL-query :  

CREATE TABLE yabbse_fixed_topics(

ID_TOPIC int( 11 ) NOT NULL AUTO_INCREMENT ,
ID_BOARD int( 11 ) NOT NULL default '0',
ID_MEMBER_STARTED int( 11 ) NOT NULL default '0',
ID_MEMBER_UPDATED int( 11 ) NOT NULL default '0',
ID_FIRST_MSG int( 11 ) NOT NULL default '0',
ID_LAST_MSG int( 11 ) NOT NULL default '0',
ID_POLL int( 11 ) NOT NULL default '-1',
numReplies int( 11 ) NOT NULL default '0',
numViews int( 11 ) NOT NULL default '0',
locked tinyint( 4 ) NOT NULL default '0',
notifies text,
isSticky tinyint( 4 ) NOT NULL default '0',
PRIMARY KEY ( ID_TOPIC )
) TYPE = MYISAM SELECT t.ID_TOPIC, t.ID_BOARD, t.ID_MEMBER_STARTED, t.ID_MEMBER_UPDATED, t.ID_FIRST_MSG, t.ID_LAST_MSG, t.ID_POLL, t.numReplies, t.numViews, t.locked, t.notifies, t.isSticky
FROM yabbse_topics AS t, yabbse_messages AS m
WHERE m.ID_MSG = t.ID_FIRST_MSG
ORDER BY m.posterTime ASC

MySQL said:


Duplicate column name 'ID_TOPIC'


I almost forgot - I got an error prior to that regarding the line with:  

ORDER m.posterTime ASC;

- so I figured it was missing the word "BY" in "Order By" - so I added it.

I think we're close here - and you're close to the jackpot. Either way, there will be a generous return for your efforts.

[LonnieS]

I split the page of code you provided into two main sections. Here's the first query I ran:

Code Select Expand

CREATE TABLE yabbse_fixed_topics (
 ID_TOPIC int(11) NOT NULL auto_increment,
 ID_BOARD int(11) NOT NULL default '0',
 ID_MEMBER_STARTED int(11) NOT NULL default '0',
 ID_MEMBER_UPDATED int(11) NOT NULL default '0',
 ID_FIRST_MSG int(11) NOT NULL default '0',
 ID_LAST_MSG int(11) NOT NULL default '0',
 ID_POLL int(11) NOT NULL default '-1',
 numReplies int(11) NOT NULL default '0',
 numViews int(11) NOT NULL default '0',
 locked tinyint(4) NOT NULL default '0',
 notifies text,
 isSticky tinyint(4) NOT NULL default '0',
 PRIMARY KEY (ID_TOPIC)
) TYPE=MyISAM

And it was successful - it created the table.

Next I ran the next section as an additional query by itself:

Code Select Expand

SELECT
  t.ID_TOPIC, t.ID_BOARD, t.ID_MEMBER_STARTED, t.ID_MEMBER_UPDATED, t.ID_FIRST_MSG, t.ID_LAST_MSG, t.ID_POLL,
  t.numReplies, t.numViews, t.locked, t.notifies, t.isSticky
FROM yabbse_topics AS t, yabbse_messages AS m
WHERE m.ID_MSG = t.ID_FIRST_MSG
ORDER BY m.posterTime ASC;

Obviously, this just creates a result set according to the query. Now it would be great to insert it into your "fixed_topics" table. Sounds easy enough, but that's where I get jammed. I can't just 'insert' - I need to know how to insert the combined data "correctly".

So close, yet so far...  

[LonnieS]

Okay Unknown - I got your code to work - just a few syntax issues I had to work out.

Your code resorted the records in the newly created "yabbse_topics" table - probably in order by m.postertime as specified.

But this isn't doing what I'm striving for. I'm trying to get the topic ID's numbered in ascending order, with the oldest topic having TOPIC_ID #1, and the newest, most recent topic having TOPIC_ID # 636. This is how it was (essentially) in YaBB 1 G SP1.2. Only in YaBB1G, it used much larger numbers for topic ID's - but they were in the order I'm trying to re-establish.

I suppose in order to do this, ultimately the messages would have to receive new message ID's too, since they are also in reverse order - just like the topics.

Arrgghhh  

[LonnieS]

Call off the dogs!  (yea right, you were hard at work on this one!  )  heheh

I found a work around for the reverse TOPIC_ID order - since the problem really stems from the fact that YaBB 1 G adds topics to the different board files at the TOP of the page, it just needed to be copy and pasted back on the page in reverse order. DONE!  Works great now.

Thanks for your efforts, though, Unknown. Even though you didn't correct it, I appreciate you trying, so be sure to check your mail!

[[Unknown]]

Actually, the problem was that I should have made it like this:

(stupid lazy MySQL, I always forget it creates the columns for you with this syntax.)

CREATE TABLE yabbse_fixed_topics (
) TYPE=MyISAM
SELECT
  t.ID_TOPIC, t.ID_BOARD, t.ID_MEMBER_STARTED, t.ID_MEMBER_UPDATED, t.ID_FIRST_MSG, t.ID_LAST_MSG, t.ID_POLL,
  t.numReplies, t.numViews, t.locked, t.notifies, t.isSticky
FROM yabbse_topics AS t, yabbse_messages AS m
WHERE m.ID_MSG = t.ID_FIRST_MSG
ORDER m.posterTime ASC;

ALTER TABLE yabbse_fixed_topics ADD PRIMARY KEY (ID_TOPIC);

Chances are you didn't change it like that.  This does precisely this:
- creates a new table for the topics table.
- select the results from the old table, ordered by the time of posting of the topic, ascending.  (lower posterTime means longer ago.. so it would go upward.)

And, I was kidding about the amazon thing.. (as seen by the .) I just poked you in the right direction.

-[Unknown]

[LonnieS]

I wasn't kidding, Unknown    I believe in grattitude, and after beating my head against the wall for a LONG time, any help is very much appreciated. I just hope your wishlist was up-to-date.

Take it easy bro.