Query that displays data within a range of dates

Question

Query that displays data within a range of dates

Navigation

#1 by (2 votes)
#2 by (1 votes)

0

I want to display the data in a PDF within a selected date range from datepicker , the PDF is generated and shows the column headers but not the information, it is most likely to be The% co_of% I'm doing.

The data that must be shown has to do with the registration date. That is, if I choose query , it will show me the 2018-12-05 a 2018-12-12 between that range

Query:

if(isset($_GET['fecha1']) && isset($_GET['fecha2']))
    {

      $fecha1 = $_GET["fecha1"];
      $fecha2 = $_GET["fecha2"];

      //Trae todos los item que esten por debajo de su minimo en stock.
      $connection = new MySqlServerConnection();
      $query = "SELECT i.description_item,i.quantity,u.name_unit,i.reorder_Level,
      i.target_Stock,l.name_location,i.commentt,io.quantity_s,io.registerTicket
      FROM inventory_list AS i
      INNER JOIN unit_mesurement AS u ON id_unit = fkUnit
      INNER JOIN location AS l on id_location = fkLocation
      INNER JOIN inventory_output as io on id_output = fkInventory
      WHERE '".$fecha1."' AND '".$fecha2."'";
      $result = $connection->executeQuery($query,array($fecha1,$fecha2));
      if ($result > 0) {...}

The field of the registration date is item of io.registerDate

When clicking on generate report onclick sends to this function in the js:

function Report(fecha1,fecha2)
{
        var fecha1 =document.getElementById("start").value;
        var fecha2 =document.getElementById("end").value;

        setTimeout(function(){ window.open("http://localhost/WareHouse/datereport.php?fecha1="+fecha1+"&fecha2="+fecha2, "_blank"); }, 1000);
        initArticles(); $('#myModalReport').modal('hide');
        console.log(fecha1);
        console.log(fecha2);
}

How could I do it, so that it works correctly according to the date?

php query datepicker

asked by Pato 12.12.2018 в 18:02

source

2 answers

How to save data from a foreach in html in an array of Javascript Problem with an iframe and bootstrap 4

score 2 · Answer 1

I recommend the use of the comparison operator BETWEEN, which compares between a minimum and a maximum value. Your query would be as follows:

SELECT 
  i.description_item,
  i.quantity,
  u.name_unit,
  i.reorder_Level,
  i.target_Stock,
  l.name_location,
  i.commentt,
  io.quantity_s,
  io.registerTicket
FROM inventory_list AS i
  INNER JOIN unit_mesurement AS u ON id_unit = fkUnit
  INNER JOIN location AS l on id_location = fkLocation
  INNER JOIN inventory_output as io on id_output = fkInventory
  WHERE io.registerDate BETWEEN '".$fecha1."' AND '".$fecha2."'"

On the other hand, I strongly recommend that you do not involve variables directly with your query (do not concatenate) because this presents a high risk of vulnerability.

What do I say? Use PDO to connect to your preferred engine and take advantage of features such as PDOStatement :: bindValue (...) to protect your system against SQL injections.

More information about the BETWEEN operator:

MySQL .
MaríaDB .

score 1 · Answer 2

can you put all the data in the table? you're comparing with two fields, you should put something like that

$query = "SELECT i.description_item,i.quantity,u.name_unit,i.reorder_Level,
  i.target_Stock,l.name_location,i.commentt,io.quantity_s,io.registerTicket
  FROM inventory_list AS i
  INNER JOIN unit_mesurement AS u ON id_unit = fkUnit
  INNER JOIN location AS l on id_location = fkLocation
  INNER JOIN inventory_output as io on id_output = fkInventory
  WHERE io.registerDate >= '".$fecha1."' AND  io.registerDate <= '".$fecha2."'";