Display multiple row data within 1 row MySQL PHP

Question

I have the following query which pulls from 3 tables. I'll end up with 1 family per row but with multiple children for each. I want to be able to show all children ages within the family row. I thought about opening another connection/query, but figured there is a smarter way.

Query:

SELECT 
    families.*, job.*, children.*, families.first_name AS fam_firstname, children.first_name AS child_firstname
FROM job
    LEFT OUTER JOIN families ON job.fam_id = families.fam_id
    LEFT OUTER JOIN children ON families.fam_id = children.fam_id
WHERE 
    job.published = 2 
GROUP BY job.job_id
ORDER BY job.created_on DESC

Loop:

if ($result = $mysqli->query($query)) {

    $from = new DateTime($row['dob']);
    $to   = new DateTime('today');

    while ($row = $result->fetch_assoc()) {
         echo '<tr>';
         echo '<td>' .$row['fam_firstname']. '</td>';
         echo '<td>' .$row['last_name'].'</td>';

         /* Looking to list all children ages. Separate by comma or break  */
         echo '<td>' . $from->diff($to)->y .'</td>';

         echo '</tr>';
    }

    $result->free();
}

Desired Output:

Family First Name  |   Family Last Name   |   Child 1 Age, Child 2 Age

score 2 · Accepted Answer · answered May 20 '15 at 17:59

You need to use the mysql group_concat function to achieve this:

SELECT 
    families.*, group_concat(children.age)
FROM job
    LEFT OUTER JOIN families ON job.fam_id = families.fam_id
    LEFT OUTER JOIN children ON families.fam_id = children.fam_id
WHERE 
    job.published = 2 
group by families.fam_id

ORDER BY job.created_on DESC

score 0 · Answer 2 · edited May 23 '17 at 12:14

Follow this question: Nested Array from multiple tables.

Refer to the second option in the question, that explains how you subtract your data from the JOIN query.

P.S. I'ts a question I've asked myself, with an implementation with what your'e trying to do here. If you need more lead on how to implement it here, ask in comments...

Here is a way to implement it in your code (notice you should order your JOIN query by "fam_firstname", for this code to work for you):

/* init temp vars to save current family's data */
$current = null;
$fam_firstname = null;
$children = array();
while ($row = mysql_fetch_assoc($result))
{
    /*
       if the current id is different from the previous id:
       you've got to a new family.
       print the previous family (if such exists),
       and create a new one
    */
    if ($row['fam_firstname'] != $fam_firstname )
    {
        // in the first iteration,
        // current (previous family) is null,
        // don't print it
        if ( !is_null($current) )
        {
            $current['children'] = $children;
            /*
                Here you print the whole line
                I'm just dumping it all here, but you can print
                it more nicer...
            */
            var_dump($current);
            $current = null;
            $fam_firstname = null;
            $children = array();
        }

        // create a new family
        $current = array();
        $current['fam_firstname'] = $row['fam_firstname'];
        /*
            Add more columns value here...
        */
        // set current as previous id
        $fam_firstname = $current['fam_firstname'];
    }

    // you always add the phone-number 
    // to the current phone-number list
    $children[] = $row['child_firstname'] . " is " . $row['child_age'] . " years old";
    }
}

// don't forget to print the last family (saved in "current")
if (!is_null($current))
    /*
            Here you print the whole line
            I'm just dumping it all here, but you can print
            it more nicer...
    */
    var_dump($current);

This is awesome, thanks. Giving it a shot will let you know how it goes — Klav, May 20 '15 at 18:00
@Klav, I've added a code implementation specified for your query in my answer, see above... don't forget to order your query also by "family_name", in order for it to work properly — Captain Crunch, May 20 '15 at 18:27
Considering that mysql already provides this functionality (see my answer), not sure why you want to do this in php... — Nagasimha Iyengar, May 20 '15 at 18:28
1. Thnx! I didn't know about that functionality (group_concat), so thnx I've also learned something from here. 2. If he just wants to show a single column data, "group_concat" is the best. but if he wants to show more columns data, like: `"{$kidFirstName}-{$kidLastName}'s age is: {$kidAge}"`, and/or add some design HTML tags in the middle, mybe it'll be better to leave the design for PHP, and getting the data to SQL? I'm not sure what's better, what do you think? — Captain Crunch, May 20 '15 at 18:40

Display multiple row data within 1 row MySQL PHP

2 Answers2