RE: [PHP-DB] repeating records From: Cosenza, Mario (mcosenza <email protected>)
Date: 08/30/00

I don't know how efficient this was, but I did nested queries in my php
code. Grab the lesson info then use the ID to do a query for the preqs.

  $result = mysql_db_query("db", "SELECT * FROM lesson");
  if ($result) {
    while ($r = mysql_fetch_array($result)) {
      @@@ ECHO YOUR HEADING OF THE LESSON DATA HERE @@@
      @@@ $r[0]=$l_id IS USED IN NEXT SELECT STATEMENT @@@
      $subresult = mysql_db_query("db", "SELECT * FROM preqs where
l_id=$r[0]");
      if ($subresult) {
        while ($subr = mysql_fetch_array($subresult)) {
            @@@ ECHO EACH PREQ ENTRY HERE @@@
        }
      }
      mysql_free_result($subresult);
    }
  }
  mysql_free_result($result);

Hope it helps,

Mario

-----Original Message-----
From: John McKown [mailto:jmckown <email protected>]
Sent: Wednesday, August 30, 2000 1:07 PM
To: Adv. Systems Design
Cc: PHP
Subject: Re: [PHP-DB] repeating records

On Wed, 30 Aug 2000, Adv. Systems Design wrote:

> I have 3 tables: lesson, lesson_preq, and
> preqs...basically each lesson has many prerequisites
> (lesson_preq composed of lesson id and prereq id) and
> the prerequisites are stored in preqs (prereq id and
> prereq name).
>
> Now I want to list all the lessons and have the name
> appear for each prerequisite...the query below gives
> me what I want, but each lesson is repeated for each
> prerequisite...I would like to list the lesson only
> once and have all prerequisites appear (in name) for
> that lesson...since we do not have subselects...this
> is kind of confusing...any ideas?
>
> SELECT lesson.l_id, lesson.title, preqs.prereq
> FROM lesson, lesson_preq, preqs
> WHERE lesson_preq.preq_id=preqs.preq_id
> AND lesson_preq.l_id=lesson.l_id
>
> Thanks

I'm not sure what the format of the output you want is, but I would use
a ORDER BY lesson.l_id so that my output was returned to me in lesson.l_id
order. That would at least look nicer. The output from this would still
look something like:

l_id title prereq
1 l1 l1p1
1 l1 l1p2
1 l1 l1p3
2 l2 l2p1
3 l3 l3p1
3 l3 l3p2

I hope this is of at least some help. Or perhaps you might what to give an
example of what you would like the output to look like.

John 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: php-db-unsubscribe <email protected>
For additional commands, e-mail: php-db-help <email protected>
To contact the list administrators, e-mail: php-list-admin <email protected>

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: php-db-unsubscribe <email protected>
For additional commands, e-mail: php-db-help <email protected>
To contact the list administrators, e-mail: php-list-admin <email protected>