question about select query - Webmaster Forums - Webmaster forum for HTML, PHP, ASP, CSS and more

sudhakararaog · 01-19-2008, 02:50 AM

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'>".$total."</th>
<th bgcolor='#cccccc'>Total</th>

<td bgcolor='#cccccc'> </td><td bgcolor='#cccccc'></td><td bgcolor='#cccccc'>$ ".$totalprice." </td><td bgcolor='#cccccc'>".$totalnumadults."</td><td

bgcolor='#cccccc'>".$totalnumchildren."</td><td bgcolor='#cccccc'>".$totalnuminfants."</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

lingsharma · 02-04-2008, 03:57 AM

Pls post the output you are getting now in the form of image so that i can understand your question better. Then i'll help you with the code.

**etono** · 03-22-2008, 10:55 PM

Hello Sudhakararaog,

Sorry for the late reply. Here is what you currently have for totaling up the amount:

$totalprice = $totalprice + $row_rs_newEnquiries['Price'];

But, I think you should be multiplying the price time the count like this:

$totalprice = $totalprice + ($row_rs_newEnquiries['Price'] * $row_rs_newEnquiries['Counts']);