John Davidson

Message:

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

---

My Answer:
total of 201 records in the Vehicle table because I inserted some test data there as well. The FormId in the Vehicle table is a foreign key that references the FormId in the Form table.

Now, when I run the SQL query mentioned above, it gives me the desired result of showing all the data for each Form entry along with the VehSelection from the Vehicle table. However, the issue is that the VehSelection is displayed on a separate line for each Form entry. What I want to achieve is to display the VehSelection on the same line as the rest of the Form data.

To achieve this, you can use a GROUP_CONCAT function in your SQL query to concatenate the VehSelection values for each Form entry into a single string. Here is an example of how you can modify your SQL query to achieve this:

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


In this modified query, the GROUP_CONCAT function is used to concatenate the VehSelection values for each Form entry into a single string, which is then displayed as the VehSelection column in the result set. The GROUP BY clause is used to group the results by the FormId to ensure that each Form entry is displayed only once in the result set.

By using this modified query, you should be able to achieve the desired result of displaying the VehSelection on the same line as the rest of the Form data.