Datatables editor, connection to external database

Datatables editor, connection to external database

tiago.fernandestiago.fernandes Posts: 13Questions: 4Answers: 0

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?

Answers

  • allanallan Posts: 62,088Questions: 1Answers: 10,180 Site admin

    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.fernandestiago.fernandes Posts: 13Questions: 4Answers: 0
    edited July 2023

    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!

  • allanallan Posts: 62,088Questions: 1Answers: 10,180 Site admin

    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.fernandestiago.fernandes Posts: 13Questions: 4Answers: 0

    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.

  • allanallan Posts: 62,088Questions: 1Answers: 10,180 Site admin

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

    Allan

Sign In or Register to comment.