Can you split phpBB's Database?

  -----  
Author Message

DarkScythe
Member
Wed Sep 06, 2006 12:02 am   Post subject:
All right!
I think I might have a working code. I've removed all the default values, since they'll be automatically assigned (hopefully) which shortens my code a huge deal. I've no idea if there are any errors though, so if I could get a quick proofread, I can stick it into the phpbb registration page and do some tests. Edited out username for security purposes, but it shouldn't affect anything.

Come to think of it, I'll also need to stick this into wherever the user can change his password/email in phpbb.

Code:


   // ------------------------------------
   // Insert EE-Specific Registration Code
   // ------------------------------------
   // Generate Proper member_id, since it differs from phpBB's user_id

      $sql = "SELECT MAX(member_id) AS total
         FROM " . ********_eeDB.exp_members;
      if ( !($result = $db->sql_query($sql)) )
      {
         message_die(GENERAL_ERROR, 'Could not obtain next member_id information', '', __LINE__, __FILE__, $sql);
      }

      if ( !($row = $db->sql_fetchrow($result)) )
      {
         message_die(GENERAL_ERROR, 'Could not obtain next member_id information', '', __LINE__, __FILE__, $sql);
      }
      $member_id = $row['total'] + 1;
   
   // Generate the unique_id required by ExpressionEngine
      
      $unique_id = sha1(uniqid(mt_rand()));
      
   // Generate authcode Possibly required by ExpressionEngine ** TEST THIS
   // -------------------------------
   // UPDATE: authcode is NOT NEEDED
   // It is placed into the table when a user registers from EE, and needs email verification.
   // The authcode is removed once the user verifies.
   // Leaving the code here for legacy purposes and future reference.
   // -------------------------------
   /*   
      $authcode = '';
      $pool = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
      for ($i=0; $i < 10; $i++)
         {
            $authcode .= substr($pool, mt_rand(0, strlen($pool) -1), 1);
         }
   */

   // Generate and Store IP address, not sure on phpBB's system - this is a precaution.

      $ip_address = $_SERVER['REMOTE_ADDR'];
   
   // Insert relevant data into main membership table

         $sql = "INSERT INTO " . ********_eeDB.exp_members . " (member_id, group_id, username, screen_name, password, unique_id, email, ip_address, join_date)
            VALUES ($member_id, 5, '" . str_replace("\'", "''", $username) . "', '" . str_replace("\'", "''", $username) . "', '" . str_replace("\'", "''", $new_password) . "', $unique_id, '" . str_replace("\'", "''", $email) . "', $ip_address, " . time() . ")";
         if( !($result = $db->sql_query($sql)) )
         {
            message_die(GENERAL_ERROR, 'Could not insert data into exp_members table', '', __LINE__, __FILE__, $sql);
         }
        
   // Insert member_id into member_data DB
    
         $sql = "INSERT INTO " . ********_eeDB.exp_member_data . " (member_id)
            VALUES ($member_id)";
         if( !($result = $db->sql_query($sql)) )
         {
            message_die(GENERAL_ERROR, 'Could not insert member_id into exp_member_data table', '', __LINE__, __FILE__, $sql);
         }
      
   // Insert member_id into member_data-homepage DB as well
      $sql = "INSERT INTO " . ********_eeDB.exp_member_homepage . " (member_id)
         VALUES ($member_id)";
      if( !($result = $db->sql_query($sql)) )
         {
            message_die(GENERAL_ERROR, 'Could not insert member_id into exp_member_homepage table', '', __LINE__, __FILE__, $sql);
         }

 

Thoul
Administrator
Wed Sep 06, 2006 1:40 pm   Post subject: re: Can you split phpBB's Database?
It looks good. The only thing I would change is how you're using the table names in the queries. For example, you have:

Code:

      $sql = "SELECT MAX(member_id) AS total
         FROM " . ********_eeDB.exp_members;


I recommend moving the table name inside the double quotes, making it:

Code:

      $sql = "SELECT MAX(member_id) AS total
         FROM ********_eeDB.exp_members";


I know phpBB does it the way you have it currently, but they also define their table names as constants. It should work the way you have it, but it might generate PHP warning errors.
 

DarkScythe
Member
Wed Sep 06, 2006 2:02 pm   Post subject: re: Can you split phpBB's Database?
Hmm, all right, I'll do that, the less errors the better Smile

Working on the EE-side script now, it's a bit more cumbersome since EE has all their variables stored inside $data['blah'] things and mess with them everywhere.
I'm trying to locate the "final" iterations of them right as they're being inserted into the database so I can assign them to phpBB-friendly varaibles.

Speaking of phpBB, I think the only things I need are
phpbb_users:
user_id, user_active, username, user_password, user_regdate, user_email
phpbb_user_group:
group_id, user_id, user_pending
phpbb_groups:
group_id, group_name, group_description

If I missed any fields, please tell me, otherwise I think these will be easy enough to get from the EE registration form.

edit: one last question on the previous code, is that trailing period supposed to be there after time()? It looks like it's trying to include something else that's not there, since it's the end of the list, but phpBB seems to have the trailing periods.. I'm not sure if I need them there or not.
 

