Previous Thread
Next Thread
Print Thread
Rate Thread
Page 1 of 3 1 2 3
Useful MySQL Queries for UBB.Threads #258506
09/11/2003 2:25 AM
09/11/2003 2:25 AM
Joined: Nov 2001
Posts: 11,330
Charlotte, NC
JoshPet Offline OP
I type Like navaho
JoshPet  Offline OP
I type Like navaho

Joined: Nov 2001
Posts: 11,330
Charlotte, NC
Backup your database first to be safe.

1) Query the DB to see how many members will allow the Admin to send them bulk emails.

SELECT COUNT(*) FROM w3t_Users WHERE U_AdminEmails='On'

2) Query to set all users to the default aged threads to be displayed (what is set per forum as that forum's default).

UPDATE w3t_Users
SET U_ActiveThread = "999"

3) Query to set all users to the default parent posts per page shown.

UPDATE w3t_Users
SET U_PostsPer = "10"

4) Query to set all users to the default total posts per page shown (when in flat mode).

UPDATE w3t_Users
SET U_FlatPosts = "10"

5) Query to allow all members to receive email from admins.

UPDATE w3t_Users SET U_AdminEmails = 'On' WHERE U_Number > 1

6) Query to set all members to receive an email notification when they receive a private message.

UPDATE w3t_Users
SET U_Notify = 'On'

7) Query to change the displayed date that a post was made.

UPDATE w3t_Posts SET B_Posted = UNIX_TIMESTAMP('2003-06-14 20:05:00') WHERE B_Number = 123456

Note: Depending on whether it's the last post in a thread or board, you'd have to do similar updates for the main post in the thread and the board that contains the thread.

8) Query to make ALL forums members only.

UPDATE w3t_Boards
SET Bo_Read_Perm = '-1-2-3-', Bo_Write_Perm = '-1-2-3-', Bo_Reply_Perm = '-1-2-3-'

"But if you have any boards that are only supposed to be accessible to admins, mods or other special groups, their permissions would get reset, unless you add an appropriate WHERE clause. "

9) Query to delete all users that have not logged-in since they registered. Submitted by Akd96

DELETE FROM w3t_Users WHERE `U_Laston` = `U_Registered`

10) Query to Update All Users to use the default stylesheet that you've defined in the Theme file Submitted by JoshPet

UPDATE w3t_Users
SET U_StyleSheet = 'usedefault'

11) Query to manually approve a user if the user hasn't verified the account yet.

UPDATE w3t_Users SET U_Approved='yes' WHERE U_Username='whatever'

(whatever would be the username)



Thanks to all who have posted these in the past. (Dave_L, Rick, me, etc... ) and to Mr.CSS for the idea.



Last edited by JoshPet; 09/11/2003 11:49 AM.
Sponsored Links
Re: Useful MySQL Queries for UBB.Threads [Re: Daine] #258507
09/26/2003 11:32 PM
09/26/2003 11:32 PM
Joined: May 2002
Posts: 160
Not here
B
Beentheredonethat Offline
Member
Beentheredonethat  Offline
Member
B

Joined: May 2002
Posts: 160
Not here
Can you give me a command to:

delete all pm's made in the last day, or a certain day, or that contain a certain word?

The command below where given to me in the past...

