[SOLVED] Very complicated question

[ballhogjoni]

I have a query that selects a few columns from the table which are displayed on a page. Then I use a function called getProjectedSales() that determines the projected sales. The problem is that I need to be able to sort by the projected sales created by my php getProjectedSales(), but I don't know how to do that if the value is created by php and not mysql.

 

query:

$aCompanyInfo = $site->db->QFetchRowArray("SELECT c.id,c.name,all_sales.gross_sales
		FROM ".$site->tables["companies"]." c
			LEFT JOIN (SELECT cs.cid, sum(cs.gross_sales) as gross_sales
					FROM ".$site->tables["company_sales"]." AS cs WHERE year = ".(date(Y)-1)."
						GROUP BY cs.cid) as all_sales
			ON all_sales.cid=c.id
				WHERE approved = 1 AND deleted = 0 $letter_clause $search_clause 
					GROUP BY c.id $orderby $limit_str");

 

foreach($aCompanyInfo as $aCompany){
		$info = getProjectedSales(date(Y),$aCompany['id']);
		$item['projectedSales'] = $info['projectedSales'];
		$item['percent'] = $info['percent'];
		$item['salesThisYear'] = $info['salesThisYear'];
		$item['totalSalesPreviousYear'] = $info['totalSalesPreviousYear'];
		$item['name'] = $aCompany['name'];
		$item['id'] = $aCompany['id'];
		$vars["data"] .= $site->templates["company_trends"]->blocks["ProjectionsElement"]->Replace($item);
	}

 

function getProjectedSales($year,$id){
$previousYear = $year - 1;
$salesLastYearOnTodaysDate = getSalesByDateRange('1/1/'.$previousYear,date("m/d/".$previousYear),$id);
$totalSalesPreviousYear = getSalesByDateRange('1/1/'.$previousYear,date("12/31/".$previousYear),$id);
$salesThisYearToDate = getSalesByDateRange('1/1/'.$year,date("m/d/".$year),$id);

$info['projectedSales'] = number_format(($salesLastYearOnTodaysDate/$totalSalesPreviousYear)*$salesThisYearToDate,2);

$percent = 100-(((int)$salesThisYearToDate/(int)$totalSalesPreviousYear)*100);

if($totalSalesPreviousYear > $salesThisYearToDate){

	$info['percent'] = '<span style="color:red"><b>'.round($percent*-1,1).'%</b></span>';

}elseif($totalSalesPreviousYear < $salesThisYearToDate){

	if(!$totalSalesPreviousYear)
		if($percent == 100)
			$info['percent'] = '0%';
		else
			$info['percent'] = '<span style="color:green"><b>+'.round($percent,1).'%</b></span>';
	else
		$info['percent'] = '<span style="color:green"><b>+'.round($percent*-1,1).'%</b></span>';

}else{

	$info['percent'] = '<span style="color:green"><b>+'.round($percent,1).'%</b></span>';

}
if( !$salesThisYearToDate ){
	$info['percent'] = '0%';
}
$info['totalSalesPreviousYear'] = number_format($totalSalesPreviousYear,2);
$info['salesThisYear'] = number_format($salesThisYearToDate,2);

return $info;
}

[.josh]

able to put it into an array and just sort it?

[Maq]

Why can't you just do what CV said and return a sorted array?

 

return sort($info);

[.josh]

well it doesn't look like $info is currently an array to be sorted.  Well, it is, but not in the sense that he wants it sorted.  Kind of like doing this:

 

while ($r = mysql_fetch_assoc($result)) {
   sort($r);
}

 

That would sort the columns returned in the current row.  It would not sort by column.  What he would need to do is in his foreach loop th calls getProjectedSales(), instead of doing this:

 

$info = getProjectedSales(date(Y),$aCompany['id']);

 

he would do like, this:

 

$info[] = getProjectedSales(date(Y),$aCompany['id']);

 

and then do a sort on $info.  Or rather, I guess he'd have to do an array_multisort