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:
JavaScript
x
224
224
1
<?php2
3
get_header_admin('Alex - AlexCRUD');4
get_content_admin('Alex', 'AlexCRUD');5
6
?>7
8
<!doctype html>9
<html lang="es">10
<head>11
<meta charset="utf-8">12
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">13
<link rel="shortcut icon" href="#" />14
<title>Alex CRUD</title>15
<link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">16
</head>17
18
<body>19
<header>20
<h3 class='text-center'>Tabla CRUD con Ajax</h3>21
</header>22
23
<div class="container">24
<div class="row">25
<div class="col-lg-12">26
<button id="btnNuevo" type="button" class="btn btn-info" data-toggle="modal"><i class="material-icons">library_add</i></button>27
</div>28
</div>29
</div>30
31
<br>32
33
<div class="container caja">34
<div class="row">35
<div class="col-lg-12 col-sm-12">36
<div>37
<table id="tablaUsuarios" class="table table-striped table-bordered table-condensed" style="width:100%" >38
<thead class="text-center">39
<tr>40
<!-- <th>ID</th> -->41
<th>Usuario</th>42
<th>Apellido 1</th>43
<th>Apellido 2</th>44
<th>Email</th>45
<th>Acciones</th>46
</tr>47
</thead>48
<tbody>49
</tbody>50
</table>51
</div>52
</div>53
</div>54
</div>55
56
<div class="modal fade" id="modalCRUD" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">57
<div class="modal-dialog" role="document">58
<div class="modal-content">59
<div class="modal-header">60
<h5 class="modal-title" id="exampleModalLabel"></h5>61
<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>62
</div>63
<form id="formUsuarios">64
<div class="modal-body">65
<div class="row">66
<div class="col-lg-6">67
<div class="form-group">68
<label for="" class="col-form-label">Usuario:</label>69
<input type="text" class="form-control" id="usuario">70
</div>71
</div>72
<div class="col-lg-6">73
<div class="form-group">74
<label for="" class="col-form-label">Apellido 1</label>75
<input type="text" class="form-control" id="apellido1">76
</div>77
</div>78
</div>79
<div class="row">80
<div class="col-lg-6">81
<div class="form-group">82
<label for="" class="col-form-label">Apellido 2</label>83
<input type="text" class="form-control" id="apellido2">84
</div>85
</div>86
<div class="col-lg-6">87
<div class="form-group">88
<label for="" class="col-form-label">Email</label>89
<input type="text" class="form-control" id="email">90
</div>91
</div>92
</div>93
</div>94
<div class="modal-footer">95
<button type="button" class="btn btn-light" data-dismiss="modal">Cancelar</button>96
<button type="submit" id="btnGuardar" class="btn btn-dark">Guardar</button>97
</div>98
</form>99
</div>100
</div>101
</div>102
103
<script>104
105
$(document).ready(function() {106
107
var id, opcion;108
opcion = 4;109
110
tablaUsuarios = $("#tablaUsuarios").DataTable({111
"language": {"url": "//cdn.datatables.net/plug-ins/1.10.20/i18n/Spanish.json"}, 112
"paging": true,113
"lengthChange": false,114
"searching": true,115
"ordering": true,116
"info": true,117
"autoWidth": false,118
"scrollX": false,119
120
"ajax": {121
"url": '<?=SITE_URL_ADMIN?>/alexcrud/crud',122
"method": 'POST', //usamos el metodo POST123
"data": {opcion: opcion}, //enviamos opcion 4 para que haga un SELECT124
"dataSrc": ""125
},126
127
"columns": [128
{"data": "id"},129
{"data": "usuario"},130
{"data": "apellido1"},131
{"data": "apellido2"},132
{"data": "email"},133
{"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>"}134
],135
success: function(data) // A function to be called if request succeeds136
{137
console.log(data);138
}139
});140
141
var fila; // Captura la fila, para editar o eliminar.142
143
// Submit para el Alta y Actualización.144
$('#formUsuarios').submit(function(e){145
e.preventDefault(); // Evita el comportambiento normal del submit.146
id = '1';147
usuario = $.trim($('#usuario').val());148
apellido1 = $.trim($('#apellido1').val());149
apellido2 = $.trim($('#apellido2').val());150
email = $.trim($('#email').val());151
$.ajax({152
"url": '<?=SITE_URL_ADMIN?>/alexcrud/crud',153
type: "POST",154
datatype: "json",155
data: {id:id, usuario:usuario, apellido1:apellido1, apellido2:apellido2, email:email, opcion:opcion},156
success: function(data) {157
tablaUsuarios.ajax.reload(null, false);158
}159
});160
$('#modalCRUD').modal('hide');161
});162
163
// Para limpiar los campos antes de dar de Alta una Persona.164
$("#btnNuevo").click(function(){165
opcion = 1; // Alta166
id = null;167
$("#formUsuarios").trigger("reset");168
$(".modal-header").css( "background-color", "#17a2b8");169
$(".modal-header").css( "color", "white" );170
$(".modal-title").text("Alta de Usuario");171
$('#modalCRUD').modal('show');172
});173
174
// Editar175
$(document).on("click", ".btnEditar", function(){176
opcion = 2; // Editar177
fila = $(this).closest("tr");178
id = parseInt(fila.find('td:eq(0)').text()); // Capturo el ID179
usuario = fila.find('td:eq(1)').text();180
apellido1 = fila.find('td:eq(2)').text();181
apellido2 = fila.find('td:eq(3)').text();182
email = fila.find('td:eq(4)').text();183
$("#usuario").val(usuario);184
$("#apellido1").val(apellido1);185
$("#apellido2").val(apellido2);186
$("#email").val(email);187
$(".modal-header").css("background-color", "#007bff");188
$(".modal-header").css("color", "white" );189
$(".modal-title").text("Editar Usuario");190
$('#modalCRUD').modal('show');191
});192
193
// Borrar194
$(document).on("click", ".btnBorrar", function(){195
fila = $(this);196
id = parseInt($(this).closest('tr').find('td:eq(0)').text());197
opcion = 3; // Eliminar198
var respuesta = confirm("¿Está seguro de borrar el registro " + id + "?");199
if (respuesta) {200
$.ajax({201
"url": '<?=SITE_URL_ADMIN?>/alexcrud/crud',202
type: "POST",203
datatype:"json",204
data: {opcion:opcion, id:id},205
success: function() {206
tablaUsuarios.row(fila.parents('tr')).remove().draw();207
}208
});209
}210
});211
212
});213
214
</script>215
216
</body>217
</html>218
219
<?php220
221
get_footer_admin();222
223
?>224
2.- crud.php:
JavaScript
1
100
100
1
<?php2
3
global $DB;4
5
$id = (isset($_POST['id'])) ? $_POST['id'] : '';6
$usuario = (isset($_POST['usuario'])) ? $_POST['usuario'] : '';7
$apellido1 = (isset($_POST['apellido1'])) ? $_POST['apellido1'] : '';8
$apellido2 = (isset($_POST['apellido2'])) ? $_POST['apellido2'] : '';9
$email = (isset($_POST['email'])) ? $_POST['email'] : '';10
11
$opcion = (isset($_POST['opcion'])) ? $_POST['opcion'] : '';12
13
/* PRE Queries */14
15
$draw = $_POST['draw'];16
$row = $_POST['start'];17
$rowperpage = $_POST['length']; // Rows display per page18
$columnIndex = $_POST['order'][0]['column']; // Column index19
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name20
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc21
$searchValue = $_POST['search']['value']; // Search value22
23
// Search24
$searchQuery = " ";25
if($searchValue != ''){26
$searchQuery = " and (nombre LIKE '%".$searchValue."%' or apellido1 like '%".$searchValue."%' or apellido2 like '%".$searchValue."%')";27
}28
29
// Total number of records without filtering30
$sel = "select count(*) as allcount from users_test";31
$records = $DB->get_record($sel);32
$totalRecords = $records->allcount;33
34
// Total number of record with filtering35
$sel = "select count(*) as allcount from users_test WHERE 1 ".$searchQuery;36
$records = $DB->get_record($sel);37
$totalRecordwithFilter = $records->allcount;38
39
switch($opcion){40
41
case 1: // Alta.42
$consulta = "INSERT INTO users_test (id, usuario, apellido1, apellido2, email) VALUES('$id', '$usuario', '$apellido1', '$apellido2', '$email') ";43
$resultado = $DB->query($consulta);44
45
$consulta = "SELECT id, usuario, apellido1, apellido2, email FROM users_test";46
$resultado = $DB->get_records($consulta);47
$data = $resultado;48
49
break;50
51
case 2: // Editar.52
53
$consulta = "UPDATE users_test SET usuario = '$usuario', apellido1='$apellido1', apellido2='$apellido2', email='$email' WHERE id = '$id' ";54
$resultado = $DB->query($consulta);55
56
$consulta = "SELECT id, usuario, apellido1, apellido2, email FROM users_test"; // $consulta = "SELECT id, usuario, apellido1, apellido2, email FROM users_test WHERE id='$id' ";57
$resultado = $DB->get_records($consulta);58
$data = $resultado;59
60
break;61
62
case 3: // Borrar.63
64
$consulta = "DELETE FROM users_test WHERE id = '$id'";65
$resultado = $DB->query($consulta);66
$data = $resultado;67
68
break;69
70
case 4: // Ver datos.71
72
$consulta = "SELECT id, usuario, apellido1, apellido2, email FROM users_test"; // WHERE 1 and ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;;73
$resultado = $DB->get_records($consulta);74
75
$data = array();76
77
foreach($resultado as $res){78
$nestedData = array();79
$nestedData[id] = $res->id;80
$nestedData[usuario] = $res->usuario;81
$nestedData[apellido1] = $res->apellido1;82
$nestedData[apellido2] = $res->apellido2;83
$nestedData[email] = $res->email;84
$data[] = $nestedData;85
}86
87
$json_data = array(88
"draw" => intval($draw),89
"iTotalRecords" => $totalRecords,90
"iTotalDisplayRecords" => $totalRecordwithFilter, // Total number of records after searching, if there is no searching then totalFiltered = totalData91
"data" => $data // Total data array92
);93
94
echo json_encode($json_data);95
96
break;97
}98
99
?>100
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:
JavaScript
1
2
1
[ [], [], ]2
Or like this – an array of objects:
JavaScript
1
2
1
[ {}, {}, ]2
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.