[SOLVED] Table and query issue

[chrispos]

I am using mySQL 5

I have a hotel booking website that is under construction the problem is I need to get the prices for a given month then add the two results together to give a price for the potential customer so he or she can decide to book the room.

 

The first query works fine and this is used to select just the available rooms

$query = "SELECT * FROM `rooms` WHERE hid = 5 AND rid NOT IN (SELECT rid FROM bookings WHERE  `dayofyear1` >= 93 AND `dayofyear` <= 95 AND `month` = 4 AND `year` = 2009 AND `month1` = 4 AND `year1` = 2009 AND `hid` = 5)"; 
$result = mysql_query($query) or die (mysql_error()); 
if (mysql_num_rows($result)>0){ 
while ($row = mysql_fetch_row($result)) { 
$rid=$row[0]; 
$hid=$row[1]; 
$number=$row[2]; 
$description=$row[3]; 
$sleeps=$row[4]; 
$bid=$row[5]; 
$rrid=$row[6]; 
$hhid=$row[7]; 
$d=$row[8]; 
$m=$row[9]; 
$y=$row[10]; 
$dy=$row[11]; 
$d1=$row[12]; 
$m1=$row[13]; 
$y1=$row[14]; 
$dy1=$row[15]; 
} 
} 

 

I have put the $rows in so you can see the table structure and rows.

 

The last problem I have is the pricing. I have set a table up called price which I tried to query with each available room having a price next to it

 

I don't know if I should set a table up for each individual month with a row for $roomid and a row for the year The code I have used with no success is as follows

 

$query = "SELECT * FROM `price`WHERE`month` = 4 AND `year` = 2009"; 
$result = mysql_query($query) or die (mysql_error()); 
if (mysql_num_rows($result)>0){ 
while ($row = mysql_fetch_row($result)) { 
$pid = $row[0]; 
$hid1 = $row[1]; 
$rid1 = $row[2]; 
$number1 = $row[3]; 
$rmonth = $row[4]; 
$ryear = $row[5]; 
$price1 = $row[6]; 
$firstprice = $montha * $price1; 
} 
} 
$query = "SELECT * FROM `price`WHERE`month` = 5 AND `year` = 2009"; 
$result = mysql_query($query) or die (mysql_error()); 
if (mysql_num_rows($result)>0){ 
while ($row = mysql_fetch_row($result)) { 
$pid = $row[0]; 
$hid1 = $row[1]; 
$rid1 = $row[2]; 
$number1 = $row[3]; 
$rmonth = $row[4]; 
$ryear = $row[5]; 
$price2 = $row[6]; 
$firstprice1 = $month2 * $price2; 
$total = $firstprice + $firstprice1; 
echo"$number1\n\n"; 
echo"$price2<br><br>"; 
echo "$firstprice1<br><br>"; 
echo "$total<br><br>"; 
} 
} 

 

The idea is that the final out put should have the room number description number of people the room sleeps and the price for the booking and its the price for the booking that I am having the problem with.

 

If I used individual tables I think I could use sub query in the first sql query ie select * from rooms. Any ideas? would be happy for any help

 

[chrispos]

I am going to mark this one as solved as i am going to use individual tables for each month use a join and sum a total row

 

Thanks for your views or not as the case may be.