sum of occurances in mysql array

nicky77uk1

This is my DB

Table: room_des
+----+------------+-------------------------------+
| ID | rtype      | Room name                     |
+----+------------+-------------------------------+
| 1  | 1          | Standard Room - 1 Double Bed  |
+----+------------+-------------------------------+
| 2  | 2          | Standard Room - 2 Double Beds |
+----+------------+-------------------------------+

Table: room
+----+------------+--------------+------------+
| ID | room_type  | Rate         | Ref        |
+----+------------+--------------+------------+
|  1 | 1          | 15           | 1163133652 |
+----+------------+--------------+------------+
|  2 | 2          | 20           | 1163133652 |
+----+------------+--------------+------------+
|  3 | 2          | 20           | 1163133652 |
+----+------------+--------------+------------+

i want to know how i can get the info of room_type 2 in one line instead of getting 2 seprate lines...

i want the ouput to be like this

Reservation Details

1 x Standard Room - 1 Double Bed

  Rate for each room: $15


2 x Standard Room - 2 Double Beds

  Rate for each room: $20

do i need some join query or some php to detect that there are 2 room_type 2 enteries where ref=1163133652

please help, am so lost on this! :bemused:

Piranha

That is more or less impossible with that database. The reason is that two rooms with the same amount of beds can have different rate since you set the rate in the room table and not the room_des table.

But let's assume that it will never be any problem with the rate, that it will always be the same rate for different rooms with the same amount of beds. Then the query would look something like this:

$sql = SELECT room_des.id, room_des.r_type, room_des.room_name, room.rate, count(room.id) as no_rooms
FROM room_des, room
WHERE room.des.id = room.room_type
AND room.ref = $ref
GROUP BY room_des.id

It may even be possible to do what you want by changing the GROUP BY, and get the different rates for the same type of room. But I can't promise you that, you have to try and see for yourself.