Don't know what version of MySQL you are using, but I upgraded to 4.1
so that I could use the GROUP_CONCAT function, which does what you are
looking for.
http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html
Otherwise, your front-end will need to do the rolling up of the records.
On Nov 10, 2004, at 1:04 PM, Chris Lott wrote:
Given a database query thats returns results from a linking (or xref)
table which includes repetition because of the joins:
+----+--------------------------+----------+
| id | title | subject |
+----+--------------------------+----------+
| 1 | Collected Poems of Keats | poetry |
| 2 | Spy High | suspense |
| 3 | Sci Fi Spies | suspense |
| 3 | Sci Fi Spies | sci-fi |
+----+--------------------------+----------+
What is the best way to go about displaying this for the user so that
the record looks "complete":
ID: 3
title: Sci Fi Spies
Subjects: suspense, scifi
or something similar? Or is there some better way to query? It's also
a problem in terms of limiting the query because if I limit the query
to 10 records I might be chopping off some subjects for the last book?
The query and tables are simple:
select books.id, books.title, subjects.subject
from books, subjects, books_subjects
where books_subjects.bid = books.id
and books_subjects.sid = subjects.id
BOOKS
1. collected poems of keats
2. spy high
3. sci-fi spies
SUBJECTS:
1. poetry
2. suspense
3. sci-fi
4. horror
5. mystery
BOOKS_SUBJECTS
bid sid
1 1
2 2
3 2
3 3
c
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
