SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Mar 2006
    Location
    UK
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting one row with multiple children

    Hi,

    I would like to think that I have a good understanding of mysql and always try to ensure I do things in the correct and most optimized way which is why Im posting. I cant get my head around how to do this query or if its even possible in one query so if anyone could help that would be great.

    I have two db tables for example "users" and "pets". Each row of the "pets" table has a foreign key referencing the id of a user. Each "user" may have multiple pets. Each "pet" only belongs to one "user".

    Users
    id
    name
    age

    Pets
    id
    user_id
    name
    type

    I want to select all "users" and also all "pets" belonging to each "user".

    I would usualy do one query that selects all "users" and then loop through the results and within each row/loop I would run another query and select all "pets" belonging to that "user" and start building up my multi dimensional array.

    I dont believe running mysql queries inside a loop is a good idea let alone a loop that could potentialy be thousands of iterations.

    Can anyone help me with the theory of how I should tackle this? Even if it means changing the structure of my tables.

    Thanks

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Join the two tables together:
    Code MySQL:
    SELECT
        u.id AS userid
      , u.name AS username
      , u.age
      , p.id AS petid
      , p.name AS petname
      , p.type
    FROM Users u
    INNER JOIN Pets p
    ON u.id = p.user_id

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2006
    Location
    UK
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi thanks for taking the time to reply.

    I have tried this before and user JOIN quite a lot but I then get multiple user details. So if a user has two pets I get two copys of that user, one merged into each pet.

    I end up with an array like

    Matt
    22
    Fred Basset
    Dog

    Matt
    22
    Bugs Bunny
    Rabbit

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Of course. You'll have to loop through the result and check for a change of user details in your code.

  5. #5
    SitePoint Zealot
    Join Date
    Mar 2006
    Location
    UK
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok cool, so long as I know that is the best method thats great. I needed to make sure I was going to tackle it with "best practice".

    So I can then loop through my results and check each time if I have already added the user to my final array, if I have just add another array within that users row that contains the additional pets details.

    Thanks for your help and time youve been very helpful!


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
  •