SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2013
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    data in mySQL column

    hello guys, i have 2 or more different data need to be inserted into 1 column in mysql database. Example:

    name = john (1st column)
    blood_type = B (2nd column)
    children = mary,keith,alex (3rd column)

    name = kenneth
    blood_type =A
    children = Oliver,Lich

    in my program, i need to display/select name who had one of their children named alex. How do i call it??

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,077
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    You shouldn't store that information in one table, but in two tables, like

    people
    Code:
    id | name   | blood_type
    ------------------------
    1  | John   | B
    2  | Kennth | A
    children
    Code:
    id | parent_id | name
    ---------------------
    1  | 1         | Mary
    1  | 1         | Keith
    1  | 1         | Alex
    1  | 2         | Oliver
    1  | 2         | Lich
    And then to query

    Code sql:
    SELECT
        p.name
    FROM
        people AS p
        INNER
          JOIN children AS c
    ON
        p.id = c.parent_id
    WHERE
        c.name = 'Alex'

    OR you could put it all in one table and join that table on itself

    people
    Code:
    id | parent_id | name    | blood_type
    -------------------------------------
    1  | (NULL)    | John    | B
    2  | (NULL)    | Kenneth | A
    1  | 1         | Mary    | (NULL)
    1  | 1         | Keith   | (NULL)
    1  | 1         | Alex    | (NULL)
    1  | 2         | Oliver  | (NULL)
    1  | 2         | Lich    | (NULL)
    Code sql:
    SELECT
        parent.name
    FROM
        people AS parent
        INNER
          JOIN people AS child
    ON
        parent.id = child.parent_id
    WHERE
        child.name = 'Alex'

    The way you're storing the data now doesn't scale and will give you more problems than benefits.
    Last edited by ScallioXTX; May 11, 2013 at 00:33. Reason: Qualified columns in SELECT -- thanks Rudy :)
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    rémon, the name column in your SELECT clause needs to be qualified
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Jan 2013
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    You shouldn't store that information in one table, but in two tables, like

    people
    Code:
    id | name   | blood_type
    ------------------------
    1  | John   | B
    2  | Kennth | A
    children
    Code:
    id | parent_id | name
    ---------------------
    1  | 1         | Mary
    1  | 1         | Keith
    1  | 1         | Alex
    1  | 2         | Oliver
    1  | 2         | Lich
    And then to query

    Code sql:
    SELECT
        name
    FROM
        people AS p
        INNER
          JOIN children AS c
    ON
        p.id = c.parent_id
    WHERE
        c.name = 'Alex'

    OR you could put it all in one table and join that table on itself

    people
    Code:
    id | parent_id | name    | blood_type
    -------------------------------------
    1  | (NULL)    | John    | B
    2  | (NULL)    | Kenneth | A
    1  | 1         | Mary    | (NULL)
    1  | 1         | Keith   | (NULL)
    1  | 1         | Alex    | (NULL)
    1  | 2         | Oliver  | (NULL)
    1  | 2         | Lich    | (NULL)
    Code sql:
    SELECT
        name
    FROM
        people AS parent
        INNER
          JOIN people AS child
    ON
        parent.id = child.parent_id
    WHERE
        child.name = 'Alex'

    The way you're storing the data now doesn't scale and will give you more problems than benefits.
    What does the id column in children table does? what if different parents have the same child name?

  5. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,077
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    rémon, the name column in your SELECT clause needs to be qualified
    Ah yes, good catch.

    For query1 it should be SELECT p.name, and for query 2 it should be SELECT parent.name
    I've amended my previous post.

    Quote Originally Posted by Marvin_G View Post
    What does the id column in children table does?
    At this moment nothing, but it might be needed for later use. Plus, we need at least one column to be unique for referencing purposes. (well, not strictly so, but it's very handy nonetheless)

    Quote Originally Posted by Marvin_G View Post
    what if different parents have the same child name?
    Just put it in there multiple times, no problem.

    people
    Code:
    id | name   | blood_type
    ------------------------
    1  | John   | B
    2  | Kennth | A
    children
    Code:
    id | parent_id | name
    ---------------------
    1  | 1         | Oliver
    1  | 1         | Lich
    1  | 2         | Oliver
    1  | 2         | Lich
    In that case John has two children named Oliver and Lich, and Kenneth also has two children named Oliver and Lich.

    There is another solution in which you only need to store unique child names once to save storage, but given how dirt cheap disk space is now, plus that it's less semantically correct, I really wouldn't opt for that.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    less semantically correct? wha?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,077
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    less semantically correct? wha?
    Because you don't know if they have the same child, or a different child who happens to have the same name. When using separate tables this distinction can be made. When using a many-to-many table however it can not.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    different children with the same name would require different PKs

    anything else, and ur doin it wrong™
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,077
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    different children with the same name would require different PKs

    anything else, and ur doin it wrong™
    I know. That's why I said 'there is another way I but highly recommend against it'
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that method works fine, but it's needlessly complex

    it would store each child name once, right? so that automatically means a separate name table, and then the actual child data row (in its parent-child table) would use a FK to get the name, but it would still have it's own unique PK separate from the name

    anything else, and ur doin it wrong™
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Jan 2013
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all the information but i just curious, is it possible select 1 out of 3 different data in 1 column?

  12. #12
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,077
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Marvin_G View Post
    Thanks for all the information but i just curious, is it possible select 1 out of 3 different data in 1 column?
    No, that's why we suggested using two tables instead.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


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
  •