Editor is not connecting to Mysql database

dt_user

Good night, When I try to edit or create a new record I am getting an error saying: A system error has occurred (More information). I think I am getting this error because editor isn't connecting to my database.
Please let me know if I am correct and how i can fix this error.

kthorngren

There are some troublehooting steps in the link provided in the error:
https://datatables.net/manual/tech-notes/12

Let us know what you find.

Kevin

dt_user

The response is empty. It says: This request has no response data available.

colin

Hi @dt_user ,

I replied on your other thread about how to configure to the database, is this a side-effect of that? If you're having problems getting the connection still, let us know - but that tech note that Kevin pointed you towards is a good place to start.

Cheers,

Colin

dt_user

Ok I will follow the instructions on the new link and do over the editor installation. Because I have already checked the lib/config folder and I did over the editor installation steps but editor did not work yesterday.
Is there another file that editor needs that could be missing?

dt_user

After following the steps on this link https://datatables.net/manual/tech-notes/12 my sever script gives me this error. This is also the script I use in the ajax options of editor.
See code below

<?php
//This file is called task_table.php

//Basic initialization of editor class.
error_reporting(E_ALL);
ini_set('display_errors', '1');

//use the editor php libraries
// DataTables PHP library
include("Editor-PHP-1.9.0/lib/DataTables.php" );
include("Editor-PHP-1.9.0/lib/Database/Driver/MysqlQuery.php");
include("Editor-PHP-1.9.0/lib/config.php");

use
    DataTables\Database,
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// require 'vendor/autoload.php';
// require 'db.php'; // defines $database


//Database connection
// $db = new DataTables\Database( array(
//   "type" => "Mysql",     // Database type: "Mysql", "Postgres", "Sqlserver", "Sqlite" or "Oracle"
//  "user" => "**** ",          // Database user name
//  "pass" => "*****",          // Database password
//  "host" => "localhost", // Database host
//   "port" => "3306",          // Database connection port (can be left empty for default)
//  "db"   => "Timesheet",          // Database name
//  "dsn"  => "charset=utf8mb4",          // PHP DSN extra information. Set as `charset=utf8mb4` if you are using MySQL
//  "pdoAttr" => array()
//   "pdo" => $database
//   ) );

  // Build our Editor instance and process the data coming from _POST
  Editor::inst( $db, 'schedules')
      ->field(
        //Field::inst( 'schedules.sch_id' )->set(false), //ID is automatically set by the database on create
        Field::inst( 'schedules.start')
          ->validator( Validate::notEmpty( ValidateOptions::inst()
              ->message( 'A start time is required' )
            )),
        Field::inst( 'schedules.end' )
          ->validator( Validate::notEmpty( ValidateOptions::inst()
              ->message( 'A start date is required' )
            )),
        Field::inst( 'schedules.taskname' ),
        Field::inst( 'schedules.status' ),
        Field::inst( 'schedules.hoursworked' ),
        Field::inst( 'schedules.details' ),
        Field::inst( 'schedules.lvl' ),
        Field::inst('schedules.comments' ),
        Field::inst( 'people.fname' ),
        Field::inst( 'people.lname' )
      )
      ->leftJoin( 'people', 'people.p_id', '=', 'schedules.p_id')
      ->process($_POST ) //editor can do CRUD
      ->json(); //returns json data to client.
?>

//This file called script.js