SELECT DISTINCT B_IP
FROM w3t_Posts
WHERE B_PosterID = $User (replace user's user Number)

that gives all the IP's a user has posted under.

SELECT U_RegIP
FROM w3t_Users
WHERE U_Number = $User (replace user's user Number)

that gives you the IP the person registered with.

SELECT B_PosterID
FROM w3t_Posts
WHERE B_IP = 'xxx.xxx.xxx.xxx' (replace IP number here)

that gives you all the people who have posted under a particular IP address. It's usually more helpful if you chop off a couple of the last numbers and use '%' at the end, so it will pull a slightly larger range of IP's.


Re: Useful MySQL Queries for UBB.Threads [Re: charts] #258508
09/27/2003 2:25 AM
09/27/2003 2:25 AM
Joined: Nov 2001
Posts: 11,330
Charlotte, NC
JoshPet Offline OP
I type Like navaho
JoshPet  Offline OP
I type Like navaho

Joined: Nov 2001
Posts: 11,330
Charlotte, NC
I helped you with this - as it was a bit more complicated than that as the user had already been deleted.

Since all the PMs were already changed to user #1 - had to do a query like this:

DELETE FROM w3t_Messages
WHERE M_Subject LIKE 'the subject here'

Had the user not been deleted, then we could have done

DELETE FROM w3t_Messages
WHERE M_Sender = $user (replace user's user number)


However, all the people that had PMs will still flash that they have a pm, but when they view the list of private messages, the counter will get reset and the flashing will stop. So it may confuse the users a bit looking for a PM that they don't have. But in your case of 6000+ spam PMs... there was really no other easy way.

Re: Useful MySQL Queries for UBB.Threads [Re: Daine] #258509
10/06/2003 12:58 AM
10/06/2003 12:58 AM
Joined: Mar 2000
Posts: 25,587
Texas
AllenAyres Offline
I type Like navaho
AllenAyres  Offline
I type Like navaho

Joined: Mar 2000
Posts: 25,587
Texas
how do I update all users with 100+ posts to a new usergroup?

I'm using your pm restrictor mod and I'm using a usergroup that not many people are a part of - works for the new people, not the old people


- Allen wavey
- What Drives You?
Re: Useful MySQL Queries for UBB.Threads [Re: SurfMinister] #258510
10/06/2003 1:23 AM
10/06/2003 1:23 AM
Joined: Nov 2001
Posts: 11,330
Charlotte, NC
JoshPet Offline OP
I type Like navaho
JoshPet  Offline OP
I type Like navaho

Joined: Nov 2001
Posts: 11,330
Charlotte, NC
OK -

Something like this:

UPDATE w3t_Users
SET U_Groups = '-3-5-'
WHERE U_Totalposts > 100
AND U_Status = 'User'


Note: That will change whatever user groups that they had - to -3- (the user group) and -5- (or whatever other group you need).

If you need to append the new user group to what they have.... I'm nor sure exactly what needs to be done. I'd write a little script for it. (Contact me and I'll help you AA).

RandyJG posted here about the use of CONCAT but I'm not familiar with it myself.


Sponsored Links
Re: Useful MySQL Queries for UBB.Threads [Re: Daine] #258511
10/06/2003 2:07 AM
10/06/2003 2:07 AM
Joined: Mar 2000
Posts: 25,587
Texas
AllenAyres Offline
I type Like navaho
AllenAyres  Offline
I type Like navaho

Joined: Mar 2000
Posts: 25,587
Texas
thank you, that covered it close enough for what we needed


- Allen wavey
- What Drives You?
Re: Useful MySQL Queries for UBB.Threads [Re: SurfMinister] #258512
10/10/2003 9:04 AM
10/10/2003 9:04 AM
Joined: Sep 2003
Posts: 70
Sapphy Offline
Power User
Sapphy  Offline
Power User

Joined: Sep 2003
Posts: 70
Nice one Josh, lots of good stuff there!

One question, how do you reverse No.8 and change it so that all guests can view all the forums?

Cheers
Hugh

Re: Useful MySQL Queries for UBB.Threads [Re: Steve_OS] #258513
10/10/2003 10:40 AM
10/10/2003 10:40 AM
Joined: Apr 2002
Posts: 1,929
Virginia, USA
D
Dave_L_dup1 Offline
Addict
Dave_L_dup1  Offline
Addict
D

Joined: Apr 2002
Posts: 1,929
Virginia, USA
Change the desired permission strings from '-1-2-3-' to '-1-2-3-4-'. The same disclaimer applies. That will change the permissions for all the forums unless you qualify the query with an appropriate WHERE clause. It also doesn't take into account any custom groups (5, 6, etc.) that you may have added.

Re: Useful MySQL Queries for UBB.Threads [Re: joeuser] #258514
10/10/2003 2:45 PM
10/10/2003 2:45 PM
Joined: Sep 2003
Posts: 70
Sapphy Offline
Power User
Sapphy  Offline
Power User

Joined: Sep 2003
Posts: 70
Top stuff, thanks Dave!

Re: Useful MySQL Queries for UBB.Threads [Re: Daine] #258515
10/12/2003 5:11 AM
10/12/2003 5:11 AM
Joined: May 1999
Posts: 1,966
Sweden, Stockholm
Gardener Offline
Addict
Gardener  Offline
Addict

Joined: May 1999
Posts: 1,966
Sweden, Stockholm
This is a nice compilation of useful queries. I made a script which shows them all and made a link to it from the admin menu. But since saving queries is a part of 6.4 I'm guessing it might not be of use for anybody?

I also have a question about deleting users who have never logged in, will this leave traces of them in other tables? If so, it might be better to change the purgeusers script so that it is possible to select whether to only delete users who have never logged in instead.

Sponsored Links
Re: Useful MySQL Queries for UBB.Threads [Re: c0bra] #258516
10/12/2003 11:56 AM
10/12/2003 11:56 AM
Joined: Apr 2002
Posts: 1,929
Virginia, USA
D
Dave_L_dup1 Offline
Addict
Dave_L_dup1  Offline
Addict
D

Joined: Apr 2002
Posts: 1,929
Virginia, USA
I think that if a user has never logged in, then there will only be rows in two tables: w3t_Users and w3t_Messages (the welcome message).

Re: Useful MySQL Queries for UBB.Threads [Re: joeuser] #258517
10/15/2003 5:46 AM
10/15/2003 5:46 AM
Joined: May 1999
Posts: 1,966
Sweden, Stockholm
Gardener Offline
Addict
Gardener  Offline
Addict

Joined: May 1999
Posts: 1,966
Sweden, Stockholm
OK, that sounds good, I could probably check it out a bit before I do the actual removing. Does someone have a script or query to remove orphan rows in w3t_Messages?

Re: Useful MySQL Queries for UBB.Threads [Re: c0bra] #258518
11/15/2003 10:20 PM
11/15/2003 10:20 PM
Joined: May 2002
Posts: 160
Not here
B
Beentheredonethat Offline
Member
Beentheredonethat  Offline
Member
B

Joined: May 2002
Posts: 160
Not here
Other that would ben ice to know

How to delete all PM's older than 30 days

How to delete all pm's that have a certain word in them

How to replace / change a pm a user sent to all members

How to make all visitors make a donation or purchase a subscription :-)

Re: Useful MySQL Queries for UBB.Threads [Re: charts] #258519
11/16/2003 2:02 AM
11/16/2003 2:02 AM
Joined: May 1999
Posts: 1,966
Sweden, Stockholm
Gardener Offline
Addict
Gardener  Offline
Addict

Joined: May 1999
Posts: 1,966
Sweden, Stockholm
How to delete all PM's older than 30 days

DELETE FROM w3t_Messages WHERE M_Sent < UNIX_TIMESTAMP('2003-10-16')

(This will delete all PM:s sent before the given date, which is in the format YYYY-MM-DD.)

DELETE FROM w3t_Messages WHERE M_Sent < UNIX_TIMESTAMP( DATE_SUB(CURDATE(), INTERVAL 1 MONTH) )

(This should delete all PM:s that are older than 1 month, regardless of what date it is.)

How to delete all pm's that have a certain word in them

DELETE FROM w3t_Messages WHERE M_Message LIKE '%yourwordhere%'

(This will remove all PM:s where the word exists, regardless of if it's only part of a word.)

How to replace / change a pm a user sent to all members

UPDATE w3t_Messages SET M_Message = 'Your new message' WHERE M_Subject = 'Subject of your PM' AND M_Sender = 2

(All PM:s sent by user number 2 will have the message replaced with the new message.)

How to make all visitors make a donation or purchase a subscription :-)

UPDATE w3t_Users SET U_Donation = 'yes'


Disclaimer: Please make a backup of your database before trying of any of these queries. Also, change "w3t_" to whatever you have set as your database prefix. Some queries might not work in all versions of UBB.threads.

Re: Useful MySQL Queries for UBB.Threads [Re: c0bra] #258520
11/20/2003 6:27 PM
11/20/2003 6:27 PM
Joined: May 2002
Posts: 160
Not here
B
Beentheredonethat Offline
Member
Beentheredonethat  Offline
Member
B

Joined: May 2002
Posts: 160
Not here
Thank you very much.
The last one may need some further tweaking :-)

Is there a way to delete all posts made by a user in the past day, two days, 10 days?

Re: Useful MySQL Queries for UBB.Threads [Re: charts] #258521
11/22/2003 5:06 PM
11/22/2003 5:06 PM
Joined: May 1999
Posts: 1,966
Sweden, Stockholm
Gardener Offline
Addict
Gardener  Offline
Addict

Joined: May 1999
Posts: 1,966
Sweden, Stockholm
DELETE FROM w3t_Messages WHERE M_Uid = userid AND M_Sent > UNIX_TIMESTAMP( DATE_SUB(CURDATE(), INTERVAL 1 DAY) )

Change userid to the U_Number of the user you want to remove messages from. Change 1 to how many days you want to remove from.

If you want to go by username, and you are running MySQL 4, you could do this:

DELETE w3t_Messages FROM w3t_Messages AS m, w3t_Users AS u WHERE m.M_Uid = u.U_Number AND u.U_Username = 'Gardener' AND M_Sent > UNIX_TIMESTAMP( DATE_SUB(CURDATE(), INTERVAL 10 DAY) )

As always, please do a backup of your data before running these queries. Don't want you to loose anything because of a spelling mistake of mine or something.

Re: Useful MySQL Queries for UBB.Threads [Re: c0bra] #258522
11/25/2003 7:54 PM
11/25/2003 7:54 PM
Joined: Nov 2001
Posts: 11,330
Charlotte, NC
JoshPet Offline OP
I type Like navaho
JoshPet  Offline OP
I type Like navaho

Joined: Nov 2001
Posts: 11,330
Charlotte, NC
For everyone using this stuff: I will stress EXTREME Caution with deleting stuff manually in the Database and would instead encourage you to use built in tools for deleting stuff.

Particularly Users, Posts, Groups etc.... never delete them directly unless you know all the other stuff that needs to be updated as well. Check the admin scripts that delete users/posts etc... to see what other stuff needs to be updated as well.

If you delete the wrong stuff it can cause threads/forums or more to simply not appear properly, as JOINs can not match up.

So excercise EXTREME caution when doing SQL Queries directly in the Database, as they are usually unreversable and/or can take hours to diagnose/fix if stuff gets out of wack.


Re: Useful MySQL Queries for UBB.Threads [Re: Daine] #258523
11/25/2003 8:25 PM
11/25/2003 8:25 PM
Joined: May 1999
Posts: 1,966
Sweden, Stockholm
Gardener Offline
Addict
Gardener  Offline
Addict

Joined: May 1999
Posts: 1,966
Sweden, Stockholm
That is very true. Also, don't forget to do a backup if you do decide to change anything. I know people who have deleted the entire basis of a company by having a < the wrong way and no proper backups. Well, they managed to fix it in the end by hiring Microsoft consultants from abroad which costed $$$++.

Re: Useful MySQL Queries for UBB.Threads [Re: Daine] #258524
11/26/2003 10:34 AM
11/26/2003 10:34 AM
Joined: May 2002
Posts: 160
Not here
B
Beentheredonethat Offline
Member
Beentheredonethat  Offline
Member
B

Joined: May 2002
Posts: 160
Not here
I do agree with the warning.
I was playing with the commands, did not back up, and ended up deleting all trace, all posts, and all pm's made by our top poster :-( and who knows what else :-)

Backing up and restoring is not very easy either.
I had a back up at the server (have to hard disks, the second for backup every 12 hours) but it was of no use... before I could get in touch with my isp, before they answered, before i confirmed, and so on... it may take a couple of days...

Could have been worst :-)

Re: Useful MySQL Queries for UBB.Threads [Re: charts] #258525
01/02/2004 4:25 AM
01/02/2004 4:25 AM
Joined: May 2002
Posts: 160
Not here
B
Beentheredonethat Offline
Member
Beentheredonethat  Offline
Member
B

Joined: May 2002
Posts: 160
Not here
I would like to know how to change the number of posts and the registration date of a member

Thanks

Re: Useful MySQL Queries for UBB.Threads [Re: charts] #258526
01/02/2004 12:46 PM
01/02/2004 12:46 PM
Joined: Apr 2001
Posts: 3,440
abingdon,md
O
omegatron Offline
Member
omegatron  Offline
Member
O

Joined: Apr 2001
Posts: 3,440
abingdon,md
UPDATE w3t_Users
SET U_Totalposts = 'NEWTOTALHERE'
WHERE U_Username = 'USERNAMEHERE'

UPDATE w3t_Users
SET U_Registered = UNIX_TIMESTAMP('2003-10-16')
WHERE U_Username = 'USERNAMEHERE'

Change the date to what you want

Re: Useful MySQL Queries for UBB.Threads [Re: sf49rminer] #258527
01/05/2004 7:38 PM
01/05/2004 7:38 PM
Joined: May 2002
Posts: 160
Not here
B
Beentheredonethat Offline
Member
Beentheredonethat  Offline
Member
B

Joined: May 2002
Posts: 160
Not here
Thanks.
Tryed that but did not work.
Is it the same if username and displayed name are not the same?

also... is there a way to show the real number of posts for a user... this user had a problem and the number of posts changed... would really like to show the reasl number of posts

Re: Useful MySQL Queries for UBB.Threads [Re: charts] #258528
01/05/2004 9:20 PM
01/05/2004 9:20 PM
Joined: Aug 2002
Posts: 263
Somewhere above Texas
Storm_dup1 Offline
Member
Storm_dup1  Offline
Member

Joined: Aug 2002
Posts: 263
Somewhere above Texas
What would a query be to look up people who have registered but not yet verified their e-mail addies to be real?


Some people read their stars..... I choose to write my own
Re: Useful MySQL Queries for UBB.Threads [Re: ] #258529
01/05/2004 9:50 PM
01/05/2004 9:50 PM
Joined: Apr 2001
Posts: 3,440
abingdon,md
O
omegatron Offline
Member
omegatron  Offline
Member
O

Joined: Apr 2001
Posts: 3,440
abingdon,md
U_Username is DisplayName

U_LoginName is the loginname

Those queries above will do exactly what you asked for. You say they dont what is the error you get. You can also suppliment the WHERE U_Username = 'USERNAMEHERE' to WHERE U_Number = 'usernumberhere' Make sure you are not forgetting the slashes around the name or number in the where clause.

As far as getting a post count

SELECT COUNT(*)
FROM w3t_Posts
WHERE B_PosterId ='USERNUMBERHERE'

Then to update the users total

UPDATE w3t_Users
SET U_TotalPosts = 'TOTALHERE'
WHERE U_Number = 'USERNUMBERHERE'

Re: Useful MySQL Queries for UBB.Threads [Re: sf49rminer] #258530
01/07/2004 6:20 PM
01/07/2004 6:20 PM
Joined: Jan 2003
Posts: 285
Amsterdam, The Netherlands
Pasqualist Offline
Member
Pasqualist  Offline
Member

Joined: Jan 2003
Posts: 285
Amsterdam, The Netherlands
Which query would I need to change the url in U_Picture (Users) ?

Example "http://www.domain1.com/forum/userpics/2.jpg"

should be changed in

Example "http://www.name2.com/forum/userpics/2.jpg"

Thanks!

Re: Useful MySQL Queries for UBB.Threads [Re: 10k] #258531
01/07/2004 7:02 PM
01/07/2004 7:02 PM
Joined: Apr 2001
Posts: 3,440
abingdon,md
O
omegatron Offline
Member
omegatron  Offline
Member
O

Joined: Apr 2001
Posts: 3,440
abingdon,md
You changed the config avatar directory in the setup? This is the directory where pictures are.

Re: Useful MySQL Queries for UBB.Threads [Re: sf49rminer] #258532
01/07/2004 7:38 PM
01/07/2004 7:38 PM
Joined: Nov 2001
Posts: 11,330
Charlotte, NC
JoshPet Offline OP
I type Like navaho
JoshPet  Offline OP
I type Like navaho

Joined: Nov 2001
Posts: 11,330
Charlotte, NC
No the URL to the avatar is stored in U_Picture - the value in the config file is just for uploads and used when it's being stored.

This should do it:

UPDATE w3t_Users
SET U_Picture = 'http://whatever/what/userpic/2.jpg'
WHERE U_Number = xxx

Put the usernumber of xxx


Re: Useful MySQL Queries for UBB.Threads [Re: Daine] #258533
01/08/2004 3:20 AM
01/08/2004 3:20 AM
Joined: Jan 2003
Posts: 285
Amsterdam, The Netherlands
Pasqualist Offline
Member
Pasqualist  Offline
Member

Joined: Jan 2003
Posts: 285
Amsterdam, The Netherlands
Thanks Josh, but your query would not do the trick for me..

I want to change the picture URL for all users, so I only need to change "domain1.com" to "name2.com".

Re: Useful MySQL Queries for UBB.Threads [Re: 10k] #258534
01/08/2004 9:29 AM
01/08/2004 9:29 AM
Joined: Oct 2003
Posts: 2,409
Richmond, VA
scroungr Offline
Old Hand
scroungr  Offline
Old Hand

Joined: Oct 2003
Posts: 2,409
Richmond, VA
you need a query that first grabs the link from the database in a query and then pulls the substring of the first domain and inserts the substring of the second domain and then inserts that into the database... but thats a little dangerous if not used correctly..

Last edited by scroungr; 01/08/2004 9:36 AM.
Re: Useful MySQL Queries for UBB.Threads [Re: 234234] #258535
01/08/2004 9:34 AM
01/08/2004 9:34 AM
Joined: Apr 2001
Posts: 3,440
abingdon,md
O
omegatron Offline
Member
omegatron  Offline
Member
O

Joined: Apr 2001
Posts: 3,440
abingdon,md
This will do what you want Pasqualist and is courtesy of Master Scream when I asked him about this issue a while back.

Now this query is mostly for those wanting to update their forums from UBB to Threads and change links in the post bodies as this is where this issue is likely to occur.

UPDATE w3t_Posts SET B_Body = REPLACE(B_Body, 'cgi-bin/ubb','ubbthreads')

Now you can change this query to suit your needs by doing the following to it.

UPDATE w3t_Users SET U_Picture = REPLACE(U_Picture, 'www.domain1.com','www.name2.com')

Now remember to backup your database before doing this but I have ran the first query on my install and it worked fine. It should automatically update the part of the string you want.

Re: Useful MySQL Queries for UBB.Threads [Re: sf49rminer] #258536
01/08/2004 9:35 AM
01/08/2004 9:35 AM
Joined: Oct 2003
Posts: 2,409
Richmond, VA
scroungr Offline
Old Hand
scroungr  Offline
Old Hand

Joined: Oct 2003
Posts: 2,409
Richmond, VA
even better

Re: Useful MySQL Queries for UBB.Threads [Re: 234234] #258537
01/08/2004 10:02 AM
01/08/2004 10:02 AM
Joined: Apr 2001
Posts: 3,440
abingdon,md
O
omegatron Offline
Member
omegatron  Offline
Member
O

Joined: Apr 2001
Posts: 3,440
abingdon,md
LOL Yeah I had asked the MASTER a while back as I still had links in posts to my old ubb install graemlins etc

Re: Useful MySQL Queries for UBB.Threads [Re: sf49rminer] #258538
01/08/2004 10:10 AM
01/08/2004 10:10 AM
Joined: Oct 2003
Posts: 2,409
Richmond, VA
scroungr Offline
Old Hand
scroungr  Offline
Old Hand

Joined: Oct 2003
Posts: 2,409
Richmond, VA
forgot all about the mysql REPLACE command

Re: Useful MySQL Queries for UBB.Threads [Re: Daine] #258539
01/08/2004 2:03 PM
01/08/2004 2:03 PM
Joined: Dec 2003
Posts: 18
C
CurlGirl Offline
Newbie
CurlGirl  Offline
Newbie
C

Joined: Dec 2003
Posts: 18
Ok....I have a request since I'm in the "throws" of re-installing.

Would it be possible to repost something as an ADMIN (with the boards closed) and then change the ADMIN association to the original poster? This is so I can re-post threads lost with my ... huhummm... re-installation?

Thanks,
Patrice

Re: Useful MySQL Queries for UBB.Threads [Re: GrandAmEmt] #258540
01/08/2004 2:27 PM
01/08/2004 2:27 PM
Joined: Oct 2003
Posts: 2,409
Richmond, VA
scroungr Offline
Old Hand
scroungr  Offline
Old Hand

Joined: Oct 2003
Posts: 2,409
Richmond, VA
think it's easier to log in as the user... you can switch to them in the admin panel... problem is boards would have to be open not closed..

Re: Useful MySQL Queries for UBB.Threads [Re: 234234] #258541
01/08/2004 5:14 PM
01/08/2004 5:14 PM
Joined: Apr 2001
Posts: 3,440
abingdon,md
O
omegatron Offline
Member
omegatron  Offline
Member
O

Joined: Apr 2001
Posts: 3,440
abingdon,md
If your missing threads from your threads database after the install of your UBB classic database conversion that malfunctioned after an import and the board went down after running threads for a few days then you can use PhpMyAdmin to input users and posts after the original conversion.

I take it now you have a correct conversion running. Just use PHPMYADMIN to import any new users into Threads that are missing from your last Threads backup when it was working before it crashed.

You then can do the same thing with the posts table.

INSERT INTO `w3t_Users` VALUES ( blah blah blah);

INSERT INTO `w3t_Posts` VALUES ( blah blah blah);

Your backups when you compare should guide you further where the cutoff is. Problem is if you opened your board back up now after the second import then you cant do this as post numbers and user numbers will have been used that you are trying to import.

Re: Useful MySQL Queries for UBB.Threads [Re: sf49rminer] #258542
01/08/2004 5:38 PM
01/08/2004 5:38 PM
Joined: Oct 2003
Posts: 2,409
Richmond, VA
scroungr Offline
Old Hand
scroungr  Offline
Old Hand

Joined: Oct 2003
Posts: 2,409
Richmond, VA
yeah but Omego the way it sounds..

[]
Would it be possible to repost something as an ADMIN (with the boards closed) and then change the ADMIN association to the original poster? This is so I can re-post threads lost with my ... huhummm... re-installation?
[/]

is that she wants to post those lost as herself then go in later and change the name of who posted it to the original person cause she lost them...

Re: Useful MySQL Queries for UBB.Threads [Re: 234234] #258543
01/08/2004 6:11 PM
01/08/2004 6:11 PM
Joined: Apr 2001
Posts: 3,440
abingdon,md
O
omegatron Offline
Member
omegatron  Offline
Member
O

Joined: Apr 2001
Posts: 3,440
abingdon,md
If she lost the posts and does not have a backup to do what I am saying then how is she going to know what to post to do what your saying

See my point.

Re: Useful MySQL Queries for UBB.Threads [Re: sf49rminer] #258544
01/09/2004 11:14 AM
01/09/2004 11:14 AM
Joined: Dec 2003
Posts: 18
C
CurlGirl Offline
Newbie
CurlGirl  Offline
Newbie
C

Joined: Dec 2003
Posts: 18
Glutton for punishment. My solution. Probably not the best, but it got the job done. I ended up make all the forums "read-only" with admin and MODERATOR read/write. Made the user a temporary moderator, logged in as them and reposted their thread and demoted them. I'm cross-eyed, but it's done. Whew. Think I'll BACK IT UP now!

-Patrice

Last edited by CurlGirl; 01/09/2004 11:16 AM.
Re: Useful MySQL Queries for UBB.Threads [Re: GrandAmEmt] #258545
01/09/2004 11:20 AM
01/09/2004 11:20 AM
Joined: Apr 2001
Posts: 3,440
abingdon,md
O
omegatron Offline
Member
omegatron  Offline
Member
O

Joined: Apr 2001
Posts: 3,440
abingdon,md
You did the things the hard way. Reporting one thread at a time. You could have inserted the new posts via PHPMYADMIN in one step.

Page 1 of 3 1 2 3

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
· Blue Host
· Interserver.net
Visit us on Facebook
Member Spotlight
Gizmo
Gizmo
Portland, OR, USA
Posts: 5,941
Joined: January 2000
Show All Member Profiles 
Forum Statistics
Forums64
Topics37,456
Posts293,497
Members13,800
Most Online1,498
Mar 17th, 2017
Top Posters(All Time)
AllenAyres 25,587
JoshPet 11,330
Rick 8,373
LK 7,396
Lord Dexter 6,503
Gizmo 5,941
Greg Hard 5,533
Top Posters(30 Days)
isaac 8
Gizmo 3
driv 1
Today's Statistics
Currently Online 872
Topics Created 2
Posts Made 6
Users Online 4
Birthdays 23
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2018 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 7.6.3
(Snapshot build 20180923.dev)
PHP: 5.4.45 DB: MySQLi Page Time: 0.134s Queries: 16 (0.052s) Memory: 3.6137 MB (Peak: 4.0590 MB) Zlib enabled. Server Time: 2018-09-24 05:24:43 UTC