Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Concatenate MySQL result (Join?)

I have the following MySQL tables:

CREATE TABLE `person` (
  `id` int NOT NULL AUTO_INCREMENT,
  `reference` varchar(100) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `person` (`id`, `reference`, `email`) VALUES
(1, 'PK001',    'paulk@gmail.com');

CREATE TABLE `review` (
  `id` int NOT NULL AUTO_INCREMENT,
  `review_type` varchar(255) NOT NULL,
  `review_body` varchar(255) NOT NULL,
  `person_id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `review` (`id`, `review_type`, `review_body`, `person_id`) VALUES
(1, 'Personality',  'He has a great personality!',  1),
(2, 'Skills',   'He has multiple skills!',  1);

If I run the following PHP:

$sql = "SELECT * FROM person, review WHERE person.id = review.person_id;";
$result = $con->query($sql);

while($row = $result->fetch_assoc())  {
    echo $row['review_body'];
    echo ' | ';
    echo $row['review_body'];
    echo '<br>';
}

My output is:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

He has multiple skills! | He has multiple skills!
He has a great personality! | He has a great personality!

I would prefer to have it like this:

He has multiple skills! | He has a great personality!

I imagine I will have to wrangle my MySQL query but really not sure where to begin to achieve it this. I would really appreciate some guidance.

>Solution :

You can do it as follows :

SELECT p.id, group_concat(r.review_body)
FROM person p
inner join review r on r.person_id = p.id
group by p.id

Using group_concat to concat data from a group

Demo here

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading