Joining table data

Question

I have a table of:

id   title     type   expl   bubble_content   onfocus   req   dorder   label    mirror
1    Fullname  1      1      Your fullname    Yes       0     0        0        NULL

Then another table of:

id     fieldid    relid    dorder
4      1          2        0
5      1          1        0

How would I join the two tables so that the result would be something like:

0 => array(
     'id' => 1,
     'title' => 'Fullname',
     .... etc ....
     'relid' => 2,
     'relid' => 1),
1 => array(
     .... etc ....
))

I've tried using INNER JOIN / LEFT JOIN but this produces two rows/arrays for each relid, I would really like all the data for the particular fieldid to exist within the same array, as illustrated above.

Answer 1

You can't have 2 keys with the same name in an array. On your example you have 2 'relid', the second one will overwrite the first.

You can code PHP so that it merges those rows into one:

    $output = array();
    while ($row = mysql_fetch_assoc($result))
    {
        // Capture all values for this row first.
        // If this is the new row from the first table, store.
        if (!isset($output[$row['id']]))
        {
            $output[$row['id']] = $row;

            // Make a new array for relids.
            $output[$row['id']]['relids'] = array();
        }


        $output[$row['id']]['relids'][] = $row['relid']; 

    }

Your output array will look like this:

0 => array(
 'id' => 1,
 'title' => 'Fullname',
 .... etc ....
 'relids' => array(2, 1),
1 => array(
 .... etc ....
))