Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# multiple "total" result

Posted on 2013-07-02
Medium Priority
421 Views
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);

\$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
Question by:rolandmy
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 42

Accepted Solution

sedgwick earned 1600 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);

\$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);

?>
``````
0

LVL 111

Assisted Solution

Ray Paseur earned 400 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);

\$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);

?>
``````
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

Question has a verified solution.

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