function tab_clicked(fname,lname){
    $(this).css("background-color", "yellow");
    $('#create').show();
    var fname = fname;
    var lname = lname;
    $.ajax({
      type: 'POST',
      url: 'task.php',
      data: {
        'send':1,
        'fname':fname,
        'lname':lname,
      },
      dataType: 'json',
      success:function(response){
        console.log(response);
        var output = " ";
        var child = " ";
        var cinfo = " ";
        $.each(response, function(i,v)  {
          console.log(v);
          id = v['sch_id'];
          //console.log("ID" + id);
          start = v['StartTD'];
          end = v['EndTD'];
          taskn = v['TaskName'];
          status = v['Status'];
          hoursworked = v['HoursWorked'];
          details = v['Details'];
          lvl = v['LevelofDifficulty'];
          comments = v['Comments'];

          output += "<tr id='"+id+"'>" + "<td class= \"details-control\"></td>" + "<td>" + start + "</td>" + "<td>" + end + "</td>"  + "<td>" + taskn + "</td>" + "<td>" + status + "</td>" + "<td>" + hoursworked + "</td>" + "<td></td>" + "</tr>";

          // child += '<table id="childTable" class="display" style="display: none;"  border="1px solid black">'+
          //             '<thead>'+
          //                 '<tr>'+
          //                     '<th>Details</th>'+
          //                     '<th>Level</th>'+
          //                     '<th>Comments</th>'+
          //                 '</tr>'+
          //             '</thead>'+
          //             '<tbody id="childinfo"></tbody>'+
          //         '</table>';

          cinfo += "<tr>" + "<td>" + details + "</td>" + "<td>" + lvl + "</td>" + "<td>" + comments + "</td>" + "</tr>";

        });
        console.log(output);
        console.log(cinfo);
        $('#info').html(output);
        $('#task_table').show();
        //$('#newrow').html(cinfo);
        // $('#childTable').show();
        //$(document).ready( function () {
          var table = $('#task_table').DataTable({
            rowId: "id",
            columns: [
              {
                  className: 'details-control',
                  orderable: false,
                  data: null,
                  defaultContent: ''
                },
                {data: "schedules.start"},
                {data: "schedules.end"},
                {data: "schedules.taskname"},
                {data: "schedules.status"},
                {data: "schedules.hoursworked"},
                {
                  data: null,
                  className: "dt-body-center",
                  defaultContent: '<a href="" class="editor_edit">Edit</a>'  /* '<i class="fa fa-pencil-square"></i>' */

                }
              ],
              order: [[1,'asc']],
              select: true
          });

          // Add event listener for opening and closing details
          $('#task_table tbody').on('click', 'td.details-control', function () {
            var tr = $(this).closest('tr');
            console.log(tr);
            var row = table.row( tr );
            console.log(row);

            if ( row.child.isShown() ) {
                // This row is already open - close it
                //row.child.hide();
                destroyChild(row); //tidy up
                tr.removeClass('shown');
            }
            else {
                //Open this row
                //row.child( cinfo ).show();
                createChild(cinfo); //dt and editor functionality
                tr.addClass('shown');
            }
        });
        //editor begin
        var editor = new $.fn.dataTable.Editor( {
            ajax: "task_table.php",
            table: "#task_table",
            idSrc: "id",
            fields: [ {
                    "label": "Start Time/Date:",
                    "name": "schedules.start"
                  }, {
                    "label": "End Time/Date:",
                    "name": "schedules.end"
                  }, {
                    "label": "Task Name:",
                    "name": "schedules.taskname"
                  }, {
                    "type": "select",
                    "label": "Status:",
                    "name": "schedules.status",
                    "options": [
                      {"label": "Select a Status", "value": ''},
                      {"label": "In-Progress", "value": 'In-progress'},
                      {"label": "Completed", "value": 'Completed'}
                    ]
                  }, {
                    "label": "Hours Worked:",
                    "name": "schedules.hoursworked"
                  }

            ]

         } );//editor


        //New Record
        $('i.fas.fa-plus').on('click', function (e) {
           e.preventDefault();

          editor.create( {
            "title": 'Create New Record',
            "buttons": 'Create'
          } );
       } );

        //Edit Record
        $("#task_table").on('click', "a.editor_edit", function (e) {
          e.preventDefault();
          //alert("hello");
          var row = $(this).closest('tr').find("td:nth-child(2)");
          var data = table.row( this ).data();

          editor.edit( $(this).closest('tr'), {
            "title": 'Edit record',
            "buttons": 'Update'
          } );
       } );
        //editor end

        //checks row
    //     $('#task_table tbody').on( 'click', 'tr', function () {
    //     var id = table.row( this ).data();
    //     console.log(id);
    //
    //     alert( 'Clicked row id '+id );
    // } );
  //}); //$(document) end

    }//success() end

  });//ajax end

  }//tab_clicked() end

I can't find these two files in my editor download package:
1. require 'vendor/autoload.php';
2. require 'db.php'; // defines $database;.
Could this cause editor not to work?

colin

One big problem here, is that you're posting multiple threads at the same time, so it's hard to understand what you're current issue. There were three from you this morning. If you are experiencing problems still, can we consolidate on your thread here.

Cheers,

Colin

colin

1. require 'vendor/autoload.php';
2. require 'db.php'; // defines $database;.

Neither of those files are ours. As I said in one of your other threads, you have to configure the DB connection in config.php

allan

Have a look at your server's error logs. An empty response is indeed invalid JSON, so DataTables is correct in giving you an error there. The key is that we need to discover what is going wrong on the server. The http server's error log should contain that information.

Allan

dt_user

Sorry about the multiple threads. This is my current issue: https://datatables.net/forums/discussion/56789/error-joining-two-mysql-tables/p1?new=1.