vb.org Archive
Page 1 of 2 1 2
Show 40 post(s) from this thread on one page

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   specific mySQL query question (https://vborg.vbsupport.ru/showthread.php?t=217901)

zeroality 07-05-2009 09:09 PM
specific mySQL query question
 
I am trying to get latest posts to display on a non vB page and I think I am close to having it work.

Code:
$lpost = $db->query_first('SELECT b.title,a.threadid,a.username,a.userid,a.dateline FROM ' . TABLE_PREFIX . 'post a,thread b WHERE a.threadid = b.threadid AND b.forumid NOT IN (31) ORDER BY a.dateline DESC LIMIT 0 , 5');

echo "test<br>";
echo "$lpost";
It returns 'Array' where the latest posts should show up.

http://www.pkmndex.com/posts

Lynne 07-05-2009 10:57 PM
It returns 'array' because it is an array. The variables would be $lpost['title'] and $lpost['threadid'] etc. And usually you would not use echo.

zeroality 07-07-2009 12:47 AM
Ah, great! That worked. Now I have another problem. It pulls the latest posts, even if they are in the same thread. So say the latest 4 forum posts are in the same thread, it pulls that - repeating the threads. How do I make it pull different threads that the latest posts have been in without repeating the threads? I hope that's clear enough for you to understand.

Here's the code:

Code:
$lpost = $db->query_first('SELECT b.title,a.threadid,a.username,a.userid,a.dateline FROM ' . TABLE_PREFIX . 'post a,thread b WHERE a.threadid = b.threadid AND b.forumid NOT IN (31) ORDER BY a.dateline DESC LIMIT 0 , 1');
$lpost_title = "<a href=\"forums/showthread.php?t=$lpost[threadid]\">$lpost[title]</a>";

$lpost2 = $db->query_first('SELECT b.title,a.threadid,a.username,a.userid,a.dateline FROM ' . TABLE_PREFIX . 'post a,thread b WHERE a.threadid = b.threadid AND b.forumid NOT IN (31) ORDER BY a.dateline DESC LIMIT 1 , 1');
$lpost2_title = "<a href=\"forums/showthread.php?t=$lpost2[threadid]\">$lpost2[title]</a>";

$lpost3 = $db->query_first('SELECT b.title,a.threadid,a.username,a.userid,a.dateline FROM ' . TABLE_PREFIX . 'post a,thread b WHERE a.threadid = b.threadid AND b.forumid NOT IN (31) ORDER BY a.dateline DESC LIMIT 2 , 1');
$lpost3_title = "<a href=\"forums/showthread.php?t=$lpost3[threadid]\">$lpost3[title]</a>";

$lpost4 = $db->query_first('SELECT b.title,a.threadid,a.username,a.userid,a.dateline FROM ' . TABLE_PREFIX . 'post a,thread b WHERE a.threadid = b.threadid AND b.forumid NOT IN (31) ORDER BY a.dateline DESC LIMIT 3 , 1');
$lpost4_title = "<a href=\"forums/showthread.php?t=$lpost4[threadid]\">$lpost4[title]</a>";

$lpost5 = $db->query_first('SELECT b.title,a.threadid,a.username,a.userid,a.dateline FROM ' . TABLE_PREFIX . 'post a,thread b WHERE a.threadid = b.threadid AND b.forumid NOT IN (31) ORDER BY a.dateline DESC LIMIT 4 , 1');
$lpost5_title = "<a href=\"forums/showthread.php?t=$lpost5[threadid]\">$lpost5[title]</a>";

echo "$lpost_title<br>";
echo "$lpost2_title<br>";
echo "$lpost3_title<br>";
echo "$lpost4_title<br>";
echo "$lpost5_title";
http://www.pkmndex.com/posts

Dismounted 07-07-2009 04:59 AM
Too many queries there...
PHP Code:
$lastpost_titles = array();
$lastpostdata = $db->query_read("
    SELECT thread.threadid, thread.title
    FROM " . TABLE_PREFIX . "thread AS thread
    LEFT JOIN " . TABLE_PREFIX . "post AS post ON(thread.lastpostid = post.postid)
    WHERE thread.forumid <> 31
    ORDER BY thread.lastpost DESC
    LIMIT 4
");

while ($post = $db->fetch_array($lastpostdata))
{
    $lastpost_titles[] = '<a href="forums/showthread.php?t=' . $post['threadid'] . '">' . $post['title'] . '</a>';
}

echo implode('<br />', $lastpost_titles); 

zeroality 07-07-2009 06:49 PM
OK, that worked great. How do I include more forums that I don't want showing up in the latest posts? I'm assuming that WHERE thread.forumid <> 31 will only work if it's the one forum? <> being less/greater than? So if it were WHERE thread.forumid <> 31,32, it wouldn't work, right?

Thanks for the excellent code, this should be the last thing I need.

Lynne 07-07-2009 06:54 PM
I'm pretty sure it would be:
PHP Code:
WHERE thread.forumid NOT IN ('31','32') 

zeroality 07-08-2009 01:31 AM
Yup that works. Thanks so much guys. :D

el diablo 07-28-2009 06:49 PM
hey guys, I know this isn't my thread, but I actually wanted to do this same thing on my site, but can't seem to pull it off without warnings. And, when I take out the warnings, it's still not pulling any data out, which was pretty obvious after reading the warnings ;-)

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/****/public_html/i/include/functions.php on line 53

I'm using the same code as above:

PHP Code:
$lastpost_titles = array();
$lastpostdata = $db->query_read("
    SELECT thread.threadid, thread.title
    FROM " . TABLE_PREFIX . "thread AS thread
    LEFT JOIN " . TABLE_PREFIX . "post AS post ON(thread.lastpostid = post.postid)
    WHERE thread.forumid <> 31
    ORDER BY thread.lastpost DESC
    LIMIT 4
");

while ($post = $db->fetch_array($lastpostdata))
{
    $lastpost_titles[] = '<a href="forums/showthread.php?t=' . $post['threadid'] . '">' . $post['title'] . '</a>';
}

echo implode('<br />', $lastpost_titles); 
I've tried a million different things, but still get the same warnings/errors.

Basically, I'm trying to pull the info from the forums onto the front page of my site, which is a static php page.

Any help would be much appreciated!

Lynne 07-28-2009 07:35 PM
The code posted was to be part of another page they made. What do you have in the rest of the page? And you really want them pulled from the same forumid?

el diablo 07-28-2009 08:22 PM
Quote:
Originally Posted by Lynne (Post 1857383)
The code posted was to be part of another page they made. What do you have in the rest of the page? And you really want them pulled from the same forumid?
Hey Lynne,

Thanks for the reply.

Well, I actually changed that part of the code to:

PHP Code:
        WHERE thread.forumid NOT IN ('3') 
I should have updated that part.

As for the rest of the page, it's all HTML, except for the following:

PHP Code:
<?php require_once('/home/******/public_html/i/include/functions.php');
require_once('/home/******/public_html/i/forums/includes/functions_misc.php'); 
require_once('/home/******/public_html/i/forums/includes/config.php');
?>
I was trying to create it as it's own function within functions.php (my own file), but then added a new function to functions_misc.php ... either way, no dice.

Maybe I'm overlooking something super simple? Not used to vBulletin these days, so who knows...

Thanks for the reply!


All times are GMT. The time now is 01:18 AM.
Page 1 of 2 1 2
Show 40 post(s) from this thread on one page

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01180 seconds
  • Memory Usage 1,758KB
  • 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
  • (2)bbcode_code_printable
  • (5)bbcode_php_printable
  • (1)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (1)pagenav_pagelink
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (10)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
  • pagenav_page
  • pagenav_complete
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete