How to get all images which belongs to user in one "loop"

I have two mysql tables called users and images. Every user have up to 5 pictures. In a page I show user info开发者_C百科rmation plus all images. When I do query like this

SELECT
  SQL_CALC_FOUND_ROWS,
  u.username,
  u.gender,
  u.etc
  .........
  i.picture
FROM users u
LEFT JOIN images i on u.id = i.id
WHERE 
 some_condition

The result is repeating user information for every image. For example

• userX age19 pic 1
• userX age19 pic 2
• userX age19 pic 3
• etc..

How to do so the result to be user information with all images which belongs to him ?

For example:

username

age

sex

pic1, pic2, pic3

etc.

---

I suggest you to run two queries, one for users and second for each user pictures. Trying to get it in one query and to be structured in a way you want it is to complicated.

---

$rowset = array(); // your fetched data

$user = $rowset[0];
unset($user['picture']);
$user['pictures'] = array();

foreach ( $rowset as $row ) {
  $user['pictures'][] = $row['picture'];
}

---

you can use mysql's group_concat.

SELECT SQL_CALC_FOUND_ROWS,
  u.username,
  u.gender,
  u.etc,
  ...
  GROUP_CONCAT(i.picture SEPARATOR ",") AS pictures
FROM users u
LEFT JOIN images i on u.id = i.id
WHERE 
  some_condition
GROUP BY u.id

I haven't tested it right now, but I think my memory isn't failing. You'll have all pictures comma-separated in "pictures" column.