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.

Post New Thread Reply

Register GFY Rules Calendar
Go Back   GoFuckYourself.com - Adult Webmaster Forum > >
Discuss what's fucking going on, and which programs are best and worst. One-time "program" announcements from "established" webmasters are allowed.

 
Thread Tools
Old 03-12-2011, 07:37 AM   #1
eMonk
Confirmed User
 
Industry Role:
Join Date: Aug 2003
Location: Canada
Posts: 2,310
MYSQL: Multiple values in column

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?
eMonk is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 03-12-2011, 07:41 AM   #2
k0nr4d
Confirmed User
 
k0nr4d's Avatar
 
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)
k0nr4d is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 03-12-2011, 07:43 AM   #3
k0nr4d
Confirmed User
 
k0nr4d's Avatar
 
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"
k0nr4d is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 03-12-2011, 07:55 AM   #4
eMonk
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?

Last edited by eMonk; 03-12-2011 at 08:02 AM..
eMonk is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 03-12-2011, 08:07 AM   #5
woj
<&(©¿©)&>
 
woj's Avatar
 
Industry Role:
Join Date: Jul 2002
Location: Chicago
Posts: 47,882
Quote:
Originally Posted by eMonk View Post
How would you have, for example, Monica displayed in Toronto, Chicago, New York and London under the models_cities table?
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
woj is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 03-12-2011, 08:09 AM   #6
k0nr4d
Confirmed User
 
k0nr4d's Avatar
 
Industry Role:
Join Date: Aug 2006
Location: Poland
Posts: 9,228
Quote:
Originally Posted by eMonk View Post
How would you have, for example, Monica displayed in Toronto, Chicago, New York and London under the models_cities table?
You would insert multiple models_cities entries per model.

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.
k0nr4d is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 03-12-2011, 08:31 AM   #7
redwhiteandblue
Bollocks
 
redwhiteandblue's Avatar
 
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
redwhiteandblue is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 03-12-2011, 09:30 AM   #8
eMonk
Confirmed User
 
Industry Role:
Join Date: Aug 2003
Location: Canada
Posts: 2,310
Quote:
Originally Posted by k0nr4d View Post
You would insert multiple models_cities entries per model.

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.
Ah thanks bro. I'll try this tonight or tomorrow. I was thinking along the lines of:

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!

Last edited by eMonk; 03-12-2011 at 09:31 AM..
eMonk is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 03-12-2011, 11:41 AM   #9
KillerK
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.
KillerK is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 03-12-2011, 12:24 PM   #10
mafia_man
Confirmed User
 
mafia_man's Avatar
 
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.
mafia_man is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 03-12-2011, 12:48 PM   #11
bbobby86
partners.sexier.com
 
bbobby86's Avatar
 
Industry Role:
Join Date: Jan 2007
Location: San Francisco, CA
Posts: 11,926
Quote:
Originally Posted by redwhiteandblue View Post
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
also can try inner join...
__________________

bbobby86 is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 03-12-2011, 01:55 PM   #12
Davy
Confirmed User
 
Davy's Avatar
 
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
Davy is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 03-12-2011, 02:01 PM   #13
redwhiteandblue
Bollocks
 
redwhiteandblue's Avatar
 
Industry Role:
Join Date: Jun 2007
Location: Bollocks
Posts: 2,792
Quote:
Originally Posted by Davy View Post
Sounds like a typical N:M relationship. That is best modelled with another database table that connects the two other tables.
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.
redwhiteandblue is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 03-12-2011, 02:30 PM   #14
Tempest
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)
Tempest is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 03-12-2011, 03:35 PM   #15
borked
Totally Borked
 
borked's Avatar
 
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
borked is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Old 03-12-2011, 05:07 PM   #16
Tempest
Too lazy to set a custom title
 
Industry Role:
Join Date: May 2004
Location: West Coast, Canada.
Posts: 10,217
Quote:
Originally Posted by borked View Post
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
That's interesting as I've read enough about table naming conventions and design to know that most of the more hardcore database designers will use singular names.. i.e. model, city and not models, cities... Mostly since the tables are a collection of singular elements and it's the elements that the table contains that you're interested in.

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.
Tempest is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote
Post New Thread Reply
Go Back   GoFuckYourself.com - Adult Webmaster Forum > >

Bookmarks



Advertising inquiries - marketing at gfy dot com

Contact Admin - Advertise - GFY Rules - Top

©2000-, AI Media Network Inc



Powered by vBulletin
Copyright © 2000- Jelsoft Enterprises Limited.