Mysql Multiple Queries HELP!

[fou2enve]

Hello,

 

I'm writing a reservation system for a small hotel and I've got a database with all the customers with unique ID's (CID) along with a plethora of other info. Where I'm running into a problem is when displaying who is currently staying at the place. There are 5 floors and each with its own page, so each of the 20 rooms on each floor has a different customer with different details, each floor has a table in a database which says what CID is in the room.

 

I know in mysql I can do a "LEFT JOIN" to put the two tables together, but if I left joined them I would have to do a different query for each room which can get messy, is there a better way to do this??

[per1os]

I do not think you would need to do a different query for each room, just a different query for each floor. I do not know why you put each floor in a seperate table. Unless I am mis-understanding.

 

Either way this might help

 

SELECT room.*, cust.* FROM customers cust LEFT JOIN (room) ON (room.custid = cust.custid) WHERE room.floor = '3' ORDER BY room.roomnum;

 

Something like that should pull the results you want just fine.

[fou2enve]

I do not think you would need to do a different query for each room, just a different query for each floor. I do not know why you put each floor in a seperate table. Unless I am mis-understanding.

 

Either way this might help

 

SELECT room.*, cust.* FROM customers cust LEFT JOIN (room) ON (room.custid = cust.custid) WHERE room.floor = '3' ORDER BY room.roomnum;

 

Something like that should pull the results you want just fine.

 

Okay that will pull all the data for the rooms on the floor, how do I assign PHP variables to each specific room, like $room308.CustName from that, where room 308 is defined in the rooms db and the custname is in the customers db?

 

I know I can do something like this:

 ${"Room".$RoomNum.CustName} 

to get the variable to work out, but how do I get mysql to only output the ones that match? Because if I remember correctly LEFT JOIN will put out all the unmatched data too, the customer database as of right now is at 400 and growing so its alot of data.

[per1os]

<?php
// assuming sql connection was done above this line
$query = mysql_query("SELECT room.*, cust.* FROM customers cust LEFT JOIN (room) ON (room.custid = cust.custid) WHERE room.floor = '3' ORDER BY room.roomnum;") or DIE(mysql_error());

while ($row = mysql_fetch_assoc($query)) {
      $room[$row['room.roomnum']] = $row; // put all information into a room array
}

echo '<pre>', print_r($room),'</pre>';
?>

 

Something like that ???

[fou2enve]

yeah something like that, i'll make it work