CODEWITHSUNDEEP
phphtmlmysql
user2338925
user2338925

Reputation:

Get sum of a column by date range

I have a small line of code that will get the sum of a column

<?php

 class ManageServices{

 function getTotalSumByDateRange()
 {
    $query = $this->link->query("SELECT SUM(amount) as sum_of_date_range FROM services ");      

    $rowcount = $query->rowCount();

    $result = $query->fetchAll();
    return $result; 
}
}

?>

//search.php

<?php
 include_once('../../classes/class.ManageServices.php');
 $init = new ManageServices();  
 $sum_of_date_range = $init->getTotalSumByDateRange();    
 ?>

//search1

<?php

if(isset($_POST['submit']))
{ 
include_once('../../classes/class.ManageServices.php');
$init = new ManageServices();  
$date_from =$_POST['to_date'];
$date_to =$_POST['from_date'];

if(empty($_POST['to_date']) && empty($_POST['from_date']))
{
    $error = "No search query";
}
elseif(empty($_POST['to_date']) && !empty($_POST['from_date']))
{
     $error ="Please specify your start date search";
}
elseif(!empty($_POST['to_date']) && empty($_POST['from_date']))
{
     $error ="Please specify your end date search";
}
else
{
    $total_by_date_range = 0;
    $total_by_date_range = $init->getSumByDateRange($date_from, $date_to);
}
}

 ?>

//html

<?php
include_once('../../libs/search/search_sales_by_date.php');
include_once('../../libs/search1/total_sales.php');
?>

<!Doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>search by dates</title>
<link rel="stylesheet" href="//code.jquery.com/ui/1.11.2/themes/smoothness/jquery-ui.css">
<script src="//code.jquery.com/jquery-1.10.2.js"></script>
<script src="//code.jquery.com/ui/1.11.2/jquery-ui.js"></script>
<link rel="stylesheet" href="/resources/demos/style.css">
<script>
$(function() {
$( ".datepicker" ).datepicker();
});
</script>

</head>
<body>
<div>
<?php
if(isset($error))
{
  echo $error;
}
?>
</div>
<h3>Search Sales</h3>
<p>From</p>
<form method="POST" action="search_sales_by_dates.php">
<p>Date: <input type="text" class="datepicker" name="from_date" id="field1"></p>
<p>To</p>
<p>Date: <input type="text" class="datepicker" name="to_date" id="field2"></p><br />
<input type="submit" value="search" name="submit" id="submitdata">
</form>
<div id ="fillmein1">
<?php foreach($sum_of_date_range as $sum): ?>
  <td>Total Sales<span class="glyphicon glyphicon-usd" aria-hidden="true"></span><?php echo     number_format($sum['total_sum'],2); ?></td>  
 <?php endforeach; ?>
 </div>
 <input type="hidden" value ="$total_by_date_range['sum_of_date_range'] ">//this is the problem
 </body>
 </html>

and in my table i have columns 'id','amount',date_of_service'.The query above will calculate all the 'amount' values and display in html, however, i want to add another query that will only get the sum of 'amount' column base on a date range input from html form.Any ideas on this? Update..I think I'm almost there,after I updated search1.php,search.php,and html my problem now is I want to show the $total_by_date_range in same form.But when I submit the form, it shows no error but will not show the $total_by_date_range.anyway,the $sum_of_date range shows result in the same page

Upvotes: 0

Views: 2515

Answers (3)

user2338925
user2338925

Reputation:

Now it's working.I rewrite my search.php

<?php
if(isset($_REQUEST['submit']))
{
include_once('../../classes/class.ManageServices.php');
$init = new ManageServices();  
$date_to =$_REQUEST['to_date'];
$date_from =$_REQUEST['from_date'];

if(empty($date_from) && empty($date_to))
{
    $error = "No search query";

}
elseif(empty($date_from) && !empty($date_to))
{
    $error = "Specify your end date search";
}
elseif(!empty($date_from) && empty($date_to))
{
    $error ="Specify your start date search";
}
else
{
    if($date_from < $date_to)
    {
    $total_by_date_range = $init->getSumByDateRange($date_from, $date_to);
    foreach ($total_by_date_range as $key) 
    {
    $success ="Your Total amount of sales from ". $date_from . ' ' . "To" . ' ' .$date_to . ' '   ."is". ' ' ."P" .number_format($key['sum_of_date_range'],2);
    }
    }
    else
    {
        $error = "You bitch, Start date should not greater than the end date on your search  query";
    }
 }
}
else
{
$error = "Because you are an idiot, the script contains some bugs that is why it can't run on the   browser!";
}

?>

and I also changed the date format of Jquery datepicker, similar to the format used by mysql,I found out that having different date format in html($_GET method) and the format used by mysql will result null in query.

Upvotes: 0

deerawan
deerawan

Reputation: 8443

First, when from date and end date is not specified, you display all the sum. But if those exists you filter your query to display sum within the date range.

Here is my solution. 

function getTotalSumByDateRange($fromDate = "", $toDate = "")
{
    $sql = "SELECT SUM(amount) as sum_of_date_range FROM services ";      

    if (!empty($fromDate) && !empty($toDate) {
       $sql .= "WHERE date_of_service BETWEEN '". $fromDate . "' AND '" . $toDate . "'";
    }

    $query = $this->link->query($sql);

    $rowcount = $query->rowCount();

    $result = $query->fetchAll();
    return $result; 
}

Upvotes: 0

JuanSedano
JuanSedano

Reputation: 1025

First you need to add another function:

class ManageServices {
  function getTotalSumByDateRange() {
     $query = $this->link->query("SELECT SUM(amount) as sum_of_date_range FROM services ");      
     $rowcount = $query->rowCount();
     $result = $query->fetchAll();
     return $result; 
  }
  function getSumByDateRange($date_from, $date_to) {
     $query = $this->link->query("SELECT SUM(amount) as sum_of_date_range FROM services where
     date_of_service between '".$date_from."' and '".$date_to."'");      
     $rowcount = $query->rowCount();
     $result = $query->fetch();
     return $result; 
  }
}

Search.php

<?php
 include_once('../../classes/class.ManageServices.php');
 $init = new ManageServices();  
 $sum_of_date_range = $init->getTotalSumByDateRange();
 $total_by_date_range = $init->getSumByDateRange($_POST['from_date'],$_POST['to_date']); 
?>

HTML

<?php
include_once('../../libs/search/search_sales_by_date.php');
?>

<div>
<?php foreach($sum_of_date_range as $sum):?> 
    <td><span class="glyphicon glyphicon-usd" aria-hidden="true"></span><?php echo    number_format($sum['sum_of_date_range'],2); ?></td>  
<?php endforeach;?>
<?php
  echo "Total by date range: ".$total_by_date_range;
?>
</div>

You can try something like this.

Upvotes: 1

Related Questions