![]() |
![]() |
![]() |
||||
Welcome to the GoFuckYourself.com - Adult Webmaster Forum forums. You are currently viewing our boards as a guest which gives you limited access to view most discussions and access our other features. By joining our free community you will have access to post topics, communicate privately with other members (PM), respond to polls, upload content and access many other special features. Registration is fast, simple and absolutely free so please, join our community today! If you have any problems with the registration process or your account login, please contact us. |
![]() ![]() |
|
Discuss what's fucking going on, and which programs are best and worst. One-time "program" announcements from "established" webmasters are allowed. |
|
Thread Tools |
![]() |
#1 |
Confirmed User
Industry Role:
Join Date: Aug 2003
Location: Canada
Posts: 2,310
|
![]() My database and columns:
province (province_id, ..) city (city_id, province_id, ..) model (model_id, age, height_in_cm, hair_color, measurements, eye_color, description, ..) model_in_city (model_id, city_id) Now I want to display all models in the chosen city by the user but each model can chose up to 4 city placements. How can this be done? |
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#2 |
Confirmed User
Industry Role:
Join Date: Aug 2006
Location: Poland
Posts: 9,228
|
Make three tables.
cities (varchar255 city_name, int11 auto incrementing ID) models (with all your model info) models_cities (model int11, city int11)
__________________
Mechanical Bunny Media Mechbunny Tube Script | Mechbunny Webcam Aggregator Script | Custom Web Development |
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#3 |
Confirmed User
Industry Role:
Join Date: Aug 2006
Location: Poland
Posts: 9,228
|
Ahh you were listing tables not columns.
SELECT model.* FROM model WHERE model.model_id = model_in_city.model_id AND model_in_city.city_id = 12345 OR SELECT model.* FROM model WHERE model.model_id = model_in_city.model_id AND model_in_city.city_id = city.city_id AND city.city_name = "Toronto"
__________________
Mechanical Bunny Media Mechbunny Tube Script | Mechbunny Webcam Aggregator Script | Custom Web Development |
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#4 |
Confirmed User
Industry Role:
Join Date: Aug 2003
Location: Canada
Posts: 2,310
|
How would you have, for example, Monica displayed in Toronto, Chicago, New York and London under the models_cities table?
|
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#5 |
<&(©¿©)&>
Industry Role:
Join Date: Jul 2002
Location: Chicago
Posts: 47,882
|
add it 4 times into that table?
__________________
Custom Software Development, email: woj#at#wojfun#.#com to discuss details or skype: wojl2000 or gchat: wojfun or telegram: wojl2000 Affiliate program tools: Hosted Galleries Manager Banner Manager Video Manager ![]() Wordpress Affiliate Plugin Pic/Movie of the Day Fansign Generator Zip Manager |
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#6 | |
Confirmed User
Industry Role:
Join Date: Aug 2006
Location: Poland
Posts: 9,228
|
Quote:
So lets say Toronto = 123 Chicago = 456 New York = 789 INSERT INTO models_cities (model, city) VALUES (modelid,123); INSERT INTO models_cities (model, city) VALUES (modelid,456); INSERT INTO models_cities (model, city) VALUES (modelid,789); SELECT model.* FROM model WHERE model.model_id = model_in_city.model_id AND model_in_city.city_id = city.city_id AND city.city_name = "Toronto" SELECT model.* FROM model WHERE model.model_id = model_in_city.model_id AND model_in_city.city_id = city.city_id AND city.city_name = "Chicago" SELECT model.* FROM model WHERE model.model_id = model_in_city.model_id AND model_in_city.city_id = city.city_id AND city.city_name = "New York" Will then all return the same model Edit: well, insert using your actual table and column names i just wrote those inserts quickly to show you.
__________________
Mechanical Bunny Media Mechbunny Tube Script | Mechbunny Webcam Aggregator Script | Custom Web Development |
|
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#7 |
Bollocks
Industry Role:
Join Date: Jun 2007
Location: Bollocks
Posts: 2,792
|
select * from model, city
left join model_in_city on model.model_id=model_in_city.model_id left join city on city.city_id=model_in_city.city_id
__________________
Interserver unmanaged AMD Ryzen servers from $73.00 |
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#8 | |
Confirmed User
Industry Role:
Join Date: Aug 2003
Location: Canada
Posts: 2,310
|
Quote:
VALUES (modelid, 123, 456, 789, ...); Which you can't do from my understanding (multiple values in column). I searched google and my book for reference but couldn't find anything on this. Thanks again! |
|
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#9 |
Confirmed User
Join Date: May 2008
Posts: 3,406
|
You could have one column called like cities_in or something
and then store it as 14|29|30 then just explode the value from that, however that's not the right way to do it. You need to use multiple tables. |
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#10 |
Confirmed User
Industry Role:
Join Date: Jul 2005
Location: icq#: 639544261
Posts: 1,965
|
k0nr4d is right. Multi-valued attributes are bad.
You need to normalise your data. It will help performance when these tables get huge.
__________________
I'm out. |
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#11 |
partners.sexier.com
Industry Role:
Join Date: Jan 2007
Location: San Francisco, CA
Posts: 11,926
|
also can try inner join...
|
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#12 |
Confirmed User
Industry Role:
Join Date: Apr 2006
Location: Germany
Posts: 4,323
|
Sounds like a typical N:M relationship. That is best modelled with another database table that connects the two other tables.
__________________
--- ICQ 14-76-98 <-- I don't use this at all |
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#13 |
Bollocks
Industry Role:
Join Date: Jun 2007
Location: Bollocks
Posts: 2,792
|
Yes like the table "model_in_city" that he describes in his OP, which you would query with the SQL I wrote, although I would have called the table something like "model_city_rel" to make it obvious what it's for.
__________________
Interserver unmanaged AMD Ryzen servers from $73.00 |
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#14 |
Too lazy to set a custom title
Industry Role:
Join Date: May 2004
Location: West Coast, Canada.
Posts: 10,217
|
What k0nr4d said. And make sure you put an index on model_in_city like KEY idx1 (city_id,model_id)
|
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#15 |
Totally Borked
Industry Role:
Join Date: Feb 2005
Posts: 6,284
|
This is why plural/singular naming of tables is important, but often overlooked. Not important as in "shit won't work" but important in seeing at a glance relationships....
models cities model_city says automatically that the models tables is where all the models are, likewise for the cities, but modelcity is a singular relationship between a model and city. minor detail, but still important... btw, make those tables innodb and add their relationships, so that if you ever delete a model, all her/his entries in model_city disappear automatically also... one of the great benefits of relationships.
__________________
![]() For coding work - hit me up on andy // borkedcoder // com (consider figuring out the email as test #1) All models are wrong, but some are useful. George E.P. Box. p202 |
![]() |
![]() ![]() ![]() ![]() ![]() |
![]() |
#16 | |
Too lazy to set a custom title
Industry Role:
Join Date: May 2004
Location: West Coast, Canada.
Posts: 10,217
|
Quote:
Just a small example of what I'm getting at, SELECT model.name FROM model is more "readable" than SELECT models.name FROM models.. There are also more readability issues for different languages. At the end of the day though it comes down to your personal preferences, what engine, language etc. you're using to develop in. The most important thing is to be consistent. |
|
![]() |
![]() ![]() ![]() ![]() ![]() |