MySQL, PHP and new posts?

[shunga]

Apologies if this has been explained previously, but I couldn't find an answer, though I did find some code to get me started. I need to pull new posts from the database and display the data on an external PHP page. This is the query I have to start out with:

SELECT postid, username, pagetext FROM post

That at least gets basic date, but I need the latest posts, with post text, date and time, from what forum, and the poster, sorted by date. I also need the link to the post.

Thanks in advance.

[Dismounted]

[sql]SELECT p.postid, p.username, p.userid, p.dateline, p.pagetext, t.forumid
FROM " . TABLE_PREFIX . "post AS p
LEFT JOIN " . TABLE_PREFIX . "thread AS t USING threadid
WHERE visible = 1
ORDER BY dateline DESC
LIMIT 10[/sql]

[Marco van Herwaarden]

Please be aware that the above query does not take permissions into consideration. You might get posts from forums that are not accessible to general public.

Maybe it is easier to use RSS for this.

[Boofo]

Adding this would take care of that, wouldn't it?

AND (forum.options & 4096)

[Marco van Herwaarden]

Not sure which forum option is 4096, but that would not take any usergroup/forum permissions into account.

[Boofo]

I looked in the bitfield file and this is what it says:

 
<bitfield name="countposts">4096</bitfield>

At least now I know what it is for.

[shunga]

Thanks for the code. I think I'm using a version of MySQL that doesn't support some of it. This is what I could get to work:

SELECT p.postid, p.username, p.userid, p.dateline, p.pagetext
FROM post AS p
WHERE visible = 1
ORDER BY dateline DESC
LIMIT 10

I also need links to the thread and the post, as well as the date and time.

[Dismounted]

The ID is the link to the thread/post. The dateline is the date/time.

[shunga]

I thought postid just gave a number, not a HTML link? I assume if I can get the threadid I can use that to do another query to get the thread title?

[Dismounted]

The number in the link to threads is the ID .