Page 1 of 1

Linking two databases.

Posted: 2019-02-06 21:49
by kanklovitch
Hi Guys & Gals

It would be very handy to lookup values from a table in another database without having to use hooks. I don't have enough knowledge yet to figure out how to do it with sql.


Re: Linking two databases.

Posted: 2019-02-07 07:54
by pböttcher

you could try to grant access to the second db and the use it in a custom query.

to grant access use as root in your DB:

GRANT select ON DB2.* TO DB1@localhost;

as a custom query you can address the DB2 like:

select <tablename_db2>.<id>, <tablename_db2>.<field> from <tablename> left join <db2>.<tablename_db2> on <tablename>.<field_match> = <db2>.<tablename_db2>.<fieldmatch_db2>


<tablename_db2>.<id> is the id of the table in DB2 to use
<tablename_db2>.<field> is the fieldvalue in the table in DB2 you want to show
<tablename>.<field_match> is the matching field from your table in DB1
<db2>.<tablename_db2>.<fieldmatch_db2> is the matching field in your table in DB2

I hope that works, could not try it yet :-)

Re: Linking two databases.

Posted: 2019-02-07 19:34
by kanklovitch
Thanks for trying to help superhero, I really appreciate you taking the time. The solution you are presenting however is beyond my understanding at this point. For my level of understanding what I need is when creating a lookup and we choose a parent table we are restricted to a table within our current database. I would like to be able to choose a table right at that point from another database.

I run a busy small business and will never be able to find the time to learn php or sql although I have managed to work my way through quite a few hooks to customize my application.

To explain my situation I have a contacts database where contacts are categorized as customer, lead, friend, health, emergency and other. Since I can't access the contacts database from my customers data base in an easy way that I can understand I have had to incorporate my entire contacts db into my customers db. I would much prefer to just lookup my customers in the contacts db and keep them separate. I hope I am explaining myself so you understand my situation. Correct me if I am wrong but I would have thought that the ability to connect two separate databases easily should be essential for AppGini. Of course this is not a criticism of AppGini, it has been amazing for me to customize Northwind with my own unique management system to keep me organized.

Re: Linking two databases.

Posted: 2019-02-08 21:59
by pböttcher

I dont think AppGini is designed (at this point) to support multiple databases.

Re: Linking two databases.

Posted: 2019-02-21 18:15
by a.gneady
Connecting to 2 databases in the same app is not in our future plans currently (except for using a separate authentication database). This would lead to some unnecessary complexities. But for your specific situation (having a contacts table in a separate database), you could create a duplicate contacts table in your AppGini database and (assuming both databases are accessible through the same login), run a query similar to this in phpMyAdmin every now and then to synchronize both table (or run it through a cron job to automate it):

Code: Select all

TRUNCATE TABLE `appgini_db`.`contacts`;
INSERT INTO `appgini_db`.`contacts` SELECT * FROM `other_db`.`contacts`;
Change 'appgini_db' above to the name of your AppGini database, and 'other_db' to the name of the other database. I hope that helps.