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
Veteran Member
Posts: 159
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

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

Re: Linking two databases.

Post by pbottcher » 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 :-)
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.

kanklovitch
Veteran Member
Posts: 159
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.

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

Re: Linking two databases.

Post by pbottcher » 2019-02-08 21:59

Hi,

I dont think AppGini is designed (at this point) to support multiple databases.
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
a.gneady
Site Admin
Posts: 1281
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: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

mramanathan
Posts: 17
Joined: 2020-01-07 15:58

Re: Linking two databases.

Post by mramanathan » 2020-08-13 13:53

Hi,

I have a suggestion. Try the following:

(1) In the script (not sure which one, please help), put in a code to to connect to the contact database and get the desired row(s) all using PHP PDO code.
(2) store the rows in an array and then access it as an associative array in memory in a TEMPORARY table in the main database.

I have done this with Reportico report generator under Joomla using MySQL as the main db and SQL Server DB as the the second. Advantage of this method is that in all happens within the app. Only thing is, where do i insert the code?

Hope this helps.

Post Reply