Select Drop Down List contains all values of parent table. How can I get just child table values?

Ovgmw7

Simple database with three tables

suppliers - a master table of suppliers for all users
my_suppliers - a table of suppliers selected by the user with additional data i.e. MySupplierNumber
my_contacts - a table containing multiple contacts for my_suppliers

The business logic /workflow is

User selects a supplier and adds it to my_suppliers

User adds multiple contacts for each of their my_suppliers

suppliers and my_suppliers are linked on SupplierID
my_suppliers and my_contacts are linked on MySupplierID

Foreign Key constraints exist to prevent:-

adding a my_suppliers record if not in suppliers (SupplierID)
adding a my_contacts record if not in my_suppliers (MySupplierID)

Therefore to display the SupplierName in the my_contacts Datatable two links are required. First to the my_suppliers table and then to the suppliers table where the field SupplierName exists.

Link to test case:

Live web pages:-

https://wastesaver.org/DataTablesEditor/suppliers.html
https://wastesaver.org/DataTablesEditor/my_suppliers.html
https://wastesaver.org/DataTablesEditor/my_contacts.html

Description of problem:

Everything works great apart form the NEW and UPDATE function on my_contacts

https://wastesaver.org/DataTablesEditor/my_contacts.html

The Select Drop Down List contains all the SupplierID + SupplierName values from the suppliers table.

I need this Select Drop Down List to be populated with just the MySupplierID + SupplierName values that exist in the my_suppliers table. Otherwise the user can select a supplier that is not present in the my_suppliers table throwing an SQL Foreign Key error and not following the business logic of the application.

I've been trying to solve this for days with no joy. If anybody can point me in the right direction I'd be very grateful!

php code for my_contacts is:-

<?php

/*
 * Editor server script for DB table my_contacts
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
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;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'my_contacts', 'MyContactID' )
    
    ->fields(
        Field::inst( 'my_contacts.MySupplierID' ),
            
        Field::inst( 'my_suppliers.SupplierID' )
            ->options( Options::inst()
                ->table( 'suppliers' )
                ->value( 'SupplierID' )
                ->label( 'SupplierName' )
            )
            ->validator( Validate::dbValues() ),
        Field::inst( 'suppliers.SupplierName' ),
        
        Field::inst( 'my_contacts.MyContactName' )
    )
    ->leftJoin( 'my_suppliers', 'my_suppliers.MySupplierID', '=', 'my_contacts.MySupplierID' )
    ->leftJoin( 'suppliers', 'suppliers.SupplierID', '=', 'my_suppliers.SupplierID' )
    
    ->process( $_POST )
    ->json();

And JS code:-

/*
 * Editor client script for DB table my_suppliers
 * Created by http://editor.datatables.net/generator
 */

(function($){

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: 'php/table.my_suppliers.php',
        table: '#my_suppliers',
        fields: [
            {
                "label": "SupplierName:",
                "name": "my_suppliers.SupplierID",
                "type": "select",
                "placeholder": "Select a Supplier"
            },
            {
                "label": "MySupplierNumber:",
                "name": "my_suppliers.MySupplierNumber"
            }
        ]
    } );

    var table = $('#my_suppliers').DataTable( {
        ajax: 'php/table.my_suppliers.php',
        columns: [
            {
                "data": "suppliers.SupplierName"
            },
            {
                "data": "my_suppliers.MySupplierNumber"
            }
        ],
        select: true,
        lengthChange: false
    } );

    new $.fn.dataTable.Buttons( table, [
        { extend: "create", editor: editor },
        { extend: "edit",   editor: editor },
        { extend: "remove", editor: editor }
    ] );

    table.buttons().container()
        .appendTo( $('.col-md-6:eq(0)', table.table().container() ) );
} );

}(jQuery));

rf1234

The Select Drop Down List contains all the SupplierID + SupplierName values from the suppliers table.

Yes that's because your options instance on the server does not contain a join and not a where clause either. Let me search for an example. There are multiple ways to do the options. As far as I remember you can't do a left join though. But let me double check. I'll be right back with some suggestions.

rf1234

This should work:

