Two Tables - Select item based on information in both tables?

[loren646]

Problem: I want it to only display the vacancy if it's in the location specified. Table "hotel" knows the "location" but table "vacancy" does not (however it does have the same exact hotel name).

 

i have this right now (which only works on table vacancy):

 

"SELECT * FROM vacancy WHERE price > $minprice AND price < $maxprice"

 

---------------------------below are the mysql table structure and user supplied information--------------------------

 

Prospective Hotel Guest supplies:

Location

Max Price/Min Price

Minimum Price

Quality of Hotel

 

table hotel

primary column1 = hotel name

column 2 = hotel owner

column 3 = location of hotel

column 4 = quality of hotel

 

table vacancy

(not primary) column 1 = hotel name

column 2 = room #

column 3 = price

Edited February 23, 2013 by loren646

[Barand]

You need to join the tables

 

http://www.phpfreaks...ta-joins-unions

 

SELECT hotelname, room, price
FROM vacancy as v
INNER JOIN hotel as h
ON v.hotelname = h.hotelname
WHERE v.price BETWEEN $minprice AND $maxprice
AND h.location = '$location'

 

The hotel table should be the only place in your database that holds the hotel name. The hotels should have a unique id column and it is that id that should be in the vacancy table to link them

Edited February 23, 2013 by Barand

[loren646]

You need to join the tables

 

http://www.phpfreaks...ta-joins-unions

 

SELECT hotelname, room, price
FROM vacancy as v
INNER JOIN hotel as h
ON v.hotelname = h.hotelname
WHERE v.price BETWEEN $minprice AND $maxprice
AND h.location = '$location'

 

The hotel table should be the only place in your database that holds the hotel name. The hotels should have a unique id column and it is that id that should be in the vacancy table to link them

 

Hotel name is the unique column. it never repeats so i set it as primary. And I thought to use that to link both tables. is that right logic?

Edited February 23, 2013 by loren646

[Jessica]

Hotels change their names. You need to use a unique id column like an auto incrementing id

[Christian F.]

Also, there are plenty of hotels that have the same name. Even if they're not related. Just search for "hotel ritz", for example.

[Barand]

Also

• Grand
  
• Imperial
  
• Palace
  
• Savoy
  

All common names

[loren646]

Hotel name is the unique column. it never repeats so i set it as primary. And I thought to use that to link both tables. is that right logic?

 

sorry i wasn't being clear hotel name is pretty much an Identification for the hotel. Not exactly the name. Similar to a building address.

[loren646]

Hotel name is the unique column. it never repeats so i set it as primary. And I thought to use that to link both tables. is that right logic?

 

Received error:

 

Column 'hotelname' in field list is ambiguous

 

So i tried:

 

SELECT v.hotelname, v.room, v.price

 

now it has no errors. but it outputs nothing when run. I tried changing the echo to $row['a.hotelname'] ,$row['v.room'] $row['v.price'] instead of hotelname, room, price

 

still nothing outputs.

 

----

 

it works perfectly fine up until adding this second table.

Edited February 24, 2013 by loren646

[Christian F.]

The alias/table designation is not a part of the name in the result.

Do a var_dump () on the $row variable, to see what it contains.

 

Also, you'll need to turn on error reporting. Ref. Jessica's signature.

Edited February 24, 2013 by Christian F.

[Jessica]

The alias/table designation is not a part of the name in the result.

 

And even if it were, he wrote v.hotelname and then a.hotelname so...

[Christian F.]

Ah, couldn't see it too well due to the minuscule font used before he fixed it. Thanks for letting me know.

[Barand]

Received error:

 

Column 'hotelname' in field list is ambiguous

 

So i tried:

 

SELECT v.hotelname, v.room, v.price

 

That was the correct thing to do, but the results will still be $row['hotelname'], $row['room'] etc

[loren646]

And even if it were, he wrote v.hotelname and then a.hotelname so...

 

sorry for the typos. i'm being slow today.