Solved

multiple "total" result

Posted on 2013-07-02
2
409 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 108

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction Knockoutjs (Knockout) is a JavaScript framework (Model View ViewModel or MVVM framework).   The main ideology behind Knockout is to control from JavaScript how a page looks whilst creating an engaging user experience in the least …
SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now