php - MYSQL: select multiple records from other table for each row -

i have 3 tables - users, items, , relations (each user have more 1 item):

users:

user_id | user_name  1       | alex 2       | john 3       | louis 

items:

item_id | item_name  1       | item 1 2       | item 2 3       | item 3 

relations:

item_id | user_id  1       | 1 2       | 1 1       | 2 3       | 2 2       | 3 3       | 3 3       | 1 

etc..

so need query can users (that's easy) items each user (it's more crazy).

so result need this:

[1] => (    'name' => alex,     'items' => (       [1] => (          'name' => 'item 1'       )       [2] => (          'name' => 'item 2'       )    ) ) [2] => (    'name' => john,     'items' => (       [1] => (          'name' => 'item 1'       )       [2] => (          'name' => 'item 3'       )    ) ) 

i've been playing join couple of hours still can't it.

thank help!

to achieve result 1 want, you'll have manipulation of data in php because mysql doesn't return multi-dimensional arrays. can use joins data you're after though:

select     users.user_name,     items.item_name users left join relations      on relations.user_id = users.user_id left join items      on relations.item_id = items.item_id 

this should return results this:

user_name | item_name alex        item 1 alex        item 2 alex        item 3 

etc. use php group nicely:

$grouped = array(); foreach($your_database_results $row) {     $username = $row['user_name'];     if(!array_key_exists($username, $grouped))         $grouped[$username] = array();     $grouped[$username][] = $row['item_name']; } 

then print_r($grouped) should similar example (i've used name array key instead of first array entry values following - easy enough convert).