Join 1 Row to Multiple Rows in PDO

Question

Here is my scenario:

Database Name: Children
+-------------+---------+---------+
| child_id   | name      | user_id |
+-------------+---------+---------+

    1           Beyonce     33
    2           Cher        33
    3           Madonna     33
    4           Eminem      33


Database Name: Parents
+-------------+---------+---------+
| parent_id   | child_id   | parent_name |
+-------------+---------+---------+

    1           1           Obama
    2           1           Michelle
    3           4           50cents
    4           4           Gaga


Desired Output:
+-------------+---------+---------+
| child_id   | name      | parent Name |
+-------------+---------+---------+
    1           Beyonce     Obama (Row 1) Michelle (Row 2)

PHP SQL Query in PDO:

$sql = "SELECT  Children.child_id, Children.name, Parents.parent_name
        FROM Children               
        LEFT JOIN Parents
            ON Children.child_id = Parents.child_id
        WHERE Children.user_id = ?
        ";

$stmt = $db_PDO->prepare($sql); 

if($stmt->execute(array($userId))) // $userId defined earlier
{
        // Loop through the returned results
        $i = 0;

        foreach ($stmt as $row) {
            $fetchArray[$i] = array (
                'childId' => $row['child_id'],
                'childName' => $row['name'],    
                'parentName' => $row['parent_name'],    
                // How do I save the multiple parents from other rows here ????
                );                  
            $i++;
        }             
}

How can I run a query that Joins 1 row to multiple rows in second table in PDO? I have read other topics here but I am unsure. Is it easier to add a second query that gets the linked parents for each child_id separately in a loop? I am worried that will be too much query. Can someone help me solve this?

Answer 1

Execute the below query . You will get the output as required, i just used the group by which will group the records as per the selected column

select a.child_id, name ,group_concat(parent_name) from children a, parents b where a.child_id =b.child_id group by a.child_id

Answer 2

Well, took me some fiddling to test it all out but here you go. Unfortunately one cannot easely pivot tables in mysql but there are alternatives.

http://sqlfiddle.com/#!9/1228f/26

SELECT GROUP_CONCAT(
  CONCAT_WS(':', Parents.parent_id,Parents.parent_name) ) FROM Parents where Parents.child_id=1

;
SELECT
  Children.child_id,
  Children.name,
  GROUP_CONCAT(
  CONCAT_WS(':', Parents.parent_id,Parents.parent_name)    ) as parents
FROM
  Children
  LEFT JOIN Parents
        ON Children.child_id = Parents.child_id
        WHERE Children.user_id = 33
  Group by Children.child_id

This query uses the group concat to concatenate all resulsts we want into a colon seperated string with the values we want, and comma's between the individual fields.

We could do some tricky magic to make them individual fields but that would break our php because we wouldnt know how much fields each query would return(adopted, orphan, no known parents, etc...)

In php you could feed them into an object

$parents = array();
$loop1 = explode(',',$row['parents']);
    foreach($loop1 as $parentset) {
        $parentdetail = explode(":",$parentset);// decide yourself how much detail you want in here... I jsut went with name and id.
        $parent = new stdClass();
        $parent->id = $parentdetail[0];
        $parent->name = $parentdetail[1];
        array_push($parents,$parent);
    }

var_dump($parents);

Answer 3

HI this query works only if you are passing child id ,

select a.child_id, name ,group_concat(parent_name ) parent_name from children a, parents b where a.child_id =b.child_id and a.child_id=1

here i am using a function called group_concat which is used for concatinating the rows.It automatically takes the rows whose count is greater than 1.So no need of the extra code again