MYSQL output multiple rows with just a single row in mysql database

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.

enter image description here

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?

why do you want to do so ? you can handle the result set by programming language . — Raptor
– Raptor, Commented Jun 13, 2013 at 3:13
Well... that what I'm trying to do, seems my question is vague enough. I'm doing it on php thou. =/ — Brian Coolidge
– Brian Coolidge, Commented Jun 13, 2013 at 3:17
@WesleyLachenal I am rather confused, you want to list how many points you got in each column per day or just that day X there was a point on a given column ? Give this a try SELECT date_participated, SUM(fblike_point) AS fblike_point, SUM(fbshare_point) AS fbshare_point, SUM(tweet_point) AS tweet_point, SUM(follow_point) AS follow_point FROM tickets GROUP BY date_participated with this query you will get all interaction a given user had per day and you can easily make a echo $row['date_participated'] . '|' . $row['fblike_point'] . "\n"; for each of it. — Prix
– Prix, Commented Jun 13, 2013 at 4:57
Just a small fix you will need to change on the above query GROUP BY date_participated as I forgot you have time on your field so you need to add GROUP BY DATE(date_participated) so it group's by the dates only — Prix
– Prix, Commented Jun 13, 2013 at 5:08

MvG · Accepted Answer · 2013-06-13 08:22:11Z

2

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.

answered Jun 13, 2013 at 8:22

MvG

61.8k25 gold badges158 silver badges294 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL output multiple rows with just a single row in mysql database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related