Shared user database for multiple forums, same server

For version 3.5x+, please see https://vborg.vbsupport.ru/showthread.php?t=118473

This thread will detail how to have a single login for multiple forums. It makes the following assumptions:

• A valid vbulletin license exists for each install
• All forums will be on the same server
• You already have one forum operational AND that forum uses a BLANK $tableprefix!
• One database will contain all the tables

This thread is a follow-up from this discussion over at vbulletin.com. Thanks to Brains for some pointers!

Here are the steps:

1. Copy your forum directories to a parallel directory (for example copy /www/forums to /www/new_forums)
2. In /includes/config.php, change $tableprefix (line 91) to a new prefix [for example $tableprefix = 'new_';]
3. Run the vb install from the new directory (/www/new_forums/install/install.php)
4. During the install, be sure NOT to empty the tables. If you have any doubt about what this means, stop NOW! If you empty the tables, you will lose all of your existing data from a prior install!
5. Download the files from these directories to your PC: /new_forums, /new_forums/admincp, /new_forums/archive, /new_forums/includes, /new_forums/modcp, /new_forums/subscriptions
6. We are now going to make global changes to the files in the folders (and subfolders) above. I used Dreamweaver's "edit-find and replace" function with "find in" set to "Entire Current Local Site". We are basically going to remove the "TABLE_PREFIX" from any code dealing with the user. (Note - if you are comfortable with unix command, you could do these changes from the command line on the server.)
7. Run the following find and replace operations:
   • Find [" . TABLE_PREFIX . "user] (find what's inside the brackets). Replace with [?.?user] This should find 562 instances of user, usergroup, userfield, and usertextfield
   • Find [" . TABLE_PREFIX . "strikes] (find what's inside the brackets). Replace with [?.?strikes] This should find 5 instances of strikes
   • Find [" . TABLE_PREFIX . "pm] (find what's inside the brackets). Replace with [?.?pm] This should find 61 instances of pm, pmtext, pmtextid, and pmreceipt.
   • Upload these directories back to the server.
8. We now need to do a little fine tuning
   • In /includes/functions.php: on line 1171 remove the table_prefix before $idname.
   • In /includes/adminfunctions: modify print_choser_row (line 1161)to check for $tableid of user, usergroups
     
     
     PHP Code:

     if ($tableid == "user" OR $tableid == "usergroup") {
                $result = $DB_site->query("SELECT title, $tableid FROM "."$tablename $wherecondition ORDER BY title");
        } else {
                $result = $DB_site->query("SELECT title, $tableid FROM " . TABLE_PREFIX . "$tablename $wherecondition ORDER BY title"); // existing code
        } 

   • In /includes/adminfunctions_user.php around line 116 (construct_style_chooser)
     
     
     PHP Code:

         $tableid = $tablename . "id";

    if ($tablename == "user" OR $tablename == "usergroupid") {
        $result = $DB_site->query("
            SELECT title, $tableid
            FROM "."$tablename
            WHERE userselect = 1
            ORDER BY title
        ");

    } else {
                // existing code
                        $result = $DB_site->query("
            SELECT title, $tableid
            FROM " . TABLE_PREFIX . "$tablename
            WHERE userselect = 1
            ORDER BY title
        ");
    } 


Done! Both forums are now accessed by the same user table! PM's are unified across forums as is the user count.

[auz1111]

any idea if this will be ported over to 3.5?

And do you think there is a way to have a parent forum where all threads are shown, but in the seperate/child forums just threads/posts from that forum are shown.

maybe an extra field in the thread row that specifies if it belongs to one of the child forums or just the main...

[tscbh]

Hi westpointer,

I am wondering if you can put each forum on its own database. I have 3 sites and I am planning to add more in the future. This would be easier to backup and maintain the all the sites.

[CyberRanger]

Originally Posted by tscbh

Hi westpointer,

I am wondering if you can put each forum on its own database. I have 3 sites and I am planning to add more in the future. This would be easier to backup and maintain the all the sites.

I'm not sure how one would do that. It's a much more complicated situation.

[CyberRanger]

Originally Posted by auz1111

any idea if this will be ported over to 3.5?

I've just moved one of my sites to 3.5 so I'll check it out. I don't imagine it would be much different.

Originally Posted by auz1111

And do you think there is a way to have a parent forum where all threads are shown, but in the seperate/child forums just threads/posts from that forum are shown....

That's a whole different creature. This hack doesn't impact threads or posts at all. Depending on exactly what you need, you may be able to achieve this through different styles. But, I'm not sure.

[Matthijs]

Originally Posted by tscbh

Hi westpointer,

I am wondering if you can put each forum on its own database. I have 3 sites and I am planning to add more in the future. This would be easier to backup and maintain the all the sites.

As long as they are on the same server, it shouldn't be a problem. You have to change the search-and-replace stuff, though.
The syntax for MySQL is "SELECT foo FROM database.table" so if you change the TABLE_PREFIX to include the database-name and give the db-user access to all databases, it should work without problems...

Edit: you might even put the shared tables in a separate database from the rest of the tables. There are no problems with JOINs and such across databases, I have done that many times...

[CyberRanger]

Originally Posted by Matthijs

As long as they are on the same server, it shouldn't be a problem. You have to change the search-and-replace stuff, though.
The syntax for MySQL is "SELECT foo FROM database.table" so if you change the TABLE_PREFIX to include the database-name and give the db-user access to all databases, it should work without problems...

Edit: you might even put the shared tables in a separate database from the rest of the tables. There are no problems with JOINs and such across databases, I have done that many times...

Duh ... you are exactly right. That would be a much cleaner solution.

[auz1111]

Originally Posted by westpointer

I've just moved one of my sites to 3.5 so I'll check it out. I don't imagine it would be much different.
That's a whole different creature. This hack doesn't impact threads or posts at all. Depending on exactly what you need, you may be able to achieve this through different styles. But, I'm not sure.

I would love to be able to do it with different style, but i want it to feel like a completely different forum. When working with styles the main forum in the breadcrumb is always the parent forum so it doesn't look like the sub forum is the main one. know what i mean?

[boostedsti]

Originally Posted by westpointer

I've just moved one of my sites to 3.5 so I'll check it out. I don't imagine it would be much different.
That's a whole different creature. This hack doesn't impact threads or posts at all. Depending on exactly what you need, you may be able to achieve this through different styles. But, I'm not sure.

I did the same it works but cant get registration to work correctly.. It still loads the data in the wrong table..

[hugojr]

here is the error I am getting

Invalid SQL: 
		SELECT username, userid, birthday
		FROM ?.?user
		WHERE (birthday LIKE '10-30-%' OR birthday LIKE '10-31-%')
		AND usergroupid IN (0, 6, 7, 2, 5)
		
	
mysql error: 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 '?.?user
		WHERE (birthday LIKE '10-30-%' OR birthday LIKE '10-31-%')
		AND use' at line 2

mysql error number: 1064

what does this mean?

[hugojr]

please help me bump