Click here to Skip to main content
15,868,065 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am using PHP and MYSQL, and I need to input 2 dates and get the total amount of money. Here is the code:
$minDate = date("Y-m-d", strtotime(str_replace("/", "-", $_GET["minDate"])));
$maxDate = date("Y-m-d", strtotime(str_replace("/", "-", $_GET["maxDate"])));
$sql = $db->prepare("SELECT final_total
                        FROM invoice
                        WHERE
                        DATE(invoice_date)
                        BETWEEN ?
                        AND ?");
$sql->bind_param('ss', $minDate, $maxDate);
$sql->execute();
$result = $sql->get_result();

$rows = array();
while($row = mysqli_fetch_assoc($result)){
  $rows[] = $row;
}

if (count($rows) > 0) {
  echo array_sum(array_column($rows, 'final_total'));
 }

The code works 100% but, if for example, I input a date that is not available in the database as the first date and I input a date that is available in the database as a second date it will still calculate the total between those 2 dates and that's not what I want. What I want to do is, if one of the dates are not available in the database just don't calculate (output null or empty). How can I do this?

What I have tried:

Where exactly is the issue? Is it in PHP code or MYSQL? And what is the solution for this?
Posted
Updated 18-Aug-22 21:57pm
v3
Comments
Richard Deeming 19-Aug-22 4:04am    
What do you mean by "not available"? Your query is getting the total for invoices between the two dates. If there are no invoices between the dates, then you won't get any results. If there are invoices between the dates, then you will get rows. What's the problem?
FRS4002 19-Aug-22 7:12am    
For example, if I have 3 dates in the database 28/07/2022, 29/07/2022 and 30/07/2022. If I input 01/05/2022 as a first date and 30/07/2022 as a second date it will calculate the total between these 2 dates, meaning, it will calculate the total of dates of 28/07/2022 until 30/07/2022, even though there is no 01/05/2022 in the database, but it will still calculate, and that's not what I want...

1 solution

You first need to run a SELECT on each date to find out if they match any actual records. If no records match then you skip the calculation. Something like:
PHP
$sql = $db->prepare("SELECT COUNT
                        FROM invoice
                        WHERE
                        DATE(invoice_date)
                        EQUALS ?);
$sql->bind_param('s', $minDate);

Do the same for maxdate, and if either count is zero then you have no records with that particular date.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900