thefollower Posted August 24, 2007 Share Posted August 24, 2007 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... Quote Link to comment https://forums.phpfreaks.com/topic/66498-solved-query-used-over-two-fields/ Share on other sites More sharing options...
Jessica Posted August 24, 2007 Share Posted August 24, 2007 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; Quote Link to comment https://forums.phpfreaks.com/topic/66498-solved-query-used-over-two-fields/#findComment-332966 Share on other sites More sharing options...
thefollower Posted August 24, 2007 Author Share Posted August 24, 2007 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? Quote Link to comment https://forums.phpfreaks.com/topic/66498-solved-query-used-over-two-fields/#findComment-332968 Share on other sites More sharing options...
Jessica Posted August 24, 2007 Share Posted August 24, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/66498-solved-query-used-over-two-fields/#findComment-332972 Share on other sites More sharing options...
radalin Posted August 24, 2007 Share Posted August 24, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/66498-solved-query-used-over-two-fields/#findComment-332974 Share on other sites More sharing options...
Jessica Posted August 24, 2007 Share Posted August 24, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/66498-solved-query-used-over-two-fields/#findComment-332983 Share on other sites More sharing options...
thefollower Posted August 24, 2007 Author Share Posted August 24, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/66498-solved-query-used-over-two-fields/#findComment-333007 Share on other sites More sharing options...
Jessica Posted August 24, 2007 Share Posted August 24, 2007 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! Quote Link to comment https://forums.phpfreaks.com/topic/66498-solved-query-used-over-two-fields/#findComment-333010 Share on other sites More sharing options...
thefollower Posted August 24, 2007 Author Share Posted August 24, 2007 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? Quote Link to comment https://forums.phpfreaks.com/topic/66498-solved-query-used-over-two-fields/#findComment-333013 Share on other sites More sharing options...
Jessica Posted August 24, 2007 Share Posted August 24, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/66498-solved-query-used-over-two-fields/#findComment-333016 Share on other sites More sharing options...
thefollower Posted August 24, 2007 Author Share Posted August 24, 2007 right gotcha. ill give it a buzz no doubt i'll get it wrong hehe. thanks for the help ! Quote Link to comment https://forums.phpfreaks.com/topic/66498-solved-query-used-over-two-fields/#findComment-333018 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.