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">×</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):
And this is my json response I get when I refresh my page and my Ajax call is thrown:
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.