Linking two databases.

Wish to see a specific feature/change in future releases? Feel free to post it here, and if it gets enough "likes", we'd definitely include it in future releases!
Post Reply
kanklovitch
Posts: 4
Joined: 2019-02-03 17:26

Linking two databases.

Post by kanklovitch » 2019-02-06 21:49

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.

Ken

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 447
Joined: 2018-04-01 10:12

Re: Linking two databases.

Post by pböttcher » 2019-02-07 07:54

Hi,

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;
FLUSH PRIVILEGES;

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>

where

<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 :-)

kanklovitch
Posts: 4
Joined: 2019-02-03 17:26

Re: Linking two databases.

Post by kanklovitch » 2019-02-07 19:34

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.

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 447
Joined: 2018-04-01 10:12

Re: Linking two databases.

Post by pböttcher » 2019-02-08 21:59

Hi,

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

User avatar
a.gneady
Site Admin
Posts: 1004
Joined: 2012-09-27 14:46
Contact:

Re: Linking two databases.

Post by a.gneady » 2019-02-21 18:15

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.
:idea: Learn all the tips and tricks of customizing AppGini apps through our online course.

:arrow: Summary Reports plugin enables you to build powerful reports and charts inside your AppGini application through a few simple steps. See the big picture and discover trends in your data that empower you to take the right decisions confidently.

:arrow: Search Page Maker enables you to build user-friendly yet powerful search pages for your AppGini application by simply dragging and dropping the fields you want to include in search.

Post Reply