Nested Query

[Anon]

i have 2 for loops and my data is displayed like this

Category1
--Forum1
--Forum2
Category2
--Forum3
--Forum4

i'm using 1 query to get Category from Cats table(i have 2 records there and query count returns 2) and under this 1st qeury i have for loop making other query to get form names

when 2nd query and loop finishes 1st loop doesn't show next category

looks compliacted to me, thnx in advance

[[deleted]]

Did you make sure to use different result variables for the two queries?

in pseudo code:

$result=do_query()
while ($result)
{
$other_result=do_query();
while ($other_result)
{
echo "other result";
}
}

if you use the same result var for both queries, the results from the second query will overwrite the first.
Then when you are finished printing the forums from the first result of the first query, you are trying to go to the next category, but instead of referring to the categories results, you accedently refer to the forums results. And you just finished the forums results, so there is nothing more to process and it stops.

[Anon]

thnx it was just what you are talking about and 2nd thnx for SUOEr fast reply

[kparker]

One other thought: why do you need nested queries at all? Assuming you have a schema something like this:

  create table category
  (
  id int auto_increment,
  name text,
  ...
 &nbsp😉;

  create table forum
  (
  id int auto_increment,
  cat_id int, / references category.id /
  name,
  ...
 &nbsp😉;

You should be able to get everything all in one gulp by saying:

  select category.id, category.name, forum.id, forum.name, ...
  from category, forum
  where forum.cat_id = category.id
  order by category.name, forum.name

[Anon]

You could do it like that, but then you'd get al the info at once, with the category and the forum on the same row.
Then you'd have to write some code to print the category, then the forums for that category, then the next category, etc...

Possible, but messy.

[kparker]

I'd a zillion times rather get it all in one query, ordered the way I want it, than to have to make multiple queries. There's a huge difference in how much overhead the two methods incur.

[[deleted]]

Both methods have their advantages.

You method is probably slightly faster,(I've used that method a few times myself) but it's a lot more complex to code.

The method I described may be slower and generate more overhead, but the code is very clean and easy to understand.

My motto is: First get it to work, then worry about the speed. :-)