Field::inst( 'my_suppliers.SupplierID' )
    ->options( Options::inst()
        ->table( 'suppliers, my_suppliers, my_contacts' )
        ->value( 'suppliers.SupplierID' )
        ->label( 'suppliers.SupplierName' )
        //where clause MUST be a closure function in Options!!!
        ->where( function($q) {
            // there should be a user id in the my_suppliers table!!
            // $q ->where('my_suppliers.user_id', $_SESSION["user_id"] );
            $q ->where('my_suppliers.SupplierID', 'suppliers.SupplierID', '=', false); //inner join
            $q ->where('my_contacts.MySupplierID', 'my_suppliers.MySupplierID', '=', false); //inner join
        } )
    )    

Since you can't do a left join in the options instance I used an implicit 1990's style INNER JOIN through the WERE clause.

But I see a more fundamental problem with your data model. There is no user table and no foreign key from the user table in the my_suppliers table. How should this work?

More about options:
https://editor.datatables.net/manual/php/joins#Options

rf1234

More links on options:
https://datatables.net/forums/discussion/comment/116150/

Reading the options using your own SQL:
https://datatables.net/forums/discussion/comment/177739/#Comment_177739

Ovgmw7

@rt1234 thank you very much for your response. It almost did what I was looking for...

... however, it returns just the my_suppliers already added to the my_contacts table.

What I was trying to achieve was to return a full list of my_suppliers so I can add a new my_contact.

How do I need to change the query to achieve this please?

Also, you are 100% correct about the user table. I created a much simplified version of the database to write this forum question. Just focusing on the 3 tables where I have this problem to solve.

I've been trying all sorts for a couple of weeks to try to get what I want. I'm sure it can be done within editor.datatables some way... but I'm lost finding it....

Your help is greatly appreciated.

Ovgmw7

@rt1234 i've update the code with your suggestion. As you can see from the live webpage it just returns my_suppliers already added to the my_contacts table

https://wastesaver.org/DataTablesEditor/my_contacts.html

https://wastesaver.org/DataTablesEditor/my_suppliers.html

rf1234

I need this Select Drop Down List to be populated with just the MySupplierID + SupplierName values that exist in the my_suppliers table.

If you want to see all suppliers that are in table my_suppliers you would need to drop the INNER JOIN on table my_contacts.

Using a custom function to retrieve the options with SQL this should do it:

Field::inst( 'my_suppliers.SupplierID' )
    ->options( function () use ( $db ) {
         $stmt =   ('SELECT DISTINCT a.SupplierName    AS label, 
                                     b.MySupplierID id AS value
                       FROM suppliers a
                 INNER JOIN my_suppliers b ON a.SupplierID = b.SupplierID
                   ORDER BY 1 ASC'); 
          $result = $db ->raw() ->exec($stmt); 
          return $result->fetchAll(PDO::FETCH_ASSOC);
    } );

Using the Editor options instance this should work (INNER JOIN using the WHERE clause):

Field::inst( 'my_suppliers.SupplierID' )
    ->options( Options::inst()
        ->table( 'suppliers, my_suppliers' )
        ->value( 'my_suppliers.MySupplierID' )
        ->label( 'suppliers.SupplierName' )
        //where clause MUST be a closure function in Options!!!
        ->where( function($q) {
            // there should be a user id in the my_suppliers table!!
            // $q ->where('my_suppliers.user_id', $_SESSION["user_id"] );
            $q ->where('my_suppliers.SupplierID', 'suppliers.SupplierID', '=', false); //inner join
        } )
    )   

Maybe there is some unclarity on what an INNER JOIN does?!
This should help: https://www.w3schools.com/sql/sql_join_inner.asp
https://www.w3schools.com/sql/sql_where.asp

If you think about it: If you can't use an INNER JOIN (like in Data Table's options instance) it is possible to implement the same thing using a condition in the WHERE clause.

So this should also work, but it is outdated syntax of course:

