Wow gmanish. This is great. I will be facing this problem over the next few weeks. Did not know where to start. This at least will get my brain headed in the right direction.

Hey everybody, give gmanish some Karma for this one!

Glad to see it could be of use to someone.
In the meantime I created a few more triggers... Just to complete the roundtrip between VM, CB and PHPBB.
Please see the attached zip file for the latest code for all the integration triggers. These would of course need some obvious changes per your setup (database name and your cb custom field names etc.)



Post edited by: gmanish, at: 2008/08/18 06:56

Post edited by: gmanish, at: 2008/08/18 06:57

#gmanish

that's quite amazing, cuz thats exactly what I need.

I'm setting up a site in J.1.5, VM.1.1.2, CB 1.2RC2 and phpBB3.0.2 - exactly like you.

Even though Im familar with sql and etc, I have never heard of a "trigger".

I wish I could just copy your bridge solution. Is Rokbridge needed for this to work?

the only different between me and your brigde is, that my users arent able to create account, since its a "paid" membership - so admins will have to create the accounts.
Does that change anything according to these triggers?

Thanks in advance

Thanks so much, gmanish! You helped me work around an impasse.

Having established the triggers, I now have a small problem that I think you could steer me straight on (as it seems to me that you know your SQL).

The problem is that, when the trigger is set on the comprofiler table, the initial 'Confirm your Registration' email is not sent by the system. However, when the new user tries to log on, the email is resent just fine.

If I drop the trigger, the initial email is fired off immediately, so there must be something in the trigger interfering with the process.

I did need to modify your original code a bit to account for the differences in our databases... the biggest issue being in Country names, as I use USA and not 'United States' as in VM.

[code:1]
create trigger `Mydb`.`sync_vm` AFTER INSERT on `Mydb`.`jos_comprofiler`
for each row BEGIN

DECLARE MY_FNAME VARCHAR(64) ;
DECLARE MY_LNAME VARCHAR(64) ;
DECLARE MY_EMAIL VARCHAR(64) ;
DECLARE MY_STATE_2_CODE VARCHAR(64) ;
DECLARE MY_STATE VARCHAR(64) ;
DECLARE MY_COUNTRY_3_CODE VARCHAR(64) ;
DECLARE MY_COUNTRY_ID INT(11) ;


IF not exists (SELECT * FROM jos_vm_user_info WHERE user_id = NEW.id AND address_type = 'BT') Then

SELECT substring_index(name,' ',1), substring_index(name,' ',-1), email INTO MY_FNAME, MY_LNAME, MY_EMAIL
from jos_users where id = NEW.id;

SELECT country_3_code, country_id INTO MY_COUNTRY_3_CODE, MY_COUNTRY_ID
FROM jos_vm_country
WHERE country_name = NEW.cb_mailingcountry ;

IF (MY_COUNTRY_3_CODE IS NULL) Then SET MY_COUNTRY_3_CODE = 'USA' ;
END IF ;

SELECT state_2_code INTO MY_STATE_2_CODE
FROM jos_vm_state
WHERE state_name = (SELECT SUBSTRING_INDEX(NEW.cb_mailingstate,' - ',1)) ;
/*AND country_id = MY_COUNTRY_ID;*/

INSERT INTO jos_vm_user_info (user_info_id, user_id, address_type, address_type_name, last_name, first_name, phone_1, phone_2, address_1, address_2, city, state, country, zip, user_email, perms) VALUES (concat('fromcb-', NEW.user_id), NEW.user_id, 'BT', '-default-', MY_LNAME, MY_FNAME, NEW.cb_primaryphone, NEW.cb_secondphone, NEW.cb_mailingstreet, NEW.cb_mailingapt, NEW.cb_mailingcity, MY_STATE_2_CODE, MY_COUNTRY_3_CODE, NEW.cb_mailingzip, MY_EMAIL, 'shopper');

END IF;
END;
[/code:1]

I don't know if you can spot any 'howlers' in my code or you have any ideas as to what might be happening, but I would sincerely appreciate your help.

Thanks - Anton

Post edited by: antonstewart, at: 2008/12/15 15:42

Hello anton,

What version of Joomla/Cb/phpBB do you use?

Hi pashmak,

You're right - it probably would have been a good idea to include that information

My setup is Apache 2.2/php 5.2.6 (with installed GD2)/MySQL 5.0.45 running on Mac OS X Server 10.5.5 - Joomla 1.5.7, CB 1.2 RC4, VirtueMart 1.1.2 stable and Fireboard 1.0.5 RC2

HTH - Anton

Post edited by: antonstewart, at: 2008/12/15 16:02