|
|
Posted by Anne Bos on 08/09/06 10:35
On 9 Aug 2006 02:33:49 -0700 wrote "strawberry" <zac.carey@gmail.com>:
>
>Anne Bos wrote:
>> 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
>
>I think that you could implement a php solution more or less in the
>manner you suggest - and the code could be simpler than you propose,
>but I strongly suspect it would be significantly slower than either of
>the 'purer' mysql methods outlined above.
>
>Also, although there's nothing wrong with it, you don't actually need
>that ID field in the autart/creatie because the idschr and idart fields
>together can serve as the primary key - the logic being that the same
>author is not going to write the same article twice!
>
>Do the queries already provided seem in some way inadequate? They
>should give the results exactly as you had intended.
I guess you are right and I should have a closer look at the
SQL-stuff.
Yet, as php can handle rows and with rows[] handle field, there must
be a way to handle fields as such as well, was my reasoning. By
concentrating on this I forgot that SQL is far better in sorting.
About the ID you are right too, in general. But this journal has a
column, written by some authors and sometimes the same author,
sometime anonymous. So, in this case an author in a way writes
sometimes the same article. Just year and volume are different.
Anyhow, many thanks.
(If I manage to get the stuff in php as I originally tried, I'll let
it know in this group.)
All the best,
Anne Bos
Navigation:
[Reply to this message]
|