show child records separated by commas

I apologize if the title of the question doesn't match the question but I didn't know how else to describe it. Here is an example of my database table which shows the item and an ID. The ID from the below table is actually from a defect table which has an ID and description. This is from a previous question I posted on EE where I was selecting reasons via checkboxes. For simplicity I have used "car" and "bike". In the real world these would be unique identifiers.

Item     Defect
  car          5
  car          3
  car          7
  bike        9
  bike        2
  bike        5

I want to present this data in a html table that would look like this:

Car      Scratch, Broken window, burst tyre
Bike    Broken light, Missing seat, Scratch

I tired using GROUP BY which helped to just show one of each thing instead of doing this:

Car
Car
Car

but then it doesn't show all of the reasons, it only shows one.

My current SQL:

SELECT `product_no`, `commit_date`, `non_green_desc` FROM `non_green` AS ng INNER JOIN `non_green_reasons` AS ngr ON ng.`ng_id` = ngr.`ng_id` WHERE `complete_date` = ? GROUP BY `tank_no`

Open in new window

LVL 1
Black SulfurAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Might be easier to get this in PHP instead of trying to do it all in MySQL.  Or maybe use two queries.  I think you want to get three rows for Car, but just display Car one time, and only display that column again when its value changes to Bike.  It's a little bit of logic, but only a little.

Also, you can put your questions into more than one Topic Area.  The MySQL topic area seems appropriate for this.
0
Black SulfurAuthor Commented:
Yes, that's it. I want to get three rows but only show Car once.
0
Black SulfurAuthor Commented:
I THINK I have to use a multidimensional array. I am going to try play around with that....
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Ray PaseurCommented:
Most of this is just setup.  The moving parts start at the end of the script at line 72.
https://iconoun.com/demo/temp_black_sulfur.php
<?php // demo/mysqli_example.php
/**
 * https://www.experts-exchange.com/questions/29012010/show-child-records-separated-by-commas.html
 */
error_reporting(E_ALL);
echo '<pre>';


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE item_defects
( id     INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, name   VARCHAR(24) NOT NULL DEFAULT ''
, d_key  INT NOT NULL DEFAULT 0
)
"
;
$mysqli->query($sql);

$mysqli->query("INSERT INTO item_defects (name, d_key) VALUES ('car', 5)");
$mysqli->query("INSERT INTO item_defects (name, d_key) VALUES ('car', 3)");
$mysqli->query("INSERT INTO item_defects (name, d_key) VALUES ('car', 7)");

$mysqli->query("INSERT INTO item_defects (name, d_key) VALUES ('bike', 9)");
$mysqli->query("INSERT INTO item_defects (name, d_key) VALUES ('bike', 2)");
$mysqli->query("INSERT INTO item_defects (name, d_key) VALUES ('bike', 5)");

// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE defects
( id     INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, d_key  INT         NOT NULL DEFAULT 0
, defect VARCHAR(24) NOT NULL DEFAULT ''
)
"
;
$mysqli->query($sql);

$mysqli->query("INSERT INTO defects (d_key, defect) VALUES (5, 'Scratch')");
$mysqli->query("INSERT INTO defects (d_key, defect) VALUES (3, 'Broken Window')");
$mysqli->query("INSERT INTO defects (d_key, defect) VALUES (7, 'Burst Tyre')");
$mysqli->query("INSERT INTO defects (d_key, defect) VALUES (9, 'Broken Light')");
$mysqli->query("INSERT INTO defects (d_key, defect) VALUES (2, 'Missing Seat')");


$sql = "SELECT name, defect FROM item_defects, defects WHERE item_defects.d_key = defects.d_key ORDER BY name DESC";
$res = $mysqli->query($sql);

$out = [];
$old = FALSE;
while ($row = $res->fetch_object())
{
    if ($row->name != $old)
    {
        $old = $row->name;
        $out[$row->name] = ucfirst($row->name) . ': ' . $row->defect;
    }
    else
    {
        $out[$row->name] .= ', ' . $row->defect;
    }
}

var_dump($out);

Open in new window

0
Black SulfurAuthor Commented:
Cool. So, I got this to work using my data and var_dump($out) but when trying to put it into a table it just shows one reason, instead of all of them.

 $old = $row->name;
echo "<tr>";
echo "<td>" . $out[$row->name] = ucfirst($row->name) . "</td>";
echo "<td>" . $row->defect . ", " . "</td>";
echo "</tr>";

Open in new window

0
Ray PaseurCommented:
Maybe something like this...
$sql = "SELECT name, defect FROM item_defects, defects WHERE item_defects.d_key = defects.d_key ORDER BY name DESC";
$res = $mysqli->query($sql);

$out = [];
$old = FALSE;
while ($row = $res->fetch_object())
{
    $row->name = ucfirst($row->name);
    if ($row->name != $old)
    {
        $old = $row->name;
        $out[$row->name] = $row->defect;
    }
    else
    {
        $out[$row->name] .= ', ' . $row->defect;
    }
}

echo '<table>' . PHP_EOL;
foreach ($out as $thing => $row_data)
{
    echo '<tr>';
    echo '<td>' . $thing    . '</td>';
    echo '<td>' . $row_data . '</td>';
    echo '</tr>';
    echo PHP_EOL;
}
echo '</table>' . PHP_EOL;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Black SulfurAuthor Commented:
That's perfect! Now all I have to do is really look at your code and figure out how and why it works exactly. No point in just using it if I don't understand it.
1
Ray PaseurCommented:
By way of explanation, we SELECT two columns from the database: name, defect.  We keep one instance of the name in the array key in $out, and we concatenate the instances of defect in the array value associated with that key.  This gives us a key => value association that makes for easy reorganization into a tabular display.
1
Black SulfurAuthor Commented:
Thanks, Ray. I need another <td> but can't seem to show data from a third column in the database. Must I open a related question or ask here?
0
Ray PaseurCommented:
Might be worth another question.  If you can post your CREATE TABLE statement so we can see the column names and data types, that might be helpful.  If you can tell us what the third column is named, that will help, too.
0
Black SulfurAuthor Commented:
Cool, will do so.
0
Ray PaseurCommented:
10-4, will look for the Q
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.