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]

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


Yes you are right, sorry.


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


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

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]

something like


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


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]

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]

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

[Xenon]

nope, that is not possible and will produce an error

[FFMG]

nope, that is not possible and will produce an error

Well, it is working for me http://www.httppoint.com/seo_tool_pr.php

And I have tried with phpMyAdmin and it works as well.

What do you mean by it would cause an error?

FFMG

[Xenon]

hmm, mysql is a bit lazy when handling grouping queries, normaly according to the sql standart that query should produce an errormessage as it's not possible to tell which information of which post should be selected

if it works, then ok, but that can change with new versions of mysql, that's why i wouldn't code it that way.

[PyroNET]

$posts = $db->query_read("SELECT * FROM post INNER JOIN thread USING(threadid) ORDER BY post.dateline DESC LIMIT 3");

while ($posts = $db->fetch_array($posts)) {

echo $posts['title'];

}


Why does this query return one result when there should be many more?