PDA

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.