leftJoin two different tables in different databases

Jenniilein1991

Hello everyone :)
i just searched for a while to solve this problem but no success.
Here my code:

config.php

$sql_details = array(
    "type" => ..
    "user" => ..
    "pass" => ..
    "host" => ..
    "port" =>..
    "db"   => "anfragenmanagement",
    "dsn"  => ..
);

staff.php

Editor::inst( $db, 'anfragen' )
    ->fields(
        Field::inst( 'anfragen.AnfrageID' ),
        Field::inst( 'anfragen.Kunde' ),
        Field::inst( 'anfragen.Ansprechpartner' ),
        Field::inst( 'anfragen.KAPMitarbeiter' ),
        Field::inst( 'anfragen.Beschreibung' ),
        Field::inst( 'anfragen.Datum' ),
        Field::inst( 'anfragen.Status' ),
        Field::inst( 'anfragen_status.ID' )
    )
    ->leftJoin( 'anfragen_status', 'anfragen_status.Status', '=', 'anfragen.Status' )
    ->process( $_POST )
    ->json();

It works fine but in addition i want to leftJoin this "anfragenmanagement.anfragen" table with a table of an other database called "projektdatenbank.members" . Both databases have the same sql configuration.
I can connect to another database through the config.php and Bootstrap.php but i dont know how to put this information into the staff.php. I hope someone can help :)

Thanks. Jenni

allan

Hi Jenni,

Thanks for your question. It should just be a case of prefixing the database names in front of the table names, in exactly the same way as you have prefixed the table names in front of the fields. For example db1.anfragen for the Editor instance and db1.anfragen.AnfrageID for the fields, etc.

Allan

Jenniilein1991

Hi allan,
thanks for your fast answer. :)
Is it right that i can just have one database connection for each editor instance?
In the code above i have $db as one database connection and access the table "anfragen" in this database. So when i want a second connection to another database i need to create a second editor instance or is it possible to access two databases in one editor instance?
I can create a second connection like this:

$sql_details2 = array(
    "type" => ..
    "user" => ..
    "pass" => ..
    "host" => ..
    "port" =>..
    "db"   => "projektdatenbank",
    "dsn"  => ..
);

and create with this connection details in the Bootstrap.php a variable $db2.
Can i access $db and $db2 in one editor instance?

Thanks
Jenni

allan

Is it right that i can just have one database connection for each editor instance?

Yes, however, it could have access to multiple databases if the SQL server is set up that way. The db property is just the default database.

Allan

Jenniilein1991

Hmm. I understand what you are writing but i'm very confused how i can access the tables of $db2 in this editor instance.
i tried this:

Editor::inst( $db, 'anfragen' , $db2 , 'members')
    ->fields(
        Field::inst( 'anfragen.AnfrageID' ),
        Field::inst( 'anfragen.Kunde' ),
        Field::inst( 'anfragen.Ansprechpartner' ),
        Field::inst( 'anfragen.KAPMitarbeiter' ),
                Field::inst( 'anfragen.KAPMitarbeiterID' ),
        Field::inst( 'anfragen.Beschreibung' ),
        Field::inst( 'anfragen.Datum' ),
        Field::inst( 'anfragen.Status' ),
        Field::inst( 'anfragen_status.ID' ),
                Field::inst( 'anfragen.FANummer' )
    )
    ->leftJoin( 'anfragen_status', 'anfragen_status.Status', '=', 'anfragen.Status' )
    ->where('anfragen.KAPMitarbeiterID' , $user_id )
    ->process( $_POST )
    ->json();

OR

Editor::inst( $db, 'anfragen')
    ->fields(
        Field::inst( 'anfragen.AnfrageID' ),
        Field::inst( 'anfragen.Kunde' ),
        Field::inst( 'anfragen.Ansprechpartner' ),
        Field::inst( 'anfragen.KAPMitarbeiter' ),
                Field::inst( 'anfragen.KAPMitarbeiterID' ),
        Field::inst('projektdatenbank.members.id' ), <----------
        Field::inst( 'anfragen.Datum' ),
        Field::inst( 'anfragen.Status' ),
        Field::inst( 'anfragen_status.ID' ),
                Field::inst( 'anfragen.FANummer' )
    )
    ->leftJoin( 'anfragen_status', 'anfragen_status.Status', '=', 'anfragen.Status' )
    ->where('anfragen.KAPMitarbeiterID' , $user_id )
    ->process( $_POST )
    ->json();

but it just will not work :(
When i have a second database connection in $db2 , how can i access the tables and fields?

Many thanks in advance
Jenni

allan

Editor::inst( $db, 'database1Name.anfragen')
  ->fields(
        Field::inst( 'database1Name.anfragen.AnfrageID' ),
// etc

Just prefix the database name in front of all the tables names like you would in an SQL query.

Allan

Jenniilein1991

Hey allan,
i unterstand it but in your presented code, how can i access for example database2Name.members?

Thanks in advance
Jenni

allan

Just exactly as you have done. With the database name prefixed. If that isn't working for you can you show me your full PHP and a link to the page you are working on please.

Allan