Hi all,
I have a question about echo query; Actually , i have 3 sql query with different condition as below(completed code please see the code inside
Sql1 → $sql=“select carriername,cdr.countryid, shortname,count(cdr.countryid)as a ,sum(totaltime) as c from…where duration >=0”
Sql2 → $sql1=“select carriername,countryid, count(countryid)as b from …where duration >0”
Sql3 → $sql2=“select carriername,countryid, count(countryid)as c from…where duration >=30”
then the result value will echo on the , but the format isn’t what i expect to output, for example, i expect the red color should echo on red column, yellow color should echo on yellow column, but now isn’t, because the result will not the same between total(yellow) and S>30(red , so the yellow will echo on the red; Please see the 2 photos was attached; So how can i fix it? The completed code also copied as below , thanks all.
[php]
asr_Sheet6| (SOFTAMED2) Carrier Report | Table | |||||
| From | time | |||||
| To | time |
<?php
if (isset($_POST[‘run’]))
{
$table=$_POST[‘table’];
$todate=$_POST[‘todate’];
$totime=$_POST[‘totime’];
$fromtime=$_POST[‘fromtime’];
$fromdate=$_POST[‘fromdate’];
require ‘conn.php’;
$sql=“select carriername,cdr.countryid, shortname,count(cdr.countryid)as a ,sum(totaltime) as c from
$table cdr ,shortName where cdr.countryid=shortName.countryid and
startdate>=’$fromdate’ and startdate<=’$todate’ and
concat(startDate ,’ ‘, startTime) >= ‘$fromdate $fromtime’ and
concat(startDate ,’ ', startTime) <= ‘$todate $totime’ and totaltime>=0 and
customerid<>‘52’ and customerid<>‘10415’ and
cause not like ‘%34%’ and cause not like ‘%38%’ and
cause not like ‘%42%’ and cause not like '%44%‘and
cause not like ‘%47%’ and cause <> ‘-3’ and cause <> ‘3’ and
cause not like ‘%41%’ and cause not like ‘%21%’ and
accounttype not like’wholesa%’ and (computername=‘SOFTAMED2’ or computername=‘SOFTAMED3’) and
cdr.countryid<>0 group by carriername,cdr.countryid order by carriername,cdr.countryid desc”;
$rs=odbc_exec($con,$sql) or die($con);
if(!$rs){exit("SQL error");}
$count=odbc_num_rows($rs);
while($results_array_all = odbc_fetch_array($rs))
{
$carrierarray_all[] = $results_array_all["carriername"];
$countryidarray_all[] = $results_array_all["countryid"];
$shortname_all[] = $results_array_all["shortname"];
$counta_all[] = $results_array_all["a"];
$sumtotal[] = $results_array_all["c"];
$carriername=odbc_result($rs,'carriername');
$countryid=odbc_result($rs,'countryid');
$shortname=odbc_result($rs,'shortname');
}
$sql1="select carriername,countryid, count(countryid)as b from $table cdr where
startdate>= '$fromdate' and startdate<='$todate' and concat(startDate ,' ', startTime) >='$fromdate $fromtime' and
concat(startDate ,' ', startTime) <='$todate $totime' and customerid<>'52' and customerid<>'10415' and duration>29
and cause not like '%34%' and cause not like '%38%' and cause not like '%42%' and cause not like '%44%' and
cause not like '%47%' and cause <> '-3' and cause <> '3' and cause not like '%41%' and
cause not like '%21%' and accounttype not like'wholesa%' and
(computername='SOFTAMED2' or computername='SOFTAMED3') and cdr.countryid<>0
group by carriername,countryid order by carriername,countryid desc";
$rs1=odbc_exec($con,$sql1) or die($con);
$count1=odbc_num_rows($rs1);
while($results_array_29 = odbc_fetch_array($rs1)){
$carrierarray_29[] = $results_array_29["carriername"];
$countryidarray_29[] = $results_array_29["countryid"];
$b_29[] = $results_array_29["b"];
if(!$rs1){exit("SQL error");}
$carriername_29=odbc_result($rs1,'carriername');
$countryid_29=odbc_result($rs1,'countryid');
}
$sql2="select carriername,countryid, count(countryid)as c from $table cdr where
startdate>= '$fromdate' and startdate<='$todate' and concat(startDate ,' ', startTime) >='$fromdate $fromtime' and
concat(startDate ,' ', startTime) <='$todate $totime' and customerid<>'52' and customerid<>'10415' and duration>0
and cause not like '%34%' and cause not like '%38%' and cause not like '%42%' and cause not like '%44%' and
cause not like '%47%' and cause <> '-3' and cause <> '3' and cause not like '%41%' and
cause not like '%21%' and accounttype not like'wholesa%' and
(computername='SOFTAMED2' or computername='SOFTAMED3') and cdr.countryid<>0
group by carriername,countryid order by carriername,countryid desc";
$rs2=odbc_exec($con,$sql2) or die($con);
$count2=odbc_num_rows($rs2);
while($results_array_0 = odbc_fetch_array($rs2)){
$carrierarray_0[] = $results_array_0["carriername"];
$countryidarray_0[] = $results_array_0["countryid"];
$c_0[] = $results_array_0["c"];
if(!$rs2){exit("SQL error");}
$carriername_0=odbc_result($rs2,'carriername');
$countryid_0=odbc_result($rs2,'countryid');
}
echo "<table border='1' >";
echo "
<tr>
<th>carrier</td>
<th>ID</td>
<th>Contshort_name</td>
<th>>=0</td>
<th bgcolor='#D41F00'>S>30</td>
<th bgcolor='#FF00FF'>R(S>30)</td>
<th bgcolor='FFFF00'>Total Time</td>
</tr>
";
for($i=0;$i<$count;$i++)
{
$shortname_aver=$shortname_all[$i];
$sql3=“select aver as e, ACD as f
from contave where
contshortname = ‘$shortname_aver’”;
$rs3=odbc_exec($con,$sql3) or die($con);
while(odbc_fetch_row($rs3))
{
$e= odbc_result($rs3,‘e’);
$f= odbc_result($rs3,‘f’);
if(!$rs1){exit("SQL error");}}
echo"<tr align='center'>";
echo"<td width=200 >$carrierarray_all[$i] </td>"; # col 1
#echo"<td ><input type='text' name='carriername_all' disabled value='$carrierarray_all[$i]'> </td>";
echo"<td width=100>$countryidarray_all[$i]</td>"; # col 2
echo"<td width=100>$shortname_all[$i]</td>"; # col 3
echo"<td width=100> $counta_all[$i]</td>"; # col 4
for($j=0;$j<$count1;$j++)
{
if ($carrierarray_all[$i]==$carrierarray_29[$j] && $countryidarray_all[$i]==$countryidarray_29[$j])
{
global $b29;
$b29=0;
if (isset($b_29[$j])){$b29=$b_29[$j];}
echo"<td width=100 bgcolor='#D41F00'>$b29</td>"; # col 5
$rs30=$b29 / $counta_all[$i];
$rs30=number_format(round($rs30*100)/100,2);
echo"<td width=100 bgcolor='#FF00FF'>$rs30</td>"; # col 6
}
}
#$sumtotal=0;
#if (isset($sumtotal[$i])){$sumtotal=$sumtotal[$i];}
echo"<td bgcolor='FFFF00' width=100>$sumtotal[$i]</td>"; # col 7
}
echo “”;
odbc_close($con);
}
?>
[/php]Please help !!! Thank you all guys.
