Solved

multiple "total" result

Posted on 2013-07-02
2
417 Views
Last Modified: 2013-07-08
How do I display the total charge for 0005, 0006, 0007, 0008 and 0009 individually in the page?

The working code previously as below:

<?php
//make sure you see all the errors
error_reporting(E_ALL);
ini_set('display_errors', 1);

//connect to your database
$con=mysqli_connect("xxx","xxx","xxx","xxx");
if (mysqli_connect_errno()) { die("Failed to connect to MySQL: " . mysqli_connect_error()); }

//select only the rows we need
//$result = mysqli_query($con,"SELECT * FROM Bill_Import WHERE (CallType != '4' AND DurationTime != '00' AND SUBSTRING(CalledPartyNumber, 1, 1) != '1');

//create a variable to hold the overall total
$totalCharge = 0;

//loop through the records
while($row = mysqli_fetch_array($result)) {
      
      //we only need to output a row if the CallingPartyNumber matches certain values
      switch ($row['CallingPartyNumber']) {
            case '0005':
            case '0006':
            case '0007':
            case '0008':
            case '0009':
                  //set some default values
                  $price = 0;
            
                  $areaCode = substr($row['CalledPartyNumber'], 0, 5);
                  $billingCode = substr($row['CalledPartyNumber'], 0, 2);
                  $timeMultiplier = ceil($row['DurationTime'] / 30);

                  //figure out what price to charge
                  switch ($billingCode) {
                        case '00':
                              if ($areaCode == '00818') {
                                    $price = 0.5;
                              } elseif ($areaCode == '00852') {
                                    $price = 0.1;
                              }
                              break;
                              
                        case '01':
                              $price = 0.12;
                              break;
                  
                        case '03':
                              $price = 0.09;
                              break;
                              
                        case '13':
                        case '18':
                              $price = 0.15;
                              break;
                  };
      
                  //now we can calculate the row charges
                  $rowCharges = $timeMultiplier * $price;
                  //$totalCharge += $rowCharges;
                  
                  //output a table row
                    printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>",
                        $row['DialedNumber'],
                        $row['CallType'],
                        $row['CallingPartyNumber'],
                        $row['CalledPartyNumber'],
                        $areaCode,
                        $row['DurationTime'],
                        $rowCharges
                  );
                  
                  //increment the total charge
                  $totalCharge += $rowCharges;
      };

}

//now output the running total
printf("<tr><td colspan=6>Total</td><td>%s</td></tr>", $totalCharge);

?>
0
Comment
Question by:rolandmy
2 Comments
 
LVL 42

Accepted Solution

by:
sedgwick earned 400 total points
ID: 39292620
check line 59.
i use dictionary to calculate the total for each CallingPartyNumber.

<?php 
//make sure you see all the errors
error_reporting(E_ALL);
ini_set('display_errors', 1);

//connect to your database
$con=mysqli_connect("xxx","xxx","xxx","xxx");
if (mysqli_connect_errno()) { die("Failed to connect to MySQL: " . mysqli_connect_error()); }

//select only the rows we need
//$result = mysqli_query($con,"SELECT * FROM Bill_Import WHERE (CallType != '4' AND DurationTime != '00' AND SUBSTRING(CalledPartyNumber, 1, 1) != '1');

//create a variable to hold the overall total 
$totalCharge = 0;
var partNumberTotals = [];
//loop through the records
while($row = mysqli_fetch_array($result)) {
      
      //we only need to output a row if the CallingPartyNumber matches certain values
      switch ($row['CallingPartyNumber']) {
            case '0005':
            case '0006':
            case '0007':
            case '0008':
            case '0009':
                  //set some default values
                  $price = 0;
            
                  $areaCode = substr($row['CalledPartyNumber'], 0, 5);
                  $billingCode = substr($row['CalledPartyNumber'], 0, 2);
                  $timeMultiplier = ceil($row['DurationTime'] / 30);

                  //figure out what price to charge
                  switch ($billingCode) {
                        case '00':
                              if ($areaCode == '00818') {
                                    $price = 0.5;
                              } elseif ($areaCode == '00852') {
                                    $price = 0.1;
                              }
                              break;
                              
                        case '01':
                              $price = 0.12;
                              break;
                  
                        case '03':
                              $price = 0.09;
                              break;
                              
                        case '13':
                        case '18':
                              $price = 0.15;
                              break;
                  };
      
//the key is the CallingPartyNumber, and the value is accumulated each loop,
//so u ended up having the total for each CallingPartyNumber.
partNumberTotals[$row['CallingPartyNumber']] += $price;

                  //now we can calculate the row charges
                  $rowCharges = $timeMultiplier * $price;
                  //$totalCharge += $rowCharges;
                  
                  //output a table row
                    printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>",
                        $row['DialedNumber'],
                        $row['CallType'],
                        $row['CallingPartyNumber'],
                        $row['CalledPartyNumber'],
                        $areaCode,
                        $row['DurationTime'],
                        $rowCharges
                  );
                  
                  //increment the total charge
                  $totalCharge += $rowCharges;
      };

}

