Datatables editor, connection to external database

tiago.fernandes

I have the following code on a controller.

include( "../lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

Editor::inst( $db, 'orders', 'order' )
    ->readTable('ic2023')
    ->fields(
        Field::inst( 'order' )->set( false ),
        Field::inst( 'truck' ),
        Field::inst( 'trailer' ),
        Field::inst( 'customer_id' )
            ->options( 'toc_customers', 'tax_registration_number', 'business_name' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'supplier' ),
        Field::inst( 'person' ),
        Field::inst( 'tlf' ),
        Field::inst( 'driver' ),
        Field::inst( 'reference' ),
        Field::inst( 'amount' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'invoicenr' ),
        ...

This connection is being done to a mySQL database.
On the field customer_id I would like to connect it into a external database (SQL SERVER). Meaning I want to show the options of a external table who has identical data configuration.

Is there a way to connect to other database through the controllers?

allan

Unusual .

Usually such a cross link wouldn't be possible, however, in this case if it is just a list of options, what to do is instead of using:

->options( 'toc_customers', 'tax_registration_number', 'business_name' )

Use a function to get the options. Since it is an option, you can get the data from anywhere, including an external database connection. Just make sure you return an array of associative arrays that contain value and label properties (like in the linked documentation).

Allan

tiago.fernandes

Hi allan,

I don't know if I explain myself correcty this cross link isn't connected into a external database, it's connected to a dummy table inside my mysql database. My idea is to replace this code into one that connects to an external table.
I'll take a look into the custom function.
Thank you!

allan

this cross link isn't connected into a external database, it's connected to a dummy table inside my mysql database

I don't quite understand I'm afraid. Your first post suggested your main table was MySQL, and you wanted to get the options list from an SQL Server db. Is that not the case?

Allan

tiago.fernandes

Yes that was the case, sorry if I confused you.
I solved the problem by using a sqlsrv_query inside a closure function, as you suggested.
Working code below:

Field::inst( 'customer_id' )
        ->options( function () use ($conn) {
            $sql = "SELECT ID, Name FROM.[dbo].[Customers] ORDER BY Name ASC";
            $stmt = sqlsrv_query( $conn, $sql );
            $data = array();
            while( $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC) ) {
                $data[] = array(
                    'value' => $row['ID'],
                    'label' => $row['Name ']
                );
            }
            return $data;
        }),

Thank you for you help.

allan

Nice one - glad to hear you've got it working!

Allan