Thoul
Administrator
Wed Sep 06, 2006 2:38 pm   Post subject: re: Can you split phpBB's Database?
For phpbb_users, you will also need:
user_style, which is a number for a style in the phpbb_themes table.
user_lang, which is typically a value of "english" for English boards.
There are several other fields in phpbb_users that don't have defaults, but I don't think you need those. You'll need the two above for the forum to work for each user that registers through EE.

For phpbb_groups, you don't need group_id. It can be done automatically... but you will need to get that same number to use as the group_id in phpbb_user_group.

Quote:

one last question on the previous code, is that trailing period supposed to be there after time()? It looks like it's trying to include something else that's not there, since it's the end of the list, but phpBB seems to have the trailing periods.. I'm not sure if I need them there or not.


Yeah, you need that. It brings the trailing ) into the query, which you need.
 

DarkScythe
Member
Wed Sep 06, 2006 2:43 pm   Post subject: re: Can you split phpBB's Database?
All right, thanks Smile

It's pretty easy to make the code for EE's script now, since I'm just mirroring the code from phpBB, and we know what to do. I'll add those fields in right now.

I assume you need quotes around words when assigning values?

For example, group_description doesn't have a defualt value, but the tables list everyone having the description "Personal User" so I've done this

Code:


   // Insert Group Data into groups DB
   
         $sql = "INSERT INTO " . ********_forumsDB.phpbb_groups . " (group_id, group_description)
            VALUES ($group_id, 'Personal User')";
         if( !($result = $db->sql_query($sql)) )
         {
            message_die(GENERAL_ERROR, 'Could not insert Group Data into phpbb_groups table', '', __LINE__, __FILE__, $sql);
         }
 

Thoul
Administrator
Wed Sep 06, 2006 6:37 pm   Post subject: re: Can you split phpBB's Database?
Yeah, exactly. That looks great. Smile
 

DarkScythe
Member
Thu Sep 07, 2006 9:07 pm   Post subject: re: Can you split phpBB's Database?
Hmm, all right, well I finished off the code, and inserted the EE registration code into phpBB's usercp_register.php file and gave it a test run.

Unfortunately, I ran into a problem.. either it couldn't access the DB or it suggests a syntax problem. I don't have a manual to refer to though, so I'm not sure where I'm supposed to look lol

Code:


Could not insert data into exp_members table

DEBUG MODE

SQL Error : 1064 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 '.121.146, 1157681002)' at line 2

INSERT INTO ********_eeDBexp_members (member_id, group_id, username, screen_name, password, unique_id, email, ip_address, join_date) VALUES (5, 5, 'SpecialTest1', 'SpecialTest1', '1c58bd92003bbaa0538e249fff6ee19a270dec5f', aa2211189dde53791904673c9eb06ad2c0b1074d, 'blaze@darkscythe.com', 151.205.121.146, 1157681002)

Line : 706
File : usercp_register.php


As far as I know, where it says "use near", it's the IP address and regdate being inserted after parsing the variables for the "real" data. Maybe the dots in the IP threw it off and I should wrap some quotes around it? Or is there another reason for this..
 

Thoul
Administrator
Thu Sep 07, 2006 10:09 pm   Post subject: re: Can you split phpBB's Database?
Yeah, you're right on the money there. It just wants some quotes around the IP. I would probably put them around the unique id too, just to be on the safe side.
 

DarkScythe
Member
Thu Sep 07, 2006 10:10 pm   Post subject: re: Can you split phpBB's Database?
All right, let me revert back to the original code and apply that lol, I tried some other things that didn't work and now the code is a mess Super Razz

Also had to delete the user from thr phpBB database a couple times but now the user_id and group_id aren't the same anymore.. where else does it keep the group id thing?
 

DarkScythe
Member
Thu Sep 07, 2006 10:17 pm   Post subject: re: Can you split phpBB's Database?
Whoops, fixed one problem, got another lol.

This one is an odd one, but I think I know the cause.. (Dunno how to fix, though)

Code:


Could not insert data into exp_members table

DEBUG MODE

SQL Error : 1146 Table '********_forumsDB.********_eeDBexp_members' doesn't exist

INSERT INTO ********_eeDBexp_members (member_id, group_id, username, screen_name, password, unique_id, email, ip_address, join_date) VALUES (5, 5, 'SpecialTest1', 'SpecialTest1', '1c58bd92003bbaa0538e249fff6ee19a270dec5f', 'a396910a5dba9f1e860144f1887661b88e13d38e', 'blaze@darkscythe.com', '151.205.121.146', 1157685196)

Line : 706
File : usercp_register.php


It appears to think that the database is a table from the first DB.. Looking it over, the code seems to have quotes around INSERT INTO and then the DB.table setting is in php, which is probably why the . has disappeared..
I could probably remove the "extra" quotes and make the DB.table command part of the SQL query, but I think it's still trying to find it as a table within phpBB's database..
Unless I'm wrong here, shouldn't I have to close the current DB connection, or at least open a new one to access EE's DB temporarily? Or is there something else I'm missing :/
 

Page 3 of 5
Go to page Previous  1, 2, 3, 4, 5  Next
Display posts from previous: