Sharing Hashes between Mambo 4.x and SugarCRM 4.5.1 Using Views

Its been a while since i posted an article, busy months have passed and im still within timeframes and deadlines.. well most of the time real life intervenes eh? anyways heres my article about Sharing Hashes between Mambo 4.x and SugarCRM 4.5.1 Using MySQL Views

MySQL Views is(are) indeed a wonder, too bad its only available to MySQL versions 5 and up (which nowadays arent offered yet by most shared hostings). Its really a timesaver to be able to construct a table out of a select query without having to build one and have it updated for a function to refer on.

In this article, I will outline simple procedures to create a User Table from within a SugarCRM Database which will hook hashes from a Mambo user table, the aftereffect are the following:

• Both Mambo and SugarCRM systems will refer to the same user MD5 password hashes, causing passwords between to be the same
  
• When a User logs into either of the system, he/she will use the same password, and changing the password from the Mambo system will also change the password in SugarCRM without the need to keep it synced

Basically, the following are the prerequisites:

• MySQL 5
• Both Mambo and SugarCRM must reside on the same Database Server Account
• There should be a common user between the system with the same username, this means that there should be a user with the same username that exitst in both User Tables of SugarCRM and Mambo.

Procedures:

1. Set up the View using the following construct within the SugarCRM Database:
   

   CREATE VIEW users_auth
   AS
   SELECT
   `e.id` AS `id`,
   `d.username` AS `user_name`,
   `d.password` AS `user_hash`,
   `e.authenticate_id` AS `authenticate_id`,
   `e.sugar_login` AS `sugar_login`,
   `e.is_admin` AS is_admin,
   `e.status` AS `status`,
   `e.portal_only` AS `portal_only`,
   `e.is_group` AS `is_group`
   FROM
   mambo_database.mos_users d
   LEFT JOIN sugarcrm_database.users e
   on d.username = e.user_name;

   The above query creates a MySQL View named "users_auth" and gets username and password info from the Mambo database and creates it within the SugarCRM database. Examine the query and modify as to your needs
   


2. Next Step is to modify the "SugarAuthenticateUser.php" file located in the sugarcrm/modules/Users/authentication/SugarAuthenticate directory so all select queries pertaining to the sugarcrm.users table will point to sugarcrm.users_auth view you have previously created. These are lines 43 and and 63 and they should appear as follows

   43: $query = "SELECT * from users_auth where...

   and
   

   63: $query = "SELECT * from users_auth where...

Thats it, the concept is much self-explanatory. If you have further specific questions, feel free to post comments and ill cover it in detail