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.

Ken

Re: Linking two databases.

Posted: 2019-02-07 07:54
by pbottcher
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 :-)

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 pbottcher
Hi,

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.

Re: Linking two databases.

Posted: 2020-08-13 13:53
by mramanathan
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.