Get data from two tables using one MySQL query?

Question

I'm building a social networking page and want to get all the user's activity on a feed page.

I have a comments table:

user_id    comment                date
1          "Hi...."               24 March 2013
2          "Hello..."             25 March 2013
1          "Another comment..."   26 March 2013

And a likes table:

user_id    id_of_liked_item     date
1          101                  24 March 2013 
1          145                  25 March 2013

Is it possible to perform a single query to get these into an array of all a user's activity, ordered by date? If so what would that query look lik? My array would need to look something like this:

$activity = array(
    array(
        "type"=>"comment",
        "content"=>"Comment goes here"
    ),
    array(
        "type"=>"like",
        "item_id"=>345,
    )
)

I'm quite good at manipulating php data, so I probably don't need much help organising the array, but my question is mainly how to get the data from the database in the first place. Thanks

Take a look at SQL JOIN

Daanvn
– Daanvn

2013-03-26 15:34:32 +00:00
Commented Mar 26, 2013 at 15:34 — Daanvn
– Daanvn, Commented Mar 26, 2013 at 15:34

Ed Gibbs · Accepted Answer · 2013-03-26 15:39:34Z

1

Yes, a UNION is the way to go:

SELECT 'comment' AS Type, Comment AS Content, NULL as Item_ID, Date
  FROM Comments
  WHERE User_Id = 1
UNION ALL SELECT 'like', NULL, id_of_liked_item, Date
  FROM Likes
  WHERE User_Id = 1

That will give you a result set that looks like this:

Type    Content            Item_ID Date
------- ------------------ ------- -------------
comment Hi....             null    24 March 2013
comment Another comment... null    26 March 2013
like    null               101     24 March 2013
like    null               145     25 March 2013

And you can take it from there.

answered Mar 26, 2013 at 15:39

Ed Gibbs

26.4k4 gold badges50 silver badges75 bronze badges

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

Comments

Explosion Pills · Accepted Answer · 2013-03-26 15:35:11Z

1

Simplest way would probably be to use a union:

SELECT
    'comment' AS type,
    comment AS content,
FROM comments
UNION
SELECT
    'like' AS type,
    id_of_liked_item AS item_id

You can of course also add WHERE conditions and additional columns to each query in the union for organization by user.

answered Mar 26, 2013 at 15:35

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

Comments

KalenGi · Accepted Answer · 2013-03-26 15:43:41Z

1

Try using a UNION:

SELECT * FROM 
(SELECT `user_id`, `comment` AS activity, `date` FROM comments

UNION

SELECT `user_id`, `id_of_liked_item` AS activity, `date` FROM likes) 

AS user_activity

ORDER BY user_activity.`date`

answered Mar 26, 2013 at 15:43

KalenGi

2,0694 gold badges27 silver badges43 bronze badges

Collectives™ on Stack Overflow

Get data from two tables using one MySQL query?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related