Simple Parent-Child Display Problem

[geosite]

I think this is a pretty simple problem. It looks like I just lost a query while reconfiguring my site, and I haven't yet been able to recreate it. While I'm at it, I thought I'd post the entire series of queries I'm using and ask if this is a good approach.

Let's start with my database table, px_topics. These are the most important fields...

URL | Topic | Parent | Site
Cold_Foods | Cold Foods | Foods | IVR
Cold_Foods | Cold Foods | Foods | FW
Hot_Foods | Hot Foods | Foods | IVR
Hot_Foods | Hot Foods | Foods | FW
Spices | Spices | Hot_Foods | IVR
Spices | Spices | Hot_Foods | FW
Mustard | Mustard | Hot_Foods | FW

The values in field Topic are displayed as page titles, while the values in field URL obviously serve as URL's. Some articles will be discussed on more than one website (indicated by the values on field Site).

In the above example, the article Hot Foods has one child article on website IVR, two child articles on the website FW. The articles "Cold Foods" have no child articles.

If I type either http://IVR/Hot_Foods/ or http://FW/Hot_Foods/ into my browser, I want to display a script that lists all the child articles, linked to the proper page.

This is the very first query in the food chain:

  $result = mysql_result(mysql_query("SELECT COUNT(URL)
  FROM px_topics
  WHERE URL = '$MyURL' AND Site = '$MySiteID'"),0); 

This simply tells us that http://FW/Hot_Foods is a valid URL, where $MyURL = Hot_Foods and $MySiteID = FW.

The next two queries are on a separate included page. This first one simply extracts some information from my table...

  $Topic = mysql_fetch_assoc(mysql_query("SELECT TOP.ID, TOP.URL, TOP.Topic, TOP.Nickname, TOP.Parent,
   TOP.Desc, TOP.KW, TOP.Site, TOP.Live
   FROM px_topics TOP
   WHERE TOP.URL = '$MyURL' AND TOP.Site = '$MySiteID'"));

  $Title = $Topic['Topic'];
  $Nickname = $Topic['Nickname'];
  $Parent = $Topic['Parent'];
  $Desc = $Topic['Desc'];
  $KW = $Topic['KW'];
  $Site = $Topic['Site'];
  $Live = $Topic['Live']; 

The next script determines whether a topic has children...

  $result = mysql_result(mysql_query("SELECT COUNT(TOP.ID)
   FROM px_topics TOP
   WHERE TOP.Parent = '$MyURL' AND TOP.Site = '$MySiteID'"),0);

  switch ($result)
  {
   case 1:
   $Children = 1;
   break;
   case 0:
   $Children = 0;
   break;
   default:
   $Children = $result;
   break;
  } 

Farther down the food chain is another included page that includes the Child Pages script with the following:

switch($Children)
{
 case 0:
 break;
 default:
 include ($BaseINC."/a1/inc/Top/Children.php");
 break;
} 

Finally, here's the script that's supposed to display a list of children:

echo '<div id="mnchildren" class="showIt">
<table>';
//<!-- BeginDynamicTable -->
while ($row = mysql_fetch_array ($res))
{
$Topic = str_replace ('_', ' ', $row[Topic]);
$NameL = $row[URL];

switch ($MySection)
{
 case 'Topics':
 $Topic = $Topic;
 break;
 case 'People':
 $Topic = $Person;
 break;
 case 'Orgs':
 $Topic = $Org;
 break;
 case 'Essays':
 $Topic = $Essay;
 break;
 case 'Exposes':
 $Topic = $Expose;
 break;
 default:
 break;
}

echo "<tr>";
echo "<td>• <a href=\"/$MySection/$NameL\">$Topic</a></td></tr>\n";
}
echo '</table>';
?>
</div> 

I have some similar examples that I tried to follow in recreating the query that prefaces the WHILE loop, but I'm doing something wrong. Can anyone help me fill in the blank?

Thanks.

[taliesinnz]

You could combine all those queries into 1 query.

In most cases it is faster to use as few queries as possible.

SELECT * FROM px_topics  WHERE Parent='$MyURL' AND Site = '$MySiteID'

should get you the list of childern for each URL.

But personally I would use a query like

SELECT * FROM px_topics WHERE Site = '$MySiteID' AND (URL = '$MyURL' OR Parent='$MySite')

[geosite]

Thanks, I'll check that out.