dataTables editor error 'Column Not Found'

tomish

Hi, I am trying to join two tables and I've followed all the examples as closely as possible
but I keep getting this error when I run the file ('suspension_editor.php' in a browser); I am expecting to get a set of well formed JSON objects.

{"error":"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'report_eligibility.emp_status' in 'field list'","data":[]}

That column does exist in my database. I've updated the config.php file to make sure I am pointing to the right db with the correct credentials. My only guess is that the leftJoin is not working.

Help is much appreciated! Thanks, Tom

Editor::inst( $db, suspension')
    ->fields(
        Field::inst( 'report_eligibility.emp_status' )->validator( function ( $editor, $action, $data ) {
        if ( $action !== Editor::ACTION_READ && $_SESSION['read_only'] ) {
            return 'Cannot modify data';
        }
    } ),
        Field::inst( 'suspension.emp_id' )->validator( function ( $editor, $action, $data ) {
        if ( $action !== Editor::ACTION_READ && $_SESSION['read_only'] ) {
            return 'Cannot modify data';
        }
    } ),
        Field::inst( suspension.nuid' )->validator( function ( $editor, $action, $data ) {
        if ( $action !== Editor::ACTION_READ && $_SESSION['read_only'] ) {
            return 'Cannot modify data';
        }
    } ),


        Field::inst( 'report_eligibility.last_name' ),
        Field::inst( 'report_eligibility.first_name' ),
        Field::inst( 'report_eligibility.region' ),
        Field::inst( 'suspension.school_name' ),
        Field::inst( 'suspension.course_name' ),

        Field::inst('suspension.begin_date') ->validator( 'Validate::dateFormat', array(
                "format"  => Format::DATE_ISO_8601,
                "message" => "Please enter a date in the format yyyy-mm-dd"
            )),
        Field::inst( suspension.end_date' )->validator( 'Validate::dateFormat', array(
                "format"  => Format::DATE_ISO_8601,
                "message" => "Please enter a date in the format yyyy-mm-dd"
            )),
        Field::inst( 'suspension.notification_date' )->validator( 'Validate::dateFormat', array(
                "format"  => Format::DATE_ISO_8601,
                "message" => "Please enter a date in the format yyyy-mm-dd"
            )),
        Field::inst( 'suspension.attempts' ),
        Field::inst( 'suspension.comments' )

            )
            ->leftJoin( 'report_eligibility',   'report_eligibility.nuid',   '=', 'suspension.nuid' )


    ->process( $_POST )
    ->json();

colin

Hi Tom,

The first spot is that the first line is missing a quote - there's only one trailing 'suspension', not one leading. Could you try that first and report back, please.

Cheers,

Colin

tomish

Thanks Colin, and good catch - typo on my part but still no luck.

Tom

tomish

Update - I removed all references to 'report_eligibility' and eliminated the leftJOIN - I still get the same error message for the other table ('suspension'). The first column listed in my code cannot be found.

I have had good success getting the editor to work in another database. These suspension and eligibility files are in another databse, so I created another instance of the 'editor' folder and associated sub-directories and called it 'editor2' because - as I understand it - I cannot specify multiple databases in the config.php file. In other words, one editor per database - I assume that's correct?

I have checked to make sure that the file that is giving me the error points to 'editor2'
in the include() statement and that the config file associated with 'editor2' has the correct database name, proper login credentials, etc.

Thanks, Tom

allan

In other words, one editor per database - I assume that's correct?

More or less . You could create another Database class instance which points to the second database. The config.php file (and Bootstrap.php) will automatically create the $db variable, but there is no reason why you couldn't have a second one, or just include a different config file, while using the same PHP libraries.

It does sounds a lot like its a default database issue. Could you try adding:

print_r( $db->sql( 'show tables' )->fetchAll() );

which will show the list of tables in the returned data (it won't be valid JSON, so you'll get an error message about that).

Does it show the list of tables you expect?

Allan

tomish

Hi Allan!

Ah, nice trick! It does return an array of the tables I expect to see in the 'second' database using editor2

Including

'''
[46] => Array ( [Tables_in_bhmt_portal_test] => report_eligibility )

and

[11] => Array ( [Tables_in_bhmt_portal_test] => bhmt_suspension )

'''

I realize the name in the second db is different than what appears in the code I posted earlier (bhmt_suspension vs suspension)

Thanks - but why am I not able to access those tables in that database if I can see them in the print_r?