Page 1 of 1

How to use set type in mysql

Posted: Fri Dec 07, 2007 4:19 am
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

I think you need more tables

Posted: Fri Dec 07, 2007 6:36 am
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

Posted: Fri Dec 07, 2007 6:38 am
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 :(

:)

Posted: Fri Dec 07, 2007 7:09 am
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.

Posted: Fri Dec 07, 2007 7:47 am
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.

Posted: Fri Dec 07, 2007 6:16 pm
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.

query will be something like this

Posted: Fri Dec 07, 2007 6:51 pm
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