Last xx posts, can you correct my code pls.

[FFMG]

Hi,

The code below displays the last 5 posts, but I am not happy with making that many queries.
Would there be a better way of doing it with just one querry?

	global $db;

	$threads = $db->query_read("
		SELECT threadid,title,lastpost,lastposter
		FROM " . TABLE_PREFIX . "thread
		WHERE visible=1 AND open=1 ORDER BY lastpost DESC LIMIT $count
	");


	$res = "";
	if ($num_thread = $db->num_rows($threads))
	{
		$data = false;
		while ($thread_get = $db->fetch_array($threads))
		{
			$lastpost = $thread_get['lastpost'];
			$poster = $thread_get['lastposter'];
			$tid = $thread_get['threadid'];

			$post = $db->query_read(
				"SELECT postid FROM " . TABLE_PREFIX . "post WHERE threadid=$tid ORDER BY postid DESC LIMIT 1"
			);

			if( $num_post = $db->num_rows($threads))
			{
				while( $getp = $db->fetch_array($post) )
				{
					$pid = $getp['postid'];
					$title = $thread_get['title'];
					$title = substr($title,0,100);

					$ref = "<a style=\"font-size:xx-small;\" href=\"showthread.php?p=$pid#post$pid\">$title</a>";

					$data .="
							<tr>
								<td  align=\"left\" width=\"100%\">
								$ref
								</td>
							</tr>";
				}
				$db->free_result($post);
			}
		}

		if( $data !== false )
		{
			$res = "
			<table class=\"tborder\" cellpadding=\"$stylevar[cellpadding]\" cellspacing=\"$stylevar[cellspacing]\" border=\"0\" width=\"100%\" align=\"center\">
			<tr>
				<td  align=\"center\" width=\"100%\" class=\"tcat\" style=\"font-size:x-small;\">Recent Posts<br />
				<a style=\"font-size:xx-small;\" href=\"faq.php?faq=new_faq_item#faq_new_faq_item1\"><u>HTTPpoint is an Ad revenue sharing forum</u></a>
				</td>
			<tr>

			".$data."

			<tr>
				<td  align=\"center\" width=\"100%\" class=\"tcat\" style=\"font-size:x-small;\">
				<a style=\"font-size:xx-small;\" href=\"showthread.php?t=3526\">Discuss this SEO tool</a>
				</td>
			<tr>

			</table>";
		}
	}
	$db->free_result($threads);

	return $res;

Thanks

FFMG

[Xenon]

hmm, that doesn't reveal the last five posts, but the five last threads posted in

you base your lissting on a threadquery, base it on a post query and use an innerjoin, to get rid of the ammount of queries

[FFMG]

Originally Posted by Xenon

hmm, that doesn't reveal the last five posts, but the five last threads posted in

Yes you are right, sorry.

Originally Posted by Xenon

you base your lissting on a threadquery, base it on a post query and use an innerjoin, to get rid of the ammount of queries

Sorry, I am not sure I understand, how would you do that?

FFMG

[Xenon]

something like

[sql]
SELECT *
FROM post
INNER JOIN thread USING(threadid)
ORDER BY post.dateline DESC
LIMIT xx[/sql]

that's just a vague statement, but you get what i mean i think

that query is enough for it alone to get all needed information without running more queries additionaly in a loop

[FFMG]

Originally Posted by Xenon

something like

[sql]
SELECT *
FROM post
INNER JOIN thread USING(threadid)
ORDER BY post.dateline DESC
LIMIT xx[/sql]

That seems to work great.
I just have two more questions,

How would I get only one post per thread? So that even if 2 or more posts are from the same thread, (and are technically one of the last xx posts), only one would be returned.

And would it be faster to do 'SELECT * ...' or 'SELECT threadid, title...'

Many thanks

FFMG

[Xenon]

well, the first one is a bit tricky

the fastest way is to get for example the last 3 times xx post, and then while running through the results, store the threadids in an array and if a new result is there, which has a threadid already cached throw it away...

the second one: it's better to just use the fieldnames you really need, yes

[Marco van Herwaarden]

Actually, since you are look for the latest X threads that had a new post, changing back to using the thread table as primary should be better.

[FFMG]

Originally Posted by MarcoH64

Actually, since you are look for the latest X threads that had a new post, changing back to using the thread table as primary should be better.

Sorry I am not sure I follow, what do you mean?

FFMG

[Xenon]

hmm, not always marco.

he needs some informations of the post itself as far as i understood, and therefore he would have to join the post table, but just joining on the post's dateline could be wrong and isn't that efficient...

of course it's doable, yes, not sure which way would be the fastest, something which could be interesti9ng in running a few benchmark tests

[FFMG]

Originally Posted by Xenon

the fastest way is to get for example the last 3 times xx post, and then while running through the results, store the threadids in an array and if a new result is there, which has a threadid already cached throw it away...

Sorry, I have been away for a while.

Wouldn't GROUP BY work in this case?

SELECT *
FROM post
INNER JOIN thread
USING ( threadid )
GROUP BY (
post.threadid
)
ORDER BY post.dateline DESC
LIMIT xx

FFMG