1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

get all tags for an item

Discussion in 'MySQL' started by harsom, Jan 4, 2015.

  1. #1
    Hi

    I have the following MySql database structure:

    `items` table:
    id | itemid | title
    ---+---------------
    1 | 314 | Black hat
    2 | 587 | Golden Pencil

    `tags` table:
    id | tag
    ---+--------
    1 | clothes
    2 | pens
    3 | presents


    `items_tags` table:
    id | item | tag
    ---+------+----
    1 | 314 | 1
    1 | 314 | 3
    1 | 587 | 2
    1 | 587 | 3

    I'm trying to create an sql query to get all items including their tags in one query (I use it in the main for the main page).
    this is what I tried:

    SELECT i.* items, t.tag
    FROM items i
    JOIN items_tags it ON i.id = it.item
    JOIN tags t ON it.tag = t.id
    it gives a row of the item for every tag in database.
    for example if an item as 5 tags, it will give me 5 rows for that item.
    I need one row per item with tags as a string, or array or something.
    maybe an Inner join or a sub query.... I'm new to this but very willing to learn...

    can you help ?
     
    harsom, Jan 4, 2015 IP
  2. Pigeon Yoga

    Pigeon Yoga Active Member

    Messages:
    52
    Likes Received:
    4
    Best Answers:
    1
    Trophy Points:
    63
    #2
    Try using GROUP_CONCAT with a GROUP statement. http://www.mysqltutorial.org/mysql-group_concat/
     
    Pigeon Yoga, Jan 4, 2015 IP
    sarahk likes this.