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.

Although I personally have no use for this I just wanted to say good work! I know a lot of people will be greatful. :)

Awww man you stole my idea!!!!! Its ok though cause I am just happy to see it!

oh dear god I have waited for this for so long :S now port it to 3.5! I command you! (;) kidding, nice job)

I would love to get this working with the drupal CMS (I'm having a cookie issue at the moment)

Very nice hack.
Very nice forums.

Thanks,
Mat

can u clear up number 8 for me. i dont understand what u mean...

before i start on a hack or modification i like to know exactly whats goin on...

this has nothing to do with posts right? just user login?

What transfers with the character to both forums? All of their setting and profile (including profile pic and avatar)? Some of it? None of it?

Ideally it would seem to me you would have password and email the same while everything was per site.

What about Subscriptions are those per user or per board?

Also is that a backup copy you are using or is it really live?
Reason I ask is

Stats: Armchairgeneral
Threads: 1, Posts: 1, Members: 7,631
Welcome to our newest member, vernon.miles

Stats:strategyzoneonline
Threads: 32,469, Posts: 421,417, Members: 7,637
Welcome to our newest member, josephhnewkirk

Either way I'm digging script.
Your sites are pretty sweet too.

Great hack...

Now only if we could use separate servers for this... :nervous:

By looking at the code it looks like you can as long as the database in shared between the boards..

The only thing bad I see is if you decide to expand and add another forum IE 3, 4, 5, 6 etc just imagine how many tables would be in that DB..

Subscriptions to forums and threads would be by board. However, settings like always getting an email to a thread you start would be by user. The trick is looking at what is in the user table. That determines if the setting is by board or by user. For example, since the custom title is in the user table, it will be the same on both forums. (It cannot be different). Same for the user's signature. Since signature is in the usertextfield table (a shared table) it will be the same for both forums.
Great question! It's live! Each forum has it's own stats table so one can see the threads and posts separately for each table. The user number will be off until the nightly cron runs that updates the user count. Some weird effects here but if you dig through the tables and the code it makes sense.
Thanks!

Yes, that would be great but ... that's an entirely different beast! :nervous:

Anything that is in the user* tables is shared between the sites. So, email, sig, custom title, etc. Avatars are trickier. Since the avatar field is in user, the user will have the same avatar number for each site. However, the avatar table is not shared so ... he could have a different avatar on each site. However, he could really only control what one of them looks like! If the user has a custom avatar, he could actually control what is displayed on each site. I guess ideally the avatar table should also be shared.

The posts and threads are not impacted at all. Only the user* tables, strike, and pm* tables are shared. (I'll clear up #8 in a bit!)

The only real use I would have for this is if it totally ran like 2 distinct forums that only shared the core user information (username, password, & email).

That would be very hard to do given the current database layout.

I might be more useful if you actually created a second prefix (TABLE_PREFIX_USERS) and used this, rather than removing the prefix - most people use a prefix for a reason. :)

Yes, absolutely. You may have noticed that in my "find and replace" commands I didn't totally remove TABLE_PREFIX. Instead, where the code reads " . TABLE_PREFIX . ", I replaced that with "." One could easily substitute anything else where the dot is between the quotes. Also, if you need to go back and replace TABLE_PREFIX, you could do a "find-replace" on say ["."user] replace with [". SHARED_TABLE_PREFIX . "user].

On a Linux/Unix machine, you can use this script

Run this script from the forum root 4 times: one for "user" (as above), one for "cp", one for "pm", and one for "custom".

Of course, you still need to manually modify the scripts that use a variable like $table instead of 'user[...]' (and stuff), as stated in first post, adding USER_PREFIX where needed, and insert somewhere your definition (I inserted it in class_core.php, just below the TABLE_PREFIX one).
I also noticed a lack in the first post: if you want to also keep avatar images, you should modify image.php in line 129, adding as usual "USER_PREFIX . " before TABLE_PREFIX.

Handy little script. Note that when you run it you should be logged as the web server user. The file's owner and group will get changed to whoever you run the script as. Also, the files will have the permissions changed to whatever that users umask is set to, which will probably be 644 and okay.

I also had to change:
to:
for it to run on my Suse system.

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...

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.

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.

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.

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?

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

here is the error I am getting
what does this mean?

please help me bump

It looks like maybe you've created a a double-double quote in that sql statement. Something that looks like I'll try to locate the exact spot where this sql is coded later today but if you can search in for "birthday" in the php files under includes.

I have look everywhere can't find nothing wrong, just incase I am using version 3.0.9

Please if you can clear up your explanation of #8 a little better..

Thank You..

In /includes/functions.php: on line 1171 remove the table_prefix before $idname.
this is what it look like after according to your explination

is that correct?

also In /includes/adminfunctions: modify print_choser_row (line 1161)to check for $tableid of user, usergroups

line 1161 before code change now what I want to know is where do I add your code before or after

also In /includes/adminfunctions_user.php around line 116 (construct_style_chooser)

line 116 before code change is the code you are giving before or after the code that is there

Can you post what you have in includes/functions_databuild.php from line 840 to 846? It should look like this (assuming you've replaced " . TABLE_PREFIX . " with "."
What do you have there?

Correct, you may want to do a "." just to help you find it in the future. To be honest, I don't know for sure what else is used by this query. This may need to be wrapped in an if statement instead. Can anyone else verify if this is only used for user type queries?

this is what I have from line 840 to 846

Could you post the URL of the problem page?

Please check your pm's

Thank You

Is there anyone else that has installed this hack that can help us out we are willing to pay $$$$ please pm us if you are intrested...