I have adapted an example of datatable with a datepicker. Now I want to add the functions to edit and delete records. I have another example with which I can edit and delete rows from the database, but I am not able to adapt it to make it work in the datatable with datepicker.
It has been very easy for me to adapt the two examples separately, but after several hours I find myself unable to make them work together.
When I click on the link "edit" it takes me to edit.php taking the ID number of the row, but it does not load the values assigned to me "client" and "provider"
When I click on the "delete" link it takes me to the url delete.php, taking the ID number and staying in it without deleting it. In the base example, when I click on the "delete" link, delete the record automatically and return to the "index.php" page.
I leave you link to the demo: link
Here I show the table with all the records, with the filters by date and a filter that searches in all the cells. index.php
<html>
<head>
<title>Filtrar datos por fechas usando datatables con PHP y MySQL</title>
<link rel="stylesheet" href="bootstrap-3.3.7/css/bootstrap.min.css">
<link rel="stylesheet" href="bootstrap-3.3.7/css/csscustom.css">
<link href="plugins/datatables/dataTables.bootstrap.css" rel="stylesheet" type="text/css" />
<link rel="stylesheet" href="plugins/datepicker/datepicker3.css">
<meta charset="UTF-8">
<style>
body
{
margin:0;
padding:0;
background-color:#f1f1f1;
}
.box
{
width:1270px;
padding:20px;
background-color:#fff;
border:2px solid #ccc;
border-radius:5px;
margin-top:25px;
}
</style>
</head>
<body>
<div class="container box">
<h1 align="center"> Filtrar datos por fechas usando datatables con PHP y MySQL</h1>
<br />
<div class="table-responsive" style="overflow-x: hidden;">
<br />
<div class="row">
<div class="input-daterange">
<div class="col-md-4">
<input type="text" name="start_date" id="start_date" class="form-control" />
</div>
<div class="col-md-4">
<input type="text" name="end_date" id="end_date" class="form-control" />
</div>
</div>
<div class="col-md-4">
<input type="button" name="search" id="search" value="Buscar" class="btn btn-info active" />
</div>
</div>
<br />
<table id="order_data" class="table table-striped table-hover">
<thead>
<tr>
<th>Orden ID</th>
<th>Documento</th>
<th>Cliente</th>
<th>Producto</th>
<th>Precio</th>
<th>Iva</th>
<th>Estado</th>
<th>Fecha</th>
<th>Editar</th>
<th>Eliminar</th>
</tr>
</thead>
</table>
</div>
</div>
<script src="bootstrap-3.3.7/js/jQuery-2.1.4.min.js"></script>
<script src="bootstrap-3.3.7/js/bootstrap.min.js"></script>
<script src="plugins/datepicker/bootstrap-datepicker.js"></script>
<script src="plugins/datatables/jquery.dataTables.js" type="text/javascript"></script>
<script src="plugins/datatables/dataTables.bootstrap.js" type="text/javascript"></script>
</body>
</html>
<script type="text/javascript" language="javascript" >
$(document).ready(function(){
$('.input-daterange').datepicker({
"locale": {
"separator": " - ",
"applyLabel": "Aplicar",
"cancelLabel": "Cancelar",
"fromLabel": "Desde",
"toLabel": "Hasta",
"customRangeLabel": "Custom",
"daysOfWeek": [
"Do",
"Lu",
"Ma",
"Mi",
"Ju",
"Vi",
"Sa"
],
"monthNames": [
"Enero",
"Febrero",
"Marzo",
"Abril",
"Mayo",
"Junio",
"Julio",
"Agosto",
"Septiembre",
"Octubre",
"Noviembre",
"Diciembre"
],
"firstDay": 1
},
format: "yyyy-mm-dd",
autoclose: true
});
fetch_data('no');
function fetch_data(is_date_search, start_date='', end_date='')
{
var dataTable = $('#order_data').DataTable({
"language":{
"lengthMenu":"Mostrar _MENU_ registros por página.",
"zeroRecords": "Lo sentimos. No se encontraron registros.",
"info": "Mostrando página _PAGE_ de _PAGES_",
"infoEmpty": "No hay registros aún.",
"infoFiltered": "(filtrados de un total de _MAX_ registros)",
"search" : "Búsqueda",
"LoadingRecords": "Cargando ...",
"Processing": "Procesando...",
"SearchPlaceholder": "Comience a teclear...",
"paginate": {
"previous": "Anterior",
"next": "Siguiente",
}
},
"processing" : true,
"serverSide" : true,
"sort": false,
"order" : [],
"ajax" : {
url:"ajax.php",
type:"POST",
data:{
is_date_search:is_date_search, start_date:start_date, end_date:end_date
}
}
});
}
$('#search').click(function(){
var start_date = $('#start_date').val();
var end_date = $('#end_date').val();
if(start_date != '' && end_date !='')
{
$('#order_data').DataTable().destroy();
fetch_data('yes', start_date, end_date);
}
else
{
alert("Por favor seleccione la fecha");
}
});
});
</script>
With this file I consult the database. In the initial example I did not have the edit and delete columns, I added them to "posteriori".
ajax.php
<?php
$connect = mysqli_connect("localhost", "user", "password", "databasename");//Configurar los datos de conexion
$columns = array('idviaje','cliente', 'proveedor', 'nombre_carga1', 'nombre_carga2', 'referencia_cliente','referencia_carga1', 'fecha_carga');
$query = "SELECT * FROM viajes WHERE ";
if($_POST["is_date_search"] == "yes")
{
$query .= 'fecha_carga BETWEEN "'.$_POST["start_date"].'" AND "'.$_POST["end_date"].'" AND ';
}
if(isset($_POST["search"]["value"]))
{
$query .= '
(idviaje LIKE "%'.$_POST["search"]["value"].'%"
OR proveedor LIKE "%'.$_POST["search"]["value"].'%"
OR nombre_carga1 LIKE "%'.$_POST["search"]["value"].'%"
OR cliente LIKE "%'.$_POST["search"]["value"].'%"
OR nombre_carga2 LIKE "%'.$_POST["search"]["value"].'%")
';
}
if(isset($_POST["order"]))
{
$query .= 'ORDER BY '.$columns[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].'
';
}
else
{
$query .= 'ORDER BY idviaje DESC ';
}
$query1 = '';
if($_POST["length"] != -1)
{
$query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$number_filter_row = mysqli_num_rows(mysqli_query($connect, $query));
$result = mysqli_query($connect, $query . $query1);
$data = array();
while($row = mysqli_fetch_array($result))
{
$idviaje=$row["idviaje"];
$fecha_carga=date("d/m/Y", strtotime($row["fecha_carga"]));
$sub_array = array();
$sub_array[] = $row["idviaje"];
$sub_array[] = $row["cliente"];
$sub_array[] = $row["proveedor"];
$sub_array[] = $row["nombre_carga1"];
$sub_array[] = $row["nombre_carga2"];
$sub_array[] = $row["referencia_cliente"];
$sub_array[] = $row["referencia_carga1"];
$sub_array[] = $fecha_carga;
$sub_array[] = '<a href="edit.php?id='.$idviaje.'" class="btn btn-xs default">Editar</a>';
$sub_array[] = '<a href="delete.php?id='.$idviaje.'" ">Eliminar</a>';
$data[] = $sub_array;
}
function get_all_data($connect)
{
$query = "SELECT * FROM viajes";
$result = mysqli_query($connect, $query);
return mysqli_num_rows($result);
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => get_all_data($connect),
"recordsFiltered" => $number_filter_row,
"data" => $data
);
echo json_encode($output);
?>
With ester file I delete records from the database with your ID.
delete.php
<?php
/*
Author: Javed Ur Rehman
Website: https://www.allphptricks.com/
*/
require('db.php');
$id=$_REQUEST['idviaje'];
$query = "DELETE FROM viajes WHERE idviaje=$id";
$result = mysqli_query($con,$query) or die ( mysqli_error());
header("Location: index.php");
?>
With this file I upload the data with the ID identifier and update them.
edit.php
<?php
/*
Author: Javed Ur Rehman
Website: https://www.allphptricks.com/
*/
require('db.php');
$idviaje=$_REQUEST['idviaje'];
$query = "SELECT * from viajes where idviaje='".$idviaje."'";
$result = mysqli_query($con, $query) or die ( mysqli_error());
$row = mysqli_fetch_assoc($result);
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Update Record</title>
<link rel="stylesheet" href="css/style.css" />
</head>
<body>
<div class="form">
<p><a href="dashboard.php">Dashboard</a> | <a href="insert.php">Insert New Record</a> | <a href="logout.php">Logout</a></p>
<h1>Update Record</h1>
<?php
$status = "";
if(isset($_POST['new']) && $_POST['new']==1)
{
$idviaje=$_REQUEST['idviaje'];
$name =$_REQUEST['cliente'];
$age =$_REQUEST['proveedor'];
$update="update viajes set cliente='".$name."', proveedor='".$age."' where idviaje='".$idviaje."'";
mysqli_query($con, $update) or die(mysqli_error());
$status = "Record Updated Successfully. </br></br><a href='view.php'>View Updated Record</a>";
echo '<p style="color:#FF0000;">'.$status.'</p>';
}else {
?>
<div>
<form name="form" method="post" action="">
<input type="hidden" name="new" value="1" />
<input name="idviaje" type="hidden" value="<?php echo $row['idviaje'];?>" />
<p><input type="text" name="cliente" placeholder="Enter Name" required value="<?php echo $row['cliente'];?>" /></p>
<p><input type="text" name="proveedor" placeholder="Enter Age" required value="<?php echo $row['proveedor'];?>" /></p>
<p><input name="submit" type="submit" value="Update" /></p>
</form>
<?php } ?>
<br /><br /><br /><br />
<a href="https://www.allphptricks.com/insert-view-edit-and-delete-record-from-database-using-php-and-mysqli/">Tutorial Link</a> <br /><br />
For More Web Development Tutorials Visit: <a href="https://www.allphptricks.com/">AllPHPTricks.com</a>
</div>
</div>
</body>
</html>