table joins

doddsey_65 · September 27, 2010

I have this code which should get all the results from forum_forums and list them in their respective parents using forum_forums.parent_id. It does work sort of but it repeats one of them and doesnt include all of them.

Can anyone see where i am going wrong?

$parent_info_query = $db->query("SELECT 
                    parent_id,
                    parent_name
                    FROM ".DB_PREFIX."parents")
                    or die(mysql_error());

while ($parent_info = mysql_fetch_object($parent_info_query)) { 

// Add parent_id into variable for later query

$parent_id = $parent_info->parent_id;

echo '<table class="forum_table" onclick="expandCollapseTable(this)">
<tr id="tr1">
<th class="forum_left_corner"></th>
<th class="forum_parent_name">'.$parent_info->parent_name.'</th>
<th class="empty"></th>
<th class="empty"></th>
<th class="forum_last_post_header">'.LAST_POST.'</th>
</tr>';

// Get Forum information from DB to show all forums
// including who the last post was posted by

$forum_info_query = $db->query("SELECT 

                    ".DB_PREFIX."forums.forum_id,
                    ".DB_PREFIX."forums.forum_name,
                    ".DB_PREFIX."forums.forum_description,
                    ".DB_PREFIX."forums.forum_topics,
                    ".DB_PREFIX."forums.forum_posts,
                    ".DB_PREFIX."forums.forum_last_poster,
                    ".DB_PREFIX."forums.forum_last_post_time,
                    ".DB_PREFIX."forums.forum_last_post,
                    
                    ".DB_PREFIX."members.user_id,
                    ".DB_PREFIX."members.user_username,
                    ".DB_PREFIX."members.user_group,
                    
                    ".DB_PREFIX."topics.topic_id,
                    ".DB_PREFIX."topics.topic_name
                    
                    FROM ".DB_PREFIX."forums
                    
                    JOIN ".DB_PREFIX."members
                    
                    ON ".DB_PREFIX."forums.forum_last_poster
                    = ".DB_PREFIX."members.user_id
                    
                    JOIN ".DB_PREFIX."topics
                    
                    ON ".DB_PREFIX."forums.forum_id
                    = ".DB_PREFIX."topics.forum_id
                    
                    WHERE ".DB_PREFIX."forums.parent_id 
                    = $parent_id") 
                    
                    or trigger_error("SQL", E_USER_ERROR);
                    
while ($forum_info = mysql_fetch_object($forum_info_query)) {

doddsey_65 · September 27, 2010

UPDATE: I have realised that the wuery is not pulling the forums with no topics or replies. I think this is due to this bit of code in the query:

JOIN ".DB_PREFIX."members
                    
                    ON ".DB_PREFIX."forums.forum_last_poster
                    = ".DB_PREFIX."members.user_id

but i need that in to pull the username of the last poster. Any other way of doing this?

Psycho · September 27, 2010

First, don't run queries in a loop. You should be joining the $forum_info_query on the $parent_info_query as well. When joining tables, if a joined table record can be null (and you want the first table record anyway) you should be using a LEFT JOIN or something similar.

Also, instead of referencing the table name (along with the DB Prefix) for all the fields in the SELECT statement, it would be easier and more readable to create an alias for the table names and use that in the SELECT portion of the query.

Here is how I would rewrite the query as you previously have it

$query = "SELECT
              f.forum_id, f.forum_name, f.forum_description, f.forum_topics, f.forum_posts,
              f.forum_last_poster, f.forum_last_post_time, f.forum_last_post,
              m.user_id, m.user_username, m.user_group,
              t.topic_id, t.topic_name
          FROM ".DB_PREFIX."forums as f
          LEFT JOIN ".DB_PREFIX."members as m
              ON f.forum_last_poster = m.user_id
          LEFT JOIN ".DB_PREFIX."topics as t
              ON f.forum_id = t.forum_id
          WHERE f.parent_id = $parent_id";

And here is how I would create ONE single query with all the necessary info.

$query = "SELECT
              f.forum_id, f.forum_name, f.forum_description, f.forum_topics, f.forum_posts,
              f.forum_last_poster, f.forum_last_post_time, f.forum_last_post,
              p.parent_id, p.parent_name
              m.user_id, m.user_username, m.user_group,
              t.topic_id, t.topic_name
          FROM ".DB_PREFIX."forums as f
          JOIN ".DB_PREFIX."parents as p
              ON f.parent_id = p.parent_id
          LEFT JOIN ".DB_PREFIX."members as m
              ON f.forum_last_poster = m.user_id
          LEFT JOIN ".DB_PREFIX."topics as t
              ON f.forum_id = t.forum_id
          ORDER BY p.parent_id";

That query should give you all the information you need without having to run queries in a loop.

doddsey_65 · September 27, 2010

thanks, it worked. I used your example and changed a few things. I also added the loop but they are no longer being grouped in their parents. They should be grouped by their parent id into different tables, but they arent anymore. Here is the link to the live version which shows what i mean about the grouping: http://thevault.cz.cc But they are all now in their own single tables.

doddsey_65 · September 27, 2010

as far as i can see(been testing) i need the 2 queries. The first gets all parents so it can group the next query results in each table.

Psycho · September 27, 2010

They should be grouped by their parent id into different tables, but they arent anymore. Here is the link to the live version which shows what i mean about the grouping: http://thevault.cz.cc But they are all now in their own single tables.

You're using the second query - and just running it once to get all the records? They should be ordered by the parent ID as specified in the last line of the query. I have no idea what you want the data to look like, but if you have ALL the data in a single query you would just need to modify the code that generates the output.

as far as i can see(been testing) i need the 2 queries. The first gets all parents so it can group the next query results in each table.

Well, you're wrong. Not trying to be an a$$, but that statement is simply not true. The query may need to be tweaked, but you don't need to run multiple queries in loops for what you are trying to achieve.

doddsey_65 · September 27, 2010

it is grouping them by parent_id but each record is in its own table with its own <th>s. What i need is so that records with parent_id 1 are in a table with <th>s and records with parent_id 2 in a different table with <th>s and so on.

Heres my code so far:

$forum_info_query = $db->query("SELECT
              f.forum_id, f.forum_name, f.forum_description, f.forum_topics, f.forum_posts,
              f.forum_last_poster, f.forum_last_post_time, f.forum_last_post,
              p.parent_id, p.parent_name,
              m.user_id, m.user_username, m.user_group,
              t.topic_id, t.topic_name,
              po.post_id, po.post_subject
          FROM ".DB_PREFIX."forums as f
          JOIN ".DB_PREFIX."parents as p
              ON f.parent_id = p.parent_id
          LEFT JOIN ".DB_PREFIX."members as m
              ON f.forum_last_poster = m.user_id
          LEFT JOIN ".DB_PREFIX."topics as t
              ON f.forum_id = t.forum_id
          LEFT JOIN ".DB_PREFIX."posts as po
              ON po.post_id = f.forum_last_post
          ORDER BY p.parent_id")
                    or trigger_error("SQL", E_USER_ERROR);

while ($forum_info = mysql_fetch_object($forum_info_query)) { 

// Add parent_id into variable for later query

$parent_id = $forum_info->parent_id;

echo '<table class="forum_table" onclick="expandCollapseTable(this)">
<tr id="tr1">
<th class="forum_left_corner"></th>
<th class="forum_parent_name">'.$forum_info->parent_name.'</th>
<th class="empty"></th>
<th class="empty"></th>
<th class="forum_last_post_header">'.LAST_POST.'</th>
</tr>';

// Get Forum information from DB to show all forums
// including who the last post was posted by

echo '<tr class="gradient">';

$forum_url_name = $forum_info->forum_name;
$forum_url_name = str_replace(' ', '_', $forum_url_name);

echo '    <td class="forum_icon">
        <div class="thread_icon">'.$forum_info->forum_id.'</div>
        </td>
        <td class="forum_name">
        <p class="forum_name"><a href="index.php?forum=
        '. $forum_info->forum_id . '
        &name=
        '.$forum_url_name.'
        ">
        ' . $forum_info->forum_name .'
        </a></p>
        <p class="forum_description">
        ' . $forum_info->forum_description .'
        </p></td>
        
        <td class="forum_topics">
        '. $forum_info->forum_topics .'
        <span class="small_word"> '.TOPICS.'</span></td>
        
        <td class="forum_posts">
        '. $forum_info->forum_posts .'
        <span class="small_word"> '.POSTS.'</span></td>
        
        <td class="forum_last_post">';
            
        if (!$forum_info->forum_last_poster) {
        
        echo '<p class="noposts">'.NO_POSTS.'</p>
                <p class="be_the_first">'.BE_FIRST.'</p>';
        
        }
        elseif ($forum_info->forum_last_post == 0) {
        
        echo '<p class="noposts">'.NO_POSTS.'</p>
                <p class="be_the_first">'.BE_FIRST.'</p>';
        
        }

        else {
        
        
        $forum_last_post_clean = str_replace(' ', '_', $forum_info->user_username);
        
        echo '<p class="last_post_name"><a href="index.php?topic='.$forum_info->topic_id.'
        &name='.$forum_last_post_clean.'">'.$forum_info->post_subject.'</a></p>';
        
        echo '<p class="posted_by">Posted By - ';
        
        $today = date('F j, Y');
        $last_post = date("F j, Y", strtotime($forum_info->forum_last_post_time));
        
        if ($last_post == $today) { $last_post = 'Today at '.date("g:i a", strtotime($forum_info->forum_last_post_time)); }
        
        else { $last_post = date("F j, Y - g:i a", strtotime($forum_info->forum_last_post_time)); }
        if ($forum_info->user_group == 1) { echo 
        '<span class="admin">'; }
        
        elseif ($forum_info->user_group == 2) { echo 
        '<span class="mod">'; }
        
        elseif ($forum_info->user_group == 3) { echo 
        '<span class="user">'; }
        
        elseif ($forum_info->user_group == 0) { echo 
        '<span class="user">'; }
        
echo    $forum_info->user_username .'</span></p>

        
        <p class="last_post_date">'
        .$last_post;
        }
echo    '</p></td></tr>';

}

also one of the records(just one) is displayed twice. I can do an export of the forum table in the database if that helps.

doddsey_65 · September 27, 2010

i just tried to echo the query and it outputted this: Resource id #11

Psycho · September 27, 2010

i just tried to echo the query and it outputted this: Resource id #11

Well, yeah. That is the pointer to the results of the query.

Sorry, but I don't have the time to invest in rewriting the code to work with a single query. It is the best process though. You just need to use a flag when looping through the records to identify when the primary ID changes so you can start/end a table as needed.

doddsey_65 · September 27, 2010

i never asked you to rewrite the code, i asked for help on my code and why it is behaving as such

doddsey_65 · September 27, 2010

what about this then:

$parent_info_query = $db->query("SELECT
              p.parent_id, p.parent_name

          FROM ".DB_PREFIX."parents as p
         
          ORDER BY p.parent_id")
         
            or trigger_error("SQL", E_USER_ERROR);
               
while ($parent_info = mysql_fetch_object($parent_info_query)) {



// Add parent_id into variable for later query

$parent_id = $parent_info->parent_id;

echo '<table class="forum_table" onclick="expandCollapseTable(this)">
<tr id="tr1">
<th class="forum_left_corner"></th>
<th class="forum_parent_name">'.$parent_info->parent_name.'</th>
<th class="empty"></th>
<th class="empty"></th>
<th class="forum_last_post_header">'.LAST_POST.'</th>
</tr>';


$forum_info_query = $db->query("SELECT
              f.forum_id,
              f.forum_name,
              f.forum_description,
              f.forum_topics,
              f.forum_posts,
              f.forum_last_poster,
              f.forum_last_post_time,
              f.forum_last_post,
              m.user_id,
              m.user_username,
              m.user_group,
              t.topic_id,
              t.topic_name,
              po.post_id,
              po.post_subject
             
          FROM ".DB_PREFIX."forums as f

          LEFT JOIN ".DB_PREFIX."members as m
              ON f.forum_last_poster = m.user_id
             
          LEFT JOIN ".DB_PREFIX."topics as t
              ON t.forum_id = f.forum_id
             
          LEFT JOIN ".DB_PREFIX."posts as po
              ON po.post_id = f.forum_last_post
             
          WHERE f.parent_id = '$parent_id'
             
          ORDER BY f.forum_id")
         
            or trigger_error("SQL", E_USER_ERROR);
               
while ($forum_info = mysql_fetch_object($forum_info_query)) {

// Get Forum information from DB to show all forums
// including who the last post was posted by

echo '<tr class="gradient">';

$forum_url_name = $forum_info->forum_name;
$forum_url_name = str_replace(' ', '_', $forum_url_name);

echo '    <td class="forum_icon">
        <div class="thread_icon"></div>
        </td>
        <td class="forum_name">
        <p class="forum_name"><a href="index.php?forum=
        '. $forum_info->forum_id . '
        &name=
        '.$forum_url_name.'
        ">
        ' . $forum_info->forum_name .'
        </a></p>
        <p class="forum_description">
        ' . $forum_info->forum_description .'
        </p></td>
       
        <td class="forum_topics">
        '. $forum_info->forum_topics .'
        <span class="small_word"> '.TOPICS.'</span></td>
       
        <td class="forum_posts">
        '. $forum_info->forum_posts .'
        <span class="small_word"> '.POSTS.'</span></td>
       
        <td class="forum_last_post">';
           
        if (!$forum_info->forum_last_poster) {
       
        echo '<p class="noposts">'.NO_POSTS.'</p>
                <p class="be_the_first">'.BE_FIRST.'</p>';
       
        }
        elseif ($forum_info->forum_last_post == 0) {
       
        echo '<p class="noposts">'.NO_POSTS.'</p>
                <p class="be_the_first">'.BE_FIRST.'</p>';
       
        }

        else {
       
       
        $forum_last_post_clean = str_replace(' ', '_', $forum_info->user_username);
       
        echo '<p class="last_post_name"><a href="index.php?topic='.$forum_info->topic_id.'
        &name='.$forum_last_post_clean.'">'.$forum_info->post_subject.'</a></p>';
       
        echo '<p class="posted_by">Posted By - ';
       
        $today = date('F j, Y');
        $last_post = date("F j, Y", strtotime($forum_info->forum_last_post_time));
       
        if ($last_post == $today) { $last_post = 'Today at '.date("g:i a", strtotime($forum_info->forum_last_post_time)); }
       
        else { $last_post = date("F j, Y - g:i a", strtotime($forum_info->forum_last_post_time)); }
        if ($forum_info->user_group == 1) { echo
        '<span class="admin">'; }
       
        elseif ($forum_info->user_group == 2) { echo
        '<span class="mod">'; }
       
        elseif ($forum_info->user_group == 3) { echo
        '<span class="user">'; }
       
        elseif ($forum_info->user_group == 0) { echo
        '<span class="user">'; }
       
echo    $forum_info->user_username .'</span></p>

       
        <p class="last_post_date">'
        .$last_post;
        }
echo    '</p></td></tr>';

}
}

As you can see the first query ($parent_info_query) gets the parent name and parent id so i can use the parent id in the next query ($forum_info_query) so i can use WHERE parent_id = $parent_id. This is so it groups the results into the respective categories. Is there a way to do this without a loop in a loop? and why is this bad practice?

Thanks

Psycho · September 28, 2010

As you can see the first query ($parent_info_query) gets the parent name and parent id so i can use the parent id in the next query ($forum_info_query) so i can use WHERE parent_id = $parent_id. This is so it groups the results into the respective categories. Is there a way to do this without a loop in a loop? and why is this bad practice?

As I already explained, yes, you can get all the data in a single query. And, that is how you should do it.

I don't understand why you are asking why this is bad practice. I already stated in a separate thread of yours that

Running queries within a loop is very inefficinet

Try running a single query with all the data and running the queries in a loop as you have it then add some metrics gathering into your scripts to see how long they take to run. You will find that as the data grows the time to execute will grow at an exponential rate. Not to mention the CPU and memory load that those cycles will consume. If you have a moderately successful site that type of code can bring it to a crawl.

You already stated that all the records are returned in the query I gave you. What remains now is to rewrite the logic that formats the output so the records from the same parent are in the same table. That will require a rewrite of your existing logic.

gizmola · September 28, 2010

Geez doddsey, what is your problem? Are you really that lazy, that you when you have the query handed to you on a silver platter, which is a lot more than many people would contribute, you don't want to take the minimal work required to take what was overly complex code, and reduce it to the much simpler code required to get what you originally asked for, or is the problem that you just don't understand the answer you were given? I don't think it's laziness, but from mjdamato's point of view, he not only gave you the answer that any competent php/mysql developer would give you, he even wrote the query for you, which you admitted was correct.

Would it help to understand that in one result set, all the data you need is available and you simply need to fetch each row, checking to see if the p.parent_id changed. If it did, you have a new table, so you need to close off the old one, start a new one, and of course assign the value to your parent_id variable.

If you have to throw away the code you have, other than the query and start from scratch.

kickstart · September 28, 2010

Hi

Each new query takes time. A noticeable part of the time a query takes is interpreting the SQL before it can even think about executing it. Using a single query gives you that overhead once rather than loads of times.

What you need to do is put in code to catch the change of parent_id and close the existing table tag & open a new table tag when that happens.

Something like this:-

<?php

$forum_info_query = $db->query("SELECT
              f.forum_id, f.forum_name, f.forum_description, f.forum_topics, f.forum_posts,
              f.forum_last_poster, f.forum_last_post_time, f.forum_last_post,
              p.parent_id, p.parent_name
              m.user_id, m.user_username, m.user_group,
              t.topic_id, t.topic_name
          FROM ".DB_PREFIX."forums as f
          JOIN ".DB_PREFIX."parents as p
              ON f.parent_id = p.parent_id
          LEFT JOIN ".DB_PREFIX."members as m
              ON f.forum_last_poster = m.user_id
          LEFT JOIN ".DB_PREFIX."topics as t
              ON f.forum_id = t.forum_id
          ORDER BY p.parent_id") or trigger_error("SQL", E_USER_ERROR);
	  
$parent_id = 0;

while ($forum_info = mysql_fetch_object($forum_info_query)) 
{
if ($parent_id != $forum_info->parent_id)
{
	if ($parent_id != 0)
	{
		echo "</table>";
	}
	// Add parent_id into variable for later query
	$parent_id = $forum_info->parent_id;

	echo '<table class="forum_table" onclick="expandCollapseTable(this)">
	<tr id="tr1">
	<th class="forum_left_corner"></th>
	<th class="forum_parent_name">'.$forum_info->parent_name.'</th>
	<th class="empty"></th>
	<th class="empty"></th>
	<th class="forum_last_post_header">'.LAST_POST.'</th>
	</tr>';
}

// Get Forum information from DB to show all forums including who the last post was posted by
echo '<tr class="gradient">';
$forum_url_name = $forum_info->forum_name;
$forum_url_name = str_replace(' ', '_', $forum_url_name);
echo '    <td class="forum_icon"><div class="thread_icon"></div></td>
<td class="forum_name">
<p class="forum_name"><a href="index.php?forum=
'. $forum_info->forum_id . '&name='.$forum_url_name.'">' . $forum_info->forum_name .'</a></p>
<p class="forum_description">' . $forum_info->forum_description .'</p></td>
<td class="forum_topics">'. $forum_info->forum_topics .'<span class="small_word"> '.TOPICS.'</span></td>
<td class="forum_posts">'. $forum_info->forum_posts .'<span class="small_word"> '.POSTS.'</span></td>
<td class="forum_last_post">';

if (!$forum_info->forum_last_poster) 
{
	echo '<p class="noposts">'.NO_POSTS.'</p><p class="be_the_first">'.BE_FIRST.'</p>';
}
elseif ($forum_info->forum_last_post == 0) 
{
	echo '<p class="noposts">'.NO_POSTS.'</p><p class="be_the_first">'.BE_FIRST.'</p>';
}
else 
{
	$forum_last_post_clean = str_replace(' ', '_', $forum_info->user_username);

	echo '<p class="last_post_name"><a href="index.php?topic='.$forum_info->topic_id.'&name='.$forum_last_post_clean.'">'.$forum_info->post_subject.'</a></p>';

	echo '<p class="posted_by">Posted By - ';

	$today = date('F j, Y');
	$last_post = date("F j, Y", strtotime($forum_info->forum_last_post_time));

	if ($last_post == $today) { $last_post = 'Today at '.date("g:i a", strtotime($forum_info->forum_last_post_time)); }
	else { $last_post = date("F j, Y - g:i a", strtotime($forum_info->forum_last_post_time)); }
	if ($forum_info->user_group == 1) { echo '<span class="admin">'; }
	elseif ($forum_info->user_group == 2) { echo '<span class="mod">'; }
	elseif ($forum_info->user_group == 3) { echo '<span class="user">'; }
	elseif ($forum_info->user_group == 0) { echo '<span class="user">'; }
	echo    $forum_info->user_username .'</span></p><p class="last_post_date">'.$last_post;
}
echo    '</p></td></tr>';
}
if ($parent_id != 0)
{
echo "</table>";
}
?>

All the best

Keith

doddsey_65 · September 28, 2010

well i used to think this was a firendly community until now. No i am not lazy, i simply didnt understand. I never asked anyone to do the work for me i asked for help. I still dont understand the solution to my problem as i am fairly new to php but it doesnt matter now. I will just close this topic and get help elsewhere.

Psycho · September 28, 2010

Jeez, what's your problem? I've tried to be helpful and you are the one who became argumentative. I suggest you take a step back and reread this post and see who was being "unfriendly".

You stated you had a problem getting the records you wanted and I provided the queries you needed. You then stated that the output was not what you wanted and I stated you would have to modify the code that generates the output for the single query.

You then gave a brief explanation of how you wanted the output displayed but didn't povide a specific question. I don't know if you were wanting code to be provided or not. Personally, I don't have a problem if you were. I will routinely provide rewrites of code for people, even when it is not asked for, if I think that trying to explain every change needed will be time consuming. So, I stated I didn't have time to rewrite it and gave a general description of the process that would need to be implemented. I take it from your response of "i never asked you to rewrite the code, i asked for help on my code and why it is behaving as such" that you took my statement negatively. But, I had already stated the code would need to be rewritten and gave a description of how that process would work. And, kickstart has now provide a rewrite which I assume will work.

I will admit I was a little peeved when you suggested not once, but twice that you had to run the queries in a loop after I had already explained that was not the case - especially after you verified that the query I provided did in fact return all the records. The fact that I have a Guru badge doesn't mean crap. But, if you are going to continually challenge something at least have some data to back it up.

doddsey_65 · September 28, 2010

okay let me put a stop to this, since i always come here first for help. Im sorry if you think i am being argumentative, or what i said was potrayed as such. Thats not what i meant to happen. Maybe just a little frustration on my part for not being able to understand. I only said i needed the loop within a loop because i couldnt see any other way. And yes your wuery did return all of the results and that has been implemented but that isnt the issue now. The new issue was about the table headers. I accept the blame though, as i never seem to be able to clearly explain a problem. It seems so simple in my head but i can never get it into words properly. Sorry

kickstart · September 28, 2010

but that isnt the issue now. The new issue was about the table headers.

My earlier reply should give you the code for that (excepting any typos) if the issue is as I understand it (ie, you want a table for each parent_id).

All the best

Keith

doddsey_65 · September 28, 2010

thanks keith, i did implement your code but it is making a new table for each parent id. ie/ every record is in a new table rather than all records under the same parent_id in the same table. I will upload the file to the server so you can have a look here: http://thevault.cz.cc

Psycho · September 28, 2010

OK, here is a rewrite of the functionality to display the records. I hope it is in a logical format to understand. Basically, the code will loop through the records from the query and create the necessary HTML code and add it to an array. Whenever a change in the parent_id is identified the array is passed to a function to create the total output for the parent ID and all the associated records. The array is then reset and the loop continues with compiling the records for the next parent ID.

I did this all without testing as I don't have your database, so I am sure there are some typos to fix

<?php
function createTable($parentData)
{
    //Exit if this is the first record
    if($parentData['parent_id']===fales) { return false; }
    
    $output  = "<table class=\"forum_table\" onclick=\"expandCollapseTable(this)\">\n";
    $output .= "<tr id=\"tr1\">\n";
    $output .= "<th class=\"forum_left_corner\"></th>\n";
    $output .= "<th class=\"forum_parent_name\">{$parentData['parent_name']}</th>\n";
    $output .= "<th class=\"empty\"></th>\n";
    $output .= "<th class=\"empty\"></th>\n";
    $output .= "<th class=\"forum_last_post_header\">".LAST_POST."</th>\n";
    $output .= "</tr>\n";
    $output .= print_r("\n"; $parentData['records']);
    return $output;
}
    
//Create and run query to get all the data
$query = "SELECT
              f.forum_id, f.forum_name, f.forum_description, f.forum_topics, f.forum_posts,
              f.forum_last_poster, f.forum_last_post_time, f.forum_last_post,
              p.parent_id, p.parent_name,
              m.user_id, m.user_username, m.user_group,
              t.topic_id, t.topic_name,
              po.post_id, po.post_subject
          FROM ".DB_PREFIX."forums as f
          JOIN ".DB_PREFIX."parents as p
              ON f.parent_id = p.parent_id
          LEFT JOIN ".DB_PREFIX."members as m
              ON f.forum_last_poster = m.user_id
          LEFT JOIN ".DB_PREFIX."topics as t
              ON f.forum_id = t.forum_id
          LEFT JOIN ".DB_PREFIX."posts as po
              ON po.post_id = f.forum_last_post
          ORDER BY p.parent_id";
$forum_info = $db->query($query) or trigger_error("SQL", E_USER_ERROR);
    
//Loop through records to process data
$parentData = array('parent_id' => false);
while ($forum_info = mysql_fetch_object($forum_info_query))
{
    //Detect if this record is in a new parent id from the last
    if($forum_info->prent_id!=$parentData['parent_id'})
    {
        //Create table output for last parent data
        echo createTable($parentData);
        //Set new parent ID/name
        $parentData['parent_id'] = $forum_info->prent_id;
        $parentData['parent_name'] = $forum_info->prent_name;
        //Reset the data array
        $parentData['records'] = array();
    }
    
    //Create url string    
    $forum_url_name = str_replace(' ', '_', $forum_info->forum_name);

    //Create HTML output for current record
    $recordHTML  = "  <tr class=\"gradient\">\n";
    $recordHTML .= "    <td class=\"forum_icon\">\n";
    $recordHTML .= "    <div class=\"thread_icon\">{$forum_info->forum_id}</div>\n";
    $recordHTML .= "    </td>\n";
    $recordHTML .= "    <td class=\"forum_name\">\n";
    $recordHTML .= "    <p class=\"forum_name\">\n";
    $recordHTML .= "    <a href=\"index.php?forum=\{$forum_info->forum_id}&name={$forum_url_name}\">{$forum_info->forum_name}</a>\n";
    $recordHTML .= "    </p>\n";
    $recordHTML .= "    <p class=\"forum_description\">{$forum_info->forum_description}</p>\n";
    $recordHTML .= "    </td>\n";
    $recordHTML .= "    <td class=\"forum_topics\">{$forum_info->forum_topics}<span class=\"small_word\">".TOPICS."</span></td>\n";
    $recordHTML .= "    <td class=\"forum_posts\">{$forum_info->forum_posts}<span class=\"small_word\">".POSTS."</span></td>\n";
    
    $recordHTML .= "    <td class=\"forum_last_post\">\n";
    if(!$forum_info->forum_last_poster || $forum_info->forum_last_poster==0)
    {
        $recordHTML .= "<p class=\"noposts\">".NO_POSTS."</p><p class=\"be_the_first\">".BE_FIRST."</p>\n";
    }
    else
    {
        $forum_last_post_clean = str_replace(' ', '_', $forum_info->user_username);
        $last_post = date("F j, Y", strtotime($forum_info->forum_last_post_time));
        if ($last_post == date('F j, Y')) { $last_post = 'Today at '.date("g:i a", strtotime($forum_info->forum_last_post_time)); }

        $recordHTML .= "<p class=\"last_post_name\">\n";
        $recordHTML .= "<a href=\"index.php?topic={$forum_info->topic_id}&name={$forum_last_post_clean}\">{$forum_info->post_subject}</a>\n";
        $recordHTML .= "</p>\n";

        switch($forum_info->forum_last_poster)
        {
            case 1:
                $userClass .= 'admin';
                break;
            case 2:
                $userClass .= 'mod';
                break;
            case 3:
            case 0:
            default:
                $userClass .= 'user';
                break;
        }
        $recordHTML .= "<p class=\"posted_by\">Posted By - <span class=\"{$userClass}\">{$forum_info->user_username}}</span></p>\n";
        $recordHTML .= "<p class=\"last_post_date\">{$last_post}</p>\n";
    }
    $recordHTML .= "</td>\n";
    $recordHTML .= "</tr>\n";
    
    //Add the record HTML to records array
    $parentData['records'][] = $recordHTML;
}
    
//Display records for last parent ID
echo createTable($parentData);

doddsey_65 · September 29, 2010

Thanks for the reply. I used your code and did get a few errors. Some were just typos though. There was one saying $userClass was undefined so i defined it as $userClass=''; before the switch. But the site isnt displaying the data properly. it just displays the three headers with the words array then the number 1 above them. Heres the code with the few changes i made to fix the errors:

function createTable($parentData)
{
    //Exit if this is the first record
    if($parentData['parent_id']===false) { return false; }
    
    $output  = "<table class=\"forum_table\" onclick=\"expandCollapseTable(this)\">\n";
    $output .= "<tr id=\"tr1\">\n";
    $output .= "<th class=\"forum_left_corner\"></th>\n";
    $output .= "<th class=\"forum_parent_name\">{$parentData['parent_name']}</th>\n";
    $output .= "<th class=\"empty\"></th>\n";
    $output .= "<th class=\"empty\"></th>\n";
    $output .= "<th class=\"forum_last_post_header\">".LAST_POST."</th>\n";
    $output .= "</tr>\n";
    $output .= print_r("\n". $parentData['records']);
    return $output;
}
    
//Create and run query to get all the data
$query = $db->query("SELECT
              f.forum_id, f.forum_name, f.forum_description, f.forum_topics, f.forum_posts,
              f.forum_last_poster, f.forum_last_post_time, f.forum_last_post,
              p.parent_id, p.parent_name,
              m.user_id, m.user_username, m.user_group,
              t.topic_id, t.topic_name,
              po.post_id, po.post_subject
          FROM ".DB_PREFIX."forums as f
          JOIN ".DB_PREFIX."parents as p
              ON f.parent_id = p.parent_id
          LEFT JOIN ".DB_PREFIX."members as m
              ON f.forum_last_poster = m.user_id
          LEFT JOIN ".DB_PREFIX."topics as t
              ON f.forum_id = t.forum_id
          LEFT JOIN ".DB_PREFIX."posts as po
              ON po.post_id = f.forum_last_post
          ORDER BY p.parent_id")
          or trigger_error("SQL", E_USER_ERROR);
    
//Loop through records to process data
$parentData = array('parent_id' => false);
while ($forum_info = mysql_fetch_object($query))
{
    //Detect if this record is in a new parent id from the last
    if($forum_info->parent_id!=$parentData['parent_id'])
    {
        //Create table output for last parent data
        echo createTable($parentData);
        //Set new parent ID/name
        $parentData['parent_id'] = $forum_info->parent_id;
        $parentData['parent_name'] = $forum_info->parent_name;
        //Reset the data array
        $parentData['records'] = array();
    }
    
    //Create url string    
    $forum_url_name = str_replace(' ', '_', $forum_info->forum_name);

    //Create HTML output for current record
    $recordHTML  = "  <tr class=\"gradient\">\n";
    $recordHTML .= "    <td class=\"forum_icon\">\n";
    $recordHTML .= "    <div class=\"thread_icon\">{$forum_info->forum_id}</div>\n";
    $recordHTML .= "    </td>\n";
    $recordHTML .= "    <td class=\"forum_name\">\n";
    $recordHTML .= "    <p class=\"forum_name\">\n";
    $recordHTML .= "    <a href=\"index.php?forum=\{$forum_info->forum_id}&name={$forum_url_name}\">{$forum_info->forum_name}</a>\n";
    $recordHTML .= "    </p>\n";
    $recordHTML .= "    <p class=\"forum_description\">{$forum_info->forum_description}</p>\n";
    $recordHTML .= "    </td>\n";
    $recordHTML .= "    <td class=\"forum_topics\">{$forum_info->forum_topics}<span class=\"small_word\">".TOPICS."</span></td>\n";
    $recordHTML .= "    <td class=\"forum_posts\">{$forum_info->forum_posts}<span class=\"small_word\">".POSTS."</span></td>\n";
    
    $recordHTML .= "    <td class=\"forum_last_post\">\n";
    if(!$forum_info->forum_last_poster || $forum_info->forum_last_poster==0)
    {
        $recordHTML .= "<p class=\"noposts\">".NO_POSTS."</p><p class=\"be_the_first\">".BE_FIRST."</p>\n";
    }
    else
    {
        $forum_last_post_clean = str_replace(' ', '_', $forum_info->user_username);
        $last_post = date("F j, Y", strtotime($forum_info->forum_last_post_time));
        if ($last_post == date('F j, Y')) { $last_post = 'Today at '.date("g:i a", strtotime($forum_info->forum_last_post_time)); }

        $recordHTML .= "<p class=\"last_post_name\">\n";
        $recordHTML .= "<a href=\"index.php?topic={$forum_info->topic_id}&name={$forum_last_post_clean}\">{$forum_info->post_subject}</a>\n";
        $recordHTML .= "</p>\n";
        $userClass = '';
        switch($forum_info->forum_last_poster)
        {
            case 1:
                $userClass .= 'admin';
                break;
            case 2:
                $userClass .= 'mod';
                break;
            case 3:
            case 0:
            default:
                $userClass .= 'user';
                break;
        }
        $recordHTML .= "<p class=\"posted_by\">Posted By - <span class=\"{$userClass}\">{$forum_info->user_username}}</span></p>\n";
        $recordHTML .= "<p class=\"last_post_date\">{$last_post}</p>\n";
    }
    $recordHTML .= "</td>\n";
    $recordHTML .= "</tr>\n";
    
    //Add the record HTML to records array
    $parentData['records'][] = $recordHTML;
}
    
//Display records for last parent ID
echo createTable($parentData);
?>

Here is the sql dump also for the database if it helps. This code will be open source when i finish so im not too fussed about people knowing the layout. There are bound to be changes anyway.


CREATE TABLE IF NOT EXISTS `forum_config` (
  `config_id` int(11) NOT NULL AUTO_INCREMENT,
  `config_site_root` varchar(55) NOT NULL,
  `config_site_name` varchar(55) NOT NULL,
  `config_word_filter` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`config_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `forum_config`
--

INSERT INTO `forum_config` (`config_id`, `config_site_root`, `config_site_name`, `config_word_filter`) VALUES
(1, 'http://localhost/myforum', 'ASF', 0);

-- --------------------------------------------------------

--
-- Table structure for table `forum_forums`
--

CREATE TABLE IF NOT EXISTS `forum_forums` (
  `forum_id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL,
  `forum_name` varchar(100) NOT NULL,
  `forum_description` varchar(500) NOT NULL,
  `forum_topics` int(11) NOT NULL,
  `forum_posts` int(11) NOT NULL,
  `forum_last_post` int(11) NOT NULL,
  `forum_last_post_time` datetime NOT NULL,
  `forum_last_poster` int(11) NOT NULL,
  PRIMARY KEY (`forum_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `forum_forums`
--

INSERT INTO `forum_forums` (`forum_id`, `parent_id`, `forum_name`, `forum_description`, `forum_topics`, `forum_posts`, `forum_last_post`, `forum_last_post_time`, `forum_last_poster`) VALUES
(4, 3, 'Offtopic Chat', 'If you have something to say, something that maybe doesn''t quite fit anywhere else, then post it here. This is where you can talk about anything and everything.', 1, 1, 11, '2010-09-12 06:11:22', 27),
(3, 2, 'Have an Idea?', 'Everyone building a project from the ground up needs ideas. And that''s where you come in. You may have an idea for a new feature or you may have a better design. Whatever it is, share it here.', 0, 0, 0, '0000-00-00 00:00:00', 0),
(1, 1, 'Welcome', 'Are you new to the forum? If so then this is the place to start. Post an introduction and get aquainted with the staff and friendly members of our community.', 1, 1, 22, '2010-09-18 04:55:49', 27),
(5, 1, 'New Features', 'All new features that have already been implemented wil be posted here. This is the place to come if you want to find out about them or talk about them.', 0, 0, 0, '0000-00-00 00:00:00', 0),
(2, 1, 'Coming Soon', 'Coming soon to a forum near you. Interested to find out what is going on behind the scenes? Well you will find out everything you need to know here, including the latest features.', 0, 0, 0, '0000-00-00 00:00:00', 0);

CREATE TABLE IF NOT EXISTS `forum_members` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_username` varchar(32) NOT NULL,
  `user_email` varchar(100) NOT NULL,
  `user_password` varchar(55) NOT NULL,
  `user_real_name` varchar(32) NOT NULL,
  `user_group` int(1) NOT NULL,
  `user_register_ip` varchar(32) NOT NULL,
  `user_regdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_birthday` varchar(5) NOT NULL DEFAULT '00-00',
  `user_online` int(1) NOT NULL,
  `user_group_color` varchar(6) NOT NULL,
  `user_avatar` varchar(150) NOT NULL DEFAULT 'avatars/none.png',
  `user_sex` varchar(1) NOT NULL,
  `user_location` varchar(32) NOT NULL,
  `user_confirmed` int(1) NOT NULL,
  `user_show_sex` int(1) NOT NULL DEFAULT '0',
  `user_show_location` int(1) NOT NULL DEFAULT '0',
  `user_show_status` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=30 ;

INSERT INTO `forum_members` (`user_id`, `user_username`, `user_email`, `user_password`, `user_real_name`, `user_group`, `user_register_ip`, `user_regdate`, `user_birthday`, `user_online`, `user_group_color`, `user_avatar`, `user_sex`, `user_location`, `user_confirmed`, `user_show_sex`, `user_show_location`, `user_show_status`) VALUES
(27, 'doddsey65', 'doddsey_65@hotmail.com', 'ff97e4e4e184e00299c90eac38d54799c97096d2', '', 1, '127.0.0.1', '2010-09-21 14:40:39', '11-11', 1, 'ff0000', 'avatars/asf.jpg', 'm', 'UK North East', 1, 1, 1, 1);


CREATE TABLE IF NOT EXISTS `forum_parents` (
  `parent_id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_name` varchar(100) NOT NULL,
  PRIMARY KEY (`parent_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `forum_parents`
--

INSERT INTO `forum_parents` (`parent_id`, `parent_name`) VALUES
(1, 'Forum and Site News'),
(2, 'Share Your Ideas'),
(3, 'Offtopic Chat');

-- --------------------------------------------------------

--
-- Table structure for table `forum_posts`
--

CREATE TABLE IF NOT EXISTS `forum_posts` (
  `post_id` int(11) NOT NULL AUTO_INCREMENT,
  `topic_id` int(11) NOT NULL,
  `forum_id` int(11) NOT NULL,
  `post_poster` int(11) NOT NULL,
  `post_subject` varchar(100) NOT NULL,
  `post_content` longtext NOT NULL,
  `post_time` datetime NOT NULL,
  `post_edit_by` varchar(32) NOT NULL,
  `post_edit_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_approved` int(1) NOT NULL DEFAULT '0',
  `post_quoting` varchar(32) NOT NULL,
  PRIMARY KEY (`post_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=110 ;

--
-- Dumping data for table `forum_posts`
--

INSERT INTO `forum_posts` (`post_id`, `topic_id`, `forum_id`, `post_poster`, `post_subject`, `post_content`, `post_time`, `post_edit_by`, `post_edit_date`, `post_approved`, `post_quoting`) VALUES
(22, 23, 1, 27, 'Welcome To ASF', 'Hi and Welcome to ASF Forums. \r\n\r\nFirst, let me thank you for visiting this site while it is still in production. If you come accross any errors please let us know via the contact page or on the forum thread [url=http://forum.nystic.com/viewtopic.php?f=27&t=11986]here[/url].\r\n\r\nAs we are still in development not all features may work. This may be due to them being currently worked on and we ask for your patience while we add such features. Please keep checking back on our progress.\r\n\r\nWe hope you like what you see. If you have any questions or comments then please register and post a thread.\r\n\r\nThanks', '2010-09-18 04:55:49', '', '0000-00-00 00:00:00', 0, ''),
(11, 19, 4, 27, 'Thanks To BillyMcguffin', 'Thanks go to [b]Billy Mcguffin[/b] for his logo, which is now the main logo for ASF forums.', '2010-09-12 02:09:13', '', '0000-00-00 00:00:00', 0, '');

-- --------------------------------------------------------

--
-- Table structure for table `forum_sessions`
--

CREATE TABLE IF NOT EXISTS `forum_sessions` (
  `session_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `session` int(11) NOT NULL,
  `ip` varchar(11) NOT NULL,
  `browser` varchar(255) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`session_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

--
-- Dumping data for table `forum_sessions`
--


-- --------------------------------------------------------

--
-- Table structure for table `forum_topics`
--

CREATE TABLE IF NOT EXISTS `forum_topics` (
  `topic_id` int(11) NOT NULL AUTO_INCREMENT,
  `forum_id` int(11) NOT NULL,
  `topic_name` varchar(100) NOT NULL,
  `topic_poster` int(11) NOT NULL,
  `topic_time_posted` datetime NOT NULL,
  `topic_views` int(11) NOT NULL,
  `topic_replies` int(11) NOT NULL,
  `topic_last_poster` int(11) NOT NULL,
  `topic_last_post_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `topic_locked` int(1) NOT NULL DEFAULT '0',
  `topic_sticky` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`topic_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=28 ;

--
-- Dumping data for table `forum_topics`
--

INSERT INTO `forum_topics` (`topic_id`, `forum_id`, `topic_name`, `topic_poster`, `topic_time_posted`, `topic_views`, `topic_replies`, `topic_last_poster`, `topic_last_post_time`, `topic_locked`, `topic_sticky`) VALUES
(19, 4, 'Thanks To BillyMcguffin', 27, '2010-09-18 04:44:25', 0, 0, 27, '2010-09-12 01:06:00', 0, 0),
(23, 1, 'Welcome To ASF', 27, '2010-09-18 04:55:49', 129, 0, 27, '2010-09-18 03:55:00', 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `forum_user_details`
--

CREATE TABLE IF NOT EXISTS `forum_user_details` (
  `user_id` int(11) NOT NULL,
  `user_fname` varchar(32) NOT NULL,
  `user_lname` varchar(32) NOT NULL,
  `user_avatar` varchar(255) NOT NULL,
  `user_sig` varchar(500) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `forum_user_details`
--

INSERT INTO `forum_user_details` (`user_id`, `user_fname`, `user_lname`, `user_avatar`, `user_sig`) VALUES
(27, '', '', 'avatars/asf.jpg', '
[left][img=http://img178.imageshack.us/img178/7157/mainlogo.jpg][/left]
 
[right][size=32][b][url=http://thevault.cz.cc]ASF - A Simple Forum[/url][/b][/size][/right]
');

By the looks of it the code is exactly what i need. Maybe just a problem with the print_r. Ive never used arrays before so i wouldnt know.

Thanks

Psycho · September 29, 2010

Instead of defining $userClass before the switch, just change the assignments in the switch to "=" instead of ".="

For the second error, I should have used implode() instead of print_r().

$output .= implode("\n"; $parentData['records']);

doddsey_65 · September 29, 2010

Instead of defining $userClass before the switch, just change the assignments in the switch to "=" instead of ".="

For the second error, I should have used implode() instead of print_r().
$output .= implode("\n"; $parentData['records']);

i get a parse error with that but i just changed the ; to a .

But now i get the error:

Warning: implode() [function.implode]: Argument must be an array in C:\wamp\www\myforum\modules\pages\forums.php on line 48

Sorry if this is frustrating you lol. And thanks for all the help thus far

doddsey_65 · September 29, 2010

sorry i fixed it, i added a comma instead of the period. It works!! Thanks alot! I couldnt have done it without you all! I can now put this topic to rest.

pengu · September 29, 2010

Mark it as solved!

Sign In

table joins

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information