How to select Mysql using input date type with php?

Question

I’m trying to list certain database columns by date but it doesn’t work.

Follows excerpt from the code:

<?php
if($_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>
?>

Answer 1

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

EX:

If you have the stored data whose dates are this way '2017-08-15', ideally you 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 as follows:

"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 using single quotes inside double quotes, because 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’ve helped.

abs.

Answer 2

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 he type only one he takes the records only from that date.

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

See if your form 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 didn’t have time to test.

Answer 3

Apparently, you are not using the SQL language correctly.

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

Example of use: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_groupby

The best one to use would be ORDER BY

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

Example of use: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_orderby

that would be the line of the code already amended:

$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