· 9 years ago · Nov 02, 2016, 09:26 PM
1id meta_value field_id item_id
2
31 Ankur Fname 1
42 Gupta Lname 1
53 AAS Holdings Company 1
64 Christie Fname 2
75 Trojillo Lname 2
86 AWS Company 2
97 Devadanam Fname 3
108 Pulukuri Lname 3
119 AAS Holdings Company 3
1210 Radha Fname 4
1311 Neupane Lname 4
14......> There is no "Company" for that person
1512 Marites Fname 5
1613 Torsar Lname 5
1714 AAS Holdings Company 5
1815 Muthanna Fname 6
1916 Shareef Lname 6
2017 GFTC Company 6
21
22First Name Last Name Company
23
24Ankur Gupta ASS Holdings
25Christie Trojillo AWS
26
27$sql="SELECT * FROM system_frm_item_metas ORDER BY item_id";
28$result=mysqli_query($con,$sql);
29if ($result->num_rows > 0) {
30
31echo "<table><tr><th>First Name</th><th>Last Name</th><th>Company</th></tr>";
32while($row = $result->fetch_assoc()) {
33 if ($row["field_id"] == "Fname") { echo "<tr><td>".$row["meta_value"]."</td>";}
34 if ($row["field_id"] == "Lname") { echo "<td>".$row["meta_value"]."</td>";}
35 if ($row["field_id"] == "Company") { echo "<td>".$row["meta_value"]."</td></tr>";}
36}
37echo "</table>";
38
39DROP TABLE IF EXISTS eav_hell;
40
41CREATE TABLE eav_hell
42(entity INT NOT NULL
43,attribute VARCHAR(12) NOT NULL
44,value VARCHAR(30) NOT NULL
45,PRIMARY KEY(entity,attribute)
46);
47
48INSERT INTO eav_hell VALUES
49(1 ,'Fname','Ankur'),
50(1 ,'Lname','Gupta'),
51(1 ,'Company','AAS Holdings'),
52(2 ,'Fname','Christie'),
53(2 ,'Lname','Trojillo'),
54(2 ,'Company','AWS'),
55(3 ,'Fname','Devadanam'),
56(3 ,'Lname','Pulukuri'),
57(3 ,'Company','AAS Holdings'),
58(4 ,'Fname','Radha'),
59(4 ,'Lname','Neupane'),
60(5 ,'Fname','Marites'),
61(5 ,'Lname','Torsar'),
62(5 ,'Company','AAS Holdings'),
63(6 ,'Fname','Muthanna'),
64(6 ,'Lname','Shareef'),
65(6 ,'Company','GFTC');
66
67SELECT entity
68 , MAX(CASE WHEN attribute = 'Fname' THEN value END) Fname
69 , MAX(CASE WHEN attribute = 'Lname' THEN value END) Lname
70 , MAX(CASE WHEN attribute = 'Company' THEN value END) Company
71 FROM eav_hell
72 GROUP
73 BY entity;
74
75+--------+-----------+----------+--------------+
76| entity | Fname | Lname | Company |
77+--------+-----------+----------+--------------+
78| 1 | Ankur | Gupta | AAS Holdings |
79| 2 | Christie | Trojillo | AWS |
80| 3 | Devadanam | Pulukuri | AAS Holdings |
81| 4 | Radha | Neupane | NULL |
82| 5 | Marites | Torsar | AAS Holdings |
83| 6 | Muthanna | Shareef | GFTC |
84+--------+-----------+----------+--------------+
85
86SELECT DISTINCT e.entity
87 , f.value fname
88 , l.value lname
89 , c.value company
90 FROM eav_hell e
91 LEFT
92 JOIN eav_hell f
93 ON f.entity = e.entity
94 AND f.attribute = 'fname'
95 LEFT
96 JOIN eav_hell l
97 ON l.entity = e.entity
98 AND l.attribute = 'lname'
99 LEFT
100 JOIN eav_hell c
101 ON c.entity = e.entity
102 AND c.attribute = 'company';