Pulling multiple MySQL tables into one PHP output
Posted: Wed Feb 04, 2004 12:39 pm
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.
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:
Can someone help me achieve this?
Also for reference, here is the original code for JUST the topics (fave topics mod)
Also one last thing... this is my current template for output, will this work or should I make it all under the code?
THANKS in advance for anyone who can help me out.
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.
Code: Select all
$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']))
);
}So like dslreports.com (which has a great layout in my opinion) I want it to be like:
Code: Select all
-------------------------------------------------------
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|
-------------------------------------------------------
-------------------------------------------------------Also for reference, here is the original code for JUST the topics (fave topics mod)
Code: Select all
$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']))
);
}Code: Select all
<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