Query call data by selecting a datatable AJAX field

0

Hello I have the following question, I call the data in my database through the current year, with the following code:

	<script>		
    		$(document).on("ready", function(){
    			listar();
    		});
    		
    		var listar = function(){
    			var table = $("#dt_cliente").DataTable({
    				"order": [[ 0, "desc" ]],
    				"destroy": true,
    				"ajax":{
    					"method": "POST",
    					"url" : "inc/listar.php"
    				},
    				"columns":[
    					{"data":"decreto"},
    					{"data":"anno"},
    					{"data":"materia"}
    					],
    					
    					
    			});

    		}
    		
    	</script>
<?php

	include 'conexion.php';
		$anno=date("Y");
	$query = "SELECT * FROM decreto WHERE fecha='$anno' ORDER BY decreto_id ASC;";

	$resultado = mysqli_query($conexion, $query);
	

	if (!$resultado) {
	die("Error");
	}else {
		while ($data = mysqli_fetch_assoc($resultado)) {
			$arreglo["data"][] =  $data;
		}
		echo json_encode($arreglo);
	}
	mysqli_free_result($resultado);
	mysqli_close($conexion);

	

My question would be if in the HTML I want to select a different date and that I load the data in the datatable with that date. I hope to be clear any question I will be aware of the post. Best regards

    
asked by MoteCL 14.09.2017 в 17:43
source

2 answers

1

Send the date by the url and from that date you get the year and do the filter in the sql:

var listar = function(año){
                var table = $("#dt_cliente").DataTable({
                    "order": [[ 0, "desc" ]],
                    "destroy": true,
                    "ajax":{
                        "method": "POST",
                        "url" : "inc/listar.php?anno=" + año// enviamos la fecha
                    },
                    "columns":[
                        {"data":"decreto"},
                        {"data":"anno"},
                        {"data":"materia"}
                        ],


                });

            }

Now in the php, you get the parameter using $_GET["anno"] :

<?php

    include 'conexion.php';

     // converti
    $anno= $_GET["anno"];

    $query = "SELECT * FROM decreto WHERE fecha='$anno' ORDER BY decreto_id ASC;";

    $resultado = mysqli_query($conexion, $query);

    //...
    mysqli_close($conexion);

Then to load the data you just have to get the value of select and pass it as a parameter to the method listar :

var $select = $("#id-select").change(function(){
  // esta funcion se ejecuta cuando se cambie el año
  var anno = $select.val(); // obtenemos el valor
  listar(anno);// cargamos la lista
});
// cargamos el año por defecto
var anno = $select.val();
listar(anno);
    
answered by 14.09.2017 / 17:54
source
0

First you have to create a FORM in your HTML, so you send a $ _POST or $ _GET to your php file, so that your PHP file will search according to the date that is sent from the HTML.

Something like this:

    <?php
        include 'conexion.php';
//En vez de declarar una variable año, la recogeremos con un $_POST
            //$anno=date("Y");
$anno = $_POST['fecha']; //que viene desde tu form en HTML
        $query = "SELECT * FROM decreto WHERE fecha='$anno' ORDER BY decreto_id ASC;";

        $resultado = mysqli_query($conexion, $query);


        if (!$resultado) {
        die("Error");
        }else {
            while ($data = mysqli_fetch_assoc($resultado)) {
                $arreglo["data"][] =  $data;
            }
            echo json_encode($arreglo);
        }
        mysqli_free_result($resultado);
        mysqli_close($conexion);
    ?>

And the form goes something like that

    <form id="reportes">
      <div class="container" style="margin-top: 5px;"> 
        <div class="row">
          <div class="panel panel-primary" id="cuerpo">
            <div class="panel-heading">
              <h1 class="panel-title">Escriba los parametros para la busqueda</h1>
            </div>
            <div class="panel-body">
              <div>
                <div class="form-group col-xs-6">
                  <label for="Estado" class="control-label">Fecha                 </div>
              <select class="target">
                 <option value="option1" selected="selected">Option 1</option>
                 <option value="option2">Option 2</option>
              </select>
              </div>
            </div>

          </div>
          <div class="text-center">

            <button type="button" id="reporte" class="btn btn-primary">Generar Reporte</button>
            <button type="reset" id="reset" class="btn btn-primary">Nueva Consulta</button>
          </div>
          <div class="container-fluid" id="respuesta"></div>
        </div>
      </div>
    </form>

I put two dates in case you want to filter between dates  Your ajax request would go something like that,

$( ".target" ).change(function() {
var listar = function(){
                var table = $("#dt_cliente").DataTable({
                    "order": [[ 0, "desc" ]],
                    "destroy": true,
                    "ajax":{
                        "method": "POST",
                        "url" : "inc/listar.php"
                    },
                    "columns":[
                        {"data":"decreto"},
                        {"data":"anno"},
                        {"data":"materia"}
                        ],


                });



    }

});

    
answered by 14.09.2017 в 17:58