Manually coding Add, Update and Delete functions for a DataTable

jmyrtle

I have a server.php file that returns ajax data from the database. Can you turn this into the data needed for Editor to load in?

kthorngren

I'm not familiar with the pHP scripts but here is an example using object data:
https://datatables.net/examples/server_side/object_data.html

Click on the Server-side script tab.

Kevin

jmyrtle

My file is similar to your example:

```
<?php

// DB table to use
$table = 'members';

// Table's primary key
$primaryKey = 'id';

// Array of database columns which should be read and sent back to DataTables.
// The db parameter represents the column name in the database, while the dt
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
array( 'db' => 'id', 'dt' => 0 ),
array( 'db' => 'name', 'dt' => 1 ),
array( 'db' => 'residential_address', 'dt' => 2 ),
array( 'db' => 'mailing_address', 'dt' => 3 ),
array( 'db' => 'precinct', 'dt' => 4),
array( 'db' => 'age', 'dt' => 5 ),
array( 'db' => 'ethnicity', 'dt' => 6 ),
array( 'db' => 'gender', 'dt' => 7 ),
array( 'db' => 'party', 'dt' => 8 ),
array( 'db' => 'race', 'dt' => 9 ),
array( 'db' => 'phone', 'dt' => 10 )
);

// SQL server connection information
$sql_details = array(
'user' => 'root',
'pass' => '',
'db' => 'ccrp_db',
'host' => 'localhost'
);

require( 'ssp.class.php' );

echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

<?php > ``` ?>

kthorngren

Are you still getting this error?

DataTables warning: table id=dataTable - Requested unknown parameter 'name' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4

If so did you follow the steps in the link to see what is being returned?

Kevin

jmyrtle

Yes, I have checked my ajax file (server.php) and my HTML file. I have only removed one column (which is the primary key of my database) and I still get the error.

I removed the primary key from the index page because Editor's generator said that the primary key does not need to have its own column inside the table.

kthorngren

Can you post the ajax response from the developer tools? Or you can use the debugger and post the ID for the developers to take a look.

Kevin

jmyrtle

Here are my debugger results:

kthorngren

Did you get a 6 character code form the debugger? If so please post it here so the developers can open the image and look at the response data, etc.

Kevin

jmyrtle

I do not see any code in the debugger - only what is in the screenshot above

kthorngren

Did you clock on the "Upload Configuration" button at the bottom?

Kevin

jmyrtle

Yes; it is uploading now

jmyrtle

Never mind. I can't seem to upload my configuration. It's timing out.

jmyrtle

Now that you mention it, I think I am returning object based data based on this.

jmyrtle

After double checking the editor manual, I found the code below. Is this required to obtain data from MySQL using DataTables Editor? If it is, then this is what I am missing.

<?php
 
/*
 * Example PHP implementation used for the index.html example
 */
 
// DataTables PHP library
include( "../../php/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, 'datatables_demo' )
    ->fields(
        Field::inst( 'first_name' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A first name is required' ) 
            ) ),
        Field::inst( 'last_name' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A last name is required' )  
            ) ),
        Field::inst( 'position' ),
        Field::inst( 'email' )
            ->validator( Validate::email( ValidateOptions::inst()
                ->message( 'Please enter an e-mail address' )   
            ) ),
        Field::inst( 'office' ),
        Field::inst( 'extn' ),
        Field::inst( 'age' )
            ->validator( Validate::numeric() )
            ->setFormatter( Format::ifEmpty(null) ),
        Field::inst( 'salary' )
            ->validator( Validate::numeric() )
            ->setFormatter( Format::ifEmpty(null) ),
        Field::inst( 'start_date' )
            ->validator( Validate::dateFormat( 'Y-m-d' ) )
            ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
            ->setFormatter( Format::dateFormatToSql('Y-m-d' ) )
    )
    ->process( $_POST )
    ->json();

allan

Yes, if you want to use our server-side libraries to read data from the database and send it back to the client you would need a PHP script structured similar to that one.

What I would suggest is you try using Generator - fill in your column names (etc) and Generator will build the PHP script for you, along with the Editor and DataTable JS code. It doesn't provide the full features of Editor through its web UI, but Generator can be useful to see what the structure of the program is and how it all fits together.

Allan

jmyrtle

I regenerated to get the PHP file, and I'm presented with this:

What needs to be fixed?

```
<?php

/*
* Editor server script for DB table members
* 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( 'members', 'id' )
->fields(
Field::inst( 'name' )
->validator( Validate::notEmpty() ),
Field::inst( 'residential_address' )
->validator( Validate::notEmpty() ),
Field::inst( 'mailing_address' ),
Field::inst( 'precinct' )
->validator( Validate::notEmpty() )
->validator( Validate::maxLen( 5 ) ),
Field::inst( 'age' )
->validator( Validate::notEmpty() )
->validator( Validate::maxNum( 2 ) ),
Field::inst( 'ethnicity' )
->validator( Validate::notEmpty() )
->validator( Validate::maxLen( 2 ) ),
Field::inst( 'gender' )
->validator( Validate::notEmpty() )
->validator( Validate::maxLen( 1 ) ),
Field::inst( 'party' )
->validator( Validate::notEmpty() )
->validator( Validate::maxLen( 3 ) ),
Field::inst( 'race' )
->validator( Validate::notEmpty() )
->validator( Validate::maxLen( 1 ) ),
Field::inst( 'phone' )
->validator( Validate::notEmpty() )
->validator( Validate::maxLen( 12 ) )
)
->process( $_POST )
->json();

<?php > ``` ?>

jmyrtle

Scratch my last comment. I found the files needed and included them, but now I see this:

{"error":"An error occurred while connecting to the database ''. The error reported by the server was: SQLSTATE[HY000] [1045] Access denied for user ''@'localhost' (using password: NO)"}

