如何编码查询以显示每个所选旅游房类型，数量和总成本的汇总价格？

I've been learning PHP+MySQL with much help from the generous coders on Stackoverlflow and am nearing the end of my first functional database but am stuck.

I'm trying to set up a database wherein a user selects a room type for each person or family (upto 12 people total). The form is setup such that each person is assigned a room category (because the product is sold per person instead of per room). Available room types are SGL, DBL (max 2 persons), TPL (max 3 persons), and QUD (max 4 persons).

I have a table with entered prices for each room type called tourprices with these prices varying based on the date of tour start in a separate table called tourstart.

Basically, I want to write code that will look at the room type selected for each person, look up the associated room price, count to the occupany per room type, multitply the price x the room type quantity (displaying the per person rate next to each person) and summarize the total cost for all rooms combined.

Price Table has rows "Id, date, SGL, DBL, TPL, QUD"
Client table has rows "Id, name, tour purchased"
Reservation table has rows *"Id, client_id, tour_id, tourdate_id, roomtype"*
Tour table has rows *"Tour_id, tour name, open/closed to sales"*
Tour dates table has rows *"Tourdate_id, tour_id, date1, date2, date3,... date25"*

For example: tourroom prices(per person)

1May2012 tour - SGL=$300; DBL=$150; TPL=$100; QUD=$75
1Oct2012 tour - SGL=$500; DBL=$250; TPL=$166.67; QUD=$125

user is wanting to book - 1 SGL, 2 DBL, + 1 TPL for 8 people for the 1May2012 tour.

So, resulting confirmation display should resemble this (along with the usual HTML: 'Thank you, you're the best customer..blah, blah' which is already good to go):

Person 1) SGL $300
Person 2) DBL $150
Person 3) DBL $150
Person 4) DBL $150
Person 5) DBL $150
Person 6) TPL $100
Person 7) TPL $100
Person 8) TPL $100

Total price = 1x$300 + 4x$150 + 3x$100 + 0x$75 = $1200

I've gotten the html forms setup for the system as well as the insert query sections and the data is posting to the database. For the calculation query (for the booking confirmation page), I've gotten as far as:

$selectedDate = $tourstart
$query = "SELECT `sgl`, `dbl`, `tpl`, `qud` FROM tourprices WHERE `tour_id` = $tour AND `tourstart` = $tourstart";
$query .= "COUNT `sgl` as SglRmCount FROM `clients`";
$query .= "COUNT `dbl` as DblRmCount FROM `clients`";
$query .= "COUNT `tpl` as TplRmCount FROM `clients`";
$query .= "COUNT `qud` as QudRmCount FROM `clients`";
$result = $pdo->query($query);
?>

a) I'm not sure if this is the most consice of queries.
b) is my SELECT query correct as far as can be determined?
c) how do I make introduce the math into the query (or is this done outside the query? if so, how do I codify?).

thank you in advance.

The query you have created will not work. Count is not being used correctly. Count is meant to return a number out of the select field as followed:

SELECT COUNT(`sgl`) FROM your_table WHERE your_value = 0;

Which will return a single number that is the summation the number of records returned. What follows is the query you have created:

SELECT `sgl`, `dbl`, `tpl`, `qud` FROM tourprices WHERE `tour_id` = INPUTED_VALUE_FROM_PHP AND `tourstart` = YOUR_tourstart_VALUE COUNT `sgl` as SglRmCount FROM `clients` COUNT `dbl` as DblRmCount FROM `clients` COUNT `tpl` as TplRmCount FROM `clients`;

This query will not work because first count is not used correctly and secondly because you can only call one FROM is a given query. It looks like you wish to return multiple unrelated values. If that is the case then you can just create multiple SELECT queries for everything you need.

Also math in line in a query is totally possible, you can do just about anything inline. Here is a resource for some of the basic math that you can do: http://www.keithjbrown.co.uk/vworks/mysql/mysql_p9.php