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
How to use set type in mysql
Moderator: General Moderators
I think you need more tables
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
zones
zone_id | zone_name
citys
city_id | city_name
city_zones
city_id|zone_id
person
person_id |person_name | zone_id
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.
Edit yacahuma posted faster than me
Second if you still want to it this way make zones a text column then just use a simple
Code: Select all
mysqli_queryEdit yacahuma posted faster than me
:)
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.
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.
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.
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.
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
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
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