Hi There,

I'm busy developing a PHP / MySQL billing system for our company but can't seem to get the following working for me.
I need to have 1 page that will display the extensions billing period / month for local, national and international calls and it needs to be displayed on 1 line per extension.

Really hope someone can help in resolving this.

Kind Regards

Can you show how your data looks, and an example of the required output?

Hi Pritaeas,

Attached is the way I would like it to be.

Regards

We need to know your table structure (preferably with some sample data) too.

Hi Pritaeas,

I attached a screenshot of the lcr_data table. THis table includes all my prefixes. I would like to know if it's possible to extract data directly from MySQL in the following manner.

Ext.....| Local Total | Local Cost | National Total | National Cost | Mobile Total
8300...30.................R 300.00....10.....................R 50.00............0
8301...10.................R 100.00....2.....................R 3.00............0

I don't know if it's possible to do this in MySQL and then just output the variables?

This is my MySQL code sofar:

select SUBSTRING(cdr.calldate,1,10) as Calldate, cdr.src, cdr.dst, SUBSTRING(cdr.dst,1,3), lcr_data.lcr_prefix, SUM(cdr.billsec) as Billsec, lcr_data.lcr_peakrate AS Rate, COUNT(src) as ExtCount, SUM(cdr.billsec*lcr_data.lcr_peakrate/60) as Tarrif, lcr_data.lcr_location from cdr, lcr_data where SUBSTRING(cdr.dst,1,3) = lcr_data.lcr_prefix GROUP by src limit 5

Regards

Hi Pritaeas,

The table structures.

Thanks

Hi Pritaeas,

This is what I get, all I would like to do now is to allign it properly.

Extension | Cell | National | Local | International

Thanks

Can you show how you are displaying the results (your code) ?

Hi Pritaeas,

Here is the code that I used to get the data per extension.

Thanks

<?php
session_start();
include "include/config.php";
?>
<table width="100%" border="0">

<?php
#Extension Numbers
$sqlSumExt1="select calldate, src, SUBSTRING(cdr.src,7,10) as Ext, cdr.dst, SUBSTRING(cdr.dst,1,3), COUNT(lcr_data.lcr_prefix) as Summery,lcr_data.lcr_prefix, SUM(cdr.billsec) as Billsec, lcr_data.lcr_peakrate AS Rate, SUM(cdr.billsec*lcr_data.lcr_peakrate/60) as Tarrif, lcr_data.lcr_location, cdr.lastdata from cdr, lcr_data where calldate > '2011-08-01' and calldate < '2011-09-31' and src like '01154483%' and lastdata like 'SIP/ECNTEB/%' and SUBSTRING(cdr.dst,1,3) = lcr_data.lcr_prefix GROUP BY src, lcr_location";
$sqlSumResultsExt1=mysql_query($sqlSumExt1)or die(mysql_error());
$sqlSumRowExt1=mysql_num_rows($sqlSumResultsExt1)or die(mysql_error());

for($a=0; $a< $sqlSumRowExt1; $a++){
    $LocalExtCount1=mysql_result($sqlSumResultsExt1, $a, 'Ext');
    $LocalTarrif1=mysql_result($sqlSumResultsExt1, $a, 'Tarrif');
    $Summ=mysql_result($sqlSumResultsExt1, $a, 'Summery');
    $lcr_location1=mysql_result($sqlSumResultsExt1, $a, 'lcr_data.lcr_location');
    $location[]=mysql_result($sqlSumResultsExt1, $a, 'lcr_data.lcr_location');

######################################################################################################################################################################################################################################################################################################################################################################################################


?>  
<?php
    #echo $LocalExtCount1;
?>
<tr>
    <td><? echo " Extension : " .$LocalExtCount1. " ............. ". $lcr_location1 ." Cost R ".round($LocalTarrif1,2)." : ". $Summ?></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>



  </tr>
<?php
    }
?>

</table>

You should put each variable in it's own <td>, then it will align automatically.

Hi Pritaeas,

I have the following code.

