How to show multiple child records for one parent record in an HTML table

Question

If I had a database table containing an item and whatever was wrong with that item, for example:

Car    Scratch
Car    Dent
Car    Rust
Bike   Broken light
Bike   Rust

How would I display this in an HTML table in the following format:

Car    Scratch, Dent, Rust
Bike   Broken light, Rust

To be insert into an HTML table as:

<tr>$name(Car)</tr>
<tr>$defects(scratch, dent, rust)</tr>

score 1 · Accepted Answer · edited May 23 '17 at 12:25

1

you can update your mysql query which select your item_name using your category as like your requirement

SELECT category, GROUP_CONCAT(CAST(item as CHAR)) as item_name FROM `table_name` group by category;

this is your final query

SELECT tank_no, GROUP_CONCAT(ng_id) as item_name FROM non_green GROUP BY `tank_no`;

where category is car,bike etc and item is scratch, dent, rust

so when you select this then you can print category and item_name and no need to process in php section.Check this attach file

more details you can check this also How to use GROUP BY to concatenate strings in MySQL?

edited May 23 '17 at 12:25

Community

1
1

answered Mar 29 '17 at 19:54

Shafiqul Islam

5,570
2
34
43

This is pretty cool! But for some reason it is leaving out the first database record and only starting from the second one? – Iggy's Pop Mar 29 '17 at 20:03
you can use order by – Shafiqul Islam Mar 29 '17 at 20:06
Like this? SELECT category, GROUP_CONCAT(CAST(item as CHAR)) as item_name FROM `table_name` group by category ORDER BY category DESC; – Iggy's Pop Mar 29 '17 at 20:08
yes it will order by category name, if you want to show car first then use ORDER BY category desc – Shafiqul Islam Mar 29 '17 at 20:12
Still doesn't show the very first database record. Weird – Iggy's Pop Mar 29 '17 at 20:13
can you give me your filed name? first filed and second filed? – Shafiqul Islam Mar 29 '17 at 20:14
SELECT `tank_no`, GROUP_CONCAT(CAST(`ng_id` as CHAR)) as `item_name` FROM `non_green` GROUP BY `tank_no – Iggy's Pop Mar 29 '17 at 20:15
SELECT tank_no, GROUP_CONCAT(ng_id) as item_name FROM non_green GROUP BY `tank_no`; – Shafiqul Islam Mar 29 '17 at 20:20
Thank you. That still only shows 2 out of 3 database records – Iggy's Pop Mar 29 '17 at 20:23
if you want to show more then select other filed also – Shafiqul Islam Mar 29 '17 at 20:24
easier for me just to show you with text. The database data looks like this: [code]Car Scratch Car Dent Car Rust Bike Broken light Bike Rust Plane Dent[code] In my it displays everything except Plane Dent – Iggy's Pop Mar 30 '17 at 16:47
I don't know how to format what I am trying to post here – Iggy's Pop Mar 30 '17 at 16:48
I can't seem to get the formatting right so just put my problem into JSFiddle https://jsfiddle.net/dn5r3rgz/#&togetherjs=GJnw7B4ihv – Iggy's Pop Mar 30 '17 at 18:40
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/139526/discussion-between-shafiqul-islam-and-jonathan). – Shafiqul Islam Mar 30 '17 at 18:52
http://chat.stackoverflow.com/rooms/139526/discussion-between-shafiqul-islam-and-jonathan – Shafiqul Islam Mar 30 '17 at 18:53
Okay, please check PM – Iggy's Pop Mar 31 '17 at 10:13

How to show multiple child records for one parent record in an HTML table

1 Answers1