Skip to content
Advertisement

CRUD table with Ajax + Datatables does not work

I’m creating a CRUD table with Ajax + Datatables, but for any reason, my code does not work.

I can get my json response with my data from my database, but I don’t know why, it can’t be printed on my table.

Im using 2 files:

1.- main.php:

<?php

    get_header_admin('Alex - AlexCRUD');
    get_content_admin('Alex', 'AlexCRUD');

?>

<!doctype html>
<html lang="es">
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
        <link rel="shortcut icon" href="#" />
        <title>Alex CRUD</title>
        <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
    </head>

    <body>
        <header>
            <h3 class='text-center'>Tabla CRUD con Ajax</h3>
        </header>

        <div class="container">
            <div class="row">
                <div class="col-lg-12">
                    <button id="btnNuevo" type="button" class="btn btn-info" data-toggle="modal"><i class="material-icons">library_add</i></button>
                </div>
            </div>
        </div>
        
        <br>
        
        <div class="container caja">
            <div class="row">
                <div class="col-lg-12 col-sm-12">
                    <div>
                        <table id="tablaUsuarios" class="table table-striped table-bordered table-condensed" style="width:100%" >
                            <thead class="text-center">
                                <tr>
                                    <!-- <th>ID</th> -->
                                    <th>Usuario</th>
                                    <th>Apellido 1</th>
                                    <th>Apellido 2</th>
                                    <th>Email</th>
                                    <th>Acciones</th>
                                </tr>
                            </thead>
                            <tbody>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>

        <div class="modal fade" id="modalCRUD" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">
            <div class="modal-dialog" role="document">
                <div class="modal-content">
                    <div class="modal-header">
                        <h5 class="modal-title" id="exampleModalLabel"></h5>
                        <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                    </div>
                    <form id="formUsuarios">
                        <div class="modal-body">
                            <div class="row">
                                <div class="col-lg-6">
                                    <div class="form-group">
                                        <label for="" class="col-form-label">Usuario:</label>
                                        <input type="text" class="form-control" id="usuario">
                                    </div>
                                </div>
                                <div class="col-lg-6">
                                    <div class="form-group">
                                        <label for="" class="col-form-label">Apellido 1</label>
                                        <input type="text" class="form-control" id="apellido1">
                                    </div>
                                </div>
                            </div>
                            <div class="row">
                                <div class="col-lg-6">
                                    <div class="form-group">
                                        <label for="" class="col-form-label">Apellido 2</label>
                                        <input type="text" class="form-control" id="apellido2">
                                    </div>
                                </div>
                                <div class="col-lg-6">
                                    <div class="form-group">
                                        <label for="" class="col-form-label">Email</label>
                                        <input type="text" class="form-control" id="email">
                                    </div>
                                </div>
                            </div>
                        </div>
                        <div class="modal-footer">
                            <button type="button" class="btn btn-light" data-dismiss="modal">Cancelar</button>
                            <button type="submit" id="btnGuardar" class="btn btn-dark">Guardar</button>
                        </div>
                    </form>
                </div>
            </div>
        </div>
                        
        <script>
        
            $(document).ready(function() {

                var id, opcion;
                opcion = 4;

                tablaUsuarios = $("#tablaUsuarios").DataTable({
                    "language": {"url": "//cdn.datatables.net/plug-ins/1.10.20/i18n/Spanish.json"},  
                    "paging": true,
                    "lengthChange": false,
                    "searching": true,
                    "ordering": true,
                    "info": true,
                    "autoWidth": false,
                    "scrollX": false,
                    
                    "ajax": {
                        "url": '<?=SITE_URL_ADMIN?>/alexcrud/crud',
                        "method": 'POST', //usamos el metodo POST
                        "data": {opcion: opcion}, //enviamos opcion 4 para que haga un SELECT
                        "dataSrc": ""
                    },
                    
                    "columns": [
                        {"data": "id"},
                        {"data": "usuario"},
                        {"data": "apellido1"},
                        {"data": "apellido2"},
                        {"data": "email"},
                        {"defaultContent": "<div class='text-center'><div class='btn-group'><button class='btn btn-primary btn-sm btnEditar'><i class='material-icons'>edit</i></button><button class='btn btn-danger btn-sm btnBorrar'><i class='material-icons'>delete</i></button></div></div>"}
                    ],
                    success: function(data)   // A function to be called if request succeeds
                    {
                        console.log(data);
                    }
                });

                var fila; // Captura la fila, para editar o eliminar.

                // Submit para el Alta y Actualización.
                $('#formUsuarios').submit(function(e){
                    e.preventDefault(); // Evita el comportambiento normal del submit.
                    id = '1';
                    usuario = $.trim($('#usuario').val());
                    apellido1 = $.trim($('#apellido1').val());
                    apellido2 = $.trim($('#apellido2').val());
                    email = $.trim($('#email').val());
                        $.ajax({
                            "url": '<?=SITE_URL_ADMIN?>/alexcrud/crud',
                            type: "POST",
                            datatype: "json",
                            data: {id:id, usuario:usuario, apellido1:apellido1, apellido2:apellido2, email:email, opcion:opcion},
                            success: function(data) {
                                tablaUsuarios.ajax.reload(null, false);
                            }
                        });
                    $('#modalCRUD').modal('hide');
                });

                // Para limpiar los campos antes de dar de Alta una Persona.
                $("#btnNuevo").click(function(){
                    opcion = 1; // Alta
                    id = null;
                    $("#formUsuarios").trigger("reset");
                    $(".modal-header").css( "background-color", "#17a2b8");
                    $(".modal-header").css( "color", "white" );
                    $(".modal-title").text("Alta de Usuario");
                    $('#modalCRUD').modal('show');
                });

                // Editar
                $(document).on("click", ".btnEditar", function(){
                    opcion = 2; // Editar
                    fila = $(this).closest("tr");
                    id = parseInt(fila.find('td:eq(0)').text()); // Capturo el ID
                    usuario = fila.find('td:eq(1)').text();
                    apellido1 = fila.find('td:eq(2)').text();
                    apellido2 = fila.find('td:eq(3)').text();
                    email = fila.find('td:eq(4)').text();
                    $("#usuario").val(usuario);
                    $("#apellido1").val(apellido1);
                    $("#apellido2").val(apellido2);
                    $("#email").val(email);
                    $(".modal-header").css("background-color", "#007bff");
                    $(".modal-header").css("color", "white" );
                    $(".modal-title").text("Editar Usuario");
                    $('#modalCRUD').modal('show');
                });

                // Borrar
                $(document).on("click", ".btnBorrar", function(){
                    fila = $(this);
                    id = parseInt($(this).closest('tr').find('td:eq(0)').text());
                    opcion = 3; // Eliminar
                    var respuesta = confirm("¿Está seguro de borrar el registro " + id + "?");
                    if (respuesta) {
                        $.ajax({
                            "url": '<?=SITE_URL_ADMIN?>/alexcrud/crud',
                            type: "POST",
                            datatype:"json",
                            data:  {opcion:opcion, id:id},
                            success: function() {
                                tablaUsuarios.row(fila.parents('tr')).remove().draw();
                            }
                        });
                    }
                });
                
            });

        </script>

    </body>
