question about select query

[runway27]

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'>&nbsp; ".$row_rs_newEnquiries['Title']."</td>
                                        <td class='default'>&nbsp; ".$row_rs_newEnquiries['City']."</td>
                                        <td class='default'>&nbsp; ".$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'>&nbsp;</td><td bgcolor='#cccccc'></td><td bgcolor='#cccccc'><b>$&nbsp;".$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

[adamalton]

I'm being lazy and not bothering to fully read your code, so forgive me if I don't get this quite right..

[PHP]$total = 0; $totalprice = 0;//this isn't really necessary, though it wont hurt

while ($row_rs_newEnquiries = mysql_fetch_assoc($rs_newEnquiries))
{
$total_price_for_this_row = $row_rs_newEnquiries['Counts'] * $row_rs_newEnquiries['Price'];

$echoStr .= "
<tr bgcolor='#ebebeb' class='default'><td>".$row_rs_newEnquiries['Counts']."</td>
<td class='default'>&nbsp; ".$row_rs_newEnquiries['Title']."</td>
<td class='default'>&nbsp; ".$row_rs_newEnquiries['City']."</td>
<td class='default'>&nbsp; ".$row_rs_newEnquiries['Price']."</td>
<td class='default'>&nbsp; ". $total_price_for_this_row ."</td>
</tr>";

$totalprice =+ $total_price_for_this_row;//add this row's total to the overall total

$total = $total + $row_rs_newEnquiries['Counts'];//this isn't needed unless you want a total for the number of counts
}[/PHP]

Note the use of +=
$a +=$b; is like saying $a = $a+$b;

I hope that's what you were looking for

[Atli]

Hi runway27.

Please use [code] tags when posting your code examples. (See How to ask a question)

[code=php] ...PHP code goes here... [/code]

Thank you.
Moderator