Two dropdown fields and left join problem

iso sio

Hello all,

I have two dropdown fields. 1st site_name and the 2nd is the site. How can I select for both fields from site.table

Below is my php and html code.

Any help would be appreciated.

join.php

Editor::inst( $db, 'users' )
    ->field( 
        Field::inst( 'users.site_name' )
            ->options( Options::inst()
                ->table( 'sites' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( Validate::dbValues() ),
        
        
        Field::inst( 'users.last_name' ),
        Field::inst( 'users.phone' ),
        Field::inst( 'users.site' )
            ->options( Options::inst()
                ->table( 'sites' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( Validate::dbValues() ),
        Field::inst( 'sites.name' )
    )
    
    ->leftJoin( 'sites', 'sites.id', '=', 'users.site','sites', 'sites.id', '=', 'users.site_name' )
    
    ->process($_POST)
    ->json();

join.html

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "../php/join.php",
        table: "#example",
        fields: [ {
                label: "Kimden:",
                name: "users.first_name",
                type: "select",
                placeholder: "Select a location"
            }, {
                label: "Last name:",
                name: "users.last_name"
            }, {
                label: "Phone #:",
                name: "users.phone"
            }, {
                label: "Site:",
                name: "users.site",
                type: "select",
                placeholder: "Select a location"
            }
        ]
    } );

    $('#example').DataTable( {
        dom: "Bfrtip",
        ajax: {
            url: "../php/join.php",
            type: 'POST'
        },
        columns: [
            { data: "sites.name" },
            { data: "users.last_name" },
            { data: "users.phone" },
            { data: "sites.name" }
        ],
        select: true,
        buttons: [
            { extend: "create", editor: editor },
            { extend: "edit",   editor: editor },
            { extend: "remove", editor: editor }
        ]
    } );
} );

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

->value( 'id' )

For users.site_name should probably be name.

However, having two select elements for this sounds like it will cause user confusion to me Can you not just use a left join to get the site name?

Allan