SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Simple Parent-Child Display Problem

    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...

    Code:
    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:

    PHP Code:
      $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...

    PHP Code:
      $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...

    PHP Code:
      $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:

    PHP Code:
    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:

    PHP Code:
    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.

  2. #2
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    365
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could combine all those queries into 1 query.

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

    Code:
    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')

  3. #3
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I'll check that out.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •