Fetch from table, switch rows and columns and filter by another column value

Question

Presumably this question has been answered elsewhere but i've not managed to accomplish what im trying to do so far (reeeaal beginner over here).

What im trying to do is retrieve data from a table called "wp_iphorm_form_entry_data" which looks something like this:

%/ element_id / value      /%
-----------------------------
%/ 4          / first name /%
%/ 5          / last name  /%
%/ 42         / e-mail     /%

Then switch the rows and columns and rename them, something like this:

%/ 4 (f-name)  / 5 (l-name) / 42 (e-mail) /%
--------------------------------------------
  first name  / last name  / e-mail      /%

And finally filter the entries by another "element_id". Thank you in advance, any takers?

How do you know what elements and values belong in the same row? — xQbert, Nov 24 '14 at 22:57

score 0 · Answer 1 · edited May 23 '17 at 12:27

This is the way it was done before pivot function existed

SELECT userGroupingID
       MAX(case when element_Id=4 then value else null end) as `First Name`,
       MAX(case when element_Id=5 then value else null end) as `Last Name`,       
       MAX(case when element_Id=42 then value else null end) as `e-mail`
FROM  wp_iphorm_form_entry_data
GROUP BY userGroupingID

UserGroupingID is some value which denotes grouped elements like a userID or a groupID for the set of elements. Without this, I can't see how you could accomplish what you're requesting for more than 1 row of data. of course with mysql's extension to the group by statement... if you don't care which values are returned for each element ID, you don't need userGroupingID in teh select, or a group by at all..

Pivot could do this simpler: example MySQL pivot table

But you will have the same problem with it. What denotes how the elements are grouped?

how to show its results in php? and element_Id are coming randomly(as variable) — Fas M, Nov 24 '14 at 23:06

score 0 · Answer 2 · answered Nov 24 '14 at 23:19

What you have to do there is, just run fetch all your results you need and initialize them to array, then print them the way you want

$el_id=[]; $val=[];   //create an array to store your results from db
$link = mysqli_connect("myhost","myuser","mypassw","mybd") or die("Error".mysqli_error($link)); //conection
$sql="select element_id,value  from wp_iphorm_form_entry_data";
$result = $link->query($sql); //execute the query.

while($row = mysqli_fetch_array($result)) {  //store your results into arrays 
$el_id = $row["element_id"]; 
 $val  = $row["value"];
} 

print "<table><tr><th>$el_id[0] (f-name)</th><th>$el_id[1] (l-name)</th><th>$el_id[2] (e-mail)</th></tr>";
//foreach($el_id as $index=>$elementId){ // i comment this since your example is not clear to me
 echo '<tr><td>$val[0]</td><td>$val[1]</td><td>$val[]</td>';
//}

Fetch from table, switch rows and columns and filter by another column value

2 Answers2