SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have two tables: topics and subtopics.

    I want to display like this

    topic1
    subtopic1
    subtopic2
    topic2
    subtopic3
    subtopic4

    The topics table has two fields: ID and TopicName

    The subtopics table has three fields: ID, TopicID, and SubtopicName

    Currently I am doing this:
    Code:
    res = query topics table
    
    while row = fetch_array(res) {
      display topic name
      res1 = query subtopics table where TopicID = $row[ID]
      while row1=fetch_array(res1) {
        display subtopics
      }
    }
    This works, but I believe it is inefficient in that as the number of topics increase the number of queries grows by one for each topic. Is there a way to do this with one query. I have looked at join queries, and although I understand how to use them in some situations, I am not sure I understand how to do this with a join query. This is all being done on MySQL. Can somebody offer a select statement that would allow me to generate an array that would allow me to eliminate the ever growing number of select queries in this scenario.

    Thank you.
    Marty H.

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This logic will do the same thing with just the one sql query

    PHP Code:
    // assuming database connection already established...

    $sql "SELECT TopicName, SubtopicName
               FROM topics, subtopics 
              WHERE topics.ID = TopicID"
    ;

    $result mysql_query($sql);

    $currentTopic ""// not necessary, just my habbit to initialize variables.

    while ($row mysql_fetch_array($result) {
       if (
    $row["TopicName"] != $currentTopic) {
          
    // display topic name
          
    $currentTopic $row["TopicName"];
       }
       
    // display subtopic


  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. That is exactly what I was looking for. The concept just escaped me for some reason.
    Marty H.


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
  •