vb.org Archive - MySQL, PHP and new posts?

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - MySQL, PHP and new posts? (https://vborg.vbsupport.ru/showthread.php?t=181830)

shunga

06-07-2008 09:45 PM

MySQL, PHP and new posts?

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:

Quote:

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

06-08-2008 04:30 AM

[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

06-08-2008 10:20 AM

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

06-08-2008 10:35 AM

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

Code:

AND (forum.options & 4096)

Marco van Herwaarden

06-08-2008 11:15 AM

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

Boofo

06-08-2008 11:23 AM

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

Code:

 

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

At least now I know what it is for.

shunga

06-09-2008 10:31 PM

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:

Code:

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

06-10-2008 07:18 AM

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

shunga

06-11-2008 11:19 PM

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

06-12-2008 05:47 AM

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

shunga

06-12-2008 10:39 PM

Quote:

Originally Posted by Dismounted (Post 1547322)

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 [DATE]1213317483[/DATE] at [TIME]1213317483[/TIME] ---------------

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

06-20-2008 09:17 AM

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

shunga

06-20-2008 11:58 AM

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

Code:

$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.

All times are GMT. The time now is 02:20 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01067 seconds Memory Usage 1,740KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (4)bbcode_code_printable (2)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (13)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: