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.