CODEWITHSUNDEEP
phpmysql
Sandokan
Sandokan

Reputation: 849

Display data only once

I have created a contactlist for parents in a daycare center. Now I wish to display it on the webpage. So far, it technically works perfect but it's the layout that I'm not happy with.

Short on how my database works: Each parent can have one or more children and each child can have one or more parents - a many to many relationship. I have broken it down to three tables - parents, children and parent_children in order to make a one to many relationship. Then I have used JOIN and everything displays nicely. However, since it show every entity it gets rather cluttered to display it on an webpage. I'll explain with an example:

Family 1: Mark and Alice Wilson have two children, John and Bill.
Family 2: Peter and Jessica Robertson have one child, Lisa.

Current layout:
Parent                Child
-------------------------------
Mark Wilson           John Wilson
Mark Wilson           Bill Wilson
Alice Wilson          John Wilson
Alice Wilson          Bill Wilson

Peter Robertson       Lisa Robertson
Jessica Robertson     Lisa Robertson
-------------------------------

Desired layout:
Parent                Child
-------------------------------
Mark Wilson           John Wilson
Alice Wilson          Bill Wilson

Peter Robertson       Lisa Robertson
Jessica Robertson
-------------------------------

Is there any good way to get the desired layout?

Ok, so now I've come so far as to get this working in ONE column, depending on how I use GROUP_CONCAT:

Code:

SELECT GROUP_CONCAT(parents.name) AS Parents, children.name
FROM parents p
LEFT JOIN parents_children pc USING(id_parent)
LEFT JOIN children c USING(id_child)
GROUP BY pc.id_parent;

Result: 

Parents                             Children
-----------------------------------------------
Mark Wilson, Alice Wilson           Bill Wilson
Mark Wilson, Alice Wilson           John Wilson
Peter Robertson, Jessica Robertson  Lisa Robertson

Likewise, if I GROUP_CONCAT children.name and GROUP BY pc.id_child instead I get:

Parents                Children
------------------------------------------
Mark Wilson            Bill Wilson, John Wilson
Alice Wilson           Bill Wilson, John Wilson
Peter Robertson        Lisa Robertson
Jessica Robertson      Lisa Robertson

I really want a combo of these, with the following result:

Parents                                Children
----------------------------------------------------
Mark Wilson, Alice Wilson              Bill Wilson, John Wilson
Peter Robertson, Jessica Robertson     Lisa Robertson

Upvotes: 1

Views: 383

Answers (4)

phsaires
phsaires

Reputation: 2378

Table parents:

CREATE TABLE `parents` (

id_part int(11) NOT NULL AUTO_INCREMENT, parent varchar(60) DEFAULT NULL, PRIMARY KEY (id_part) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

Table child:

CREATE TABLE `child` (

id_child int(11) NOT NULL AUTO_INCREMENT, child varchar(60) DEFAULT NULL, f_name int(11) DEFAULT NULL, m_name int(11) DEFAULT NULL, PRIMARY KEY (id_child), KEY m_key (m_name), KEY f_key (f_name), CONSTRAINT f_key FOREIGN KEY (f_name) REFERENCES parents (id_part) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT m_key FOREIGN KEY (m_name) REFERENCES parents (id_part) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

Query to join two tables

SELECT DISTINCT

GROUP_concat(child.child), p1.parent, p2.parent FROM child Inner Join parents AS p1 ON p1.id_part = child.f_name Inner Join parents AS p2 ON p2.id_part = child.m_name GROUP BY p1.parent, p2.parent

Result

Children's name | father's name | mother's name

x,y,z | father 1 | mother 1

q,r | father 2 | mother 2

t,j | father 3 | mother 3

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

SELECT
    Parents
  , GROUP_CONCAT(name ORDER BY name) AS Children
FROM
  ( SELECT 
        GROUP_CONCAT(p.id_parent ORDER BY p.name) AS parents_ids
      , GROUP_CONCAT(p.name ORDER BY p.name) AS Parents
      , pc.id_child
      , c.name
    FROM parents p 
      JOIN parents_children pc USING(id_parent)
      JOIN children c USING(id_child)
    GROUP BY pc.id_child
  ) AS tmp 
GROUP BY parents_ids ;

You can test it in: SQL-Fiddle

Upvotes: 1

tereško
tereško

Reputation: 58444

There are two solutions for this issue:

  1. On php side iterate through the array and create an array where key of
    [$parent_1 . '|' . $parent2] contains another array with children.

  2. On SQL end of thing do something similar to this: 

    SELECT 
    GROUP_CONCAT(Parents.name ORDER BY Parents.parents_id, '|') AS parents, 
    Children.name as child
FROM Children 
    LEFT JOIN CildrentParents USING( children_id )
    LEFT JOIN Parents USING( parent_id )
GROUP BY Childrent.children_id

    and then work with this dataset.

P.S. i don't see, how just adding DISTINCT to the original query, would solve this ... maybe i am missing something here.

Update

And what exactly is stopping you from using PHP to create an array, where each parent key contains a list of children?

Upvotes: 0

Hituptony
Hituptony

Reputation: 2860

SELECT DISTINCT should limit them down to one instance of each entity in the parent table.

Upvotes: 2

Related Questions