Link to home
Start Free TrialLog in
Avatar of Robert Francis
Robert Francis

asked on

Calculate values in an array

I have the following code:

$query3 = "SELECT SUM(tpri) AS tpri FROM (SELECT DISTINCT Job, Total_Price AS tpri FROM V_Cust_Cost_Det_Inv_New_B WHERE (Invoice_Date BETWEEN '" . $date . "' AND '" . $date2 . "' AND Customer_Since BETWEEN '" . $date . "' AND '" . $date2 . "' AND Customer = '" . $Customer . "')) AS derivedtbl_1";
	$results3 = sqlsrv_query($connPpp, $query3);
	$row3 = sqlsrv_fetch_array($results3);	
	print_r($row3);
	$tpri = $row3['tpri'];

Open in new window


The print_r returns this:

Array ( [0] => 231875.0000 [tpri] => 231875.0000 ) Array ( [0] => 12179.7800 [tpri] => 12179.7800 ) Array ( [0] => 11883.8100 [tpri] => 11883.8100 ) Array ( [0] => 9492.8000 [tpri] => 9492.8000 ) Array ( [0] => 6950.0000 [tpri] => 6950.0000 ) Array ( [0] => 7130.0300 [tpri] => 7130.0300 ) Array ( [0] => 7759.4500 [tpri] => 7759.4500 ) Array ( [0] => 12150.9700 [tpri] => 12150.9700 ) Array ( [0] => 7450.0000 [tpri] => 7450.0000 ) Array ( [0] => 3356.8300 [tpri] => 3356.8300 ) Array ( [0] => 1717.0000 [tpri] => 1717.0000 ) Array ( [0] => 505.8000 [tpri] => 505.8000 ) Array ( [0] => 668.6400 [tpri] => 668.6400 ) Array ( [0] => 161.2500 [tpri] => 161.2500 )

First question - why is it returning [0] and [tpri] for each record when I am only selecting one thing and how do I get a sum of either the [0] or [tpri]? I understand it is most likely a foreach but not sure how to write it.

Thanks in advance for your help.
Avatar of pritaeas
pritaeas
Flag of Netherlands image

You can tell sqlsrv_fetch_array to retrieve the columns by index, by name, or both. Both is the default option.

See fetchType in the documentation.
Avatar of Robert Francis
Robert Francis

ASKER

I got that but that does not help me write a foreach statement
Thjis is untested but probably correct in principle.
$row3 = sqlsrv_fetch_array($results3);	
$sum = 0.0;
foreach ($row3 as $row)
{
    $sum += $row['tpri'];
}
var_dump($row3, $sum);

Open in new window

... Or maybe not.  I'm wondering if your SELECT query is giving you exactly what we're seeing with the question.  Have you tested to ensure that the query results set is really what you want?

I only see one print_r() statement in the code example, but it looks like there are several print_r() outputs in the question.  How can this be?
Apparently tpri is a string because I am getting a bunch of this:

Warning: Illegal string offset 'tpri' in C:\inetpub\wwwroot\portal2\cost_details_new_customers.php on line 93

Warning: Illegal string offset 'tpri' in C:\inetpub\wwwroot\portal2\cost_details_new_customers.php on line 93
array(2) { [0]=> string(11) "231875.0000" ["tpri"]=> string(11) "231875.0000" } float(4)
Warning: Illegal string offset 'Invoice_Amount' in C:\inetpub\wwwroot\portal2\cost_details_new_customers.php on line 103

Warning: Illegal string offset 'hrs2' in C:\inetpub\wwwroot\portal2\cost_details_new_customers.php on line 105

Warning: Illegal string offset 'material3' in C:\inetpub\wwwroot\portal2\cost_details_new_customers.php on line 106

Warning: Illegal string offset 'service2' in C:\inetpub\wwwroot\portal2\cost_details_new_customers.php on line 107

Warning: Illegal string offset 'labor2' in C:\inetpub\wwwroot\portal2\cost_details_new_customers.php on line 108

Warning: Illegal string offset 'gross2' in C:\inetpub\wwwroot\portal2\cost_details_new_customers.php on line 110

Warning: Illegal string offset 'gross4' in C:\inetpub\wwwroot\portal2\cost_details_new_customers.php on line 111
Ray - Yes the query results is exactly what I want.
All query results sets are returned in strings; that is expected behavior.  PHP uses loose typing, so strings will work correctly.

Let's try to drill down into this a little more.  Please run just this script (no loops or anything extra, beyond what is needed to make the script work) and post both the script you used, and the output in the code snippet, thanks.  I want to see one and only one row from the query results set
$query3 = "SELECT SUM(tpri) AS tpri FROM (SELECT DISTINCT Job, Total_Price AS tpri FROM V_Cust_Cost_Det_Inv_New_B WHERE (Invoice_Date BETWEEN '" . $date . "' AND '" . $date2 . "' AND Customer_Since BETWEEN '" . $date . "' AND '" . $date2 . "' AND Customer = '" . $Customer . "')) AS derivedtbl_1";
$results3 = sqlsrv_query($connPpp, $query3);
$row3 = sqlsrv_fetch_array($results3, SQLSRV_FETCH_ASSOC);	
var_dump($row3);

Open in new window

Avatar of Julian Hansen
There is a bit you are not showing us.

Your output suggests that the print_r was executed multiple times - so is there an outer loop that contains the code you posted and if so what is it doing  - the only variables you are using are the date inputs and customer - what is it that is changing on each iteration.

Can you explain your use of DISTINCT
array(2) { [0]=> string(11) "231875.0000" ["tpri"]=> string(11) "231875.0000" } array(2) { [0]=> string(10) "12179.7800" ["tpri"]=> string(10) "12179.7800" } array(2) { [0]=> string(10) "11883.8100" ["tpri"]=> string(10) "11883.8100" } array(2) { [0]=> string(9) "9492.8000" ["tpri"]=> string(9) "9492.8000" } array(2) { [0]=> string(9) "6950.0000" ["tpri"]=> string(9) "6950.0000" } array(2) { [0]=> string(9) "7130.0300" ["tpri"]=> string(9) "7130.0300" } array(2) { [0]=> string(9) "7759.4500" ["tpri"]=> string(9) "7759.4500" } array(2) { [0]=> string(10) "12150.9700" ["tpri"]=> string(10) "12150.9700" } array(2) { [0]=> string(9) "7450.0000" ["tpri"]=> string(9) "7450.0000" } array(2) { [0]=> string(9) "3356.8300" ["tpri"]=> string(9) "3356.8300" } array(2) { [0]=> string(9) "1717.0000" ["tpri"]=> string(9) "1717.0000" } array(2) { [0]=> string(8) "505.8000" ["tpri"]=> string(8) "505.8000" } array(2) { [0]=> string(8) "668.6400" ["tpri"]=> string(8) "668.6400" } array(2) { [0]=> string(8) "161.2500" ["tpri"]=> string(8) "161.2500" }
Reformatted and code tagged. Still say we are missing a piece of the puzzle here - what does the code before and after the code that produced this look like
array(2) {
	
	[0]=> string(11) "231875.0000" 
	["tpri"]=> string(11) "231875.0000" 
	} 
array(2) {
	
	[0]=> string(10) "12179.7800" 
	["tpri"]=> string(10) "12179.7800" 
}
array(2) {
	[0]=> string(10) "11883.8100"
	["tpri"]=> string(10) "11883.8100"
}
array(2) {
	[0]=> string(9) "9492.8000"
	["tpri"]=> string(9) "9492.8000"
}
array(2) {
	[0]=> string(9) "6950.0000"
	["tpri"]=> string(9) "6950.0000"
}
array(2) {
	[0]=> string(9) "7130.0300"
	["tpri"]=> string(9) "7130.0300"
}
array(2) {
	[0]=> string(9) "7759.4500"
	["tpri"]=> string(9) "7759.4500"
}
array(2) {
	[0]=> string(10) "12150.9700"
	["tpri"]=> string(10) "12150.9700"
}
array(2) {
	[0]=> string(9) "7450.0000"
	["tpri"]=> string(9) "7450.0000"
}
array(2) {
	[0]=> string(9) "3356.8300"
	["tpri"]=> string(9) "3356.8300"
}
array(2) {
	[0]=> string(9) "1717.0000"
	["tpri"]=> string(9) "1717.0000"
}
array(2) {
	[0]=> string(8) "505.8000"
	["tpri"]=> string(8) "505.8000"
}
array(2) {
	[0]=> string(8) "668.6400"
	["tpri"]=> string(8) "668.6400"
}
array(2) {
	[0]=> string(8) "161.2500"
	["tpri"]=> string(8) "161.2500"
}

Open in new window

Here is the code from body to body:

<body>
<div class="container-fluid">
<br>
<p class="noprint" ><a href = "home.php">Return to Main Page</a></p>
<h2 class="noprint" >Choose Dates</h2>
<form class="noprint" id="frmcapacitydate" name="frmcapacitydate" action="" method="GET">
<label><strong>Choose starting date: </strong></label>
</br>
<input type="text" name="capdate" class="capdate" value="" />
</br>
</br>
<label><strong>Choose ending date: </strong></label>
</br>
<input type="text" name="capdate2" class="capdate2" value="" />
</br>
</br>
<input id="btnSubmit" name="btnSubmit" type="submit" value="Submit">
</form>

<!--                                                        START OF BUTTON SUBMIT                                                           -->
<?php if (isset($_GET['btnSubmit'])) {?>
<h1 style="margin-bottom:0px;">Cost Summary for New Customers</h1>
<?php $date = substr($_GET["capdate"],0,10);?>
<?php $date2 = substr($_GET["capdate2"],0,10);?>
<?php $query = "SELECT MAX(Customer_Since) as Customer_Since, Customer, SUM(Invoice_Amount) AS Invoice_Amount, SUM(hrs2) AS hrs2, SUM(labor2) AS labor2, SUM(material3) AS material3, SUM(service2) AS service2,  SUM(Invoice_Amount - (Invoice_Amount * .2534 + service2 + material3 + labor2)) AS gross2, SUM(Invoice_Amount - (service2 + material3 + labor2)) AS gross4 FROM V_Cust_Cost_Det_Inv_New_B WHERE (Customer_Since BETWEEN '" . $date . "' AND '" . $date2 . "') AND (Invoice_Date BETWEEN '" . $date . "' AND '" . $date2 . "') GROUP BY Customer ORDER BY gross4 DESC";
$results = sqlsrv_query($connPpp, $query);?>

<h3 style="margin-bottom:10px;margin-top:0px;">Between <?php echo $date;?> and <?php echo $date2;?></h3>

<table  class="table table-striped table-bordered table-condensed">
<tr class="table-header">
<td><b>Customer</b></td>
<td><b>Customer<br>Since</b></td>
<td><b>Expected Revenue</b></td>
<td><b>Total<br>Invoiced<br>Revenue</b></td>
<td><b>Invoiced<br>Job<br>Revenue</b></td>
<td><b>Invoiced<br>Non-Job<br>Revenue</b></td>
<td><b>% of<br>
Expected<br>
Revenue</b></td>
<td><b>Material<br>Cost</b></td>
<td><b>Service<br>Cost</b></td>
<td><b>Labor<br>Cost</b></td>
<td><b>Hours</b></td>
<td><b>AHLR</b> <a id="show-option" title="(Revenue - Service - Material) / Actual Hours"><i class="fa fa-question-circle"></i></a></td>
<td><b>Profit<br>(using 25.34%<br>overhead)</b> <a id="show-option2" title="Revenue - ((Revenue * 0.2534) + Service + Material + Labor)"><i class="fa fa-question-circle"></i></a></td>
<td><b>Gross<br>Profit</b> <a id="show-option3" title="Revenue - (Service + Material + Labor)"><i class="fa fa-question-circle"></i></a></td>
</tr>

<?php While ($row = sqlsrv_fetch_array($results)) { 	
	$Customer = $row['Customer'];
	$Since = $row['Customer_Since'];
	
	//This query gets the expected revenue from every distinct job number
	$query3 = "SELECT SUM(tpri) AS tpri FROM (SELECT DISTINCT Job, Total_Price AS tpri FROM V_Cust_Cost_Det_Inv_New_B WHERE (Invoice_Date BETWEEN '" . $date . "' AND '" . $date2 . "' AND Customer_Since BETWEEN '" . $date . "' AND '" . $date2 . "' AND Customer = '" . $Customer . "')) AS derivedtbl_1";
	$results3 = sqlsrv_query($connPpp, $query3);
	$row3 = sqlsrv_fetch_array($results3);	
	var_dump($row3);
	$tpri = $row3['tpri'];
	//$sum = 0.0;
	//foreach ($row3 as $row){$sum += $row['tpri'];}
	//This query get the sum of all non-job invoices
	$query2 = "SELECT SUM(Invoice_Detail.Amount) AS tnjpri FROM Invoice_Detail INNER JOIN Invoice_Header ON Invoice_Detail.[Document] = Invoice_Header.[Document]
WHERE (Invoice_Header.Document_Date BETWEEN '" . $date . "' AND '" . $date2 . "') AND (Invoice_Detail.Job IS NULL)  AND Customer = '" . $Customer . "'";
	$results2 = sqlsrv_query($connPpp, $query2);
	$row2 = sqlsrv_fetch_array($results2);
	$tnjpri = $row2['tnjpri'];
	$Invoice_Amount = $row['Invoice_Amount'] + $tnjpri;
	$tjpri = $Invoice_Amount - $tnjpri;
	$hrs2 = $row['hrs2'];
	$material3 = $row['material3'];
	$service2 = $row['service2'];
	$labor2 = $row['labor2'];
	$perc = $tjpri/$tpri*100;
	$gross2 =  $row['gross2'];
	$gross4 =  $row['gross4'];
	$gross4per =  $gross4/$Invoice_Amount*100;
	if ($gross4per < 40) {$css_per = 'redtext';} else {$css_per = '';}
	if ($hrs2 > 0) {$AHLR2 = (($Invoice_Amount - $service2 - $material3)/$hrs2);} else {$AHLR2 = 'No Hours';}?>
<tr>
<td> <a href="cost_details_all_invoice_date.php?capdate=<?php echo $date;?>&capdate2=<?php echo $date2;?>&txtcustomer=<?php echo $Customer;?>&btnSubmit=Submit" target="new"><?php echo $Customer;?></a></td>
<td><?php echo date_format($Since, 'm/d/y');?></td>
<td>$<?php echo number_format($tpri,2);?></td>
<td>$<?php echo number_format($Invoice_Amount,2);?></td>
<td>$<?php echo number_format($tjpri,2);?></td>
<td>$<?php echo number_format($tnjpri,2);?></td>
<td><?php echo number_format($perc,2);?>%</td>
<td>$<?php echo number_format($material3,2);?></td>
<td>$<?php echo number_format($service2,2);?></td>
<td>$<?php echo number_format($labor2,2);?></td>
<td><?php echo number_format($hrs2,2);?></td>
<td>$<?php if ($AHLR2 == 'No Hours') {echo '0.00';} else {echo number_format($AHLR2,2);}?></td>
<td>$<?php echo number_format($gross2,2);?> (<?php echo number_format($gross2/$Invoice_Amount*100,2);?>%)</td>
<td>$<?php echo number_format($gross4,2);?> <span class="<?php echo $css_per;?>">(<?php echo number_format($gross4per,2);?>%)</span></td>
</tr>
<?php } //End of While?>
<?php } //End of Submit?><br>
</table>
<?php echo $sum;?>

