CODEWITHSUNDEEP
phpmysqljoingroup-bygroup-concat
Binyamin
Binyamin

Reputation: 7803

How to group on PHP MySQL JOIN results?

Expected result:

Berries: blueberry, raspberry, strawberry
Citrus: grapefruit, lime
Pear


SQL:

CREATE TABLE IF NOT EXISTS `fruits` (
  `id` varchar(8) NOT NULL,
  `group` varchar(8) NOT NULL,
  `name` varchar(250) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `fruits` (`id`, `group`, `name`) VALUES
('03E7', '', 'Berries'),
('0618', '03E7', 'blueberry'),
('051B', '03E7', 'raspberry'),
('02AA', '03E7', 'strawberry'),
('035F', '', 'Citrus'),
('07A5', '035F', 'grapefruit'),
('0633', '035F', 'lime'),
('05E1', '', 'Pear');

Current PHP:

<?php

header("Content-Type: text/plain");

mysql_connect("localhost", "root", "");
mysql_select_db("test");

$query = mysql_query("SELECT a.name as `group`, b.name as name FROM fruits a LEFT JOIN fruits b ON b.`group` = a.id WHERE a.`group` = ''");

if ($query) {
    while ($row = mysql_fetch_array($query)) {
        echo $row['group'] . ': ' . $row['name'] . "\n";        
    }
    mysql_free_result($query);
}

?>

Current result:

Berries: blueberry
Berries: raspberry
Berries: strawberry
Citrus: grapefruit
Citrus: lime
Pear:

Upvotes: 2

Views: 2141

Answers (2)

Binyamin
Binyamin

Reputation: 7803

Thanks @ExplosionPills discovering me about MySQL GROUP_CONCAT I have updated my snippet that returns the expected result:

<?php

$query = mysql_query("SELECT a.name as `group`, GROUP_CONCAT(b.name ORDER BY b.name SEPARATOR ', ') as name FROM fruits a LEFT JOIN fruits b ON b.`group` = a.id WHERE a.`group` = '' GROUP BY a.name");

if ($query) {
    while ($row = mysql_fetch_array($query)) {
        $group = $row['group'];
        $name  = $row['name'];

        if (isset($name)) {
            echo $group . ': ' . $name . "\n";
        } else  {
            echo $group . "\n";
        }
    }
    mysql_free_result($query);
}

?>

SQL result:

Showing rows 0 - 2 ( 3 total, Query took 0.0004 sec).

group   | name
--------+--------
Berries | blueberry, raspberry, strawberry
Citrus  | grapefruit, lime
Pear    | NULL

PHP result:

Berries: blueberry, raspberry, strawberry
Citrus: grapefruit, lime
Pear

Everyone is welcome for any better implement with better improvement?

Upvotes: 2

Explosion Pills
Explosion Pills

Reputation: 191729

SELECT a.name, GROUP_CONCAT(b.name) ... WHERE a.group = '' GROUP BY a.name

In summary, you need a GROUP BY clause, and the grouped column needs to be aggregated in some way (otherwise you will get only one value). If this doesn't work, you can easily do it in PHP code with the results you got:

$fruits = array();
while ($row = fetch($query)) {
   //Can be skipped, but you will get notices
   if (!isset($fruits[$row['group']]) {
      $fruits[$row['group']] = array();
   }
   $fruits[$row['group']] = $row['name'];
}

Upvotes: 2

Related Questions