Professional Web Applications Themes

many-to-many Question - MySQL

Hi there. I'm building a music site, and there are a few so called many-to-many (i believe) related tables in it. For instance i have Artists, Genres, Art_Genr_combo Artists contains artist info, Genres contains a genre name and description Art_Genr_combo contains id's of both preveious tables, and tells what id they're connected by. Everytime when i ask for Artist where id = 1 (query below), it only returns the first Genre connected to the artist, how could i fix this? Frizzle. SELECT a.`id`, a.`artist_name`, a.`artist_info`, a.`created`, a.`updated`, a.`views`, c.`genre_id` g.`genre_name`, g.`genre_url` FROM `Artists` a LEFT JOIN `Art_Genr_combo` c ON c.`artist_id` ...

  1. #1

    Default many-to-many Question

    Hi there.

    I'm building a music site, and there are a few so called many-to-many
    (i believe) related tables in it.
    For instance i have Artists, Genres, Art_Genr_combo

    Artists contains artist info,
    Genres contains a genre name and description
    Art_Genr_combo contains id's of both preveious tables, and tells what
    id they're connected by.

    Everytime when i ask for Artist where id = 1 (query below), it only
    returns the first Genre connected to the artist, how could i fix this?

    Frizzle.

    SELECT a.`id`,
    a.`artist_name`,
    a.`artist_info`,
    a.`created`,
    a.`updated`,
    a.`views`,
    c.`genre_id`
    g.`genre_name`,
    g.`genre_url`
    FROM `Artists` a
    LEFT JOIN `Art_Genr_combo` c
    ON c.`artist_id` = a.`id`
    LEFT JOIN `Genres` g
    ON c.`genre_id` = g.`id`
    WHERE a.`id` = 1
    GROUP BY a.`id`
    LIMIT 1

    frizzle Guest

  2. #2

    Default Re: many-to-many Question

    frizzle wrote:
    > Everytime when i ask for Artist where id = 1 (query below), it only
    > returns the first Genre connected to the artist, how could i fix this?
    >
    > SELECT a.`id`,
    > g.`genre_name`,
    > g.`genre_url`
    > FROM `Artists` a
    > LEFT JOIN `Art_Genr_combo` c
    > ON c.`artist_id` = a.`id`
    > LEFT JOIN `Genres` g
    > ON c.`genre_id` = g.`id`
    > WHERE a.`id` = 1
    > GROUP BY a.`id`
    > LIMIT 1
    Either one of these causes only one row to be returned, thus only one
    genre (BTW, it's redundant to use both GROUP BY a.id and LIMIT 1 in this
    case).

    You need to remove both the GROUP BY and the LIMIT if you want to get
    multiple rows, one per genre.

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Re: many-to-many Question

    "Bill Karwin" <billkarwin.com> wrote in message
    news:e6ni2p0l7genews3.newsguy.com...
    > frizzle wrote:
    >> Everytime when i ask for Artist where id = 1 (query below), it only
    >> returns the first Genre connected to the artist, how could i fix this?
    >>
    >> SELECT a.`id`,
    >> g.`genre_name`,
    >> g.`genre_url`
    >> FROM `Artists` a
    >> LEFT JOIN `Art_Genr_combo` c
    >> ON c.`artist_id` = a.`id`
    >> LEFT JOIN `Genres` g
    >> ON c.`genre_id` = g.`id`
    >> WHERE a.`id` = 1
    >> GROUP BY a.`id`
    >> LIMIT 1
    >
    > Either one of these causes only one row to be returned, thus only one
    > genre (BTW, it's redundant to use both GROUP BY a.id and LIMIT 1 in this
    > case).
    >
    > You need to remove both the GROUP BY and the LIMIT if you want to get
    > multiple rows, one per genre.
    >
    > Regards,
    > Bill K.
    You must remove the LIMIT to get all the records, but you can keep the GROUP
    BY if you add the genre_id to the GROUP BY.

    "GROUP BY a.id, genre_id"

    Or you can group by any other column in the genre table for that matter,
    provided that the row you group on has unique values, if you want all
    associated records to the author...

    Having said that, keeping the GROUP BY clause would be meaningless at that
    point. <g>

    Cheers!

    ~ Duane Phillips.


    Duane Phillips Guest

  4. #4

    Default Re: many-to-many Question


    Duane Phillips wrote:
    > "Bill Karwin" <billkarwin.com> wrote in message
    > news:e6ni2p0l7genews3.newsguy.com...
    > > frizzle wrote:
    > >> Everytime when i ask for Artist where id = 1 (query below), it only
    > >> returns the first Genre connected to the artist, how could i fix this?
    > >>
    > >> SELECT a.`id`,
    > >> g.`genre_name`,
    > >> g.`genre_url`
    > >> FROM `Artists` a
    > >> LEFT JOIN `Art_Genr_combo` c
    > >> ON c.`artist_id` = a.`id`
    > >> LEFT JOIN `Genres` g
    > >> ON c.`genre_id` = g.`id`
    > >> WHERE a.`id` = 1
    > >> GROUP BY a.`id`
    > >> LIMIT 1
    > >
    > > Either one of these causes only one row to be returned, thus only one
    > > genre (BTW, it's redundant to use both GROUP BY a.id and LIMIT 1 in this
    > > case).
    > >
    > > You need to remove both the GROUP BY and the LIMIT if you want to get
    > > multiple rows, one per genre.
    > >
    > > Regards,
    > > Bill K.
    >
    > You must remove the LIMIT to get all the records, but you can keep the GROUP
    > BY if you add the genre_id to the GROUP BY.
    >
    > "GROUP BY a.id, genre_id"
    >
    > Or you can group by any other column in the genre table for that matter,
    > provided that the row you group on has unique values, if you want all
    > associated records to the author...
    >
    > Having said that, keeping the GROUP BY clause would be meaningless at that
    > point. <g>
    >
    > Cheers!
    >
    > ~ Duane Phillips.
    Thank you both for your reply!
    How do you mean the Group By would be meaningless ?

    Frizzle.

    frizzle Guest

  5. #5

    Default Re: many-to-many Question


    frizzle wrote:
    > Duane Phillips wrote:
    > > "Bill Karwin" <billkarwin.com> wrote in message
    > > news:e6ni2p0l7genews3.newsguy.com...
    > > > frizzle wrote:
    > > >> Everytime when i ask for Artist where id = 1 (query below), it only
    > > >> returns the first Genre connected to the artist, how could i fix this?
    > > >>
    > > >> SELECT a.`id`,
    > > >> g.`genre_name`,
    > > >> g.`genre_url`
    > > >> FROM `Artists` a
    > > >> LEFT JOIN `Art_Genr_combo` c
    > > >> ON c.`artist_id` = a.`id`
    > > >> LEFT JOIN `Genres` g
    > > >> ON c.`genre_id` = g.`id`
    > > >> WHERE a.`id` = 1
    > > >> GROUP BY a.`id`
    > > >> LIMIT 1
    > > >
    > > > Either one of these causes only one row to be returned, thus only one
    > > > genre (BTW, it's redundant to use both GROUP BY a.id and LIMIT 1 in this
    > > > case).
    > > >
    > > > You need to remove both the GROUP BY and the LIMIT if you want to get
    > > > multiple rows, one per genre.
    > > >
    > > > Regards,
    > > > Bill K.
    > >
    > > You must remove the LIMIT to get all the records, but you can keep the GROUP
    > > BY if you add the genre_id to the GROUP BY.
    > >
    > > "GROUP BY a.id, genre_id"
    > >
    > > Or you can group by any other column in the genre table for that matter,
    > > provided that the row you group on has unique values, if you want all
    > > associated records to the author...
    > >
    > > Having said that, keeping the GROUP BY clause would be meaningless at that
    > > point. <g>
    > >
    > > Cheers!
    > >
    > > ~ Duane Phillips.
    >
    > Thank you both for your reply!
    > How do you mean the Group By would be meaningless ?
    >
    > Frizzle.
    Also i just came to think of another scenario in my site where songs
    are displayed per artist. Each song can have unlimited 'emotions' (e.g.
    romantic, aggressive, etc.), and that works with the same structure.
    Only the result will be more then 1 (song).
    The way you showed me, i cannot imagine how the result would be,
    because it should look something like

    Song1 - Dramatic, Romantic, Melancholic
    Song2 - Aggressive
    Song3 - Etc.

    The problem is quite similar, yet very different.

    Frizzle.

    frizzle Guest

  6. #6

    Default Re: many-to-many Question

    "frizzle" <phpfrizzle> wrote in message
    news:1150239356.814625.78310f14g2000cwb.googlegro ups.com...
    > Hi there.
    >
    > I'm building a music site, and there are a few so called many-to-many
    > (i believe) related tables in it.
    > For instance i have Artists, Genres, Art_Genr_combo
    >
    > Artists contains artist info,
    > Genres contains a genre name and description
    > Art_Genr_combo contains id's of both preveious tables, and tells what
    > id they're connected by.
    >
    > Everytime when i ask for Artist where id = 1 (query below), it only
    > returns the first Genre connected to the artist, how could i fix this?
    >
    > Frizzle.
    >
    > SELECT a.`id`,
    > a.`artist_name`,
    > a.`artist_info`,
    > a.`created`,
    > a.`updated`,
    > a.`views`,
    > c.`genre_id`
    > g.`genre_name`,
    > g.`genre_url`
    > FROM `Artists` a
    > LEFT JOIN `Art_Genr_combo` c
    > ON c.`artist_id` = a.`id`
    > LEFT JOIN `Genres` g
    > ON c.`genre_id` = g.`id`
    > WHERE a.`id` = 1
    > GROUP BY a.`id`
    > LIMIT 1
    >
    No need for the LEFT JOINs here from what I understand your purpose to be,
    just use normal (inner) joins

    -- John


    John Smith Guest

  7. #7

    Default Re: many-to-many Question

    John Smith wrote:
    > No need for the LEFT JOINs here from what I understand your purpose to be,
    > just use normal (inner) joins
    I assumed the LEFT JOIN was needed, because some of his artists are not
    associated with genre yet. That is, the data entry for associating
    artists with genre is not yet complete.

    Regards,
    Bill K.
    Bill Karwin Guest

  8. #8

    Default Re: many-to-many Question

    frizzle wrote:
    > How do you mean the Group By would be meaningless ?
    If one uses GROUP BY on a column of unique entries, such as a primary
    key, you are guaranteed to get as many groups as there are rows. So if
    you were doing this in a query from 1 table, the GROUP BY would return
    the same result as if you had not used GROUP BY.

    Since you are joining with additional tables, the grouping is useful.
    But if you were to GROUP BY both the primary key of Artists and the
    additional key of Genres, then you'd again guarantee that the result set
    would consist of groups of 1 row each. That would make the GROUP BY
    "meaningless" in that its result set is the same as if you had not used
    GROUP BY.
    > Also i just came to think of another scenario in my site where songs
    > are displayed per artist. Each song can have unlimited 'emotions' (e.g.
    > romantic, aggressive, etc.), and that works with the same structure.
    > Only the result will be more then 1 (song).
    > The way you showed me, i cannot imagine how the result would be,
    > because it should look something like
    >
    > Song1 - Dramatic, Romantic, Melancholic
    > Song2 - Aggressive
    > Song3 - Etc.
    MySQL has a function called GROUP_CONCAT() in version 4.1 and later.
    This creates a comma-separated list of values from its argument column.

    SELECT s.`song_name`, GROUP_CONCAT(e.`emotion_name`) AS `emotion_list`
    FROM `Songs` s
    LEFT JOIN `Song_Emot_combo` c ON c.`song_id` = s.`id`
    LEFT JOIN `Emotions` e ON c.`emotion_id` = e.`id`
    WHERE s.`id` = 1
    GROUP BY s.`id`

    See [url]http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html[/url] for
    more details on GROUP_CONCAT().

    Regards,
    Bill K.
    Bill Karwin Guest

  9. #9

    Default Re: many-to-many Question


    Bill Karwin wrote:
    > frizzle wrote:
    > > How do you mean the Group By would be meaningless ?
    >
    > If one uses GROUP BY on a column of unique entries, such as a primary
    > key, you are guaranteed to get as many groups as there are rows. So if
    > you were doing this in a query from 1 table, the GROUP BY would return
    > the same result as if you had not used GROUP BY.
    >
    > Since you are joining with additional tables, the grouping is useful.
    > But if you were to GROUP BY both the primary key of Artists and the
    > additional key of Genres, then you'd again guarantee that the result set
    > would consist of groups of 1 row each. That would make the GROUP BY
    > "meaningless" in that its result set is the same as if you had not used
    > GROUP BY.
    >
    > > Also i just came to think of another scenario in my site where songs
    > > are displayed per artist. Each song can have unlimited 'emotions' (e.g.
    > > romantic, aggressive, etc.), and that works with the same structure.
    > > Only the result will be more then 1 (song).
    > > The way you showed me, i cannot imagine how the result would be,
    > > because it should look something like
    > >
    > > Song1 - Dramatic, Romantic, Melancholic
    > > Song2 - Aggressive
    > > Song3 - Etc.
    >
    > MySQL has a function called GROUP_CONCAT() in version 4.1 and later.
    > This creates a comma-separated list of values from its argument column.
    >
    > SELECT s.`song_name`, GROUP_CONCAT(e.`emotion_name`) AS `emotion_list`
    > FROM `Songs` s
    > LEFT JOIN `Song_Emot_combo` c ON c.`song_id` = s.`id`
    > LEFT JOIN `Emotions` e ON c.`emotion_id` = e.`id`
    > WHERE s.`id` = 1
    > GROUP BY s.`id`
    >
    > See [url]http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html[/url] for
    > more details on GROUP_CONCAT().
    >
    > Regards,
    > Bill K.
    Great Bill!
    This is getting me closer to what i want! Only one problem i
    encountered: when a genre-name contains a comma, it s things up
    ....
    How is this generally fixed ?

    Frizzle.

    frizzle Guest

  10. #10

    Default Re: many-to-many Question


    frizzle wrote:
    > Bill Karwin wrote:
    > > frizzle wrote:
    > > > How do you mean the Group By would be meaningless ?
    > >
    > > If one uses GROUP BY on a column of unique entries, such as a primary
    > > key, you are guaranteed to get as many groups as there are rows. So if
    > > you were doing this in a query from 1 table, the GROUP BY would return
    > > the same result as if you had not used GROUP BY.
    > >
    > > Since you are joining with additional tables, the grouping is useful.
    > > But if you were to GROUP BY both the primary key of Artists and the
    > > additional key of Genres, then you'd again guarantee that the result set
    > > would consist of groups of 1 row each. That would make the GROUP BY
    > > "meaningless" in that its result set is the same as if you had not used
    > > GROUP BY.
    > >
    > > > Also i just came to think of another scenario in my site where songs
    > > > are displayed per artist. Each song can have unlimited 'emotions' (e.g.
    > > > romantic, aggressive, etc.), and that works with the same structure.
    > > > Only the result will be more then 1 (song).
    > > > The way you showed me, i cannot imagine how the result would be,
    > > > because it should look something like
    > > >
    > > > Song1 - Dramatic, Romantic, Melancholic
    > > > Song2 - Aggressive
    > > > Song3 - Etc.
    > >
    > > MySQL has a function called GROUP_CONCAT() in version 4.1 and later.
    > > This creates a comma-separated list of values from its argument column.
    > >
    > > SELECT s.`song_name`, GROUP_CONCAT(e.`emotion_name`) AS `emotion_list`
    > > FROM `Songs` s
    > > LEFT JOIN `Song_Emot_combo` c ON c.`song_id` = s.`id`
    > > LEFT JOIN `Emotions` e ON c.`emotion_id` = e.`id`
    > > WHERE s.`id` = 1
    > > GROUP BY s.`id`
    > >
    > > See [url]http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html[/url] for
    > > more details on GROUP_CONCAT().
    > >
    > > Regards,
    > > Bill K.
    >
    > Great Bill!
    > This is getting me closer to what i want! Only one problem i
    > encountered: when a genre-name contains a comma, it s things up
    > ...
    > How is this generally fixed ?
    >
    > Frizzle.
    What i forgot: i noticed the great [SEPARATOR str_val] part, but if i
    want to allow any character in the genre name, how is this generally
    fixed ...

    Frizzle.

    frizzle Guest

  11. #11

    Default Re: many-to-many Question


    Gary L. Burnore wrote:
    > On 15 Jun 2006 15:13:31 -0700, "frizzle" <phpfrizzle> wrote:
    >
    > >
    > >frizzle wrote:
    > >> Bill Karwin wrote:
    > >> > frizzle wrote:
    > >> > > How do you mean the Group By would be meaningless ?
    > >> >
    > >> > If one uses GROUP BY on a column of unique entries, such as a primary
    > >> > key, you are guaranteed to get as many groups as there are rows. Soif
    > >> > you were doing this in a query from 1 table, the GROUP BY would return
    > >> > the same result as if you had not used GROUP BY.
    > >> >
    > >> > Since you are joining with additional tables, the grouping is useful.
    > >> > But if you were to GROUP BY both the primary key of Artists and the
    > >> > additional key of Genres, then you'd again guarantee that the resultset
    > >> > would consist of groups of 1 row each. That would make the GROUP BY
    > >> > "meaningless" in that its result set is the same as if you had not used
    > >> > GROUP BY.
    > >> >
    > >> > > Also i just came to think of another scenario in my site where songs
    > >> > > are displayed per artist. Each song can have unlimited 'emotions' (e.g.
    > >> > > romantic, aggressive, etc.), and that works with the same structure.
    > >> > > Only the result will be more then 1 (song).
    > >> > > The way you showed me, i cannot imagine how the result would be,
    > >> > > because it should look something like
    > >> > >
    > >> > > Song1 - Dramatic, Romantic, Melancholic
    > >> > > Song2 - Aggressive
    > >> > > Song3 - Etc.
    > >> >
    > >> > MySQL has a function called GROUP_CONCAT() in version 4.1 and later.
    > >> > This creates a comma-separated list of values from its argument column.
    > >> >
    > >> > SELECT s.`song_name`, GROUP_CONCAT(e.`emotion_name`) AS `emotion_list`
    > >> > FROM `Songs` s
    > >> > LEFT JOIN `Song_Emot_combo` c ON c.`song_id` = s.`id`
    > >> > LEFT JOIN `Emotions` e ON c.`emotion_id` = e.`id`
    > >> > WHERE s.`id` = 1
    > >> > GROUP BY s.`id`
    > >> >
    > >> > See [url]http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html[/url] for
    > >> > more details on GROUP_CONCAT().
    > >> >
    > >> > Regards,
    > >> > Bill K.
    > >>
    > >> Great Bill!
    > >> This is getting me closer to what i want! Only one problem i
    > >> encountered: when a genre-name contains a comma, it s things up
    > >> ...
    > >> How is this generally fixed ?
    > >>
    > >> Frizzle.
    > >
    > >What i forgot: i noticed the great [SEPARATOR str_val] part, but if i
    > >want to allow any character in the genre name, how is this generally
    > >fixed ...
    > >
    > Make the separator a pipe ( | ) and disallow pipes in your genre
    > field.
    >
    > --
    > gburnore at DataBasix dot Com
    > ---------------------------------------------------------------------------
    > How you look depends on where you go.
    > ---------------------------------------------------------------------------
    > Gary L. Burnore | ۳ݳ޳ݳۺݳ޳ݳݳ޳ݳ۳
    > | ۳ݳ޳ݳۺݳ޳ݳݳ޳ݳ۳
    > Official .sig, Accept no substitutes. | ۳ݳ޳ݳۺݳ޳ݳݳ޳ݳ۳
    > | 0 1 7 2 3 / ݳ 3 74 9 3 0 ۳
    > Black Helicopter Repair Services, Ltd.| Official Proof of Purchase
    > ================================================== =========================
    Ok, thanks. So the solution would still be to exclude a char.
    Would it be an option to use some kind of weirder char then a pipe ?
    Since the pipe is used more often these days for e.g. decorational
    purposes ...

    Frizzle.

    frizzle Guest

  12. #12

    Default Re: many-to-many Question

    frizzle wrote:
    > Ok, thanks. So the solution would still be to exclude a char.
    > Would it be an option to use some kind of weirder char then a pipe ?
    > Since the pipe is used more often these days for e.g. decorational
    > purposes ...
    I can think of two other options:

    1. You can specify a string of more than one character for the
    separator. So even if you permit something like semicolon ";" in your
    strings, you can disallow something less likely to be needed, like two
    semicolons together.

    GROUP_CONCAT( ... SEPARATOR ';;')

    2. Use string delimiters (such as a double-quote), in addition to a
    separator:

    GROUP_CONCAT( CONCAT('"', e.emotion_name, '"') SEPARATOR ',')

    The combination of delimiters and separators make it unambiguous whether
    the comma is a separator or is part of the string in the list.

    Regards,
    Bill K.
    Bill Karwin Guest

  13. #13

    Default Re: many-to-many Question


    Bill Karwin wrote:
    > frizzle wrote:
    > > Ok, thanks. So the solution would still be to exclude a char.
    > > Would it be an option to use some kind of weirder char then a pipe ?
    > > Since the pipe is used more often these days for e.g. decorational
    > > purposes ...
    >
    > I can think of two other options:
    >
    > 1. You can specify a string of more than one character for the
    > separator. So even if you permit something like semicolon ";" in your
    > strings, you can disallow something less likely to be needed, like two
    > semicolons together.
    >
    > GROUP_CONCAT( ... SEPARATOR ';;')
    >
    > 2. Use string delimiters (such as a double-quote), in addition to a
    > separator:
    >
    > GROUP_CONCAT( CONCAT('"', e.emotion_name, '"') SEPARATOR ',')
    >
    > The combination of delimiters and separators make it unambiguous whether
    > the comma is a separator or is part of the string in the list.
    >
    > Regards,
    > Bill K.
    Thanks Bill!

    I tried
    GROUP_CONCAT( CONCAT( ',', e.emotion_name, ',' ) SEPARATOR
    '"')

    But AFAIK the result is the same ...
    I put a comma in emotion Dramatic for testing purposes, but the result
    doesn't do anything different ...
    Result looks like: [emotion_list] => ,Drama,tic,",Happy,

    The mysql-manual doesn't really clarify things for me ...
    I feel my solution is in this corner, and i already could solve things
    by using "forbidden" chars as a delimiter, but i belive there should be
    another way ...

    Frizzle.

    frizzle Guest

  14. #14

    Default Re: many-to-many Question

    frizzle wrote:
    > Bill Karwin wrote:
    >> frizzle wrote:
    >>> Ok, thanks. So the solution would still be to exclude a char.
    >>> Would it be an option to use some kind of weirder char then a pipe ?
    >>> Since the pipe is used more often these days for e.g. decorational
    >>> purposes ...
    >>
    >> I can think of two other options:
    >>
    >> 1. You can specify a string of more than one character for the
    >> separator. So even if you permit something like semicolon ";" in
    >> your strings, you can disallow something less likely to be needed,
    >> like two semicolons together.
    >>
    >> GROUP_CONCAT( ... SEPARATOR ';;')
    >>
    >> 2. Use string delimiters (such as a double-quote), in addition to a
    >> separator:
    >>
    >> GROUP_CONCAT( CONCAT('"', e.emotion_name, '"') SEPARATOR ',')
    >>
    >> The combination of delimiters and separators make it unambiguous
    >> whether the comma is a separator or is part of the string in the
    >> list.
    >>
    >> Regards,
    >> Bill K.
    >
    > Thanks Bill!
    >
    > I tried
    > GROUP_CONCAT( CONCAT( ',', e.emotion_name, ',' ) SEPARATOR
    > '"')
    >
    > But AFAIK the result is the same ...
    > I put a comma in emotion Dramatic for testing purposes, but the result
    > doesn't do anything different ...
    > Result looks like: [emotion_list] => ,Drama,tic,",Happy,
    >
    > The mysql-manual doesn't really clarify things for me ...
    > I feel my solution is in this corner, and i already could solve things
    > by using "forbidden" chars as a delimiter, but i belive there should
    > be another way ...
    >
    > Frizzle.
    How about using a non-printable value or even a newline value


    Paul Lautman Guest

  15. #15

    Default Re: many-to-many Question


    Paul Lautman wrote:
    > frizzle wrote:
    > > Bill Karwin wrote:
    > >> frizzle wrote:
    > >>> Ok, thanks. So the solution would still be to exclude a char.
    > >>> Would it be an option to use some kind of weirder char then a pipe ?
    > >>> Since the pipe is used more often these days for e.g. decorational
    > >>> purposes ...
    > >>
    > >> I can think of two other options:
    > >>
    > >> 1. You can specify a string of more than one character for the
    > >> separator. So even if you permit something like semicolon ";" in
    > >> your strings, you can disallow something less likely to be needed,
    > >> like two semicolons together.
    > >>
    > >> GROUP_CONCAT( ... SEPARATOR ';;')
    > >>
    > >> 2. Use string delimiters (such as a double-quote), in addition to a
    > >> separator:
    > >>
    > >> GROUP_CONCAT( CONCAT('"', e.emotion_name, '"') SEPARATOR ',')
    > >>
    > >> The combination of delimiters and separators make it unambiguous
    > >> whether the comma is a separator or is part of the string in the
    > >> list.
    > >>
    > >> Regards,
    > >> Bill K.
    > >
    > > Thanks Bill!
    > >
    > > I tried
    > > GROUP_CONCAT( CONCAT( ',', e.emotion_name, ',' ) SEPARATOR
    > > '"')
    > >
    > > But AFAIK the result is the same ...
    > > I put a comma in emotion Dramatic for testing purposes, but the result
    > > doesn't do anything different ...
    > > Result looks like: [emotion_list] => ,Drama,tic,",Happy,
    > >
    > > The mysql-manual doesn't really clarify things for me ...
    > > I feel my solution is in this corner, and i already could solve things
    > > by using "forbidden" chars as a delimiter, but i belive there should
    > > be another way ...
    > >
    > > Frizzle.
    >
    > How about using a non-printable value or even a newline value
    Non printable would be great!
    Any suggestions except for newline, because that could be contained in
    some returned strings ... ?

    Frizzle.

    frizzle Guest

  16. #16

    Default Re: many-to-many Question

    frizzle wrote:
    > Bill Karwin wrote:
    >>
    >> GROUP_CONCAT( CONCAT('"', e.emotion_name, '"') SEPARATOR ',')
    >>
    > I tried GROUP_CONCAT( CONCAT( ',', e.emotion_name, ',' ) SEPARATOR '"')
    You used the opposite of what I suggested.

    Regards,
    Bill K.
    Bill Karwin Guest

  17. #17

    Default Re: many-to-many Question


    Bill Karwin wrote:
    > frizzle wrote:
    > > Bill Karwin wrote:
    > >>
    > >> GROUP_CONCAT( CONCAT('"', e.emotion_name, '"') SEPARATOR ',')
    > >>
    > > I tried GROUP_CONCAT( CONCAT( ',', e.emotion_name, ',' ) SEPARATOR '"')
    >
    > You used the opposite of what I suggested.
    >
    > Regards,
    > Bill K.
    I turned it back to what you said, edited the emotion to be
    'Drama"tic', but
    GROUP_CONCAT( CONCAT( '"', e.`emotion_name`, '"' ) SEPARATOR ',')
    still returns "Drama"tic","Happy".

    Frizzle.

    frizzle Guest

Similar Threads

  1. Newbie Question: Biz Card Template Question
    By Thomas_Porter@adobeforums.com in forum Adobe Indesign Windows
    Replies: 4
    Last Post: May 30th, 08:08 AM
  2. Replies: 9
    Last Post: April 27th, 04:44 AM
  3. Pen Tool Use Question. (Embarrassingly Newbie Question)
    By Bozo Schmozo in forum Macromedia Flash
    Replies: 0
    Last Post: November 12th, 10:00 PM
  4. Global variable question question
    By Jason Giangrande in forum PHP Development
    Replies: 3
    Last Post: July 23rd, 08:27 PM
  5. newB question: related tables question
    By Blue man in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: June 30th, 04:13 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139