Latest Actions in PHP?

Question

If you go to http://www.foxnews.com, and scroll down a little you would see the box "LATEST NEWS".

Now I want to do something like that, but with modification.

I have 3 queries, that get out information from 3 tables. But this doesn't give me the "latest news", because it's 3 separate queries.

Now I want to make a box, (I can do this in css), that displays the latest column of those 3 tables, sorted by dato DESC.

These are the tables:

Tablename: Member_meetups

Column to show: meetup

Table: member_film

Column to show: navn

Table: member_tutorials

Column to show: title

Sort by dato.. All these tables have the column "dato" too, so I want to sort out from dato DESC..

And in that way that would be latest actions from those 3 tables..

FOR EXAMPLE it would show like this: Latest Actions: I love Meetups - 23:11:02 - 01/01/01 (a column in member_meetups) I love tutorials - 23:10:00 - 01/01/01 (a column in member_tutorials) I love tutorials first - 22:59:00 01/01/01 (a column in member_tutorials) I hate movies! - 22:42:00 01/01/01 (a column in member_film)

and so on.. as you see sorting by DATO, shows the last column's title+dato in the 3 tables.

Questions please comment, and please answer with not so hard English, as you may hear, I'm not English myself. Thank you

My question is: How to do this?

Answer 1

You don't actually ask a question from what I can gather.

But I shall try to answer your question as best as I can.

---

You should use mySQL join. (I'm assuming you are using mySQL).

You can then do what you want in a single query. If you provide more information on your DB schema then it will be possible to construct the exact query for you.

Answer 2

You want to use UNION. Here's a sample query:

SELECT meetup AS text, dato FROM Member_meetups UNION
SELECT navn AS text, dato FROM member_film UNION
SELECT title AS text, dato FROM member_tutorials
ORDER BY dato DESC
LIMIT 10;

I only selected the columns you indicated; if you're going to need more columns from each table then alter the query as you need to. Just make sure you line up the column names using the AS function as needed to normalize the data.

You should think about caching this data; this can often be quite a slow query.

Edit to answer question in comments:

It's not entirely clear to me how you want it to work, and it's difficult to write a full query without your DB's schema, but here's the gist:

SELECT m.meetup AS text, m.dato FROM Member_meetups m
INNER JOIN member_battles b
ON m.battle=b.id
WHERE b.fighter1=$pUsername OR b.fighter2=$pUsername UNION
SELECT navn AS text, dato FROM member_film UNION
SELECT title AS text, dato FROM member_tutorials
ORDER BY dato DESC
LIMIT 10;

Again, that's very rough because I don't know your DB's schema or quite understand what you're wanting the query to do. If you need more specific help, I'd suggest either editing your question or starting a new one. It's very difficult to work through these types of things with comments.