Field::inst( 'my_suppliers.SupplierID' )
    ->options( function () use ( $db ) {
         $stmt =   ('SELECT DISTINCT a.SupplierName  AS label, 
                                     b.MySupplierID id AS value
                       FROM suppliers a, my_suppliers b
                 WHERE a.SupplierID = b.SupplierID
                   ORDER BY 1 ASC'); 
          $result = $db ->raw() ->exec($stmt); 
          return $result->fetchAll(PDO::FETCH_ASSOC);
    } );

Ovgmw7

@rt1234 Half way there!

All three code examples populate the select DDL correctly now with a list of my_suppliers. The SQL queries had an extra id field name which needed deleting

b.MySupplierID **id** AS value

However, when I add or edit a record only the MyContactName field is updated. The MySupplierID field is blank.

https://wastesaver.org/DataTablesEditor/my_contacts.html

Maybe this is a problem with the JS file?

/*
 * Editor client script for DB table my_contacts
 * Created by http://editor.datatables.net/generator
 */

(function($){

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: 'php/table.my_contacts.php',
        table: '#my_contacts',
        fields: [
            {
                "label": "SupplierName:",
                "name": "my_suppliers.SupplierID",
                "type": "select",
                "placeholder": "Select a Supplier"
            },
            {
                "label": "MyContactName:",
                "name": "my_contacts.MyContactName"
            }
        ]
    } );

    var table = $('#my_contacts').DataTable( {
        ajax: 'php/table.my_contacts.php',
        columns: [
            {
                "data": "suppliers.SupplierName"
            },
            {
                "data": "my_contacts.MyContactName"
            }
        ],
        select: true,
        lengthChange: false
    } );

    new $.fn.dataTable.Buttons( table, [
        { extend: "create", editor: editor },
        { extend: "edit",   editor: editor },
        { extend: "remove", editor: editor }
    ] );

    table.buttons().container()
        .appendTo( $('.col-md-6:eq(0)', table.table().container() ) );
} );

}(jQuery));

Ovgmw7

Yeah! I've fixed this. Field names was the problem needed to be my_contacts.MySupplierID many thanks to @rt1234 for sorting out the db queries. Much appreciated!

PHP code

<?php

/*
 * Editor server script for DB table my_contacts
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
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;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'my_contacts', 'MyContactID' )
    
    ->fields(
        
        Field::inst( 'my_contacts.MySupplierID' )
            ->options( function () use ( $db ) {
                 $stmt =   ('SELECT DISTINCT a.SupplierName AS label,
                                             b.MySupplierID AS value
                               FROM suppliers a, my_suppliers b
                         WHERE a.SupplierID = b.SupplierID
                           ORDER BY 1 ASC');
                  $result = $db ->raw() ->exec($stmt);
                  return $result->fetchAll(PDO::FETCH_ASSOC);
              } ),   
            
        Field::inst( 'suppliers.SupplierName' ),
        
        Field::inst( 'my_contacts.MyContactName' )
    )
    ->leftJoin( 'my_suppliers', 'my_suppliers.MySupplierID', '=', 'my_contacts.MySupplierID' )
    ->leftJoin( 'suppliers', 'suppliers.SupplierID', '=', 'my_suppliers.SupplierID' )
    
    ->process( $_POST )
    ->json();

JS Code

/*
 * Editor client script for DB table my_contacts
 * Created by http://editor.datatables.net/generator
 */

(function($){

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: 'php/table.my_contacts.php',
        table: '#my_contacts',
        fields: [
            {
                "label": "SupplierName:",
                "name": "my_contacts.MySupplierID",
                "type": "select",
                "placeholder": "Select a Supplier"
            },
            {
                "label": "MyContactName:",
                "name": "my_contacts.MyContactName"
            }
        ]
    } );

    var table = $('#my_contacts').DataTable( {
        ajax: 'php/table.my_contacts.php',
        columns: [
            {
                "data": "suppliers.SupplierName"
            },
            {
                "data": "my_contacts.MyContactName"
            }
        ],
        select: true,
        lengthChange: false
    } );

    new $.fn.dataTable.Buttons( table, [
        { extend: "create", editor: editor },
        { extend: "edit",   editor: editor },
        { extend: "remove", editor: editor }
    ] );

    table.buttons().container()
        .appendTo( $('.col-md-6:eq(0)', table.table().container() ) );
} );

}(jQuery));

Hope this helps somebody else with the same problem!

allan

Nice one - thanks for sharing your solution with us.

Allan