...

View Full Version : Trying to display a certain post from phpbb



yaMz
10-12-2011, 02:09 AM
I'm trying to display the first post of the newest topic in a phpbb forum (the forum is specific, only one forum: [82].

The sql I'm looking at is here:


$sql = 'SELECT p.post_id, p.topic_id, p.post_subject, p.post_text, p.post_time, u.username
FROM ' . POSTS_TABLE . ' p
INNER JOIN ' . USERS_TABLE . ' u on (p.poster_id = u.user_id)
WHERE p.post_approved = 1
AND ' . $db->sql_in_set('p.forum_id', $authed_news_ary) . ' GROUP BY topic_id
ORDER BY 1 DESC';

$result = $db->sql_query_limit($sql, $num_posts);
$row = $db->sql_fetchrowset($result);



The part that is probably screwy (is) is:
AND ' . $db->sql_in_set('p.forum_id', $authed_news_ary) . ' GROUP BY topic_id
ORDER BY 1 DESC';

Can it even be done with normal statements or is more php required? The first post = the first post of any topic that is posted in the forum. Not the comments on the post thereafter

Source:


<!-- News -->
<div class="subheader">
<div class="title">Recent Clan News -
<?
// The below is not my function, I only configured it, then modified it
$forum_ids = array(82); //Forum ID(s) to pull posts from
$num_posts = 1; //Number of posts to display
$num_chars = 500; //Number of characters to show in the post text

//
// Trim text to a certain length
//
function phpbb_trim_text(&$text, &$is_trimmed, $number)
{
if ($number != 0 and strlen($text) > $number)
{
$text = substr($text, 0, $number);
$is_trimmed = true;
}

return true;
}

//
// Auth
//
$can_read_forum = $auth->acl_getf('f_read'); //Get the forums the user can read from
//Rework the array some
$forum_id_ary = array();
foreach($can_read_forum as $key => $forum)
{
if($forum['f_read'] != 0)
{
$forum_id_ary[] = $key;
}
}
$authed_news_ary = array();

//Of the desired forums, pull out the authed ones
if(!sizeof($forum_ids))
{
$authed_news_ary = $forum_id_ary;
}
else
{
$authed_news_ary = array_intersect($forum_ids, $forum_id_ary);
}

unset($can_read_forum);

//
// Recent post
//

if(sizeof($authed_news_ary))
{
$sql = 'SELECT p.post_id, p.topic_id, p.post_subject, p.post_text, p.post_time, u.username
FROM ' . POSTS_TABLE . ' p
INNER JOIN ' . USERS_TABLE . ' u on (p.poster_id = u.user_id)
WHERE p.post_approved = 1
AND ' . $db->sql_in_set('p.forum_id', $authed_news_ary) . ' GROUP BY topic_id
ORDER BY topic_id DESC';

$result = $db->sql_query_limit($sql, $num_posts);
$row = $db->sql_fetchrowset($result);

for($i = 0; $i < sizeof($row); $i++)
{
$post_url = "http://forum.dgclan.com/redirect/?location=viewtopic.php?t=" . $row[$i]['topic_id'] . "&amp;p=" . $row[$i]['post_id'];


//Prepare text (strip bbcodes and trim it)
$row[$i]['post_trimmed'] = false;
$change = $row[$i]['post_text'];
strip_bbcode($change);
$one = str_replace("{b}","<br />",$change);
$two = str_replace("{strong}","<b>",$one);
$three = str_replace("{/strong}","</b>",$two);

phpbb_trim_text($change, $row[$i]['post_trimmed'], $num_chars);

$post_text = '';
if ($row[$i]['post_trimmed'])
{
$post_text = $three . '...' . '<br /><br />[ <a href="' . $post_url . '">Read Full</a> ]';
}
else
{
$post_text = $three;
}

echo '<a href="' . $post_url . '">' . $row[$i]['post_subject'] . '</a></div><div class="date"></div></div><div class="newsarea"> <font color="SlateGray" style="padding-bottom:2px;">Posted by: ' . $row[$i]['username'] . ' - ' . $user->format_date($row[$i]['post_time']) . ' </font><br /><div style="background-color:#1A1A1A;border: 1px solid #3C3C3C;width:92%; padding:6px;"><font color="#D3D3D3">' . $post_text . '</font></div></td>
<br />';
}

$db->sql_freeresult($result);
}
else
{
echo 'No posts to display<br />';
}
?>

Old Pedant
10-12-2011, 02:54 AM
DEBUG DEBUG DEBUG.

Start by doing this:


...
$sql = 'SELECT p.post_id, p.topic_id, p.post_subject, p.post_text, p.post_time, u.username
FROM ' . POSTS_TABLE . ' p
INNER JOIN ' . USERS_TABLE . ' u on (p.poster_id = u.user_id)
WHERE p.post_approved = 1
AND ' . $db->sql_in_set('p.forum_id', $authed_news_ary) . ' GROUP BY topic_id
ORDER BY 1 DESC';

echo "<hr>DEBUG SQL: " . $sql . "<hr>\n";
...

Look at that query as it is being present to MySQL. Is it legal? Does it make sense?

But if you only want *ONE* post, then why are you asking for *ALL* of them? And then you do a loop on all of the found posts, so you will effectively be finding only the *last* one.

At a minimum, I think you need a LIMIT 1 tacked onto the end of that query.

Old Pedant
10-12-2011, 02:56 AM
And why do you have a GROUP BY clause in the SQL when you have no aggregate functions in the SELECT list?

Finally, what is sql_in_set? I don't find that in the PHP docs.

yaMz
10-12-2011, 12:30 PM
^ phpBB DBAL

I appreciate the criticism and structure. I'll fixer up.

Edit: Limit 1 is already tagged onto the query.
$result = $db->sql_query_limit($sql, $num_posts);

where $num_posts is 1

Old Pedant
10-12-2011, 10:09 PM
Limit 1 is already tagged onto the query.

$result = $db->sql_query_limit($sql, $num_posts);

where $num_posts is 1

Might not be as efficient as putting the LIMIT 1 into the query. That simply prevents the PHP client from reading more than one result. Putting LIMIT 1 in the query allows MySQL to sometimes be much more efficient, esp. if the ORDER BY is based on an indexed column.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum