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]

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

[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 ;).

[shunga]

The number in the link to threads is the ID ;).

I realised my mistake after I posted.

I need to do a second query to get the thread text, but without knowing the names of everything I'm struggling. I've searched through Google and through the owners area without any luck in the search for documentation.

Any online source for the MySQL table layout?

--------------- Added 1213317483 at 1213317483 ---------------

As always, as soon as you ask for something, you find it. ;)

I have it all working fine now. Thanks for all your help. :)

[marin]

Hey
It would be great if you post the code here. I've been looking for this for a long time!
Thanks, Marin

[shunga]

The code is below. I'm sure there are much more elegant methods, but it works:


$result = mysql_query("SELECT postid, username, userid, dateline, pagetext, threadid
FROM post
WHERE visible = 1
ORDER BY dateline DESC
LIMIT 10")
or die('I cannot select from the database because: ' . mysql_error());

while($rows = mysql_fetch_assoc($result)) {
$result_thread = mysql_query("SELECT title,forumid FROM thread WHERE threadid='" . $rows['threadid'] . "'")
or die('I cannot select from the database because: ' . mysql_error());
$rows_thread = mysql_fetch_assoc($result_thread);

$result_forum = mysql_query("SELECT title FROM forum WHERE forumid='" . $rows_thread['forumid'] . "'")
or die('I cannot select from the database because: ' . mysql_error());
$rows_forum = mysql_fetch_assoc($result_forum);

// basic variables required to output latest posts
print $rows_thread['forumid'], $rows_forum['title'], $rows['threadid'], $rows_thread['title'], $rows['pagetext'], $rows['username'], $rows['dateline'];
}


I hope that's helpful.