CODEWITHSUNDEEP
phpmysqldatedatetime
Liz
Liz

Reputation: 1048

How to display MySQL query results only from current month?

I have the following PHP script that grabs data from MySQL table and prints it in a HTML table. I want to edit the data to only display information from the current month.

In the MySQL table, there is another column in my table, InvoiceDate which is a DATETIME type that I can use to see if these values are from this month. I am hoping to either adjust my query or add a snippet of PHP code that will automatically display results from the current month so that I don't need to update the code every month to display the new values.

What is the best way to accomplish this?

<?php

require_once 'config.php';

// create connection
$conn = new mysqli($currentConfig['host'], $currentConfig['user'], $currentConfig['pass'], $currentConfig['name']);
// check connection
if ($conn->connect_error) {
die('Connection failed: '.$conn->connect_error);
}

$sql = "SELECT SUM(InvoiceAmount) AS TotalBilling,SUM(ClientCostToDate) AS WIP FROM Estimates";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
 while ($row = $result->fetch_assoc()) {
  $wip = $row['WIP'];
  $total_billing = $row['TotalBilling'];

  // echo data into HTML table
  echo
  '<tbody>'.'<tr>'.'<td>'.$total_billing.'</td>'.'<td>'.$wip.'</td>'.'</tr>'.'</tbody>';
}
} else {
echo 'No results';
}
$conn->close();

I tried this query that I found on Stack Overflow and it does not work:

SELECT InvoiceDate, SUM(InvoiceAmount) AS TotalBilling,SUM(ClientCostToDate) AS WIP FROM Estimates WHERE DATE_FORMAT(InvoiceDate,"%m")=DATE_FORMAT(NOW(),"%m");

I also found this query, which also does not work:

SELECT InvoiceDate, SUM(InvoiceAmount) AS TotalBilling, SUM(ClientCostToDate) AS WIP FROM Estimates WHERE MONTH(InvoiceDate) = MONTH(now()) AND YEAR(InvoiceDate) = MONTH(now());

The only thing that works properly for me so far is this query:

SELECT SUM(InvoiceAmount) AS TotalBilling,SUM(ClientCostToDate) AS WIP FROM Estimates WHERE InvoiceDate > '2016-09-01';

Which displays results from this month just fine, but is obviously not ideal as I would have to edit the query month-by-month.

Upvotes: 0

Views: 759

Answers (1)

PyQL
PyQL

Reputation: 1830

Use DATE_FORMAT

SELECT SUM(InvoiceAmount) AS TotalBilling,SUM(ClientCostToDate) AS WIP 
FROM Estimates
WHERE date_format(InvoiceDate, '%Y-%m')=date_format(now(), '%Y-%m')

Upvotes: 3

Related Questions