get a running total for each day, between start date and end date

[pagegen]

Hi Guys,

 

I am trying to get a running running total, or sub-total, for each day, and then a full total, for the dates selected..

 

My query works when I pass it, start date 3/03/2010 till 6/03/2010, it get the full total, but I dont no how to get the running total for each day..

 

All help is welcome

 

 

<?php
if($_POST['action']) {

$start_date = $_POST['start_date'];
$end_date = $_POST['end_date'];
$campaign_id = $_SESSION['campaign_id'];

$sql_get_data = "SELECT * FROM sold_leads WHERE deal_date BETWEEN '".$start_date."' AND '".$end_date."' ORDER BY deal_date ASC";
	$result_get_data = mysql_query($sql_get_data, $AngelG) or die(mysql_error());

	while($row_get_data = mysql_fetch_array($result_get_data)){
	if($row_get_data['sellers_company'] == $my_company){
	$total_credit_added = $total_credit_added + $row_get_data['price'];
	}
	else if($row_get_data['buyers_company'] == $my_company){
	$total_credit_spend = $total_credit_spend + ($row_get_data['price']);
	}

	$yeasterdays_total_spend = $row_get_data['price'];
	}

	echo $total_credit_added;
	echo "<br/>";
	echo "-" . $total_credit_spend;

}
?>

[5kyy8lu3]

too lazy to read through all the code but if you didn't mind doing a query for each day, do something like this using mysql's SUM() function

 

$q = "SELECT SUM(price) AS DailyTotal FROM sold_leads WHERE deal_date = ".$specific_day"' ORDER BY deal_date ASC";
$r = mysqli_query($connection, $q);
$row = mysqli_fetch_array($q);
echo 'Daily total for ' . $specific_day . ' is ' . $row['DailyTotal'] . '.';

[PFMaBiSmAd]

Assuming your deal_date column is an actual mysql DATE data type, you can use SUM(price) in the SELECT term and a GROUP BY deal_date term to give the sum for each day. You can also use a WITH ROLLUP term to give the total of all the sums.

 

If your deal_date column is actually storing values in the format - 3/03/2010, you will find that your query will only work for a very small set of dates.

[pagegen]

Yes I am using date format so am hoping all goes will will put this to test later on, Thank you very much guys