-2

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:

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.

Shadow
  • 33,525
  • 10
  • 51
  • 64
michaelmcgurk
  • 6,367
  • 23
  • 94
  • 190
  • `FROM person, review WHERE person.id = review.person_id` is not how a `join` should be written in 2023. `FROM person as p join review as r on p.id = r.person_id` – user3783243 Feb 02 '23 at 17:07
  • You also don't need an `echo` for every PHP line. `echo $row['review_body'] . ' | ' . $row['review_body'] . '
    ';`     – user3783243 Feb 02 '23 at 17:08
  • @user3783243 Thank you. Would you be able to add as an Answer. I've tried one answer given so far but unsuccessful sadly : ( – michaelmcgurk Feb 02 '23 at 17:13
  • @Stu I'm looking into this but stumbling in the dark. Trying with `SELECT person.id, GROUP_CONCAT(review.review_body SEPARATOR ' ') FROM person INNER JOIN review ON person.id = review.person_id GROUP BY person.id` – michaelmcgurk Feb 02 '23 at 17:17

3 Answers3

3

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

SelVazi
  • 10,028
  • 2
  • 13
  • 29
1

you are calling the two tables separately, you need to make an inner join between the two

exemple:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

or

see this link

Bruno Santos
  • 35
  • 5
  • Thank you so much. I've updated my SQL to: `$sql = "SELECT review_body FROM person INNER JOIN review ON person.id = review.person_id";` but it's still the same output :( Would you be able to show me based on my example above how to implement? – michaelmcgurk Feb 02 '23 at 17:12
1

you should index your shared id in both tables and use foreign keys for better performance. this approach is based on the Group concat function.

if you want 1 row for all results.

SELECT 1,GROUP_CONCAT( review_body SEPARATOR ' | ') as goruped_result 
FROM person
INNER JOIN review
ON review.person_id = person.id;

if you want 1 row per person.id

SELECT person.id,GROUP_CONCAT( review_body SEPARATOR ' | ') as   
goruped_result 
FROM person
INNER JOIN review
ON review.person_id = person.id
group by person.id
Amin S
  • 546
  • 1
  • 14