specific mySQL query question

[zeroality]

I am trying to get latest posts to display on a non vB page and I think I am close to having it work.

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

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]

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:

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

Too many queries there...
$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]

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]

I'm pretty sure it would be:
WHERE thread.forumid NOT IN ('31','32')

[zeroality]

Yup that works. Thanks so much guys. :D

[el diablo]

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:


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

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]

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:

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 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!

[Lynne]

If you are going to write your own vbulletin powered page, you really should follow this tutorial - [How-To] vBulletin API Basics: Creating Custom Pages & Misc. (https://vborg.vbsupport.ru/showthread.php?t=98009) If it's just gonna be a function, then you really don't need to include other pages in there at all and you really should put all the output into a variable, not echo it. Echoing it can produce some strange results.