Manually coding Add, Update and Delete functions for a DataTable

jmyrtle

Is there anyone who has tried to manually code Add Update and Delete script for their MySQL DataTable? I am trying to add more functionality to my PHP CRUD app and DataTable by creating buttons inside an empty column that will update or delete that specific record, and the add function on a different webpage.

Here is my current js file. I already have the form made to add new, but I do not have anything for update and delete nor do I have any functionality except viewing the database. What do I do?

$(document).ready(function() {
    $('#dataTable').DataTable( {
        "processing": true,
        "serverSide": true,
        "order": [],
        "pageLength": 25,
        "ajax": "api/server.php",
    });
});

kthorngren

I would suggest using Editor. It is well worth the minimal cost of not having to write your own editor functionality. However if you wish to roll your own then you would use jQuery to send the updates to the server. The server response should be either the updated or new row which you can then update the Datatable row with the new info. This will allow you validate the update happened.

Kevin

jmyrtle

@kthorngren Is Editor compatible with a MySQL database?

I already have the form that users have to fill out in order to create the new record, but I don't have the API to add the input to the database. I also have nothing for Edit (Update) and Delete functionality.

Also, I have a few columns that contain data that is in all capital letters. Can I edit the form in Editor to force uppercase inputs?

colin

Is Editor compatible with a MySQL database?

Yep, we use MySQL (along with SQL Server, Postgres, Oracle and SQLite) in our testing, so no problem there.

Can I edit the form in Editor to force uppercase inputs?

Yep - you can either convert the text to upper-case after the user inputs it, or just reject lower-case strings - both are possible. See the name field in this example: http://live.datatables.net/fenujate/1/edit

Cheers,

Colin

jmyrtle

I have downloaded a JS/CSS trial for now. How do I implement this into my current DataTable?

jmyrtle

The manual and installation guide doesn't make much sense to me...

kthorngren

Maybe the examples will help.
https://editor.datatables.net/examples/index

Please let us know if you have specific questions.

Kevin

jmyrtle

@kthorngren Would it be possible to change this code below to match my data?

var editor; // use a global for the submit and return data rendering in the examples
 
$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "../php/staff.php",
        table: "#example",
        fields: [ {
                label: "First name:",
                name: "first_name"
            }, {
                label: "Last name:",
                name: "last_name"
            }, {
                label: "Position:",
                name: "position"
            }, {
                label: "Office:",
                name: "office"
            }, {
                label: "Extension:",
                name: "extn"
            }, {
                label: "Start date:",
                name: "start_date",
                type: "datetime"
            }, {
                label: "Salary:",
                name: "salary"
            }
        ]
    } );
 
    $('#example').DataTable( {
        dom: "Bfrtip",
        ajax: "../php/staff.php",
        columns: [
            { data: null, render: function ( data, type, row ) {
                // Combine the first and last names into a single table field
                return data.first_name+' '+data.last_name;
            } },
            { data: "position" },
            { data: "office" },
            { data: "extn" },
            { data: "start_date" },
            { data: "salary", render: $.fn.dataTable.render.number( ',', '.', 0, '$' ) }
        ],
        select: true,
        buttons: [
            { extend: "create", editor: editor },
            { extend: "edit",   editor: editor },
            { extend: "remove", editor: editor }
        ]
    } );
} );

colin

It would be worth looking at the examples here, and the manual here, that should get you going.

Colin

jmyrtle

@colin The above is the basic initialization code I found. The manual doesn't make much sense to me.

kthorngren

Its hard to help without knowing what your questions are. Trying to rewrite the manual in this thread would be difficult. I'll take a guess that your question is around using array based data. I'm not sure how well that will work but here is an example that shows how to do this in the client:
http://live.datatables.net/kaqudubu/1/edit

This is an example of what would be sent to the server to update the data source:

data[Airi][0]: Airi
data[Airi][2]: Accountant
data[Airi][4]: 28th Nov 08
data[Airi][5]: $162,700
action: edit

The server code would need to know what each column is. You would be better off using object based data. Here is the server side example using object based data:
https://editor.datatables.net/examples/simple/server-side-processing.html

Click on the Ajax Data tab to see what is sent to and received from the server for the updated record.

If this doesn't help then please ask more specific questions.

Kevin

jmyrtle

I see. Of course, my apologies on that.

I already have DataTables, but all I need to do is to implement the editing part. Since you recommended using Editor, I downloaded a trial for now to see how it works. I know that I will need the CDN's to put Editor in place. What I don't understand is what I need to add to my current js file to make editor work?

I also may need the ability to download the database to a CSV or XLSX file (with admin authentication that is)

kthorngren

need the CDN's to put Editor in place

The CDN section of the install docs state this:

The DataTables CDN provides a convenient way to load files that are already hosted on the internet and includes all of our open source software. Editor needs to be hosted locally due to its commercial nature,

You will need to load the Editor JS and CSS files locally.

What I don't understand is what I need to add to my current js file to make editor work?

We've given several examples to look at. Here are the basics:

• You will need to provide the ajax url to save the updates.
• You will use the label to map to the Datatables column. As . Inoted it would probably better and easier fi you use objects instead of array based data. Note in the server side example the label maps to the data option. In the array example I provided the label maps to the array/column position.
• You will define the label to define the label used in the editor form for the field.

If you need more specifics for this then we will need to see an example of your data/data structure.

I also may need the ability to download the database to a CSV or XLSX file (with admin authentication that is)

See this FAQ.

Kevin

jmyrtle

I still can't get the code to work. Now, I have a fatal error in my browser:

jquery.min.js:2 Uncaught TypeError: $.fn.dataTable.Editor is not a constructor
    at HTMLDocument.<anonymous> (table.members.js:10)
    at e (jquery.min.js:2)
    at t (jquery.min.js:2)
(anonymous) @ table.members.js:10
e @ jquery.min.js:2
t @ jquery.min.js:2
setTimeout (async)
k.readyException @ jquery.min.js:2
(anonymous) @ jquery.min.js:2
e @ jquery.min.js:2
t @ jquery.min.js:2
setTimeout (async)
(anonymous) @ jquery.min.js:2
c @ jquery.min.js:2
fireWith @ jquery.min.js:2
fire @ jquery.min.js:2
c @ jquery.min.js:2
fireWith @ jquery.min.js:2
t @ jquery.min.js:2
setTimeout (async)
(anonymous) @ jquery.min.js:2
c @ jquery.min.js:2
fireWith @ jquery.min.js:2
fire @ jquery.min.js:2
c @ jquery.min.js:2
fireWith @ jquery.min.js:2
ready @ jquery.min.js:2
B @ jquery.min.js:2

Here is the JS code that was generated:

/*
 * 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',
        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',
        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));

colin

jquery.min.js:2 Uncaught TypeError: $.fn.dataTable.Editor is not a constructor

This is because you haven't included the Editor JS file. See Kevin's last reply for this one.

jmyrtle

I am not sure which js file(s) to include. I have several of them.

-- dataTables.editor.min.js
-- editor.bootstrap.min.js
-- editor.bootstrap4.min.js
-- editor.foundation.min.js
-- editor.jqueryui.min.js
-- editor.semanticui.min.js
-- table.members.js

kthorngren

The Editor Styling doc discusses which files to use. An easy way to see what to use is by looking at the examples. You will want to look at both the Javascript tab and CSS tab. They will show the Datatables files to load and the order they should be loaded in.

If you aren't using a styling framework like Bootstrap then look at this example:
https://editor.datatables.net/examples/simple/simple.html

If you are using a styling framework then look at the appropriate example here:
https://editor.datatables.net/examples/styling/index.html

Kevin

jmyrtle

I am using Bootstrap 4 since the template I'm using was created with it.

I have tried copying Bootstrap 4's styling links (JS and CSS with CDN's) along with the generated scripts I have and no matter how many times I try, none of them are producing the full table.

kthorngren

none of them are producing the full table.

What does this mean? Are you missing expected rows? Do the search and sort functions work? Do you get errors in your browser's console?

I used the Downlaod Builder to build a set of Datatables files for a basic Bootstrap 4 page. I deselected the concatenate option so you can see all the files. Its recommended to use the concatenated version.

The I added the Editor JS and CSS files as documented in this example:
https://editor.datatables.net/examples/styling/bootstrap4.html

Note that the Editor files are to be loaded locally not via CDN or from the URL shown in my example.

Here is my example: http://live.datatables.net/guwafemu/28/edit

Without seeing your page it will be difficult to help. Can you post a link to your page or build up or modify my test case to show the issues?
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

jmyrtle

Here is my webpage:

  <!-- Custom fonts for this template -->
  <link href="vendor/fontawesome-free/css/all.min.css" rel="stylesheet" type="text/css">
  <link href="https://fonts.googleapis.com/css?family=Nunito:200,200i,300,300i,400,400i,600,600i,700,700i,800,800i,900,900i" rel="stylesheet">

  <!-- Custom styles for this template -->
  <link href="css/sb-admin-2.min.css" rel="stylesheet">

  <!-- Custom styles for this page -->
  <link href="vendor/datatables/dataTables.bootstrap4.min.css" rel="stylesheet">
  <link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.css" rel="stylesheet">
  <link href="https://cdn.datatables.net/1.10.20/css/dataTables.bootstrap4.min.css" rel="stylesheet">
  <link href="https://cdn.datatables.net/buttons/1.6.1/css/buttons.bootstrap4.min.css" rel="stylesheet">
  <link href="https://cdn.datatables.net/select/1.3.1/css/select.bootstrap4.min.css" rel="stylesheet">
  <link href="css/editor/editor.bootstrap4.min.css" rel="stylesheet">
  
...
<!-- Bypass Body Code -->
...

  <!-- Bootstrap core JavaScript-->
  <script src="vendor/jquery/jquery.min.js"></script>
  <script src="vendor/bootstrap/js/bootstrap.bundle.min.js"></script>

  <!-- Core plugin JavaScript-->
  <script src="vendor/jquery-easing/jquery.easing.min.js"></script>

  <!-- Custom scripts for all pages-->
  <script src="js/sb-admin-2.min.js"></script>

  <!-- Page level plugins -->
  <script src="vendor/datatables/jquery.dataTables.min.js"></script>
  <script src="vendor/datatables/dataTables.bootstrap4.min.js"></script>

  <!-- Page level custom scripts -->
  <script src="js/datatables.js"></script>
  
  <!-- Editor CSS -->
  <script src="https://code.jquery.com/jquery-3.3.1.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.3/umd/popper.min.js|https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/js/bootstrap.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.20/js/dataTables.bootstrap4.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.6.1/js/dataTables.buttons.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.6.1/js/buttons.bootstrap4.min.js"></script>
  <script src="https://cdn.datatables.net/select/1.3.1/js/dataTables.select.min.js"></script>
  <script src="js/editor/dataTables.editor.min.js"></script>
  <script src="js/editor/editor.bootstrap4.min.js"></script>
  

</body>

</html>

I do not see the New, Edit or Delete functions, nor can I select the row to edit... It's almost like editor isn't even working. Yet the styles work?

kthorngren

You are loading jquery.js twice (lines 21 and 38). It should only be loaded once. You are loading datatables.js and dataTables.bootstrap4.min.js multiple times (Lines 31,32 and lines 40,41 and 35). These also should only be loaded once.

See if removing the duplicates helps;

Kevin

jmyrtle

It didn't help. I also see this error inside my browser console:

GET https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.3/umd/popper.min.js%7Chttps://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/js/bootstrap.min.js net::ERR_ABORTED 404

kthorngren

HTTP 404 response means "Not Found". The server is telling you it can't find what you requested. Looks like line 39 has two URLs. THey probably should be split into separate lines. The 2nd URL is bootstrap.js. Looks like you are loading it on line 22.

Keep in mind that sometimes order is important as some JS include files require others to be loaded first. Like datatables.js requires jquery.js to be loaded first. Also load them only once or you will have conflicts.

Looks like you are trying to build a complex page. My suggestion is to start with something simpler and just build a simpel page using Datatables and Editor then once you get that going slowly add the other components.

Kevin

jmyrtle

The template I'm using has DataTables included, so I am using what the template has provided. I'm trying to add Editor functionality while my trial is going on. I only have 9 days left and it isn't working. Without Editor functionality, the DataTable works, but I cannot add, edit or delete records from my database.

kthorngren

Allan will extend your trial if needed.

Based on what you have provided I have told you what needs fixed. I'm not sure what your template contains and what you are adding to it for Editor but if you have a working page with Bootstrap 4 then all you should need to add the Editor code and if you don't already have them the Select and Buttons extension. Just add the following to the end of your CSS includes:

  <link href="https://cdn.datatables.net/buttons/1.6.1/css/buttons.bootstrap4.min.css" rel="stylesheet">
  <link href="https://cdn.datatables.net/select/1.3.1/css/select.bootstrap4.min.css" rel="stylesheet">
  <link href="css/editor/editor.bootstrap4.min.css" rel="stylesheet">

Add this to the end of your Javascript includes:

  <script src="https://cdn.datatables.net/buttons/1.6.1/js/dataTables.buttons.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.6.1/js/buttons.bootstrap4.min.js"></script>
  <script src="https://cdn.datatables.net/select/1.3.1/js/dataTables.select.min.js"></script>
  <script src="js/editor/dataTables.editor.min.js"></script>
  <script src="js/editor/editor.bootstrap4.min.js"></script>

Only add the select and buttons code if you aren't including them elsewhere on your page.

As I mentioned before duplicating the JS include files does not work. It causes problems for Datataables and likely other JS libraries.

Kevin

jmyrtle

I added the code posted above and still do not see any buttons or select functionality nor do I see anywhere else in my code that they are mentioned.

UPDATE: I now have functionality, but I do not see my data.

kthorngren

I realized my example above had more JS include files than needed. Here is the updated example:
http://live.datatables.net/guwafemu/30/edit

Unfortunately I can't see what you are doing so I'm not sure how to help at this point. I suggested before to try building a basic page to see if you can get that to work. Once you get it working then start merging slowly with your templated page until you find the problem.

It is possible that some of your includes are concatenated with jquery.js which would duplicate it being installed. You would have to experiment by removing the jquery..js or opening the other JS files to see if they note including jquery.js.

Do you have any errors in your browser's console?

You either have a conflict with loading something twice or the page is not loading the buttons or editor code.

Kevin

jmyrtle

My browser's console says this:

jquery.min.js:2 Uncaught TypeError: Cannot set property 'nTf' of undefined
at lb (jquery.dataTables.min.js:27)
at ha (jquery.dataTables.min.js:48)
at e (jquery.dataTables.min.js:93)
at HTMLTableElement.<anonymous> (jquery.dataTables.min.js:93)
at Function.each (jquery.min.js:2)
at k.fn.init.each (jquery.min.js:2)
at k.fn.init.n [as dataTable] (jquery.dataTables.min.js:83)
at k.fn.init.h.fn.DataTable (jquery.dataTables.min.js:165)
at HTMLDocument.<anonymous> (table.members.js:58)
at e (jquery.min.js:2)

kthorngren

That looks like a Datatables error not an Editor error. Do you get the error without the Editor code?

There are lots of threads with this error such as this one. I think typically its a mismatch between the HTML table and Datatables config. Like for example the table header columns don't match the number of columns defined in Datatables. Above you defined 10 columns in Datatables. Does your thead contain 10 columns?

That error is likely stopping Javascript processing and causing the buttons to not load, etc.

When the page is not behaving as expected the first place to look is at the browser's console for errors.

Kevin

jmyrtle

I have 11 columns instead of 10. I removed one of them and that cleared the console, but now I'm left with this:

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

kthorngren

Follow the troubleshooting steps in the link provided.
http://datatables.net/tn/4

What is the server returning for data?

Your first code snippet didn't use columns.data. Are you now returning object based data? It is recommended to do so as it makes it easier to use Editor.

Kevin