|
|
Posted by Anne Bos on 08/08/06 20:23
Op 7 Aug 2006 18:15:06 -0700 schreef "strawberry"
<zac.carey@gmail.com>:
>
>Rik wrote:
>> Anne Bos wrote:
>> > Op 5 Aug 2006 12:22:36 -0700 schreef "strawberry"
>> > <zac.carey@gmail.com>:
>> >
>> >>
>> >> Anne Bos wrote:
>> >>> Hi everybody,
>> >>> I have a small database of a journal. It contains a table AUTHORS,a
>> >>> table ARTICLES and a link-table AUTART.
>> >>> Constructing a query asking for articles having some special word in
>> >>> the title and showing the results in a table is not that difficult,
>> >>> but if a resulting article has two or more authors, I get the
>> >>> following:
>> >>> Adamson, A. Some new ideas
>> >>> Bacardi, C. Some new ideas
>> >>> etc.
>> >>> I want in such case show the result like this:
>> >>> Adamson, A. and Bacardi, C. Some new ideas.
>> >>> (And of course more people likewise if an article has more authors.)
>> >>>
>> >>> I understand that, to do that I have to compare row i and row i+1
>> >>> and act appropriately if these rows have identical results in the
>> >>> articles-column.
>> >>> I'm sure it has a simple solution but somehow I'm struck with
>> >>> blindness in this respect.
>> >>>
>> >>> Can anybody help?
>> >>> Thanks in advance,
>> >>> Anne Bos
>> >>
>> >> Have a look at GROUP_CONCAT
>> >>
>> >> Something like this, although this isn't quite the answer - that
>> >> would be too easy :-):
>> >>
>> >> SELECT articles.article_id,GROUP_CONCAT(autart.author_id)
>> >> FROM articles
>> >> LEFT JOIN autart ON autart.article_id = articles.article_id
>> >> GROUP BY article_id;
>> >
>> > The problem then is how to determine what is a group. The articles
>> > written by more authors are subgroups of the querry result.
>>
>> How do you mean that exactly?
>> Tell us your table structure, and your current query, and we'll have an
>> easier time to steer you in the right direction.
>>
>> I think strawbarry gave you a great starting point:
>> GROUP_CONCAT([DISTINCT] expr [,expr ...]
>> [ORDER BY {unsigned_integer | col_name | expr}
>> [ASC | DESC] [,col_name ...]]
>> [SEPARATOR str_val])
>>
>> Something like (untested):
>>
>> SELECT
>> articles.article_id,
>> articles.article_title,
>> GROUP_CONCAT(DISTINCT authors.author_name SEPARATOR ' and ')
>> FROM articles
>> JOIN autart
>> ON articles.article_id = autart.article_id
>> JOIN authors
>> ON autart.author_id = authors.author_id
>> WHERE article.article_title LIKE '%searchstring%'
>> GROUP BY articles.article_id, articles.article_title
>>
>> Grtz,
>> --
>> Rik Wasmus
>
>Ah, you've given the game away ;-).
>
>Yep, unless Rik and I are missing something, that's pretty much spot
>on. I'd give the GROUP_CONCAT an alias - and all those 'ands' could get
>a little tedious if the article was by several authors (as academic
>articles so often are). Also, the article_id isn't strictly necessary
>Anne in the final results unless you want to be able to provide a link
>back to the article itself, say, through a hyperlink in a little bit of
>php. Oh, and to preserve my sanity (if it's not too late), I'd actually
>rename the autart table!!!:
>
>SELECT article, authors FROM
>(
>SELECT DISTINCT authors_articles.article_id,articles.article,
>GROUP_CONCAT(authors.author ORDER BY author) AS authors
>FROM authors_articles
>LEFT JOIN articles ON authors_articles.article_id = articles.article_id
>LEFT JOIN authors ON authors_articles.author_id = authors.author_id
>WHERE article LIKE '%searchstring%'
>GROUP BY article_id) t1
>;
I see,
actually I looked in php for a solution.
My tables are:
AUTHORS with the fields idauth, author,
ARTICLES with the fields idart, title (and some others irrelevant for
now)
AUTART with the fields ID, idauth, idart.
Then:
SELECT author.author, articles.idart, articles.title (and yet a few)
FROM author, autart, articles
WHERE author.idauth = autart.idauth AND articles.idart = autart.idart
AND articles.title LIKE '%$search%' ORDER BY articles.idart.
(Actually it is in Dutch. I translated some names. So in my real
database the tables are AUTEUR, with idschr, schrijver, ARTIKEL with
idart, titel and CREATIE with ID, idschr, idart. It is less confusing
than in English.)
In this way I get a list the way I already described. I hoped that by
ordering this way two or more numbers representing the idarts could be
detected so that in those cases I could do something like $author =
$author(i-1). ", ". $author(i).
Greetings,
Anne Bos
Navigation:
[Reply to this message]
|