Building a forum PT. II, unknown query

**Lüpher Cypher** · Jan 4 '06, 06:55 PM

Re: Building a forum PT. II, unknown query

frizzle wrote:[color=blue]
> 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() )
>[/color]

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.descript ion,COUNT(threa ds.id) FROM threads LEFT JOIN
forums ON threads.forum_i d=forums.id WHERE forums.cat_id={ $cat} GROUP BY
threads.forum_i d

So the PHP would look like this:

$query = "SELECT * FROM categories";
$result = mysql_query($qu ery);
if (!$result) { echo mysql_error(); exit; }
echo "<table>";
while ($row = mysql_fetch_ass oc($result)) {
echo "<tr><td colspan=\"2\">{ $row["descriptio n"]}</td></tr>";
$query = "SELECT forums.descript ion,COUNT(threa ds.id) FROM threads ".

"LEFT JOIN forums ON threads.forum_i d=forums.id ".
"WHERE forums.cat_id={ $row["id"]} ".
"GROUP BY threads.forum_i d";
$result1 = mysql_query($qu ery);
if (!$result) { echo mysql_error(); exit; }
if (mysql_num_rows ($result) == 0) {
echo "<tr><td colspan=\"2\"> --> Sorry, no forums</td></tr>";
} else {
while ($row1 = mysql_fetch_row ($result1)) {
echo "<tr><td> --> {$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.desc ription, 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)

**frizzle** · Jan 5 '06, 07:05 PM

Re: Building a forum PT. II, unknown query

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.

**Lüpher Cypher** · Jan 6 '06, 01:55 PM

Re: Building a forum PT. II, unknown query

frizzle wrote:[color=blue]
> 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?[/color]

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)

**frizzle** · Jan 7 '06, 08:15 AM

Re: Building a forum PT. II, unknown query

Lüpher Cypher schreef:
[color=blue]
> frizzle wrote:[color=green]
> > 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?[/color]
>
> 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)[/color]

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_thre ads' => $forums['number_of_thre ads']
);

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.

Building a forum PT. II, unknown query

Building a forum PT. II, unknown query

Comment

Comment

Comment

Comment