Manually coding Add, Update and Delete functions for a DataTable

jmyrtle · November 2019

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 · November 2019

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 · November 2019

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 · November 2019

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 · November 2019

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 · November 2019

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 · November 2019

Here are my debugger results:

kthorngren · November 2019

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 · November 2019

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

kthorngren · November 2019

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

Kevin

jmyrtle · November 2019

Yes; it is uploading now

jmyrtle · November 2019

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

jmyrtle · November 2019

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

jmyrtle · November 2019

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 · November 2019

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 · November 2019

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 · November 2019

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 · November 2019

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

jmyrtle · November 2019

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 · November 2019

@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 · November 2019

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 · November 2019

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

jmyrtle · November 2019

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 · November 2019

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

jmyrtle · November 2019

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 · November 2019

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

allan · November 2019

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

Manually coding Add, Update and Delete functions for a DataTable - Page 2

Manually coding Add, Update and Delete functions for a DataTable

Answers

Howdy, Stranger!

Categories

DataTables

Manually coding Add, Update and Delete functions for a DataTable - Page 2

Manually coding Add, Update and Delete functions for a DataTable

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables