Robert Francis
asked on
Calculate values in an array
I have the following code:
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.
$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'];
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.
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);
... 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?
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?
ASKER
Apparently tpri is a string because I am getting a bunch of this:
Warning: Illegal string offset 'tpri' in C:\inetpub\wwwroot\portal2 \cost_deta ils_new_cu stomers.ph p on line 93
Warning: Illegal string offset 'tpri' in C:\inetpub\wwwroot\portal2 \cost_deta ils_new_cu stomers.ph p 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_deta ils_new_cu stomers.ph p on line 103
Warning: Illegal string offset 'hrs2' in C:\inetpub\wwwroot\portal2 \cost_deta ils_new_cu stomers.ph p on line 105
Warning: Illegal string offset 'material3' in C:\inetpub\wwwroot\portal2 \cost_deta ils_new_cu stomers.ph p on line 106
Warning: Illegal string offset 'service2' in C:\inetpub\wwwroot\portal2 \cost_deta ils_new_cu stomers.ph p on line 107
Warning: Illegal string offset 'labor2' in C:\inetpub\wwwroot\portal2 \cost_deta ils_new_cu stomers.ph p on line 108
Warning: Illegal string offset 'gross2' in C:\inetpub\wwwroot\portal2 \cost_deta ils_new_cu stomers.ph p on line 110
Warning: Illegal string offset 'gross4' in C:\inetpub\wwwroot\portal2 \cost_deta ils_new_cu stomers.ph p on line 111
Warning: Illegal string offset 'tpri' in C:\inetpub\wwwroot\portal2
Warning: Illegal string offset 'tpri' in C:\inetpub\wwwroot\portal2
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
Warning: Illegal string offset 'hrs2' in C:\inetpub\wwwroot\portal2
Warning: Illegal string offset 'material3' in C:\inetpub\wwwroot\portal2
Warning: Illegal string offset 'service2' in C:\inetpub\wwwroot\portal2
Warning: Illegal string offset 'labor2' in C:\inetpub\wwwroot\portal2
Warning: Illegal string offset 'gross2' in C:\inetpub\wwwroot\portal2
Warning: Illegal string offset 'gross4' in C:\inetpub\wwwroot\portal2
ASKER
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
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);
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
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
ASKER
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"
}
What code did you use to get the output posted here:
https://www.experts-exchange.com/questions/28986632/Calculate-values-in-an-array.html?anchorAnswerId=41908800#a41908800
https://www.experts-exchange.com/questions/28986632/Calculate-values-in-an-array.html?anchorAnswerId=41908800#a41908800
ASKER
Here is the code from body to body:
Here is a screenshot of the page. I want to sum the expected revenue at the bottom of the table.
<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>
Here is a screenshot of the page. I want to sum the expected revenue at the bottom of the table.
ASKER
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.
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);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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))
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.
ASKER
Ray - You are always the best
See fetchType in the documentation.