hashref complication

[bazz]

Hi,

The following query get the data I need from the table but, it makes a separate row for each booking. I expect that this is caused by the dates being different for each booking but I am not sure nor, can I work out a solution.

OK so here's the select


my $sth = $bookings_db_connect->prepare ("SELECT
RD.room_name,
RD.room_number,
RB.booking_id,
RB.check_in_date,
RB.check_out_date,
RB.room_status

FROM
tbl_rooms_details RD
left outer join tbl_rooms_booked RB
ON
RD.room_id = RB.room_id
AND business_id = ?
AND check_in_date <= ?
AND check_out_date >= ?
") or die "prepare statement failed: $DBI::errstr\n";





$sth->execute($business_id, $last_date, $first_date);

while ( my $room = $sth->fetchrow_hashref)
{
$room_number = $room->{room_number};
$room_name = $room->{room_name};
$check_in_date = $room->{check_in_date};
$check_out_date = $room->{check_out_date};
$room_status = $room->{room_status};
$booking_id = $room->{booking_id};

print qq(
<tr>
etc.......



the room row is outputted seperately for each different booking. If there was only one booking, the room 11 row, would show only once :
Red numbers = booked, black = vacant.


room 11 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12
room 11 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12


what would you suggest for getting this all into one row? a sub query for the dates or even two queries? what would be more efficient?

bazz

[bazz]

A better explantion is shown in the jpeg attached. room 11 shows twice when it should show once. both bookings for room 11 should be on the same line. I shall ponder this as I sleep (0330 here) over whether a sub query to handle the date part would work out. It seems clear to me that it is the differing dates that are making for separate hash items, so by removing them from the main query, I might remove the problem also.

Any suggestions or pointers would be most welcome.

bazz

[oesxyl]

A better explantion is shown in the jpeg attached. room 11 shows twice when it should show once. both bookings for room 11 should be on the same line. I shall ponder this as I sleep (0330 here) over whether a sub query to handle the date part would work out. It seems clear to me that it is the differing dates that are making for separate hash items, so by removing them from the main query, I might remove the problem also.

Any suggestions or pointers would be most welcome.

bazz
where the duplicate rows come from? since room 11 is same room in both rows seems normal to apear only once.
I don't know the logic of the application so I could be wrong.

regards

[bazz]

I have gone back to my table to see if I manually made an error. The table is like this and the code to get the data is in my first post.

tbl_rooms_booked

| booking_id | guest_id | room_id | room_type_id | check_in_date | check_out_date | number_of_nights | actual_occupancy | room_status |
| 000001 | 000012 | 000014 | 000029 | 2008-06-27 | 2008-07-04 | 7 | 2 | booked |

It seems not to want to let me match up the values with the fileds; sorry

bazz

[oesxyl]

select
RD.room_name, RD.room_number,
RB.booking_id, RB.check_in_date, RB.check_out_date,
RB.room_status, RB.room_id
from tbl_rooms_booked as RB, tbl_rooms_details as RD
where
RD.room_id = RB.room_id and
alias.business_id = ? and
? between RB.check_in_date and RB.check_out_date

in which table is business_id in tbl_rooms_booked or in tbl_rooms_details ?

regards

[bazz]

in which table is business_id in tbl_rooms_booked or in tbl_rooms_details ?

regards

Thanks oesxyl,

I'll try that and read up on 'alias' as I have no idea what that does here.

business_id is in both tables. I need it to be there to distinguish which accommodation the record refers to.

bazz

[oesxyl]

Thanks oesxyl,

I'll try that and read up on 'alias' as I have no idea what that does here.

business_id is in both tables. I need it to be there to distinguish which accommodation the record refers to.

bazz
RD and RB are alias, no need to search for that, :)
because is in both tables you must use the alias of the table RD or RB you want to use.

the query I post is your query and I post it only to make more clear what I want to ask.
I change to between because you need to pass date to the query only once, but this is not important, you can remove this.

if you want to use business_id from both tables you must use two condition:
RD.business_id = ? and RB.business_id = ?

regards

[bazz]

DBD::mysql::st execute failed: Unknown column 'error returned: alias.business_id' in 'where clause' at month_overview.pl line 407.

If I remove the alias part, it gives me only the rooms that are booked but still on separate lines per booking. I need to show all rooms and then loop through the bookings status to determine the output.

I am off now to read the DBI docs in case there is an easier way to achieve this than by using a hash ref.

bazz

[oesxyl]

read my previous post, :)
if you say:
tbl_rooms_booked as RB

that means that you define RB as alias of table tbl_rooms_booked.

so this part of the query must be one of:

RD.business_id = ?



RB.business_id = ?


or

RD.business_id = ? and RB.business_id = ?


probably last one.

regards

[bazz]

RD and RB are alias, no need to search for that, :)


Yep, I knew that but I thought there might have been another feature to them that I wasn't aware of.


the query I post is your query and I post it only to make more clear what I want to ask.
Hmm, that is a different query. after I remove the 'business_id' lines (for testing/learning) it only returns a row if there has been a booking. It doesn't return room data if there is no booking. I need to return the room details all the time. if there is no booking then thats ok, but I still need to show the room. BTW. room_status is used later when I loop through each date of the month and use it to show whether a room is booked or vacant.

I change to between because you need to pass date to the query only once, but this is not important, you can remove this.

I am not checking the values against a date. I need to get them anyway but to show them as a hash normally outputs. The image in my earlier post shows how it should be done. the only thing wrong with it is that room 11 outputs three times, instead of just once. I have removed my other code to see if it were the cause and it wasn't.


if you want to use business_id from both tables you must use two condition:
RD.business_id = ? and RB.business_id = ?

I made an error in my ealrier post. business_id is only in the rooms_details table.


bazz

[bazz]

ok, here is the code as it is now.

It doesn't output a room unless there is a booking for it. This means, it doesn't output a room 12 because it is not mentioned in tbl_rooms_booked. I need to show all rooms regardless of bookings and use the room status to determine how booked/vacant/closed, show.




my $sth = $bookings_db_connect->prepare ("SELECT
RD.room_name,
RD.room_number,
RB.booking_id,
RB.check_in_date,
RB.check_out_date,
RB.room_status,
RB.room_id
from tbl_rooms_booked as RB,
tbl_rooms_details as RD
where
RD.room_id = RB.room_id and
RD.business_id = ?
AND RB.check_in_date <= ?
AND RB.check_out_date >= ?
") or die "prepare statement failed: $DBI::errstr\n";

$sth->execute($business_id, $last_date, $first_date);

bazz

[oesxyl]

AND RB.check_in_date <= ?
AND RB.check_out_date >= ?

this exclude any row from RD that don't match full where condition.

May I ask you to PM your database structure? Is a kind of blind help if I don't know how it is and can't test anything whithout it.

regards