Data showing for all results I want to show for specific foreign key only

Question

I am no longer pursuing to display the old data which I had before I got the normalization part working in my script.

This thread is now about trying to get the VehSelection displaying on the same line. Please continue reading to understand what I am trying to achieve.

Code I was looking for in my original question is SELECT VehicleId, VehSelection, Form.FormId, Form.FirstName, Form.LastName, Form.Email, Form.Age, Form.Birthdate, Form.FavLanguage FROM Vehicle INNER JOIN Form ON Form.FormId = Vehicle.FormId

I combined 2 SQL select statements into 1 to do the job. This SQL gives me the result I wanted. The situation is confusing so I will explain it.

My database looks like this

Form (FormId, Name, Lastname, age, birthday, email, FavLanguage, Vehicle)

Vehicle (VehicleId, FormId (FK), VehSelection)

Form table - has 186 records. Vehicle table has 201 records in total. This is the confusing part I will try to explain. I created an INSERT Form to start with and I have 186 records in Form Table, I was inserting test data as I went along. I have a "Vehicle" field in the Form table and I was planning to insert the Type of vehicle in there (plane etc.) Then I remembered what If there is more than 1, how would I store that. So I used IMPLODE, knowing that wasn't going to be my final solution, but I went ahead and did it anyway. So saving it like this in one field, comma separated: Plane, Yacht, Supercar. But that goes against 1NF, so I decided to store them as separate records into a Vehicle table.

So I stored them in the Vehicle table like this: If FormId 132 selects 2 of the 3 boxes, it will store like this: 132 Plane, 132 Yacht, as separate records. This part is working fine. There are 3 check boxes in total, one is a Plane, another is Yacht, and the final one is Supercar

Now I have a view.php page which lists all of the records from the Form table with the SQL at the top of this post. And next to each record there is an "Edit" and "Delete" link.

Now What I want is to display the VehSelection on the same line. So if someone has a Plane and a Yacht, it will show that on one line, instead of 2 lines as separate records.

Display data: (Updated) ** (form View.php)**

<?php 

    if ($table) {
        foreach ($table as $d_row) {
            ?>
            <tr>
                <td><?php echo($d_row["FirstName"]); ?></td>
                <td width="10"> </td>
                <td><?php echo($d_row["LastName"]); ?></td>
                <td width="10"> </td>
                <td><?php echo($d_row["Email"]); ?></td>
                <td width="10"> </td>
                <td><?php echo($d_row["Age"]); ?></td>
                <td width="10"> </td>
                <td><?php echo($d_row["Birthdate"]); ?></td>
                <td width="10"> </td>
                <td><?php echo($d_row["FavLanguage"]); ?></td>
                <td width="10"> </td>
                <td><?php echo($d_row["VehSelection"]); ?></td>
                <td width="10"> </td>
                <td><?php echo("<a href='edit1.php?user_id=" . $d_row["FormId"] . "'>Edit</a>"); ?></td>
                <td width="10"> </td>
                <td> <?php echo("<a href='delete_feedback.php?user_id=" . $d_row["FormId"] . "'>Delete</a>"); ?></td>
            </tr>
            <?php 
        }
    }
?>

Sample data from Form Table:

191 tom smith [email protected] 33 09-06-1997 CSS NULL
192 Frank Lampard [email protected] 39 10-06-1992 CSS NULL
193 John Atkins [email protected] 23 11-07-2006 JavaScript NULL

Note The NULLS are from the redundant Vehicle attribute in the Form table

Sample data from Vehicle table:

216 191 Plane
217 192 Yacht
218 192 SuperCar
219 192 Plane
220 193 SuperCar
221 193 Plane

Sample data from view.php

191  tom  smith [email protected]  33  9-06-1997  CSS  Plane
192  Frank  Lampard  [email protected]  39  10-06-1992  CSS  Yacht
192  Frank  Lampard  [email protected]  39  10-06-1992  CSS  SuperCar   
192  Frank  Lampard  [email protected]  39  10-06-1992  CSS  Plane
193  John   Atkins   [email protected]  23  11-07-2006  JavaScript  SuperCar
193  John  Atkins  [email protected]  23  11-07-2006  JavaScript  Plane

This is what my desired output is:

192  Frank  Lampard  [email protected]  39  10-06-1992  CSS  Yacht, Supercar, Plane

Answer 1

The easiest way to do this is to use MySQL's GROUP_CONCAT function to pull all the vehicle names from the Vehicle table, and put them into a comma-separated list in a single column of the SQL result set.

SELECT 
 Form.FormId, 
 Form.FirstName, 
 Form.LastName, 
 Form.Email, 
 Form.Age, 
 Form.Birthdate, 
 Form.FavLanguage, 
 GROUP_CONCAT(Vehicle.VehSelection SEPARATOR ', ') AS VehSelection
FROM 
  Vehicle 
  INNER JOIN Form 
    ON Form.FormId = Vehicle.FormId
GROUP BY 
  Form.FormId

Based on your sample data, this will output:

191 Tom Smith     [email protected]     33  1997-06-09  CSS         Plane
192 Frank Lampard [email protected] 39  1992-10-06  CSS         Yacht, SuperCar, Plane
193 Jon Atkins    [email protected]     23  2006-07-11  JavaScript  SuperCar, Plane

Live demo: http://sqlfiddle.com/#!9/a167841/3

Then it's just a case of looping through the data once in PHP to echo it, as per your current PHP code.

Further info on GROUP_CONCAT:

• Official documentation: https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat
• Tutorials:
  • https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php
  • https://www.mysqltutorial.org/mysql-group_concat/

---

The rest of the SQL source code posted here for completeness, in case SQLFiddle becomes inaccessible:

create table form (FormId int, FirstName varchar(50), LastName varchar(50), Email varchar(100), Age int, Birthdate date, FavLanguage varchar(20));
create table vehicle  (VehicleId int, FormId int, VehSelection varchar(50));
insert into form (FormId, FirstName, LastName, Email, Age, Birthdate, FavLanguage) VALUES (191, 'Tom', 'Smith', '[email protected]', 33, '1997-06-09', 'CSS'),(192, 'Frank', 'Lampard', '[email protected]', 39, '1992-10-06', 'CSS'),(193, 'Jon', 'Atkins', '[email protected]', 23, '2006-07-11', 'JavaScript');
insert into vehicle (VehicleId, FormId, VehSelection) VALUES (216, 191, 'Plane'), (217, 192, 'Yacht'), (218, 192, 'SuperCar'), (219, 192, 'Plane'), (220, 193, 'SuperCar'), (221, 193, 'Plane');