Selecting one row with multiple children

[Mezzi]

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

[guido2004]

Join the two tables together:

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

[Mezzi]

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

[guido2004]

Of course. You'll have to loop through the result and check for a change of user details in your code.

[Mezzi]

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!