MYSQL在mysql数据库中只输出一行多行

Question

I have this data that should output to corresponding number of social media that he interacted with.

There's 4 interaction which is fblike_point, fbshare_point, tweet_point, and follow_point

So let's say, I've interacted with fblike_point and tweet_point judging from the data below.

So what I want to do is, it should output 2 times since I've interacted with fblike_point and tweet_point.

Output:

2013-05-14 | fblike_point

2013-05-14 | tweet_point

If I interacted 4 times, it should output 4 times with the corresponding social media interaction that he made.

Well I can manage to do this stuff but, it was like redundancy, for example I'm using a mysql query in PHP for selecting data:

SELECT date_participated, fblike_point FROM table WHERE fblike_point = 1
SELECT date_participated, fbshare_point FROM table WHERE fbshare_point = 1
SELECT date_participated, tweet_point FROM table WHERE tweet_point = 1
SELECT date_participated, follow_point FROM table WHERE follow_point = 1

So is there any other way to have a short method or something?

Answer 1

If I interacted 4 times, it should output 4 times

With your data schema, you'd either need the four distinct queries you quoted, or a UNION over these.

it was like redundancy

This is redundant because the way your schema is organized. If you want to be able to treat these different interactions alike (which makes a lot of sense), then you'd want an extra table for these, with one column identifying the row of your original table that this refers to, and a second column (probably of an ENUM type) identifying the social media. Both together would form the primary key of that table.

You can then create a VIEW from the actual tables which looks just like your table does now. That way you can maintain compatibility to existing queries and still provide more flexible queries for those cases where you need them.