RIX RIX - 1 month ago 6
MySQL Question

View data from a pivot table (Vertical mysql table)

I'm really confused with pivot tables, please see what I have and what I need to achieve:

This is my mysql table structure

id meta_value field_id item_id

1 Ankur Fname 1
2 Gupta Lname 1
3 AAS Holdings Company 1
4 Christie Fname 2
5 Trojillo Lname 2
6 AWS Company 2
7 Devadanam Fname 3
8 Pulukuri Lname 3
9 AAS Holdings Company 3
10 Radha Fname 4
11 Neupane Lname 4
......> There is no "Company" for that person
12 Marites Fname 5
13 Torsar Lname 5
14 AAS Holdings Company 5
15 Muthanna Fname 6
16 Shareef Lname 6
17 GFTC Company 6


I need to be able to present the data in HTML table using php mysql in that way

First Name Last Name Company

Ankur Gupta ASS Holdings
Christie Trojillo AWS


And I also need to be able to sort that table by Company for example.

Here is what I tried to do but I know its totally wrong because the database could have only the Fname and Lname without the company which will cause to escape and use the next value and break the table and also the data could be stored in different order

$sql="SELECT * FROM system_frm_item_metas ORDER BY item_id";
$result=mysqli_query($con,$sql);
if ($result->num_rows > 0) {

echo "<table><tr><th>First Name</th><th>Last Name</th><th>Company</th></tr>";
while($row = $result->fetch_assoc()) {
if ($row["field_id"] == "Fname") { echo "<tr><td>".$row["meta_value"]."</td>";}
if ($row["field_id"] == "Lname") { echo "<td>".$row["meta_value"]."</td>";}
if ($row["field_id"] == "Company") { echo "<td>".$row["meta_value"]."</td></tr>";}
}
echo "</table>";


Any help will be really appreciated

Answer
DROP TABLE IF EXISTS eav_hell;

CREATE TABLE eav_hell
(entity INT NOT NULL
,attribute VARCHAR(12) NOT NULL
,value VARCHAR(30) NOT NULL
,PRIMARY KEY(entity,attribute)
);

INSERT INTO eav_hell VALUES
(1 ,'Fname','Ankur'),
(1 ,'Lname','Gupta'),
(1 ,'Company','AAS Holdings'),
(2 ,'Fname','Christie'),
(2 ,'Lname','Trojillo'),
(2 ,'Company','AWS'),
(3 ,'Fname','Devadanam'),
(3 ,'Lname','Pulukuri'),
(3 ,'Company','AAS Holdings'),
(4 ,'Fname','Radha'),
(4 ,'Lname','Neupane'),
(5 ,'Fname','Marites'),
(5 ,'Lname','Torsar'),
(5 ,'Company','AAS Holdings'),
(6 ,'Fname','Muthanna'),
(6 ,'Lname','Shareef'),
(6 ,'Company','GFTC');

SELECT entity
     , MAX(CASE WHEN attribute = 'Fname' THEN value END) Fname
     , MAX(CASE WHEN attribute = 'Lname' THEN value END) Lname
     , MAX(CASE WHEN attribute = 'Company' THEN value END) Company
  FROM eav_hell
 GROUP
    BY entity;

+--------+-----------+----------+--------------+
| entity | Fname     | Lname    | Company      |
+--------+-----------+----------+--------------+
|      1 | Ankur     | Gupta    | AAS Holdings |
|      2 | Christie  | Trojillo | AWS          |
|      3 | Devadanam | Pulukuri | AAS Holdings |
|      4 | Radha     | Neupane  | NULL         |
|      5 | Marites   | Torsar   | AAS Holdings |
|      6 | Muthanna  | Shareef  | GFTC         |
+--------+-----------+----------+--------------+

or (faster but less elegant)...

SELECT DISTINCT e.entity
              , f.value fname
              , l.value lname
              , c.value company
           FROM eav_hell e
           LEFT 
           JOIN eav_hell f
             ON f.entity = e.entity
            AND f.attribute = 'fname'
           LEFT 
           JOIN eav_hell l
             ON l.entity = e.entity
            AND l.attribute = 'lname'
           LEFT 
           JOIN eav_hell c
             ON c.entity = e.entity
            AND c.attribute = 'company';