CODEWITHSUNDEEP
mysqlsql
Victor Bocharsky
Victor Bocharsky

Reputation: 12306

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

I try to explain what I need... I have a simple table data:

id | post_id | color | year
---------------------------
1  | 1       | red   | 1989
2  | 1       | red   | 1990
3  | 1       | green | 1991

The next query work fine:

SELECT * 
FROM  `data` 
WHERE color = 'red'
AND year = 1989

and this return result:

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

I need to escape from columns color and year, because they must be dynamic.

I try to do something like this:

id | post_id | property
-----------------------
1  | 1       | red 
2  | 1       | green
3  | 1       | 1989
4  | 1       | 1990
5  | 1       | 1991

But there is some problem, because when I execute next query:

SELECT * 
FROM  `data` 
WHERE property = 'red'
AND property = 1989

I can't get post with color = 'red' and year = 1989, mysql return the empty result.

How can I modify my table for escape from columns color and year and leave query logic of first example?

Upvotes: 0

Views: 49

Answers (1)

xQbert
xQbert

Reputation: 35333

Keep your first query but only return two columns by using a union for the results:

SELECT POST_ID, color as property 
FROM  `data` 
WHERE color = 'red'
AND year = 1989
UNION ALL
SELECT Post_ID, Year as Property 
FROM  `data` 
WHERE color = 'red'
AND year = 1989

Though I can't imagine using this myself. I much prefer the separate columns; I cant envision a business need to or a technical need to return the list as your describing. the presentation should be controlled at the display level, if you need one column, iterate though the columns and render it differently.

---EDIT BASED ON COMMENT--

Ah custom fields: So it doesn't seem like your question is on design, you have that covered.. There are several ways to do this. your question seems to be this:

When a user defines 2 different values to filter by, how to you make sure each data object has all two elements?

using a having clause and or statements:

Select post_ID, property
from data
Where property = 'red'
or property = '1989'
group by post_Id, property
having count(post_ID) = 2

You know how many properties are being looked for based on the number selected. so just use this as an attribute in your having clause.

Upvotes: 1

Related Questions