PHP: SELECT ing 2 tables?

Question

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?

OMG Ponies · Accepted Answer · 2010-09-19 17:59:08Z

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.

edited Sep 19, 2010 at 17:59

answered Sep 19, 2010 at 17:14

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

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

13 Comments

Karem Over a year ago

When im using this in a while($show = mysql_fetch_array($query)) im receiving: mysql_fetch_array() expects parameter 1 to be resource, boolean given in. This is because ORDER BY ua.date , how can i solve this?

Karem Over a year ago

And while waiting for answer on the ORDER part, i tried echo $show["msg"] and it works well, gets all the user´s status, but when I do $show["message"], show anything.

Karem Over a year ago

just noticed when i do $show["msg"] it shows BOTH msg and messages like I wanted

OMG Ponies Over a year ago

@Karem: Yes, the column reference is based on the first of the UNION'd queries. Sorry, mentioned everything but that.

Karem Over a year ago

Ok, last thing i think, im trying to sort out WHERE uID = '$USER', but then i dont get any results. How is this wrong?

|

Galen · Accepted Answer · 2010-09-19 17:14:40Z

0

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

answered Sep 19, 2010 at 17:14

Galen

30.2k9 gold badges75 silver badges90 bronze badges

Comments

RobertPitt · Accepted Answer · 2010-09-19 17:15:47Z

0

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

answered Sep 19, 2010 at 17:15

RobertPitt

57.3k21 gold badges117 silver badges161 bronze badges

1 Comment

Extrakun Over a year ago

You may have misread the question. There is no relationship between activities and statuses, as far as I can tell.

Collectives™ on Stack Overflow

PHP: SELECT ing 2 tables?

3 Answers 3

UNION

13 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

UNION

13 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related