On Mon, 29 Nov 2004 16:56:11 -0500, Ross Singer
<[log in to unmask]> wrote:
> Andrew,
>
> I realize this doesn't answer your question in the slightest, but why
> not just do this in PHP after your SQL statement?
>
> It's going to be a lot easier to wrote the PHP code for displaying
> properly rather than fiddle with the awkward SQL statement that would
> replicate it.
>
> But then, I'm very lazy.
> -Ross.
>
>
>
> Andrew Darby wrote:
>
> > Hello, all.  Based on the pretty SQL I saw in some recent posts, I
> > thought I would fall on the mercy of the crowd, and see if anyone could
> > help . . .
> >
> > I'm trying to display (using MySQL/PHP) a list of subject librarians and
> > their associated subjects, in a neat table.  This gets me close:
> >
> > SELECT lname, fname, title, tel, email, supersubject FROM supersubs,
> > staff WHERE supersubs.staff_id = staff.staff_id GROUP BY lname
> >
> > except only the first associated subject is delivered.
> >
> > The following query gets me all the data I need, but with all the
> > librarian info duplicated:
> >
> > SELECT lname, fname, title, tel, email, supersubject FROM supersubs,
> > staff WHERE supersubs.staff_id = staff.staff_id GROUP BY lname,
> > supersubject
> >
> > Is there a way, in the SQL, to generate my "mama bear" set: i.e., with
> > the subjects grouped together like so:
> >
> > Smith, Joe | English, History, Italian | 425-5000 | [log in to unmask]
> > Turner, Ted | Math, Politics, Zoology | 425-5111 | [log in to unmask]
> >
> > Or do I have to run another query inside the php loop?  (I seem to
> > remember, from a previous life, that you could do these "queries within
> > queries" in ColdFusion.)
> >
> > Thanks, and I hope this doesn't qualify as a dumb question . . .

A good question that is bound to elicit several useful approaches.

I haven't written PHP for a while, but here's what I would do in
Python, which ought to be reasonably clear conceptually.  I would use
a variant of the second query, using an ORDER BY clause (perhaps
that's what you intended in the GROUP BY clause?) to order by last
name, first name, and subject:

----------snippet----------
stmnt = '''
  SELECT staff.staff_id, lname, fname, title, tel, email, supersubject
  FROM supersubs,staff
  WHERE supersubs.staff_id = staff.staff_id
  ORDER BY lname, fname, supersubject
'''
staff_id = None
lname, fname, title, tel, email, supersubject = '', '', '', '', '', ''
prev_staff_id = None
cursor.execute(stmnt)
while True:
    row = cursor.fetchone()
    #-- if we've reached the end of the data, print the last librarian
    #-- and break out of the loop
    if not row:
        print '%s, %s | %s | %s | <a href="mailto:%s">%s</a>' \
          % (lname, fname, ', '.join(subjects), tel, email
        break
    staff_id = row[0]
    #-- if we are onto a new librarian, print out data from
    #-- previous librarian
    if staff_id != prev_staff_id and pref_staff_id != None:
        print '%s, %s | %s | %s | <a href="mailto:%s">%s</a>' \
          % (lname, fname, ', '.join(subjects), tel, email
        #-- unpack the row into variables
        lname, fname, title, tel, email, supersubject = row[1:]
        #-- accumulate supersubject for later output
        subjects.append(supersubject)
    #-- since we already have lname, fname, title, tel, email for this
    #-- librarian, all we need is subject to accumulate
    subjects.append(row[5])
    #-- save this staff id for comparision with next row
    prev_staff_id = staff_id
----------snippet----------

On each iteration, append the subject to a list.  When you come to the
next librarian, or to the last row of data, output the accumulated
data, joining the subject list with ', ' before doing so.  Does that
make sense?

Chuck