Mysql PHP Two Table Tree or Nested Results

[dprichard]

Okay, I have been putting off trying to figure out how to do this and I am at a crossroad where I just gotta figure it out.

I have the following

Table 1

NewsCategoryId
Category Name

Table 2

NewsStoryId
NewsCategoryId

I want to be able to show it on my PHP page page like this:

News Category1
- Story 1
- Story 2
- Story 3

News Category2
- Story 1
- Story 2
- Story 3

I have been searching google, but am not sure what I am trying to do is called so I am not getting good results. Any help or direction would be greatly appreciated.

I know I can do the following, but I can't imagine doing a query for each result from table 1 is good practice and will probably be a load on the server:

Query 1

While $row = mysql fetch array

Query 2

But I am sure there is a better way.

[oesxyl]

"News Category1..." are in what table, what field?
same question about "Story 1-...".

I guess that a "select distinct 'field with new category'..." could be first query and second "select whatever from ... where 'field with new category' = 'this new category'" in a loop.

regards

[dprichard]

Sorry, I guess more like this

TableNewsCategories
- CategoryId
- CategoryName


TableStories
- StoryId
- StoryName
- CategoryId


Then in my page like a tree I want to echo out

CategoryName1
- Stories under that category

CategoryName2
- Stories under that category

Just not sure what doing this is called so I dont know how to query it in google to figure out how to do it.

[oesxyl]

let's try this way:

$query1 = "select distinct CategoryName from TableNewsCategories";
$res1 = mysql_query($query1);
if($res1){
while($cat = mysql_fetch_assoc($res1)){
print $cat['CategoryName']."<br/>";
$query2 = "select StoryName from TableStories where CategoriyId in (select CategoryId from TableNewsCategories where CategoryName = '".$cat['CategoriyName']."')";
$res2 = mysql_query($query2);
if($res2){
while($story = mysql_fetch_assoc($res2)){
print '-'.$story['StoryName'].'<br/>';
}
}
}
}else{
print mysql_error();
}


regards