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
