Submit to 2 database tables.

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
User avatar
Jay Webb
Veteran Member
Posts: 80
Joined: 2017-08-26 15:27
Contact:

Submit to 2 database tables.

Post by Jay Webb » 2018-03-31 07:08

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.
Image

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

User avatar
Jay Webb
Veteran Member
Posts: 80
Joined: 2017-08-26 15:27
Contact:

Re: Submit to 2 database tables.

Post by Jay Webb » 2018-04-09 03:28

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.
What we envision, we make happen.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Submit to 2 database tables.

Post by pbottcher » 2018-04-09 08:32

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
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
Jay Webb
Veteran Member
Posts: 80
Joined: 2017-08-26 15:27
Contact:

Re: Submit to 2 database tables.

Post by Jay Webb » 2018-04-10 05:36

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.
What we envision, we make happen.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Submit to 2 database tables.

Post by pbottcher » 2018-04-10 06:59

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
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

Post Reply