How to do select MySQL using input date type with php?

0

I'm trying to list certain columns of the database by date but it does not work.

Here'ssomecodesnippet:

<?phpif($_GET['busca']=="data") {        
  $datai = date('Y/m/d', strtotime($_POST['data']));
  $nome  = $_POST['nome'];
  $preco = $_POST['preco'];
  $qtd   = $_POST['qtd'];

  $query = mysqli_query("SELECT data, nome, preco, SUM(preco) AS pr, SUM(qtd) AS qtd, date_format(data, '%d/%m/%Y') AS data FROM tbl_carrinho WHERE idGarcon='$garcon' AND data BETWEEN '$datai' AND '$dataf' AND time BETWEEN '$hora1' AND '$hora2' GROUP BY data") or die(mysqli_error());
}

while($resultado = mysqli_fetch_array($query)) {    
  $data = $resultado['data'];    
  $nome = $resultado['nome'];    
  $qtd = $resultado['qtd'];    
  $preco = $resultado['preco'];
?>

<tr style="border:1px solid #f2f2f2;">    
  <td align="center" style="border:1px solid #f2f2f2;"><?php echo $data ?></td>
  <td align="left" style="border:1px solid #f2f2f2;"><?php echo $nome ?></td>
  <td align="center" style="border:1px solid #f2f2f2;"><?php echo $preco ?></td>
  <td align="center" style="border:1px solid #f2f2f2;"><?php echo $qtd ?></td>
</tr>
?>
    
asked by anonymous 15.08.2017 / 22:46

3 answers

0

Well, if the date column of your mysql table is in the correct format type (date = Y-m-d), I would convert the date according to the mysql table.

EX:

If you have stored data whose dates are in this way '2017-08-15', you should ideally convert your variable:

$datai = date('Y/m/d', strtotime($_POST['data']));

for

$datai = date('d-m-Y', strtotime($_POST['data']));

The same thing I would do with the $ dataf variable and in the mysql statement I would do it like this:

"SELECT data, nome, preco, SUM(preco) AS pr, SUM(qtd) AS qtd, data FROM tbl_carrinho WHERE idGarcon='$garcon' AND data BETWEEN ".$datai." AND ".$dataf." AND time BETWEEN ".$hora1." AND ".$hora2." GROUP BY data"

Try not to put your variables in single quotation marks inside double quotes, as php can interpret this as a string and not as a variable. EX.

Problem:

$sql = "SELECT * FROM table WHERE col='$val'";

Workaround

$sql = "SELECT * FROM table WHERE col=".$val."";

or

$sql = "SELECT * FROM table WHERE col = {$val}";

I hope I have helped.

abs.

    
15.08.2017 / 23:21
0

First, you are only passing a date, if you want to filter for a period between two dates it is better to pass the two dates (logically) and if you type only one it will get the records only from that date.

First create the fields for start date and end date in the form.

See if your form's method is POST or GET and change the code.

The code would look something like this:

<?php
if(isset($_POST['dataIni']) && (isset($_POST['dataFin']))) {        
  $dataIni = date('Y/m/d', strtotime($_POST['dataIni']));
  $dataFin = date('Y/m/d', strtotime($_POST['dataFin']));
  $nome  = $_POST['nome'];
  $preco = $_POST['preco'];
  $qtd   = $_POST['qtd'];

  $sql = "SELECT data, nome, preco, SUM(preco) AS pr, SUM(qtd) AS qtd, date_format(data, '%d/%m/%Y') AS data FROM tbl_carrinho WHERE idGarcon='$garcon' AND data ";

  if ($dataFin != '') {
    $sql .= "BETWEEN '$dataIni' AND '$dataFin' GROUP BY data"
  else
    $sql .= "= '$dataIni' GROUP BY data"

  $query = mysqli_query($sql) or die(mysqli_error());

  while($resultado = mysqli_fetch_array($query)) {    
?>    
<tr style="border:1px solid #f2f2f2;">    
  <td align="center" style="border:1px solid #f2f2f2;"><?php echo $resultado['data'] ?></td>
  <td align="left" style="border:1px solid #f2f2f2;"><?php echo $resultado['nome'] ?></td>
  <td align="center" style="border:1px solid #f2f2f2;"><?php echo $resultado['preco'] ?></td>
  <td align="center" style="border:1px solid #f2f2f2;"><?php echo $qtd ?></td>
</tr>
<?php } } else { echo 'Informe o período!'} ?>

I did not have time to test.

    
15.08.2017 / 23:31
0

Apparently, you are not correctly using the SQL language.

The GROUP BY statement is usually used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result set into one or more columns.

  

Example usage: link

The best to use would be ORDER BY

The ORDER BY statement is used to sort the result set in ascending or descending order.

  

Example usage: link

this would be the line of code already changed:

$query = mysqli_query("SELECT data, nome, preco, SUM(preco) AS pr, SUM(qtd) AS qtd, date_format(data, '%d/%m/%Y') AS data FROM tbl_carrinho WHERE idGarcon='$garcon' AND data BETWEEN '$datai' AND '$dataf' AND time BETWEEN '$hora1' AND '$hora2' ORDER BY data") or die(mysqli_error());

// was used order by instead of group by

    
15.08.2017 / 23:47