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