//now output the running total
printf("<tr><td colspan=6>Total</td><td>%s</td></tr>", $totalCharge);

?>

Open in new window

0
 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 100 total points
ID: 39292984
Please use the code snippet feature to post code examples here at EE. It gives us a unispace font and line numbers that facilitate discussion.  Here is the code from the original question, shown in the code snippet.
<?php
//make sure you see all the errors
error_reporting(E_ALL);
ini_set('display_errors', 1);

//connect to your database
$con=mysqli_connect("xxx","xxx","xxx","xxx");
if (mysqli_connect_errno()) { die("Failed to connect to MySQL: " . mysqli_connect_error()); }

//select only the rows we need
//$result = mysqli_query($con,"SELECT * FROM Bill_Import WHERE (CallType != '4' AND DurationTime != '00' AND SUBSTRING(CalledPartyNumber, 1, 1) != '1');

//create a variable to hold the overall total
$totalCharge = 0;

//loop through the records
while($row = mysqli_fetch_array($result)) {
      
      //we only need to output a row if the CallingPartyNumber matches certain values
      switch ($row['CallingPartyNumber']) {
            case '0005':
            case '0006':
            case '0007':
            case '0008':
            case '0009':
                  //set some default values
                  $price = 0;
            
                  $areaCode = substr($row['CalledPartyNumber'], 0, 5);
                  $billingCode = substr($row['CalledPartyNumber'], 0, 2);
                  $timeMultiplier = ceil($row['DurationTime'] / 30);

                  //figure out what price to charge
                  switch ($billingCode) {
                        case '00':
                              if ($areaCode == '00818') {
                                    $price = 0.5;
                              } elseif ($areaCode == '00852') {
                                    $price = 0.1;
                              }
                              break;
                              
                        case '01':
                              $price = 0.12;
                              break;
                  
                        case '03':
                              $price = 0.09;
                              break;
                              
                        case '13':
                        case '18':
                              $price = 0.15;
                              break;
                  };
      
                  //now we can calculate the row charges
                  $rowCharges = $timeMultiplier * $price;
                  //$totalCharge += $rowCharges;
                  
                  //output a table row
                    printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>",
                        $row['DialedNumber'],
                        $row['CallType'],
                        $row['CallingPartyNumber'],
                        $row['CalledPartyNumber'],
                        $areaCode,
                        $row['DurationTime'],
                        $rowCharges
                  );
                  
                  //increment the total charge
                  $totalCharge += $rowCharges;
      };

}

//now output the running total
printf("<tr><td colspan=6>Total</td><td>%s</td></tr>", $totalCharge);

?> 

Open in new window

That said, is this a real application with economic value?  If so, please consider getting professional help with the project.  There are a number of red flags here that lead me to believe you may be building a brittle piece of software.  Examples:

SELECT *
This causes a complete table scan, transferring all fields in all matched rows.  It's usually the first culprit when performance suffers.  A better strategy would be to SELECT the columns you need by name.

no LIMIT, GROUP or ORDER clauses
Again, performance related, but also this indicates that the data may not be well organized.  A professional DBA could give you a moment's advice that would be hugely valuable.

SUBSTRING in a WHERE clause
Make a Google search for the exact phrase, Should I Normalize My Database, and read the very interesting positions on either side of the argument.  With very tiny tables, it may not matter.  If your tables grow to any size at all, you want to be able to write queries that do their work quickly.  SUBSTRING makes me think that your tables may not have the appropriate indexes.

PHP substr() in retrieved rows
Another data organization problem.  It appears that the data base may fail the ACID test.  A better strategy would be to organize the tables' columns into elements that can be addressed in the query, rather than after the data (perhaps too much data) has been transferred.

Use of MySQLI_Fetch_Array() function
By default this function returns twice as much data as you need, so it is guaranteed to create a performance penalty.  Fortunately you can find all of the PHP functions documented in the online man pages, so by simply reading the function descriptions you can avoid mistakes like this one.

Use of switch/case without default
This virtually guarantees that a data error can cause the script to behave in an unpredictable manner.

Use of program code to determine data values
This should really be part of a data base lookup.  As written, you will find that you have to change the programming when a rate change occurs.  And that means that you must rerun all of your unit and integration tests for even a penny-change in the rates.  That is what we call a design flaw; a better design would abstract the list of numbers and the rate table so that maintenance, additions and deletions of numbers and rates could be done without any programming changes.

If you're new to PHP and MySQL, and only have time to read one book on the subject, this would be my recommendation.  It will put you on a firmer footing.
http://www.amazon.com/PHP-MySQL-Web-Development-Edition/dp/0321833899

Best regards, and best of luck with your project, ~Ray
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will show, step by step, how to integrate R code into a R Sweave document
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question