Selecting Most Recent From Multiple Tables

[TWTCommish]

Hi,

I have several tables on a site I'm working on - one for articles, one for reviews, and one for tutorials. I'm wondering if I can somehow display the 4 most recent documents - IE: the 4 most recently posted out of any of those 3 tables - it may be 4 tutorials, 2 tutorials an article and a review, or 3 reviews and an article.

I've thought it over quite a bit, but just can't figure it out. Anyone have any advice?

[freddydoesphp]

You are probably going to have to run multiple queries so if you wanted 4 articles

SELECT * from articles ORDER BY datefield DESC LIMIT 4

or 1 Article and three reviews
SELECT * from articles ORDER BY datefield DESC LIMIT 1

SELECT * from reviews ORDER BY datefield DESC LIMIT 3

[TWTCommish]

That just won't work I'm afraid - because I don't post certain types of documents at regular intervals - what I meant was that I may have 4 articles as the 4 most recent, or 3 reviews and a tutorial - I just don't know.

Isn't there some kind of way to store the results in an array and order them that way?

[freddydoesphp]

How about making a glue table to glue them all together that has an id field a date field a cetogry field like atricle or review or tutorial and an id from its corresponding table that way you can grab the fourmost recent and then grab the item details from their respective tables, does that make more sense?

[TWTCommish]

I'd thought of that just recently - at first I figured it would be a problem if I had to edit the title, for example, of the document, since the old version would still be in there, however obviously if I store the ID number, I can lookup the most recent title.

Thanks - I probably would've forgotten.

[freddydoesphp]

Not sure what you mean if you want to edit a record in one of the tables you would simply update the record and then updAte the timestamp/datefield in the glue table taht corresponds to that record.

[aspen]

why not combine it all into one table? This would make your job alot easier.

Just add a column to hold what kind of listing it is, tutorial, article, whatever.

This was is also more scalable since you can easily add new content types.

Chris

[TWTCommish]

Originally posted by aspen
why not combine it all into one table? This would make your job alot easier.

Just add a column to hold what kind of listing it is, tutorial, article, whatever.

This was is also more scalable since you can easily add new content types.

Chris

Unfortunatly I thought of that too late. At this point it wouldn't be worth the work to re-do everything when it's working just fine the way it is now. Shame, isn't it? I'll live, though.