[SOLVED] Query used over two fields...

[thefollower]

Is it possible to make a query to find a field using another field of a table without using any user ID.

 

Because basically i have:

 

House table

country Table

 

 

in country table i have "cityID" and in house table i have "cityID" so what i wanted to do was call up a variable as:

 

$Country by using CityID in house table and searching that cityID in country table to find the country name and put it to "$Country"

 

can this be done using a query.. im only used to using queries using sessions of user ID this is the first time it wont use a session...

[Jessica]

This is a bit confusing. It sounds like you could have more than one "House" with the same CityID, so how do you know which CityID from the House table you are selecting?

 

Can you provide a bit more info? It sounds like your table structure might be a bit out of whack. A country should not have a CityID, a city is inside a country and more than one city per country, so your cities should have countryIDs, like how your houses have cityIds.

 

What you've said so far is like this:

 

SELECT countryName FROM country WHERE country.cityID = house.cityID;

 

[thefollower]

ok well ill explain to see what u think.

 

A user can have:

 

Many houses in one city..

The are many Cities in one country

 

The user can have many houses in one country (obviously)

The user can also have many houses in many countries.

 

User can also have none at all.

 

 

County Table has:

Country ID and City ID and extra fields

 

Then City table has:

Country ID and City ID and extra fields

 

User Table has:

City ID and House ID and extra fields

 

House Table Has:

House ID and City ID and extra fields

 

 

would that be the best way?

[Jessica]

Not really.

Why would a country have a city id? You just said the countries can have more than one city, so how many entries are in your country table?

 

I would do it like this:

 

Country Table (just one entry per country.)

CountryID

Name

 

City Table (just one entry per city, but there will be many cities with the same country ID.)

CityID

Name

CountryID <- This is the county it is in.

 

House Table (etc as above with city - one entry per house, many or no houses per user)

HouseID

UserID

CityID <- From this, you can extrapolate the Country ID.

 

User Table

UserID

Any other info, but No House ID - You said they can have more than one house! You can't store just one ID for them if they have more than one. If they can ONLY ever have one house, you can store all the info in the user table, but since they can't each item needs it's own table.

 

Then you write the statements to get their house info.

 

If you know just the userID: (You'll probably have to edit this to fit your variable names!

SELECT house.*, city.*, country.* FROM house, city, country WHERE house.userID = $userID AND city.cityID = house.cityID AND city.countryID = country.countryID.

 

That will give you ALL of the info on every entry for a user.

 

If you just have the house ID it's similar

 

SELECT house.*, city.*, country.* FROM house, city, country WHERE house.houseID = $houseID AND city.cityID = house.cityID AND city.countryID = country.countryID.

[radalin]

Does every house should have an owner? What if it does not have a one?

Maybe it's better to remove userid from houses tables and create a new table like:

 

UserOwnings:

ID

UserID

HouseID

 

by this way you can insert houses which does not have an owner.

[Jessica]

That is a good idea if you will have houses without owners. If a house has to have an owner (hey, even IRL it's the bank, right? ) then this is not needed, but good idea if they can be ownerless! Thanks!

 

Does every house should have an owner? What if it does not have a one?

Maybe it's better to remove userid from houses tables and create a new table like:

 

UserOwnings:

ID

UserID

HouseID

 

by this way you can insert houses which does not have an owner.

[thefollower]

they cant have an owner if a user doesnt own one.. it would mean i would have to physically input like millions of house rows. Id rather let the user buy then it inputs it into the database. Ill sort my tables out and clean it up a bit like you said.

[Jessica]

Well, can a user like, abandon their house? In that case, you'd have to delete it from the DB, or assign the userID as 0. (That would mean you don't need that extra table - any house with UserID 0 has no user. Easy!) Anyway that is a way a house could have no owner.

 

Up to you Good luck!

[thefollower]

they can only sell it to some one else on the game once bought.  i aint got that far yet one step at a time

 

with the user ID thing can it work using my session cos i have userid in a session for the query like this:

 

 

SELECT house.*, city.*, country.* FROM house, city, country WHERE house.houseID = $houseID AND city.cityID = house.cityID AND city.countryID = country.countryID.

 

 

how does that fine the userID's house.. wouldnt that find any house of any userID?

[Jessica]

I don't quite understand your question. The userID is in the session? So do this:

$userID = $_SESSION['userID'];
SELECT house.*, city.*, country.* FROM house, city, country WHERE house.userID = $userID AND city.cityID = house.cityID AND city.countryID = country.countryID.

[thefollower]

right gotcha. ill give it a buzz no doubt i'll get it wrong hehe. thanks for the help !