Changing the select list options for Add New Record

Datagaard

Hi,

I have a situation where I have a left outer join to return the Name of the EquipmentID field to populate the datatable column.

However I wish to populate the select type option values differently for Adding a new record. (I have No Edit option allowed)

Here is my php code to populate the Editor:

<?php
    ini_set("display_errors",1);
/*
 * Editor server script for DB table Configuration_Equipment
 * 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\Upload,
    DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'Configuration_Equipment', 'CEID' )
    ->fields(
        Field::inst( 'Configuration_Equipment.RoomID' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'Configuration_Equipment.ConfigurationID' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'Configuration_Equipment.EquipmentID' )
            ->options( 'Equipment','EquipmentID', 'Name')
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'Equipment.Name' ),
        Field::inst( 'Equipment.Available'),
        Field::inst( 'Equipment.Notes')
    )
    ->leftJoin( 'Equipment', 'Equipment.EquipmentID', '=', 'Configuration_Equipment.EquipmentID' )
    ->where( function ( $q ) {
        $q
            ->where('Configuration_Equipment.RoomID', $_REQUEST['RoomID'], '=')
            ->and_where('Configuration_Equipment.ConfigurationID', $_REQUEST['ConfigurationID'], '=');
    })
    ->process( $_POST )
    ->json();

The editor code above gives me the Name of the equipment to display in the dataTable, but not sure how to get the select values to change on Adding a new record.

The new select options should be based on the following SQL code:

SELECT EquipmentID, Name FROM Equipment WHERE EquipmentID NOT IN
(SELECT EquipmentID FROM Configuration_Equipment WHERE RoomID <> $_REQUEST['RoomID'])
OR EquipmentID IN
(SELECT EquipmentID FROM Configuration_Equipment WHERE RoomID = $_REQUEST['RoomID'] AND
ConfigurationID <> $_REQUEST['ConfigurationID'])

allan

I would suggest removing the use of the Field->options() method (which is how Editor is getting the list of options), and instead querying the database to get your list of options directly.

Then use Editor->data() rather than Editor->json() to have it put the information into a variable, attach it to the options parameter and send it back as JSON - e.g.:

$data = Editor::inst( $db, 'Configuration_Equipment', 'CEID' )
    ...
    ->process( $_POST )
    ->data();

$data['options']['Configuration_Equipment.EquipmentID'] = $myOptions;

echo json_encode( $data );

Where of course $myOptions is your list of options.

Allan

Datagaard

Hi Allan,

Sorry about delay in getting back on this.

I have made changes from what I interpret above as follows:

<?php
    ini_set("display_errors",1);
/*
 * Editor server script for DB table Configuration_Equipment
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "lib/DataTables.php" );
include_once( "../Includes/db.inc.php" );

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

if(PHP_SAPI === 'cli') {
    $roomID = $argv[1];
    $configID = $argv[2];
}
else{
    $roomID = $_REQUEST['RoomID'];
    $configID = $_REQUEST['ConfigurationID'];
}

try
    {
        $sql = "SELECT EquipmentID, Name FROM Equipment WHERE EquipmentID NOT IN
                (SELECT EquipmentID FROM Configuration_Equipment WHERE RoomID <> :roomid1)
                OR EquipmentID IN
                (SELECT EquipmentID FROM Configuration_Equipment WHERE RoomID = :roomid2 AND
                ConfigurationID <> :configid)";
        $el = $pdo->prepare($sql);
        $el->bindValue(':roomid1', $roomID);
        $el->bindValue(':roomid2', $roomID);
        $el->bindValue(':configid', $configID);
        $el->execute();
        
        foreach($el as $row)
        {
            $myOptions[] = array('EquipmentID' => $row['EquipmentID'], 'Name' => $row['Name']);
        }
    }
catch(PDOException $e)
    {
        $error = 'Unable to obtain Equipment. ' . $e->getMessage(); 
        echo '<script type="text/javascript">alert(' .$error . ');</script>';
    }
    
    
// Build our Editor instance and process the data coming from _POST
$data = Editor::inst( $db, 'Configuration_Equipment', 'CEID' )
    ->fields(
        Field::inst( 'Configuration_Equipment.RoomID' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'Configuration_Equipment.ConfigurationID' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'Configuration_Equipment.EquipmentID' )
            //->options( 'Equipment','EquipmentID', 'Name')
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'Equipment.Name' ),
        Field::inst( 'Equipment.Available'),
        Field::inst( 'Equipment.Notes')
    )
    ->leftJoin( 'Equipment', 'Equipment.EquipmentID', '=', 'Configuration_Equipment.EquipmentID' )
    ->where( function ( $q ) {
        $q
            ->where('Configuration_Equipment.RoomID', $_REQUEST['RoomID'], '=')
            ->and_where('Configuration_Equipment.ConfigurationID', $_REQUEST['ConfigurationID'], '=');
    })
    ->process( $_POST )
    ->data();
    
    $data['options']['Configuration_Equipment.EquipmentID'] = $myOptions;
    
//  ->json();
    echo json_encode($data);

Now the data gets returned as :

{"data":[{"DT_RowId":"row_1","Configuration_Equipment":{"RoomID":"4","ConfigurationID":"1","EquipmentID":"4"},"Equipment":{"Name":"TV & Video","Available":"0","Notes":"Conference Room 1"}}],"options":{"Configuration_Equipment.EquipmentID":[{"EquipmentID":"2","Name":"Overhead Projector"},{"EquipmentID":"4","Name":"TV & Video"},{"EquipmentID":"5","Name":"Slide Projector"},{"EquipmentID":"6","Name":"Electronic Whiteboard"},{"EquipmentID":"7","Name":"Laptop & Projector (1)"},{"EquipmentID":"11","Name":"Overhead Projector"},{"EquipmentID":"13","Name":"TV & Video"},{"EquipmentID":"14","Name":"Slide Projector"},{"EquipmentID":"15","Name":"Overhead Projector"},{"EquipmentID":"33","Name":"Laptop & Projector (2)"},{"EquipmentID":"34","Name":"Laptop & Projector (3) (Elec Whiteboard)"},{"EquipmentID":"35","Name":"Electronic Whiteboard Accessories"},{"EquipmentID":"37","Name":"Admin & Learning Hot desk1"},{"EquipmentID":"43","Name":"5"},{"EquipmentID":"44","Name":"5"},{"EquipmentID":"45","Name":"5r54"},{"EquipmentID":"46","Name":"5"}]},"files":[]}

But my drop down in the Editor has nothing in it, when I click on the New button.

Any suggestions to what I have done incorrectly.

Thanks

Datagaard

Silly me, the myOptions should be as follows:

$myOptions[] = array('label' => $row['Name'] , 'value' => $row['EquipmentID']);

All good now.

allan

Thanks for the update - good to hear that it's all working now.

Allan