Submit to 2 database tables.

[Jay Webb]

I'm using AppGini 5.70, mySql, php7.

Looking for a solution, maybe a hook or a new form and config.php
I have 2 separate databases, it's not the way I would have set it up but it's what I got.
I have AppGini working on both, and customized.
1st database is a membership roster, and, the 2nd is a family file reference, some of the table columns in both use the same
information. Is there a way, upon add new, upon submit from DB1, data is sent to DB2 with 2 fields needing column name translation.

See image for columns of DB1 and DB2, both are on the same server and domain.
CLANID and IDNUM, are the samething and are, unique, required and sequential, same with ID's.


I'm a novice at mysql and php, I have manage, but this is beyond what I could figure out.

[Jay Webb]

Well I've tried constructing a hook and would like some input, from what I have read I could use the (AS) alias statement, but would like an opinion before I try it out, I'm open to suggestions.

Code: Select all

function MembershipRoster_after_insert($data, $memberInfo, &$args){

	$q = intval($_GET['q']);

	$con = mysqli_connect('My online database server','My username','My password','Genealogy');
		if (!$con) {
			die('Could not connect: ' . mysqli_error($con));
	}
				$MTYPE=$data['MTYPE'];
				$CLANID=$data['CLANID'];
				$LASTNAME=$data['LASTNAME'];
				$FIRSTNAME=$data['FIRSTNAME'];
				$JOINED=$data['JOINED'];
					

		mysql("INSERT INTO `Genealogy`.`FF_REFERENCE` (`MTYPE`,(CLANID) AS `IDNUM`,`LASTNAME`,`FIRSTNAME`,(JOINED) AS `JOINDEDDATE`) VALUES ('{$data['MTYPE']}','{$data['IDNUM']}','{$data['LASTNAME']}','{$data['FIRSTNAME']}','{$data['JOINEDDATE']}')", $eo);
		
			mysqli_close($con);

		return TRUE;
	}

The connection is being made, tested that, but passing the input data after insert, should it be before the connection or after the connection like I have it.

[pbottcher]

Hi Jay,
not sure why you would need a hook to acomplish this task. You could use the MySQL trigger to have the data entered into your 2'nd database.

USE database1;

DELIMITER //
CREATE TRIGGER sync_insert AFTER INSERT ON MEMBERSHIPROSTER
BEGIN
INSERT INTO database2.FF_REFERENCE SET .... put here your fields and values ......
END; //
DELIMITER ;

regards
Pascal

[Jay Webb]

Hi Pascal
Well i tried what you suggested knowing nothing about mySql triggers, I used SQL fiddle and everything I tried I got a syntax errors. I know next to nothing about mysql programming.

[pbottcher]

Hi Jay,

I'll try to give you the full code. You need to replace the corresponding data.

USE %%DATABASE_WHERE_INSERT_HAPPEND%%;
DELIMITER //
CREATE TRIGGER `%%TRIGGERNAME%%` AFTER INSERT ON %%TABLE_WHERE_INSERT_HAPPEND%% FOR EACH ROW
BEGIN
INSERT INTO %%DATABASE_WHERE_SYNC_RECORDS_NEED_TO_BE_INSERTED%%.%%TABLE_WHERE_SYN_RECORDS_NEED_TO_BE_INSERTED%% SET .... put here your fields and values ......
END //
DELIMITER ;

I used this sampe on my DB and it worked perfectly. DB1 = tranzzlate1, DB2 = tranzzlate2
USE tranzzlate1;
DELIMITER //
CREATE TRIGGER `test_me` AFTER INSERT ON `sprachen` FOR EACH ROW
BEGIN
INSERT INTO `tranzzlate2`.`sprachen` (QT,QTSprache) VALUES (new.QT,=new.QTSprache)
END //
DELIMITER ;

Hope that helps