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

Manually coding Add, Update and Delete functions for a DataTable

2»

Answers

  • jmyrtlejmyrtle Posts: 52Questions: 3Answers: 0

    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?

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736
    edited 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

  • jmyrtlejmyrtle Posts: 52Questions: 3Answers: 0

    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 )
    );
    ?>
    
  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    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

  • jmyrtlejmyrtle Posts: 52Questions: 3Answers: 0

    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.

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    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

  • jmyrtlejmyrtle Posts: 52Questions: 3Answers: 0
    edited November 2019

    Here are my debugger results:

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    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

  • jmyrtlejmyrtle Posts: 52Questions: 3Answers: 0

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

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

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

    Kevin

  • jmyrtlejmyrtle Posts: 52Questions: 3Answers: 0
    edited November 2019

    Yes; it is uploading now

  • jmyrtlejmyrtle Posts: 52Questions: 3Answers: 0

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

  • jmyrtlejmyrtle Posts: 52Questions: 3Answers: 0

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

  • jmyrtlejmyrtle Posts: 52Questions: 3Answers: 0

    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();
    
  • allanallan Posts: 61,443Questions: 1Answers: 10,053 Site admin

    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

  • jmyrtlejmyrtle Posts: 52Questions: 3Answers: 0

    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();
    ?>
    
  • jmyrtlejmyrtle Posts: 52Questions: 3Answers: 0
    edited 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?

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

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

  • jmyrtlejmyrtle Posts: 52Questions: 3Answers: 0

    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)); 
    
  • jmyrtlejmyrtle Posts: 52Questions: 3Answers: 0

    @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

  • jmyrtlejmyrtle Posts: 52Questions: 3Answers: 0
    edited 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

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

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

  • jmyrtlejmyrtle Posts: 52Questions: 3Answers: 0
    edited 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"
    );
    
    ?>
    
  • jmyrtlejmyrtle Posts: 52Questions: 3Answers: 0

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

  • jmyrtlejmyrtle Posts: 52Questions: 3Answers: 0

    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();
    ?>
    
  • jmyrtlejmyrtle Posts: 52Questions: 3Answers: 0

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

  • allanallan Posts: 61,443Questions: 1Answers: 10,053 Site admin

    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

This discussion has been closed.