Seperate user and forum database.

[MrLeN]

This is a popular request that I've seen on other forums, time and time again. I have actually asked this of all the other forum software that I have used, but each time I get told it's way to hard. Just not possible. I'm dreaming. Forget it. etc..

I have over a dozen SMF's running. I want all of my board to have one username/password database. I wanted this with IPB (no go). I wanted it with phpBB (no go), vBulletin (no go).

How hard could it be? I mean, how card could it be to just create a forum that has two databases? One for the forums, etc and one for the users/passwords?

If you make SMF like this, then the WHOLE INTERNET will flock to SMF. I have absolutely no doubt. Now, if this can't be done, how can I arrange for it to be done?

I want all of my SMF's to use a single username and password database, so that people don't have to register again and again. It really couldn't possibly be all that hard, could it? I mean, to someone who knows how to do it? Exactly how much work would it require?

For example, I can get mkportal to run from any one of my SMF's. So if that can be done, why couldn't I say: Install one SMF and use it just as a database. Then install 12 others and modify them, so that they are using their own post/forum databases, but the master database for the username/password's?

If this can be done, I'll be about as happy as Larry.

MrLeN

[MrLeN]

Ok, I am going to try and do this myself..

I am going to log everything I do here, and eventually maybe I'll have a tutorial of sorts.

Now, I have a dozen forums to consider. One of them I have to convert from VB. The rest of them I have to double check to see if they are all the same version, and upgrade any that are old.

That would be a good start, so I'm off to do that now..

I'll come back and post here when I'm done. If anyone else has any suggestions/advice, please don't be shy. I'm going to need all the help I can get.

MrLeN

[MrLeN]

Since I am using mkPortal, I am going to have to take that into consideration as well.

http://mkportal.it/support/showthread.php?p=40047#post40047

I am also going to upgrade all my mkPortals.

MrLeN

[dschwab9]

Why would you need 2 separate databases?   Just put all the forums in one database with different table prefixes and a common user table.

[MrLeN]

Yeah, I've kind of figured that far now - thanks

Sorry, I was trippin'..

Now also, I have to amalgamate all of my existing usernames and passwords for 12 forums and install them all into the main database.

Donno how I am gonna do that yet..

But it should be done before I go any further.

MrLeN

[MrLeN]

Ok, I think I've found out which prefixes I need to change. Can you think of any more?

http://www.simplemachines.org/community/index.php?topic=12010.msg347942#msg347942

members
instant_messages
im_recipients
banned
Edit: Actually, don't need membergroups
permissions
log_banned
log_karma

MrLeN

[orange]

Why would you need 2 separate databases?   Just put all the forums in one database with different table prefixes and a common user table.

That's very messy though, and makes backing up the database a nightmare because having 12 forums in it would make it huge.

It would be neat if SMF could optionally connect to a seperate database for the user information.

[MrLeN]

I don't care how I get it working. I just want it working. So I'm on a mission. I don't really know what I'm doing, so I'll just have to learn as I go. Don't know much about databases. So I need help. That's why I am logging everything here. If I'm doing something that makes someone laugh, please just tell me

MrLeN

[MrLeN]

Can anyone tell me, what do I replace {$db_prefix} with?

I can work out that $db_prefix is smf_

But all of my databases are called smf_

I think I have just come to a point of understanding that dschwab9 was implying that I should rename all of my smf_ tables to something else, maybe like site1smf_ , site2smf_ etc and then merge all of those databases into one database, and then to an S&R.

I was kind of hoping that I could get SMF to just use it's own database for everything, except for the user/member related stuff. But I now realise that I'll have to get the script to connect to a main database (also), which is under a different domain.

Problem is, that the script connects to:

localhost
user_smf
user_smf
password

So If I start trying to direct parts of the script to:

anotheruser_smf, {$db_prefix}members

Then it's just not going to (unless, somehow I make it).

Ok, this isn't so easy. I wont give up though, but yeah. Not so easy. Doh!

MrLeN

[MrLeN]

Ok, I think I have to first of all find:

$db_connection = @mysql_connect($db_server, $db_user, $db_passwd);

and add:

$db_connection = @mysql_connect($db_mainserver, $db_mainuser, $db_main password);

Then I have to S&R

{$db_prefix}

and replace with

mainuser_smf_

for the things I want to use:

mainuser_smf_members
mainuser_smf_instant_messages
mainuser_smf_im_recipients
mainuser_smf_banned
mainuser_smf_permissions
mainuser_smf_log_banned
mainuser_smf_log_karma


Lol, that probably makes absolutely no sense. But hey, I'm trying. I'll figure it out

MrLeN

[MrLeN]

Ok, I have to try and get the script to connect to two databases at once.

I don't know if I should change settings.php to:

$db_server = 'localhost', 'anotherhost';
$db_name = 'smf';
$db_user = 'root';
$db_passwd = 'both hosts have same password';
$db_prefix = 'smf_';
$db_persist = 0;
$db_error_send = 1;

or

$db_server = 'localhost';
$db_name = 'smf';
$db_user = 'root';
$db_passwd = 'password';
$db_prefix = 'smf_';
$db_persist = 0;
$db_error_send = 1;

else..

$db_server = 'anotherhost';
$db_name = 'smf';
$db_user = 'root';
$db_passwd = 'both hosts have same password';
$db_prefix = 'smf_';
$db_persist = 0;
$db_error_send = 1;

..or what.  So I have to try and work that out.

Or maybe that's not possible. Maybe I just have to make a new settings.php and make the member/pass related pages connect to that seperately. But then again, those pages probably "also" ahve to connect to two servers. I've got a headache.

Now I see why no one ever wants to do this, lol

But if I knew how I wouldn't have a problem. People are just lazy. I'll just have to work it out. Don't think I'm gonna get much help. Also, this tutotial is going to turn into 97,000 posts (I can see that coming).. so instead of listing everything that doesn't work. I'll try to just list things that do.

No one is anwering any of my questions, so I guess I'm on my own.

If anyone wants to steer me in the right direction, go for it. If you don't know what your talking about, please don't post or you'll confuse me and set me back.

MrLeN

[MrLeN]

Man I've wanted this for over two years!

If you want something done properly, you gotta do it yourself.

It's probably going to take me six months to work out how to do all this, because I don't know jack about databases.

See you all then.

MrLeN

[Captain_Morrigan]

Well I have two user databases that I enter my information into. Click on the link below to look at my site if you wish. But I have one registration form for both sites and it enters all the information into both completely duplicate.

I haven't went into advanced features of having each profile form update which I could do but don't feel like it. I believe it's as simple as this:

Create your single registration form with what information you require, make sure that the information that you require is what you want to have immediately in the databases when the user signs up.

Now for each database that you want to have the universal registration in do the following type coding:

Code Select Expand

<?php
$conn = mysql_connect( CONNECTION INFORMATION )
or die( "Error! Could not connect to database: " . mysql_error() );

// select the database
mysql_select_db( 'DATABASE1', $conn )
or die( "Error! Could not select the database: " . mysql_error() );




// Check for empty fields add to this for any addtional feilds that you need to make sure they were not empty of course.
if (empty($name) || empty($email) || empty($password))
{
die ("Error. Please fill in all required fields."); // once a die statement is execute, the whole script stops executing
}

//this is verification that there is no duplicate usernames or emails in DATABASE1 which you would base most of your information on since they will all have the same information.
$query = "SELECT username, email from members WHERE username='$name' or email='$email'";
$result = mysql_query($query) or die ("Could not execute query : $query." . mysql_error());
$rowCount = mysql_num_rows($result);



if ($rowCount != '1' )
{

// Next check that the email address entered is a valid format
if (!(ereg ("^.+@.+\..+$", $email)) )
{
die ("Error. $email does not look like a valid email address.");
}

// Now email the user his registration details
$from = "EMAIL";
$subject = "Your Registration";

$headers = "MIME-Version: 1.0\r\n";
$headers = "From: Name<$from>\n";
$headers .= "Content-type: text/html; charset=iso-8859-1\r\n";

$message = "Message information.";

if (mail($email,$subject,$message,$headers)) // if mail is successful
{
// Store data into database
$q = "insert into DATABASE1 (id, realname, username, email, status, code, dateadded, password, websitename, websiteurl) VALUES ('','$realname','$name','$email','N','$code', now(), '$password', '$websitename', '$websiteurl' )";
$rs = mysql_query($q) or die ("Could not execute query : $q." . mysql_error());

//This closes the intial connection with DATABASE1 and makes it to where you can open a connection with DATABASE2.
mysql_close($conn);

$connection = mysql_connect( CONNECTION INFORMATION)
or die( "Error! Could not connect to database: " . mysql_error() );

// select the database
mysql_select_db( 'DATABASE2', $connection )
or die( "Error! Could not select the database: " . mysql_error() );

//This enters the same information into the second database.

$query = "insert into DATABASE2 (ID_MEMBER, realName, memberName, emailAddress, dateRegistered, passwd, websiteTitle, websiteUrl) VALUES ('','$realname','$name','$email', time(), '$password', '$websitename', '$websiteurl' )";
$rs2 = mysql_query($query) or die ("Could not execute query : $query." . mysql_error());

//From here just continue to close and open new connections with your multiple databases until you have all 12 of them here. You will have to test it to make sure all databases have the correct information and let you log in.

if ($rs2)
{
echo "Confirmed Registration message.";
}

}
}

//Error if the username or email already exist.
else
{echo ("Your username or password is already registered.");}
include($footervar.$skin.$extension);
?>

If you are using SMF (assuming you are) you will need to put early on in the coding the following:

Code Select Expand

$password = md5($password);


This will make sure that your passwords encrypt in your database from what the person entered in the registration form.

If this doesn't work tell me and I'll about explaining it in a different way.

Also, if you want one profile for all of them then you will do the same thing with the profile information, just have it update all of the databases from one form.

[Captain_Morrigan]

I forgot one last thing, if you plan to continue on with your plan of having each forum in one database you will only need to change the forum and the post information. The only thing you will not have to modify per one is the user information since you want it all to be universal.

I suggest my way. Less hassle of switching where the databases are and less coding for you in the long run. I can tell you that mine does work because it works successfully on my site.

For verification of this I will give you access to my test account just send me a PM and I will give you the login information for both sites.