How to use set type in mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
the_power
Forum Newbie
Posts: 10
Joined: Tue Dec 04, 2007 2:34 pm

How to use set type in mysql

Post by the_power »

I have column `zones` with type set( '1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20' ) in mysql.
example table:
| id | city | zones |
| 1 | London | 1,3,5 |
| 2 |New York| 2,4,6 |

Another table:
| id | Person | zone |
| 1 | Name | 1 |
| 2 | Name 1 | 2 |
| 2 | Name 2 | 5 |
| 2 | Name 3 | 4 |

How to get everyone values on "example table" and compare everyone values with values on "zone" in "another table"?

I want get this result:
London -> Name and Name2
New York -> Name1 and Name3
User avatar
yacahuma
Forum Regular
Posts: 870
Joined: Sun Jul 01, 2007 7:11 am

I think you need more tables

Post by yacahuma »

I think you need 4 tables and not 2

zones
zone_id | zone_name

citys
city_id | city_name

city_zones
city_id|zone_id

person
person_id |person_name | zone_id
Rovas
Forum Contributor
Posts: 272
Joined: Mon Aug 21, 2006 7:09 am
Location: Romania

Post by Rovas »

First: Your tables are badly designed: if you have multiple values: zones for a single object this case being cities you should make another table name Zones that has a foreign key that references to cities and in the Person table you should make zone as a foreign key.
Second if you still want to it this way make zones a text column then just use a simple

Code: Select all

mysqli_query
.
Edit yacahuma posted faster than me :(
the_power
Forum Newbie
Posts: 10
Joined: Tue Dec 04, 2007 2:34 pm

:)

Post by the_power »

this is example,but
real is thus:
user
id | username| password | office | zones

request
id | office | name | address | zone | currier | shipment |

When i login and want to view request table where:
user(office) = request(office) -> this is easily

but this filter who i want:
user(zones) = request(zone)
but everyone user have one two or more zones who it answerer.

This is real case.
Rovas
Forum Contributor
Posts: 272
Joined: Mon Aug 21, 2006 7:09 am
Location: Romania

Post by Rovas »

If you wanted like that you get a lot of code both php, sql and a slow down of the database. Basically for each person you make the same number of queries as zones he is in.
To do this using only queries I don' t know if is possible.
For php use: mysql commands to interogate the database, array (to retain the outputed data), string (to retain the zones) , http://docs.php.net/manual/en/function.explode.php to get each zone then another set of mysql query and array to get the zones.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

What Rovas and yacahuma are trying to tell you is that your table definitions violate the rules of relational database design and you will just be fighting with query problems forever until you correct it. The first rule of table design is that a column should be "atomic", which means that it can have only one value, not several values, like your "zones" column. To achieve what you want, you need to redesign your schema to use relational tables, as suggested.
User avatar
yacahuma
Forum Regular
Posts: 870
Joined: Sun Jul 01, 2007 7:11 am

query will be something like this

Post by yacahuma »

SELECT
person.personname,
city.cityname
FROM
person
Inner Join city_zones ON person.zoneid = city_zones.zoneid
Inner Join city ON city_zones.cityid = city.cityid
Post Reply