By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,740 Members | 844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,740 IT Pros & Developers. It's quick & easy.

Building a forum PT. II, unknown query

P: n/a
Hi there,

Still building my forum.
I have a certain mysql-query i just can't figure out.
These are my tables (simplified):
categories -> id, description
forums -> id, cat_id, description
threads -> id, forum_id, date, title, text

I suppose each speaks for itself.

What i want is below, but i just somehow cannot fix the query. I've
tried AND, GROUP BY, etc.
Table Contents:
Table 'categories'
- id - description
1 Category One
2 Category Two
3 Category Three
Table 'forums'
- id - cat_id - description
1 1 Forum 1
2 2 Second Forum
3 3 Another Forum in
category two!

Table 'threads'
- id - forum_id etc.
dozens of records.
What i need as an output is the following:
***************************************

Category One
--> Forum 1 4567 threads
--> Second Forum 867 threads

Category Two
--> Another Forum in category two! 67 threads

Category Three
--> Sorry, no forums at the moment.

***************************************

I hope it's clear. I need to get the Categories first,
then the forums belonging to them (grouped with them)
and then the number of threads belonging to those forums. ( Count() )
Greetings.

Jan 4 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
frizzle wrote:
Hi there,

Still building my forum.
I have a certain mysql-query i just can't figure out.
These are my tables (simplified):
categories -> id, description
forums -> id, cat_id, description
threads -> id, forum_id, date, title, text

I suppose each speaks for itself.

What i want is below, but i just somehow cannot fix the query. I've
tried AND, GROUP BY, etc.
Table Contents:
Table 'categories'
- id - description
1 Category One
2 Category Two
3 Category Three
Table 'forums'
- id - cat_id - description
1 1 Forum 1
2 2 Second Forum
3 3 Another Forum in
category two!

Table 'threads'
- id - forum_id etc.
dozens of records.
What i need as an output is the following:
***************************************

Category One
--> Forum 1 4567 threads
--> Second Forum 867 threads

Category Two
--> Another Forum in category two! 67 threads

Category Three
--> Sorry, no forums at the moment.

***************************************

I hope it's clear. I need to get the Categories first,
then the forums belonging to them (grouped with them)
and then the number of threads belonging to those forums. ( Count() )

Hmm, typos? Category One id=1, Second Forum has id=2 (Category Two).
Same for Another Forum - id=3 - Category Three. Assuming

Table 'categories'
- id - description
1 Category One
2 Category Two
3 Category Three
Table 'forums'
- id - cat_id - description
1 1 Forum 1
2 1 Second Forum
3 2 Another Forum in
category two!

Table 'threads'
- id - forum_id etc.
dozens of records.
and the mentioned output, I'd use two queries, the first to get
categories, and the second to get forums and number of threads:

SELECT * FROM categories

SELECT forums.description,COUNT(threads.id) FROM threads LEFT JOIN
forums ON threads.forum_id=forums.id WHERE forums.cat_id={$cat} GROUP BY
threads.forum_id
So the PHP would look like this:

$query = "SELECT * FROM categories";
$result = mysql_query($query);
if (!$result) { echo mysql_error(); exit; }
echo "<table>";
while ($row = mysql_fetch_assoc($result)) {
echo "<tr><td colspan=\"2\">{$row["description"]}</td></tr>";
$query = "SELECT forums.description,COUNT(threads.id) FROM threads ".

"LEFT JOIN forums ON threads.forum_id=forums.id ".
"WHERE forums.cat_id={$row["id"]} ".
"GROUP BY threads.forum_id";
$result1 = mysql_query($query);
if (!$result) { echo mysql_error(); exit; }
if (mysql_num_rows($result) == 0) {
echo "<tr><td colspan=\"2\"> --&gt; Sorry, no forums</td></tr>";
} else {
while ($row1 = mysql_fetch_row($result1)) {
echo "<tr><td> --&gt; {$row1[0]}</td>";
echo "<td>{$row1[1]} threads.</td></tr>";
}
}
echo "<tr><td colspan=\"2\">&nbsp;</td></tr>";
}
echo "</table>";
Alternatively, you can combine the two queries into one, but you'll get
data redundancy - you can also select categories.description, but it'll
repeat for each forum in the same category, plus you'll have to check
whether next row is in a new category.. So, I'd go with two queries :)

--

- lüpher
---------------------------------------------
"Man sieht nur das, was man weiß" (Goethe)
Jan 4 '06 #2

P: n/a
Maybe i'm wrong, but you're saying you're using two queries,
but if i get this right, you're running a query for each category,
or am i wrong?

Frizzle.

Jan 5 '06 #3

P: n/a
frizzle wrote:
Maybe i'm wrong, but you're saying you're using two queries,
but if i get this right, you're running a query for each category,
or am i wrong?


Well, the first query retrieves categories. They are then output from
while loop and inside the second second query retrieves forums and
thread count :) I meant that there are two query strings, there's going
to be #cats+1 query executions :)

--

- lüpher
---------------------------------------------
"Man sieht nur das, was man weiß" (Goethe)
Jan 6 '06 #4

P: n/a
Lüpher Cypher schreef:
frizzle wrote:
Maybe i'm wrong, but you're saying you're using two queries,
but if i get this right, you're running a query for each category,
or am i wrong?


Well, the first query retrieves categories. They are then output from
while loop and inside the second second query retrieves forums and
thread count :) I meant that there are two query strings, there's going
to be #cats+1 query executions :)

--

- lüpher
---------------------------------------------
"Man sieht nur das, was man weiß" (Goethe)


Ok, then i understood what you meant.
I sort of made a combination of one query, what i wanted at first, and
your solution;
I run a query to get the categories, and one to get the forums and
their 'belongings'. (So two in total, no matter how many categories
there are ... )

I put results of both queries in an array.
Array 1 is the one of categories:
$cat_array[ $cats['cat_id'] ] = array(
'cat_id' => $cats['cat_id'],
'cat_descr' => $cats['cat_descr']
);

Array 2 (forums):
$forum_array[ $forums['forum_id'] ] = array(
'forum_id' => $forums['forum_id'],
'forum_cat_id' => $forums['forum_cat_id'],
'forum_title' => $forums['forum_title'],
'forum_descr' => $forums['forum_descr'],
'number_of_threads' => $forums['number_of_threads']
);

What i wonder now, if i run a foreach with the cat_array
How can i get the items from the forum_array that correspond to the
current cat_array
( so where $forum_array['forum_cat_id'] = $cat_array[ current id ] )
So now we've moved from a mysql to PHP question. :)

Frizzle.

Jan 7 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.