</html>

<?php

    get_footer_admin();

?>

2.- crud.php:

<?php

    global $DB;

    $id = (isset($_POST['id'])) ? $_POST['id'] : '';
    $usuario = (isset($_POST['usuario'])) ? $_POST['usuario'] : '';
    $apellido1 = (isset($_POST['apellido1'])) ? $_POST['apellido1'] : '';
    $apellido2 = (isset($_POST['apellido2'])) ? $_POST['apellido2'] : '';
    $email = (isset($_POST['email'])) ? $_POST['email'] : '';

    $opcion = (isset($_POST['opcion'])) ? $_POST['opcion'] : '';
    
    /* PRE Queries */
    
        $draw = $_POST['draw'];
        $row = $_POST['start'];
        $rowperpage = $_POST['length']; // Rows display per page
        $columnIndex = $_POST['order'][0]['column']; // Column index
        $columnName = $_POST['columns'][$columnIndex]['data']; // Column name
        $columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
        $searchValue = $_POST['search']['value']; // Search value

        // Search
        $searchQuery = " ";
        if($searchValue != ''){
            $searchQuery = " and (nombre LIKE '%".$searchValue."%' or apellido1 like '%".$searchValue."%' or apellido2 like '%".$searchValue."%')";
        }
            
        // Total number of records without filtering
        $sel = "select count(*) as allcount from users_test";
        $records = $DB->get_record($sel);
        $totalRecords = $records->allcount;

        // Total number of record with filtering
        $sel = "select count(*) as allcount from users_test WHERE 1 ".$searchQuery;
        $records = $DB->get_record($sel);
        $totalRecordwithFilter = $records->allcount;

    switch($opcion){

        case 1: // Alta.
            $consulta = "INSERT INTO users_test (id, usuario, apellido1, apellido2, email) VALUES('$id', '$usuario', '$apellido1', '$apellido2', '$email') ";
            $resultado = $DB->query($consulta);

            $consulta = "SELECT id, usuario, apellido1, apellido2, email FROM users_test";
            $resultado = $DB->get_records($consulta);
            $data = $resultado;

            break;

        case 2: // Editar.

            $consulta = "UPDATE users_test SET usuario = '$usuario', apellido1='$apellido1', apellido2='$apellido2', email='$email' WHERE id = '$id' ";
            $resultado = $DB->query($consulta);

            $consulta = "SELECT id, usuario, apellido1, apellido2, email FROM users_test"; // $consulta = "SELECT id, usuario, apellido1, apellido2, email FROM users_test WHERE id='$id' ";
            $resultado = $DB->get_records($consulta);
            $data = $resultado;

            break;

        case 3: // Borrar.

            $consulta = "DELETE FROM users_test WHERE id = '$id'";
            $resultado = $DB->query($consulta);
            $data = $resultado;

            break;

        case 4: // Ver datos.

            $consulta = "SELECT id, usuario, apellido1, apellido2, email FROM users_test"; // WHERE 1 and ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;;
            $resultado = $DB->get_records($consulta);

            $data = array();

            foreach($resultado as $res){
                $nestedData = array();
                $nestedData[id] = $res->id;
                $nestedData[usuario] = $res->usuario;
                $nestedData[apellido1] = $res->apellido1;
                $nestedData[apellido2] = $res->apellido2;
                $nestedData[email] = $res->email;
                $data[] = $nestedData;
            }

            $json_data = array(
                    "draw" => intval($draw),
                    "iTotalRecords" => $totalRecords,
                    "iTotalDisplayRecords" => $totalRecordwithFilter, // Total number of records after searching, if there is no searching then totalFiltered = totalData
                    "data" => $data   // Total data array
            );

            echo json_encode($json_data);
            
            break;
    }

?>

Just to clarify some things … I’m working under a custom CMS, that’s why I don’t need to import bootstrap, jquery or Datatables, because is already imported.

Also, I’m loading header, content and footer from a custom function, so … Just to clatify that.

So, this is my table (I can show the data):

enter image description here

And this is my json response I get when I refresh my page and my Ajax call is thrown:

enter image description here

I can’t show my data completelly, but I get all data from my database, the information what I need, so Ajax is working properly.

I need to show what I get from my response on my table. How can do that? My code is not working 🙁

Thanks in advance, guys

Advertisement

Answer

Remove "dataSrc": "" – you should not need to use the dataSrc option at all, because it looks as if your JSON row data is already in a top-level named array: { ... "data": [...] ... }.

By default, the name used by DataTables for the row iteration entry point is data. So, not using dataSrc at all is the same as using "dataSrc": "data".

If you use "dataSrc": "", that is telling DataTables that your row array is in a JSON structure like this – an array of arrays:

[ [...], [...],  ]

Or like this – an array of objects:

[ {...}, {...},  ]

And in both of the above cases, that outer array is the top-level of the JSON string, and it does not have a name, and there is no containing {...}.

The dataSrc option is documented here, for reference.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement