I am trying to write a mysql query to pull data in this format:
<table>
<caption>table title and/or explanatory text</caption>
<thead>
<tr>
<th>User ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Field Name 1</th>
<th>Field Name 2</th>
<th>Field Name 3</th>
<th>Field Name 4</th>
<th>Field Name 5</th>
</tr>
</thead>
<tbody>
<?php
while ($row = mysqli_fetch_array($query)) {
echo "<tr>
<td>" .$row{'user_id'}. "</td>
<td>" .$row{'first_name'}. "</td>
<td>" .$row{'last_name'}. "</td>
<td>" .$row{'field_name_1'}. "</td>
<td>" .$row{'field_name_2'}. "</td>
<td>" .$row{'field_name_3'}. "</td>
<td>" .$row{'field_name_4'}. "</td>
<td>" .$row{'field_name_5'}. "</td>
</tr>";
}
?>
</tbody>
</table>
The tables in the database are formatted like the following.
Table: user_data
user_id | first_name | last_name |
------------------------------------------------------
1 | Jeff | Smith |
------------------------------------------------------
2 | Bob | Smith |
------------------------------------------------------
3 | Steve | Smith |
------------------------------------------------------
4 | Mary | Smith |
------------------------------------------------------
5 | Anna | Smith |
------------------------------------------------------
Table: custom_fields
custom_field_id | name |
-------------------------------------
3 | field name |
-------------------------------------
5 | field name |
-------------------------------------
7 | field name |
-------------------------------------
9 | field name |
-------------------------------------
11 | field name |
-------------------------------------
Table: custom_field_data
user_id | custom_field_id | value |
------------------------------------------------------
1 | 3 | XXXX |
------------------------------------------------------
1 | 5 | BBBB |
------------------------------------------------------
1 | 7 | CCCC |
------------------------------------------------------
1 | 9 | ZZZZ |
------------------------------------------------------
1 | 11 | YYYY |
------------------------------------------------------
2 | 3 | XXXX |
------------------------------------------------------
2 | 5 | BBBB |
------------------------------------------------------
2 | 7 | CCCC |
------------------------------------------------------
2 | 9 | ZZZZ |
------------------------------------------------------
3 | 3 | XXXX |
------------------------------------------------------
3 | 5 | BBBB |
------------------------------------------------------
3 | 9 | ZZZZ |
------------------------------------------------------
3 | 11 | YYYY |
------------------------------------------------------
I am looking for the best solution for querying the data and then printing it to the screen using PHP or AJAX. Is this possible? Would it be better to use json? And a sample query would be great.
FYI: In the long run all the data being extracted will need to be filtered on the screen. Thanks for the help.
My desired output would be
user_id | first_name | last_name | custom_field_3 | custom_field_5 | custom_field_7 | custom_field_9 | custom_field_11 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | Jeff | Smith | XXXX | BBBB | CCCC | ZZZZ | YYYY |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
2 | Bob | Smith | XXXX | BBBB | CCCC | ZZZZ | YYYY |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
3 | Steve | Smith | XXXX | BBBB | CCCC | ZZZZ | YYYY |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
4 | Mary | Smith | XXXX | BBBB | CCCC | ZZZZ | YYYY |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
5 | Anna | Smith | XXXX | BBBB | CCCC | ZZZZ | YYYY |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
You can use the group_concat function, https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat, with my previous query to get a single record per user...
If you had a SQLfiddle I could test this out but as is I don't have any data to test with...
select ud.firstname, ud.lastname, group_concat(cf.name)
from user_data as ud
join custom_field_data as cfd
on cfd.user_id = ud.user_id
join custom_fields as cf
on cf.custom_field_id = cfd.custom_field_id
I tested this on 3 tables I have with a similar setup so I think it should work; names may need to be tweaked.
Update:
select ud.firstname, ud.lastname, group_concat(cf.name)
from user_data as ud
join custom_field_data as cfd
on cfd.user_id = ud.user_id
join custom_fields as cf
on cf.custom_field_id = cfd.custom_field_id
group by ud.user_id