</div>
</body>

Open in new window


Here is a screenshot of the page. I want to sum the expected revenue at the bottom of the table.

User generated image
var_dump($row3);
It's beginning to make a little more sense now.  We can see that the code is inside two while() loops.  That explains the multiple outputs from var_dump().  

You might want to change your fetch to an associative fetch, as shown above.  It's currently causing the database to produce each data element twice, making it the least efficient way to get to the "tpri" information.

<?php While ($row = sqlsrv_fetch_array($results)) {     
    $Customer = $row['Customer'];
    $Since = $row['Customer_Since'];
    
    //This query gets the expected revenue from every distinct job number
    $query3 = "SELECT SUM(tpri) AS tpri FROM (SELECT DISTINCT Job, Total_Price AS tpri FROM V_Cust_Cost_Det_Inv_New_B WHERE (Invoice_Date BETWEEN '" . $date . "' AND '" . $date2 . "' AND Customer_Since BETWEEN '" . $date . "' AND '" . $date2 . "' AND Customer = '" . $Customer . "')) AS derivedtbl_1";
    $results3 = sqlsrv_query($connPpp, $query3);
    $row3 = sqlsrv_fetch_array($results3, SQLSRV_FETCH_ASSOC); // FIX THIS 
    var_dump($row3);

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ray - Let me absorb that. Might be a few hours before I can respond. Thanks for your quick responses.
While ($row = sqlsrv_fetch_array($results))

Open in new window

Kind of important - I would look at trying to do as much of this in the DB as possible - but don't have the time right now - if not solved by tomorrow will check back in.
Ray - You are always the best