What am I missing? Is there another file included that connects to my database? If so, what file is this error coming from?

colin

The database name is empty in the comment, so it suggests missing configuration. Have you updated config.php? If not, see instructions here.

jmyrtle

I already used Generator to grab the files. The problem I have is this error on my screen:

but I'm not sure how to resolve it. I looked at the link the error said and it didn't really mention how to resolve it. I also looked at different forums and tried the debugger, but nothing works.

Here is my current JS code. Based on the error above, it looks like the problem starts on line 71.

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

(function($){

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: 'api/server.php',
        processing: true,
        serverSide: true,
        order: [],
        pageLength : 25,
        table: '#dataTable',
        fields: [       
            {
                "label": "Name:",
                "name": "name"
            },
            {
                "label": "Residential Address:",
                "name": "residential_address"
            },
            {
                "label": "Mailing Address:",
                "name": "mailing_address"
            },
            {
                "label": "Precinct:",
                "name": "precinct"
            },
            {
                "label": "Age:",
                "name": "age"
            },
            {
                "label": "Ethnicity:",
                "name": "ethnicity"
            },
            {
                "label": "Gender:",
                "name": "gender"
            },
            {
                "label": "Party:",
                "name": "party",
                "def": "REP"
            },
            {
                "label": "Race:",
                "name": "race"
            },
            {
                "label": "Phone:",
                "name": "phone"
            }
        ]
    } );

    var table = $('#dataTable').DataTable( {
        dom: 'Bfrtip',
        processing: true,
        serverSide: true,
        order: [],
        pageLength: 25,
        ajax: 'api/server.php',
        columns: [      
            {
                "data": "name"
            },
            {
                "data": "residential_address"
            },
            {
                "data": "mailing_address"
            },
            {
                "data": "precinct"
            },
            {
                "data": "age"
            },
            {
                "data": "ethnicity"
            },
            {
                "data": "gender"
            },
            {
                "data": "party"
            },
            {
                "data": "race"
            },
            {
                "data": "phone"
            }
        ],
        select: true,
        lengthChange: false,
        buttons: [
            { extend: 'create', editor: editor },
            { extend: 'edit',   editor: editor },
            { extend: 'remove', editor: editor }
        ]
    } );
} );
    
}(jQuery)); 

jmyrtle

@colin Okay, I filled it in, now I see this:

Fatal error: Uncaught Error: Call to a member function transaction() on string in C:\xampp\htdocs\ccrp\api\lib\Editor.php:964 Stack trace: #0 C:\xampp\htdocs\ccrp\api\lib\Editor.php(700): DataTables\Editor->_process(Array) #1 C:\xampp\htdocs\ccrp\api\server_data.php(53): DataTables\Editor->process(Array) #2 {main} thrown in C:\xampp\htdocs\ccrp\api\lib\Editor.php on line 964

jmyrtle

How do I fix this error:

Fatal error: Uncaught Error: Call to a member function transaction() on string in C:\xampp\htdocs\ccrp\api\lib\Editor.php:964 Stack trace: #0 C:\xampp\htdocs\ccrp\api\lib\Editor.php(700): DataTables\Editor->_process(Array) #1 C:\xampp\htdocs\ccrp\api\server_data.php(53): DataTables\Editor->process(Array) #2 {main} thrown in C:\xampp\htdocs\ccrp\api\lib\Editor.php on line 964

colin

The error changed, I'm gathering, when you corrected the config.php file. Could you post it here, please?

jmyrtle

Here is my config.php file: (I do not have a database password or a port)

```
<?php if (!defined('DATATABLES')) exit(); // Ensure being used in DataTables env.

/*
* DB connection script for Editor
* Created by http://editor.datatables.net/generator
*/

// Enable error reporting for debugging (remove for production)
error_reporting(E_ALL);
ini_set('display_errors', '1');

/*
* Edit the following with your database connection options
*/
$sql_details = array(
"type" => "Mysql",
"user" => "root",
"pass" => "",
"host" => "localhost",
"port" => "",
"db" => "ccrp_db",
"dsn" => "charset=utf8"
);

<?php > ``` ?>

jmyrtle

Also, I am running out of time to complete the setup as my trial only has 4 days remaining.

jmyrtle

Is it possible that I am missing something in this file?

```
<?php

/*
* Editor server script for DB table members
* 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( 'members', 'id' )
->fields(
Field::inst( 'name' )
->validator( Validate::notEmpty() ),
Field::inst( 'residential_address' )
->validator( Validate::notEmpty() ),
Field::inst( 'mailing_address' ),
Field::inst( 'precinct' )
->validator( Validate::notEmpty() )
->validator( Validate::maxLen( 5 ) ),
Field::inst( 'age' )
->validator( Validate::notEmpty() )
->validator( Validate::maxNum( 2 ) ),
Field::inst( 'ethnicity' )
->validator( Validate::notEmpty() )
->validator( Validate::maxLen( 2 ) ),
Field::inst( 'gender' )
->validator( Validate::notEmpty() )
->validator( Validate::maxLen( 1 ) ),
Field::inst( 'party' )
->validator( Validate::notEmpty() )
->validator( Validate::maxLen( 3 ) ),
Field::inst( 'race' )
->validator( Validate::notEmpty() )
->validator( Validate::maxLen( 1 ) ),
Field::inst( 'phone' )
->validator( Validate::notEmpty() )
->validator( Validate::maxLen( 12 ) )
)
->process( $_POST )
->json();

<?php > ``` ?>

jmyrtle

Solved. The table is now getting data. I was missing the $db variable that went into the instance constructor.

allan

That would do it - good to hear you have it fixed now.

Also, if you do run out of time with the trial, just let me know and I'll reset the 15 day trial for you.

Allan