PHP: SELECT ing 2 tables?

Question

Hello. I have a activities page and a statusmessages page for each user.

In activities it contains what the users have done, such as being friends with someone, commented on pictures and so.

users_activities
id | uID | msg | date  

In users_statusmessages, I got the statusmessages, the user creates.

users_statuses
id | uID | message | date

uID is the user´s id.

Now i would like to select both of them and while{} them in one. Where they get sorted by date desc ( as the date in both tables is UNIX timestamp).

So something like WHERE uID = '$userID' ORDER BY date DESC

So example of how i wish it to look:

User: Today is a good day (message) (date: 1284915827) (users_statuses)
User have added as Jack as friend (msg) (date: 1284915811) (users_activities)
User: I have a hard day today (message) (date: 1284915801) (users_statuses)
User have commented on Jacks picture (msg) (date: 1284915776) (users_activities)

How should i do this the right way?

Answer 1

You're going to want to use a union

http://dev.mysql.com/doc/refman/5.0/en/union.html

This is untested...

(SELECT uID, msg as message, date from users_activities)
UNION
(SELECT uId, message, date from users_statuses) order by date desc limit 20

There are a lot more examples on that page

Answer 2

You need to use the UNION operator:

  SELECT ua.msg, 
         ua.date,
         'activity' AS is_table
    FROM USERS_ACTIVITIES ua
   WHERE ua.uid = '{$userID}'
UNION ALL
  SELECT us.message, 
         us.date, 
         'status'
    FROM USERS_STATUSES us
   WHERE us.uid = '{$userID}'
ORDER BY `date`

UNION

UNION removes duplicates. UNION ALL does not remove duplicates, and is faster for it.

But the data types at each position in the SELECT must match. In the query provided, you can see that the date column is referenced in the second position. You'd get an error if the column order were reversed between the first and second query.

The ORDER BY is applied to the result of the UNION'd query in standard SQL. In MySQL, that includes the LIMIT clause. But MySQL also supports putting brackets around the UNION'd queries so you can use ORDER BY & LIMIT before the queries are UNION'd.

Answer 3

Something like this would do

SELECT act.*,status.* FROM users_activities act, users_statuses status WHERE act.id = status.id AND status.id = '$UID' ORDER BY status.date,act.date DESC LIMIT 30

Spaced out for visual purposes:

SELECT
    act.*,status.*
FROM
    users_activities act,
    users_statuses status
WHERE
    act.id = status.id
AND
    status.id = '$UID'
ORDER BY 
    status.date,act.date DESC
LIMIT
    30