Need help with getting year totals

ok, I have a script that that gets date ranges from a database to display as part of another script. I need to modify this script to display annual totals for the optgroup. This is the code as I have it now
[php]$com_cont = ‘’;
$qry_a = mysql_query(“SELECT start_date, end_date FROM venzo_app_sales
WHERE title IN (SELECT appname FROM venzo_user_apps WHERE uid = '”.$_SESSION[‘uid’]."’)
GROUP BY start_date ORDER BY STR_TO_DATE(start_date,’%m/%d/%Y’) desc"
) or die(mysql_error());

$sub = array();
$sub[] = “”;
$com_cont.='Select Date Range: ';

while($r = mysql_fetch_array($qry_a)) {

$tm = explode('/', $r['start_date']);
$yr = $tm[2];								
$dte = $r['start_date']. " - " .$r['end_date'];
				
$qry = mysql_query("SELECT partner_share_currency, extended_partner_share_currency AS psc FROM venzo_app_sales 
WHERE title IN (SELECT appname FROM venzo_user_apps WHERE uid = '".$_SESSION['uid']."')");
			
while($as = mysql_fetch_array($qry)) {
	//$as = mysql_fetch_array($qry);
	$as['total'] = $as['psc'] * $arr_currency[$as['partner_share_currency']];
				
}
//if($prev_yrs == $yr) {
$abs_total += $as['total'];
//	$prev_yrs = $tm[2];
//}
					
					
if($prev_yr != $yr) {
			
	$sub[] = "<optgroup label='$yr ($".number_format($abs_total, 2).")'>";
	$prev_yr = $tm[2];
}
$sub[] = "<option value='$dte'>$dte</option>";									
if($prev_yr != $yr) {
	$sub[] = "</optgroup>";
	$prev_yr = $tm[2];
}

}
$sub[] = “”;

for($k = 0; $k < count($sub); $k++) {
$com_cont.= $sub[$k];
}[/php]
This displays something like
2013
09/01/2013 - 09/30/2013
08/01/2013 - 08/31/2013
etc
2012
etc
2011
and so on

What I have to do is get the annual totals to display next to the year, so I end up with
2013 ($1000.00)
09/01/2013 - 09/30/2013
08/01/2013 - 08/31/2013
etc
2012 ($800.00)
etc
2011 ($100.00)
and so on

I’ve gotten it to return values, but they’re never correct. I know i’m close, but can’t figure out what i’m doing wrong. Any help would be greatly appreciated.

Richei, wouldn’t it be easier just to sum it up in the mysql query?

It also looks like your first query gets a date range, but the query inside the loop doesn’t limit it to the data range you selected, so it appears to be totaling everything.

No, because I don’t want to limit the calculation to just one month. I can’t use SUM in the query because the results to have to converted to US currency first

And you can’t convert the currency in the query?

dont think so, wouldnt know how to it though. theres several counties in there too.

What are you using to convert the currency, I don’t see anything that resembles that in what you posted.

theres an Included file that contains the code for that. the conversion is there though, as[psc]=

I guess what I’m struggling with is this.

[php]$qry = mysql_query(“SELECT partner_share_currency, extended_partner_share_currency AS psc FROM venzo_app_sales
WHERE title IN (SELECT appname FROM venzo_user_apps WHERE uid = '”.$_SESSION[‘uid’]."’)");

while($as = mysql_fetch_array($qry)) {
	//$as = mysql_fetch_array($qry);
	$as['total'] = $as['psc'] * $arr_currency[$as['partner_share_currency']];
				
}[/php]

Are you expecting more then 1 row to come back? if you are, you keep resetting $as[‘total’] to the last row that’s returned from the query. I’m assuming you only get 1 row back.

Dude, you’re amazing :slight_smile: That’s supposed to be a += I’ll switch it when i get home tonight and see how it goes :slight_smile:

That didn’t do the trick, was hoping that’s all it was :frowning:

Well, I made a tiny bit of progress.

I decided to put all the dates into an array, took me 4 hours to do that, but i’m still not any further a long. I know what I need to do, just have no clue how to go about doing it. Updated code is below [php]<?php
session_start();
include ‘config.php’;
//ini_set(‘display_errors’,1);
//ini_set(‘display_startup_errors’,1);
//error_reporting(-1);
require ‘currency_values.php’;
$arr_currency=get_all_currency_values();

$com_cont = ‘’;
$prev_year = ‘’;
$prev_yr = ‘’;

$qry_a = mysql_query(“SELECT start_date, end_date FROM venzo_app_sales
WHERE title IN (SELECT appname FROM venzo_user_apps WHERE uid = '”.$_SESSION[‘uid’]."’)
GROUP BY start_date ORDER BY STR_TO_DATE(start_date,’%m/%d/%Y’) desc"
) or die(mysql_error());

$sub = array();
$drange = array();

$sub[] = “”;
$com_cont.='Select Date Range: ';

while($r = mysql_fetch_assoc($qry_a)) {

$tm = explode('/', $r['start_date']);
$yr = $tm[2];
$dte = $r['start_date']. " - " .$r['end_date'];

$sdate[] = $r['start_date'];
$months = array_unique($sdate);

if($prev_yr != $yr) {
	
	for($j=0; $j < count($months); $j++) {
		$qry = mysql_query("SELECT label, partner_share_currency, extended_partner_share_currency AS psc 
				 FROM venzo_app_sales WHERE sales_or_returns = 'S' AND title IN (SELECT appname FROM venzo_user_apps WHERE uid = '".$_SESSION['uid']."')
				 AND start_date = '$months[$j]'");
	 }
	
	while($row = mysql_fetch_assoc($qry)) {
		$row['psc_int'] = $row['psc'] * $arr_currency[$row['partner_share_currency']]; // currency conversion
		$rows[$row['label']][] = $row;
	}
	
	foreach($rows as $key => $row) {
		for($i = 0; $i < count($row); $i++) {
			$rows[$key]['total'] += $row[$i]['psc_int'];
			$total += $row['total'];					
			echo $total."<br />";
		}
	}
	
	$sub[] = "<optgroup label='$yr ($".number_format($total, 2).")'>";
	$prev_yr = $yr;
}

$sub[] = "<option value='$dte'>$dte</option>";

if($prev_yr != $yr) {
	$sub[] = "</optgroup>";
	$prev_yr = $tm[2];
}

}
$sub[] = “”;

for($k = 0; $k < count($sub); $k++) {
$com_cont.= $sub[$k];
}
echo $com_cont;

?>[/php]

You’re still doing that loop without adding…

[php] while($row = mysql_fetch_assoc($qry)) {
$row[‘psc_int’] = $row[‘psc’] * $arr_currency[$row[‘partner_share_currency’]]; // currency conversion
$rows[$row[‘label’]][] = $row;
}[/php]

So are you really only get 1 row back per query?

it works in the other scripts, but its probably not working as it should.

im on my phonit works in the other scripts, but its probably not working as it should.

I think the totals script needs an overhaul at this point. what im using now is part of a date picking script for a sales script

I understand it a little better now, but I don’t understand where $row[‘total’] gets populated. Wouldn’t $total always be blank… If you make the change below wouldn’t that sum up the totals?

Change:

[php] foreach($rows as $key => $row) {
for($i = 0; $i < count($row); $i++) {
$rows[$key][‘total’] += $row[$i][‘psc_int’];
$total += $row[‘total’];
echo $total."
";
}
}[/php]

To:

[php] foreach($rows as $key => $row) {
for($i = 0; $i < count($row); $i++) {
$rows[$key][‘total’] += $row[$i][‘psc_int’];
$total += $row[$i][‘psc_int’];
echo $total."
";
}
}[/php]

Didn’t work.

I’m thinking maybe its the second query that’s messing things up. Maybe instead of trying to do it month by month, there’s a way to do it with a given range? I already have an array with the months in it, if that can be used to create 2 other variables, a fresh start date and an end date.

idk, i’m thinking out loud now. If you had this to do, how you would go about doing it?

I would try to do it all in a sql statement and where ever you are pulling the data from for the currency conversion I would toss that in a database as well and get the results you want with 1 query.

the conversion rates are in a table. ill post the code for it.

Well, I made a tiny bit of progress on this. I have part of the array created, but it gets wiped out when add in the conversions. This is the array print_r[code]Array
(
[2013] => Array
(
[startdate] => Array
(
[0] => 09/01/2013
[1] => 06/30/2013
[2] => 06/02/2013
[3] => 05/05/2013
[4] => 03/31/2013
[5] => 03/03/2013
[6] => 02/03/2013
)

    )

[2012] => Array
    (
        [startdate] => Array
            (
                [0] => 12/30/2012
                [1] => 12/02/2012
                [2] => 11/04/2012
                [3] => 08/05/2012
                [4] => 07/01/2012
                [5] => 06/03/2012
                [6] => 05/06/2012
                [7] => 04/01/2012
                [8] => 03/04/2012
                [9] => 02/05/2012
                [10] => 01/01/2012
            )

    )

[2011] => Array
    (
        [startdate] => Array
            (
                [0] => 11/27/2011
                [1] => 10/30/2011
            )

    )

)[/code]and this is the code[php]<?php
session_start();
$_SESSION[‘uid’] = 62;
function display($arg) {
echo “

”;
print_r($arg);
echo “
”;
}

include ‘config.php’;
//ini_set(‘display_errors’,1);
//ini_set(‘display_startup_errors’,1);
error_reporting(-1);
require ‘currency_values.php’;
$arr_currency=get_all_currency_values();

$com_cont = ‘’;
$total = 0;

$qry = mysql_query(“SELECT start_date, end_date FROM venzo_app_sales WHERE title IN (SELECT appname FROM venzo_user_apps WHERE uid = '”.$_SESSION[‘uid’]."’) GROUP BY start_date ORDER BY STR_TO_DATE(start_date,’%m/%d/%Y’) desc") or die(mysql_error());

$sub = array();
$dt = array();
$dates = array();
$sort = array();

$sub[] = “”;
$com_cont.='Select Date Range: ';

while($r = mysql_fetch_assoc($qry)) {

$tm = explode('/', $r['start_date']);
$yr = $tm[2];
$dte = $r['start_date']. " - " .$r['end_date'];
$month[$yr]['startdate'][] = $r['start_date'];

}
display($month);
$sub[] = “”;

for($k = 0; $k < count($sub); $k++) {
$com_cont.= $sub[$k];
}[/php]This is what I need the array to look like[code]
[2011] => Array
(
[startdate] => Array
(
[0] => 11/27/2011
[psc_int] => Array
(
[0] => 1.00
[1] => 2.00
)
[1] => 10/30/2011
[psc_int] => Array
(
[0] => 1.00
[1] => 2.00
)
)

    )[/code]

But when I add in[php]foreach ($month as $year => $start) {
$dates = array_shift($month[$year]);
}
foreach($dates as $dt) {
for($q=0; $q < count($dt); $q++) {
$qry_b = “SELECT partner_share_currency, extended_partner_share_currency psc FROM venzo_app_sales
WHERE start_date = '”.$dt[$q]."’ AND title IN (SELECT appname FROM venzo_user_apps WHERE uid = “.$_SESSION[‘uid’].”)
ORDER BY STR_TO_DATE(start_date,’%m/%d/%Y’) desc";
$qry_c = mysql_query($qry_b) or die(mysql_error());
}
}

while($row = mysql_fetch_assoc($qry_c)) {
	$month[$yr]['startdate']['psc_int'][] = $row['psc'];
}[/php]$month[$yr]['startdate']['psc_int'][] = $row['psc']; wipes out the array completely, leaving just the year

ok, well I’ve been told that what I need done can be done in the query if switch the data type to date, which I’ve done (which has pretty much broken the sales page for now). But I don’t know how to set up the query to get the results I need.

Sponsor our Newsletter | Privacy Policy | Terms of Service