for($a=0; $a< $sqlSumRowExt1; $a++){
    $LocalExt1=mysql_result($sqlSumResultsExt1, $a, 'Ext');
    $LocalTarrif1=mysql_result($sqlSumResultsExt1, $a, 'Tarrif');
    $Summ=mysql_result($sqlSumResultsExt1, $a, 'Summery');
    $lcr_location1=mysql_result($sqlSumResultsExt1, $a, 'lcr_data.lcr_location');




?>  
<?php
    #echo $LocalExt1;
?>

My extensions is in the $LocalExt1 and the cost involed is LocalTarrif1. If I put the variable $LocalExt1 and LocalTarrif1 etc in between each <td> </td> I will get the same data over and over.

I would like to have all data per Extension on 1 line.

Extension | Cell | National | Local | International

Regards

for ($a = 0; $a < $sqlSumRowExt1; $a++) {
    $LocalExt1 = mysql_result($sqlSumResultsExt1, $a, 'Ext');
    $LocalTarrif1 = mysql_result($sqlSumResultsExt1, $a, 'Tarrif');
    $Summ = mysql_result($sqlSumResultsExt1, $a, 'Summery');
    $lcr_location1 = mysql_result($sqlSumResultsExt1, $a, 'lcr_data.lcr_location');

    echo '<tr>';
    echo "<td>$LocalExt1</td>";
    echo "<td>$lcr_location1</td>";
    echo "<td>" . round($LocalTarrif1, 2) . "</td>";
    echo '</tr>';
}

Hi Pritaeas,

It's displaying exactly the same as in example 1.

Extension | Cell | National | Local | International
8303..........80.....45............50.......3
8304..........70.....35............10.......1
8310..........50.....25............20.......5
8311..........40.....15............30.......7

and not
Extension
8303........80....Cell
8303........35....National
8303........50....Local
8303........73....International
8304........70....Cell
8304........35....National
8304........10....Local
8304........17....International
8310........50....Cell
8310........25....National
8310........20....Local
8310........25....International
8311........40....Cell
8311........15....National
8311........30....Local
8311........17....International

At this stage I'm getting it in the 2nd example but it takes up allot of space. I would like to get them group by extension as in example 1.

Hope this makes more sense.
Thanks for your assistance so far.

Regards

If you want to do it in PHP, then you can start a new row every four records.

Not all the extension have 4 records, it's dependend on if the extension dialed cell phones, local, national and international calls. If the extension only did cell calls he will have only 1 row etc.

Don't know if what I want is even possible.

Regards

If they are ordered by extension, and by call type (cell, national, local, international) then you can easily insert an empty cell. I'd execute the query and then build an array first, and the table after that. Something like this:

$values = array ();
for ($a = 0; $a < $sqlSumRowExt1; $a++) {
    $LocalExt1 = mysql_result($sqlSumResultsExt1, $a, 'Ext');
    $LocalTarrif1 = mysql_result($sqlSumResultsExt1, $a, 'Tarrif');
    $Summ = mysql_result($sqlSumResultsExt1, $a, 'Summery');
    $lcr_location1 = mysql_result($sqlSumResultsExt1, $a, 'lcr_data.lcr_location');

    $values[$LocalExt1][$lcr_location1] = round($LocalTarrif1, 2);
}

$types = array ('Cell', 'National', 'Local', 'International');
foreach ($values as $extension => $value) {
    echo '<tr>';
    echo "<td>$extension</td>";
    foreach ($types as $type) {
        echo '<td>';
        if (isset($values[$extension][$type])) {
            echo $values[$extension][$type];
        }
        else {
            echo '&nbsp;';
        }
        echo '</td>';
    }
    echo '</tr>';
}

Hi Pritaeas,

That works 100%, how will I get the count for local, national, cell and international calls. Would like to have the count for all local, national, cell and international calls before I display the cost

Thanks for your assistance.

Between line 14 and 15 add:

$sum = array_sum($values[$extension]);
echo "<td>$sum</td>";

$sum = array_sum($values[$extension]);
echo "<td>$sum</td>";

Hi Pritaeas,

This gives me the sum for the cost, I would like to have the total count (Number) for local calls, national calls, etc.

extension | local count | local cost | national count | national cost
8303..........10...............50.............5.....................30

The costing is working 100%, just want to get the ammount of local calls before local cost etc.

Once again thanks for your assistance.

Regards

What column in your query is it in? Just use that one.

Hi Pritaeas,

The values are located in

$Summ = mysql_result($sqlSumResultsExt1, $a, 'Summery');

How would I implement it from this variable to display the number of local calls, national calls etc?

Thanks

Hi Pritaeas,

I'm stuck again and don't know what to do anymore. Hope you will assist me again. I can't seem to get the number off calls for Local, National etc in line with the data that's being displayed.

Thanks

Show your new code (the relevant part).

Hi Pritaeas,

I'm so lost with this at ths stage. Sorry for being an idiot..

$values = array ();
for ($a = 0; $a < $sqlSumRowExt1; $a++) {
    $LocalExt1 = mysql_result($sqlSumResultsExt1, $a, 'Ext');
    $LocalTarrif1 = mysql_result($sqlSumResultsExt1, $a, 'Tarrif');
    $Summ = mysql_result($sqlSumResultsExt1, $a, 'Summary');
    $lcr_location1 = mysql_result($sqlSumResultsExt1, $a, 'lcr_data.lcr_location');
    $values[$LocalExt1][$lcr_location1] = round($LocalTarrif1, 2);
    $TotalSum[]=mysql_result($sqlSumResultsExt1, $a, 'Summary');

}   

foreach($TotalSum as $Total){
    echo $Total;    
}

$types = array ('Local', 'National', 'Cell', 'IntNational', '086National');
foreach ($values as $extension => $value) {
    echo '<tr>';
    echo "<td>$extension</td>";

    foreach ($types as $type) {
        echo '<td>';
        if (isset($values[$extension][$type])) {
            echo $values[$extension][$type];
        }
        else {
            echo '&nbsp;';
        }
        echo '</td>';
    }

    echo '</tr>';
}

Regards

I can't seem to get the number off calls for Local, National

Which variables contain those values?

Hi Pritaeas,

This Variable contains the code $TotalSum[]=mysql_result($sqlSumResultsExt1, $a, 'Summary');

Thanks

Add:

echo "<td>$Total</td>";

on line 20.

Hi Pritaeas,

Nope it only displays 1 for every row.

Regards