MySQL Query where I add multiple records to a single record

So I'm really bad at wording this so please bear with me.

I am building a site where people can buy gifts for kids and each kid can have multiple gifts in their list.

I have two tables children and gift_list. The tables are very basic with the following rows:

children
  - id
  - name
  - age
  - status

gift_list
  - id
  - child_id
  - description
  - status

I'm trying to grab all the gifts that belong to a child and return them as an array of the children object, but I'm getting strange results.

Here's my current SQL statement:

SELECT * FROM children JOIN gift_list WHERE gift_list.child_id = children.id

The result is:

Child A:
  - id
  - name
  - age
  - status
  - gift A

Child A:
  - id
  - name
  - age
  - status
  - gift B

I know I need to loop through the gift_list table and then add them to an array, but I'm new to MySQL and I'm lost with the concept and can't think of how to properly search for what I'm trying to accomplish. The idea end result would be:

Child A:
  - id
  - name
  - age
  - status
  - gifts
     - gift A
        - description
        - status
     - gift B
        - description
        - status

Any help would be much appreciated! As a side note and probably important, I'm using the PHP framework CodeIgniter.

EDIT:

After several failed attempts using the suggestions below I finally got the following code to work...there may be a better开发者_开发问答/cleaner way to do this...if so please clue me in :)

$children = $this->db->get('children');
$gift_list = $this->db->get('gift_list');

foreach ( $children->result() as $child )
{
    $gifts = array();
    foreach ( $gift_list->result() as $gift )
    {
        if ( $child->id == $gift->children_id )
        {
            array_push($gifts, $gift);
            $child->gifts = $gifts;
        }
    } 
}

echo "<pre>";
print_r($children->result());
echo "</pre>";

Thanks for all the suggestions!

---

You need to perform 2 different queries. The first to get the children, the other to get their gifts.

Example of the first query:

SELECT * FROM children WHERE ... any children-related criteria here ...

You should now have the array (no gifts yet):

Child A:
  - id (let's say this equals 1)
  - name
  - age
  - status

Child A:
  - id (let's say this equals 2)
  - name
  - age
  - status

It's important that the keys of that array are the children IDs (well, at least it will make things easier).

Using that array, build the following query:

SELECT * FROM gift_list WHERE child_id IN (1, 2);

Which you can do using something like:

$gifts_sql = 'SELECT * FROM gift_list WHERE child_id IN (' . implode(', ', array_keys($children)) . ')';

You should now loop over the result of that query and attach it like that:

while ($row = mysql_fetch_assoc($result)) {
    $children[$row['child_id']]['gifts][$row['gift_id']] = $row;
}

Your $children array should now look like how you want it to be.

---

You say you're getting strange results, but not what those results are. If you're iterating over the results of the query somehow, maybe you need to order the results? Add something like

ORDER BY children.id, gift.id

to the end, maybe.