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?
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);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments