Hey guys, I am trying to pull data from 3 tables and compile it into one page. Basically I am setting a favorite forums view for my web site and have to be able to pull from phpbb_favorites, phpbb_forums AND phpbb_topics.
Adding the topics section is the part I'm having trouble with. I am wanting it to list all the topics for each forum, or preferrably unread ones, similar to here.
Here is the working PHP/MySQl statement without the topics.
$sql = "SELECT phpbb_favorites.forum_id, phpbb_forums.*
FROM phpbb_favorites LEFT JOIN phpbb_forums
ON phpbb_favorites.forum_id = phpbb_forums.forum_id
WHERE phpbb_favorites.user_id = '" . $userdata['user_id'] . "'";
$result1 = $db->sql_query($sql);
while ( $row = $db->sql_fetchrow($result1) ) {
$template->assign_block_vars('forumrow', array(
'S_FOLDER' => $folder,
'S_FOLDER_ALT' => $folder_alt,
'S_FOLDER_TEXT' => $folder_text,
'L_FORUM_TITLE' => $row['forum_name'],
'VIEWS' => $row['topic_views'],
'REPLIES' => $row['topic_replies'],
'U_FORUM_TITLE' => append_sid("viewforum.$phpEx?f=" . $row['forum_id']),
'L_REMOVE' => $lang['Delete'],
'U_REMOVE' => append_sid("favforums2.$phpEx?mode=remove&f=" . $row['forum_id']))
);
}
Now I need to be able to pull the topic_id and topic_title from the table, phpbb_topics. Then loop it in, in a html table below it.
So like dslreports.com (which has a great layout in my opinion) I want it to be like:
[code]
Forum_name | delete |
topic_title | topic_replies | topic_views|
topic_title | topic_replies | topic_views|
Forum_name | delete |
topic_title | topic_replies | topic_views|
topic_title | topic_replies | topic_views|
[/code]
Can someone help me achieve this?
Also for reference, here is the original code for JUST the topics (fave topics mod)
$sql = "SELECT phpbb_favorites.topic_id, phpbb_topics.*
FROM phpbb_favorites LEFT JOIN phpbb_topics
ON phpbb_favorites.topic_id = phpbb_topics.topic_id
WHERE phpbb_favorites.user_id = '" . $userdata['user_id'] . "'";
$result = $db->sql_query($sql);
while ( $row = $db->sql_fetchrow($result) ) {
//START // 1.0.2 update // Simple Post Icon
if( $row['topic_type'] == POST_ANNOUNCE )
{
$folder = $images['folder_announce'];
//$folder_new = $images['folder_announce_new'];
$folder_alt = $lang['Post_Announcement'];
$folder_text = $lang['Topic_Announcement'];
}
else if( $row['topic_type'] == POST_STICKY )
{
$folder = $images['folder_sticky'];
//$folder_new = $images['folder_sticky_new'];
$folder_alt = $lang['Post_Sticky'];
$folder_text = $lang['Topic_Sticky'];
}
else {
$folder = $images['folder'];
$folder_new = $images['folder_new'];
$folder_alt = $lang['Post_Normal'];
$folder_text = "";
}
if( $row['topic_status'] == 1 )
{
$folder = $images['folder_locked'];
//$folder_new = $images['folder_locked_new'];
$folder_alt = $lang['Topic_locked'];
$folder_text = "";
}
//END // 1.0.2 update // Simple Post Icon
$template->assign_block_vars('topicrow', array(
'S_FOLDER' => $folder,
'S_FOLDER_ALT' => $folder_alt,
'S_FOLDER_TEXT' => $folder_text,
'L_TOPIC_TITLE' => $row['topic_title'],
'VIEWS' => $row['topic_views'],
'REPLIES' => $row['topic_replies'],
'U_TOPIC_TITLE' => append_sid("viewtopic.$phpEx?t=" . $row['topic_id']),
'L_REMOVE' => $lang['Delete'],
'U_REMOVE' => append_sid("favorites.$phpEx?mode=remove&t=" . $row['topic_id']))
);
}
Also one last thing... this is my current template for output, will this work or should I make it all under the code?
<a href="{U_INDEX}" class="nav">{L_INDEX}</a>
<table width="100%" cellpadding="1" cellspacing="1" border="0" class="forumline">
<tr>
<th colspan="1" height="21" class="thCornerL" align="center"> {L_FORUM} </th
*>
<th width="50" class="thTop" align="center"> {L_DELETE} </th>
</tr>
<!-- BEGIN forumrow -->
<tr>
<td height="21" class="row1" valign="middle"><span class="forumlink"><img src="{forumr
*ow.S_FOLDER}" width="19" height="18" alt="{forumrow.S_FOLDER_ALT}" title="{forumrow.S_FOLD
*ER_ALT}" />{forumrow.S_FOLDER_TEXT} <a href="{forumrow.U_FORUM_TITLE}">{forumrow.L_FO
*RUM_TITLE}</a></span></td>
<td valign="middle" class="row1" align="center"><span class="genmed"><a href="{forumro
*w.U_REMOVE}">{forumrow.L_REMOVE}</a></span></td>
</tr>
<!-- END forumrow -->
<tr>
<td height="21" class="row1" valign="middle"><span class="forumlink"><img src="{topicr
*ow.S_FOLDER}" width="19" height="18" alt="{topicrow.S_FOLDER_ALT}" title="{topicrow.S_FOLD
*ER_ALT}" />{topicrow.S_FOLDER_TEXT} <a href="{topicrow.U_TOPIC_TITLE}">{topicrow.L_TO
*PIC_TITLE}</a></span></td>
<td valign="middle" class="row1" align="center"><span class="genmed"><a href="{topicro
*w.U_REMOVE2}">{topicrow.L_REMOVE2}</a></span></td>
<td class="row1" align="center" valign="middle"><span class="postdetails">{topicrow.RE
*PLIES}</span></td>
<td class="row1" align="center" valign="middle"><span class="postdetails">{topicrow.VI
*EWS}</span></td>
</tr>
<tr>
<td height="21" class="row2"></td>
<td valign="top" class="row2"></td>
</tr>
</table>
(*) WARNING 7 long line(s) split
THANKS in advance for anyone who can help me out.
