Home  >   Forum Home  >  ExpressionEngine Community Forums  >  "How to"  >  Thread
   
 
SQL query for 10 recent Forum entries
 
sigork
Posted: 17 August 2006 03:53 AM   [ Ignore ]  
Research Assistant
RankRankRank
Total Posts:  978
Joined  06-13-2005

I’m trying to get 10 recent Forum Entries (Topic Entries + Post Entries).

My best is this code:

SELECT exp_forum_topics.body AS t_body, exp_forum_posts.body AS p_body, exp_forum_topics.topic_date AS t_date, exp_forum_posts.post_date AS p_date FROM exp_forum_posts, exp_forum_topics WHERE exp_forum_posts.topic_id = exp_forum_topics.topic_id LIMIT 10


It gives:

Topic_1 | Post_1 | Topic_date | Post_date
Topic_1 | Post_2 | Topic_date | Post_date
Topic_1 | Post_3 | Topic_date | Post_date
Topic_2 | Post_1 | Topic_date | Post_date


But I need smth. like:

Topic_2
Post_3
Post_2
Post_1
Topic_1

How to write this SQL?

Thanks.

 Signature 

tulks.com
Profile
 
 
Sue Crocker
Posted: 17 August 2006 04:09 AM   [ Ignore ]   [ # 1 ]  
Moderator
Avatar
RankRankRankRankRankRankRank
Total Posts:  12273
Joined  04-29-2002

You don’t have an order by in there. How do you want it ordered? Also what doi you have in between your {exp:query and {/exp:query tags?

 Signature 

Quick Reference - EE Trial Options - EE Wiki - Docs for updating a build
Profile
MSG
 
 
sigork
Posted: 17 August 2006 04:16 AM   [ Ignore ]   [ # 2 ]  
Research Assistant
RankRankRank
Total Posts:  978
Joined  06-13-2005
Sue Crocker - 17 August 2006 04:09 AM

You don’t have an order by in there. How do you want it ordered?

10 latest smile
That is the problem to get the latest entries from 2 tables. I don’t know a typical php solution for such cases.

Also what doi you have in between your {exp:query and {/exp:query tags?

{t_body}
(p_body)

I think these fields are main/key to understand the principle.

 Signature 

tulks.com
Profile
 
 
Ronny
Posted: 17 August 2006 07:58 AM   [ Ignore ]   [ # 3 ]  
Lab Assistant
Avatar
RankRank
Total Posts:  242
Joined  05-24-2002

Do you need to show the latest topics on your forum or any other template? If you need it somewehere else than your forum, check this out: http://expressionengine.com/docs/modules/forum/recent_forum_topics.html

 Signature 

You know me better than that, love. I don’t “do” anything. Things just happen.

Profile
 
 
sigork
Posted: 17 August 2006 09:32 AM   [ Ignore ]   [ # 4 ]  
Research Assistant
RankRankRank
Total Posts:  978
Joined  06-13-2005

Thanks but Displaying Recent Forum Topics doesn’t work in this case. I tested this code here

This code shows Topics only. But how about Replies?

I need the latest 10 Messages.

The situation with vBulletin is simpler—it uses 1 table for Messages. But EE uses 2 tables for Messages (1 table for Topic Messages, and 1 table for Reply Messages).

Is there any solution?

 Signature 

tulks.com
Profile
 
 
Ingmar Greil
Posted: 17 August 2006 10:15 AM   [ Ignore ]   [ # 5 ]  
Moderator
Avatar
RankRankRankRankRankRankRank
Total Posts:  15167
Joined  05-15-2004

JOIN the two tables:

SELECT * FROM `exp_forum_posts` INNER JOIN `exp_forum_topics` ON exp_forum_topics.topic_id = exp_forum_posts.topic_id

 Signature 

Everything will be good in the end. If it’s not good, it’s not the end.
Profile
MSG
 
 
sigork
Posted: 17 August 2006 12:21 PM   [ Ignore ]   [ # 6 ]  
Research Assistant
RankRankRank
Total Posts:  978
Joined  06-13-2005

And how to get the Latest Messages list from your query?

Thanks.

 Signature 

tulks.com
Profile
 
 
Ingmar Greil
Posted: 17 August 2006 01:35 PM   [ Ignore ]   [ # 7 ]  
Moderator
Avatar
RankRankRankRankRankRankRank
Total Posts:  15167
Joined  05-15-2004

I’d try “ORDER BY post_date DESC LIMIT 10”, but I have not sufficient data to test.

 Signature 

Everything will be good in the end. If it’s not good, it’s not the end.
Profile
MSG
 
 
sigork
Posted: 17 August 2006 01:58 PM   [ Ignore ]   [ # 8 ]  
Research Assistant
RankRankRank
Total Posts:  978
Joined  06-13-2005

Ingmar, you gave me several very useful solutions, but sorry, in this case you rewrote my JOIN code (my 1st message). You used one of 3 (that I know) JOIN methods. I got 5 of 7 entries of mine, and your code gives the same 5 entries.

Unfortunately, the same question:

How to get 10 Latest Forum Messages?

Thanks.

 Signature 

tulks.com
Profile
 
 
Ronny
Posted: 21 August 2006 03:58 AM   [ Ignore ]   [ # 9 ]  
Lab Assistant
Avatar
RankRank
Total Posts:  242
Joined  05-24-2002

Sigork,

This query comes pretty close to what you want:

<table style="border: 1px solid #c8dce9;">
    {exp:query sql="SELECT
    exp_forum_topics.topic_id as topicid,
    exp_forum_topics.title,
    exp_forum_topics.forum_id,
    exp_forum_topics.topic_date,
    exp_forum_posts.post_id,
    exp_forum_posts.topic_id,
    exp_forum_posts.body,
    exp_forum_posts.post_date
    FROM exp_forum_topics, exp_forum_posts
    WHERE exp_forum_topics.topic_id = exp_forum_posts.topic_id
    ORDER BY exp_forum_posts.post_date DESC
    LIMIT 10"}
    <tr>
        <td colspan="3"><a href="forums/viewthread/{topicid}" title="{title}"><b>{title}</b></a></td>
    </tr>
    <tr>
        <td style="width: 30px;">&nbsp;</td>
        <td>{post_id}</td>
        <td>{body}</td>
    </tr>
    {/exp:query}
</table>

This will show your lates entries, but it doesn’t groep all entries that belong to one topic. So if there are two new posts in one topic you’ll see them on seperate rows. If you want to group those entries as well, you’ll need some php-knowledge to achieve that. Something I don’t have.

 Signature 

You know me better than that, love. I don’t “do” anything. Things just happen.

Profile
 
 
sigork
Posted: 04 March 2007 01:52 PM   [ Ignore ]   [ # 10 ]  
Research Assistant
RankRankRank
Total Posts:  978
Joined  06-13-2005

Thanks a lot for the reply.

But unfortunately that is not a solution. This code shows forum post messages only, without topic messages.

The question was:

I’m trying to get 10 recent Forum Entries (Topic Entries + Post Entries).

That is the reason why the title of this thread is “Forum entries”, not “Forum posts” or “Forum topics”.

I can get “bodies” from vB & SMF forums. They don’t have “topics” and “posts”.

But EE has these 2 categories of posts. The above code takes info from topics & posts, but it shows replies only. It ignores topic posts.

Maybe EE v.2.0 will have tags to do that simpler, but how to solve this task now?

Thanks.

 Signature 

tulks.com
Profile
 
 
tpayton
Posted: 02 July 2008 04:01 PM   [ Ignore ]   [ # 11 ]  
Lab Assistant
Avatar
RankRank
Total Posts:  228
Joined  11-16-2004

sigork - I’m trying to do the same thing. Did you ever get this to work with a SQL Query?

 Signature 

T. Payton
OneCreative | Albuquerque, NM
Profile
 
 
   
 
 
‹‹ Spell Check- compiling pspell support....      Is it possible to pass a variable (username) to a page ? ››
Post Marker Legend
New Topic New posts Hot Topic Hot Topic with new posts New Poll New Poll Moved Topic Moved Topic Sticky Topic Sticky topic
Old Topic No new posts Hot Old Topic Hot Topic with no new posts Old Poll Old Poll Closed Topic Closed Topic Announcement Announcements
Theme
Change Theme
Visitor Statistics
The most visitors ever was 1149, on July 16, 2007 09:33 AM
Total Registered Members: 64535 Total Logged-in Users: 22
Total Topics: 81114 Total Anonymous Users: 18
Total Replies: 436444 Total Guests: 192
Total Posts: 517558    
Members ( View Memberlist )
Newest Members:  marlus,  bluespotmusic,  voland,  spinhirne,  theminio,  kostia,  masterje,  MBoss,  bchaley,  alvonsius
Active Members:    Alicat,  Carlo,  craiguffman,  Crucial,  cschlorman,  Dave_H,  DEA,  hotknife,  JohnD,  Joobs,  JT Thompson,  Lyle Anderson,  MarcusNeto,  pcmeissner,  Richard DeGrandpre,  Robert Aitken,  Sentinel Mike,  spinhirne,  StaleBetty,  stephenslater,  Tammo
Powered By ExpressionEngine
ExpressionEngine Discussion Forum - Version 2.1.1 (20081028)
Script Executed in 0.4832 seconds
Atom Feed   RSS 2.0