there is a report which displays the enquiries made by users in the
below mentioned format.
the count column refers to how many number of times an enquiry was
made for a particular city
count city price
2 sydney $100
1 perth $200
2 melbourne $300
the total value of the enquiries should be $1000 ( 2 X $100 + 1 X
$200 + 2 X $300) presently i am able to add the price column which
displays $600 actually it should be $1000
following is the code presently
======================================================================
========
mysql_select_db($database_connEnquiry, $connEnquiry);
$sql = " SELECT Count(*) as Counts, Title, City, Price, FROM
`enquiry` WHERE date_format(en_date,'%Y-%m-%d') BETWEEN '" .
$startDate . "' AND '" . $endDate . "' " . "
Group By Title, City, Price Order By Counts Desc, Title Desc, mode,
Price";
$rs_newEnquiries = mysql_query($sql, $connEnquiry) or die(dbError
(query1));
$echoStr = "<table width='80%' cellpadding=1 cellspacing=1
border=0><tr bgcolor='#cccccc;'><th bgcolor='#cccccc'
width=10>Count</th><th bgcolor='#cccccc'
width=400>Destination</th><th bgcolor='#cccccc' width=100>Direct
Enquiries</th><th bgcolor='#cccccc' width=100>Mode</th> <th
bgcolor='#cccccc' width=50>Priced From</th>
<th bgcolor='#cccccc' width=35>numAdults</th> <th bgcolor='#cccccc'
width=35>numChildren</th> <th bgcolor='#cccccc'
width=30>numInfants</th> <th align='center'
bgcolor='#cccccc'>Story</th> </tr>";
$total = 0; $totalprice = 0;
while ($row_rs_newEnquiries = mysql_fetch_assoc($rs_newEnquiries))
{
$echoStr .= "
<tr bgcolor='#ebebeb'
class='default'><td>".$row_rs_newEnquiries['Counts']."</td>
<td
class='default'> ".$row_rs_newEnquiries['Title']."</td>
<td
class='default'> ".$row_rs_newEnquiries['City']."</td>
<td
class='default'> ".$row_rs_newEnquiries['Price']."</td>
</tr>";
$total = $total +
$row_rs_newEnquiries['Counts'];
$totalprice = $totalprice + $row_rs_newEnquiries['Price'];
}
$echoStr .= "<tr bgcolor='#cccccc;'><th align=\"left\"
bgcolor='#cccccc'><b>".$total."</b></th>
<th bgcolor='#cccccc'>Total</th>
<td bgcolor='#cccccc'> </td><td bgcolor='#cccccc'></td><td
bgcolor='#cccccc'><b>$ ".$totalprice."</b> </td><td
bgcolor='#cccccc'><b>".$totalnumadults."</b></td><td
bgcolor='#cccccc'><b>".$totalnumchildren."</b></td><td
bgcolor='#cccccc'><b>".$totalnuminfants."</b></td><td
bgcolor='#cccccc'></td></tr></table>";
======================================================================
========
With the above code i am able to add the value of the price and
display the $ amount. however if the count for a city is for example
2 which means there were 2 enquiries for
example city sydney and the price is $100 then the total should be 2
X $100 which is $200 how can i change the above above code in a way
that the $totalprice will be able to
1) multiply the numeric value in count column with the price column
and similarly for all the rows
2) and add the $ amount in the price column and sum it up and be able
to display in echo $totalprice;
please advice.
thanks
