mysql - How can I modify table structure from few columns to one column? -

mysql - How can I modify table structure from few columns to one column? -

i seek explain need... have simple table data:

id | post_id | color | year --------------------------- 1 | 1 | reddish | 1989 2 | 1 | reddish | 1990 3 | 1 | greenish | 1991

the next query work fine:

select * `data` color = 'red' , year = 1989

and homecoming result:

id | post_id | color | year --------------------------- 1 | 1 | reddish | 1989

i need escape columns color , year, because must dynamic.

i seek this:

id | post_id | property ----------------------- 1 | 1 | reddish 2 | 1 | greenish 3 | 1 | 1989 4 | 1 | 1990 5 | 1 | 1991

but there problem, because when execute next query:

select * `data` property = 'red' , property = 1989

i can't post color = 'red' , year = 1989, mysql homecoming empty result.

how can modify table escape columns color , year , leave query logic of first example?

keep first query homecoming 2 columns using union results:

select post_id, color property `data` color = 'red' , year = 1989 union select post_id, year property `data` color = 'red' , year = 1989

though can't imagine using myself. much prefer separate columns; cant envision business need or technical need homecoming list describing. presentation should controlled @ display level, if need 1 column, iterate though columns , render differently.

---edit based on comment--

ah custom fields: doesn't seem question on design, have covered.. there several ways this. question seems this:

when user defines 2 different values filter by, how create sure each info object has 2 elements?

using having clause , or statements:

select post_id, property info property = 'red' or property = '1989' grouping post_id, property having count(post_id) = 2

you know how many properties beingness looked based on number selected. utilize attribute in having clause.

mysql sql