Как сделать MySQL Join и использовать PHP для получения значений

Я пытаюсь написать запрос MySQL для извлечения данных в этом формате:

<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>

Таблицы в базе данных отформатированы следующим образом.

Таблица: user_data

user_id      |     first_name     |    last_name     |
------------------------------------------------------
1            |     Jeff           |    Smith         |
------------------------------------------------------
2            |     Bob            |    Smith         |
------------------------------------------------------
3            |     Steve          |    Smith         |
------------------------------------------------------
4            |     Mary           |    Smith         |
------------------------------------------------------
5            |     Anna           |    Smith         |
------------------------------------------------------

Таблица: настраиваемые поля

custom_field_id   |    name         |
-------------------------------------
3                 |    field name   |
-------------------------------------
5                 |    field name   |
-------------------------------------
7                 |    field name   |
-------------------------------------
9                 |    field name   |
-------------------------------------
11                |    field name   |
-------------------------------------

Таблица: 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      |
------------------------------------------------------

Я ищу лучшее решение для запроса данных, а затем распечатать их на экране с помощью PHP или AJAX. Это возможно? Было бы лучше использовать JSON?
И образец запроса был бы отличным.

К вашему сведению: в конечном итоге все извлекаемые данные необходимо будет отфильтровать на экране. Спасибо за помощь.

Мой желаемый результат будет

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                |
-------------------------------------------------------------------------------------------------------------------------------------------------------------

3

Решение

Вы можете использовать group_concat функция, https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat, с моим предыдущим запросом, чтобы получить одну запись на пользователя …

Если бы у вас был SQLfiddle, я мог бы проверить это, но у меня нет данных для тестирования …

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

Я проверил это на 3 таблицах, которые у меня были с похожей настройкой, поэтому я думаю, что это должно работать; имена могут быть изменены.

Обновить:

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
3

Другие решения

Давайте начнем с простого соединения вашего custom_field_data в user_data таблица, в которой значения и имена полей жестко закодированы:

SELECT
u.*,
f1.value as "<Field 1 Name>",
...
f2.value as "<Field N Name>"FROM
user_data u LEFT JOIN
custom_field_data f1 ON u.user_id = f1.user_id AND f1.custom_field_id = 1
LEFT JOIN
custom_field_data f ON u.user_id = fn.user_id AND fn.custom_field_id = <N>

Теперь, если вы хотите извлечь данные без жесткого кодирования, вам нужно будет динамически строить SQL на основе данных в custom_fields Таблица:

SELECT * FROM custom_fields;

Если некоторые из ваших настраиваемых полей никогда не используются по отношению к пользователям, вы можете ограничить количество настраиваемых полей следующим образом:

SELECT * FROM custom_fields f
WHERE EXISTS
(SELECT * FROM custom_field_data
WHERE f.custom_field_id = custom_field_id)

Наконец, для построения необходимого SQL в PHP вам понадобятся следующие строковые разделы:

// Begin SELECT clause (static):
$sql = "SELECT u.*,"
// Add all fields that you selected form the custom_fields:
foreach ($result as $row) { // Don't forget to handle commas
$sql = $sql + <expression that gets codes and name>
}

// Start FROM clause (static):
$sql = $sql + " FROM user_data u "

// Join to custom_field_data:
foreach ($result as $row) {
$sql = $sql + " LEFT JOIN custom_field_data <alias expression> ON ..
}

В конце вы должны получить строку с SQL, которая присоединит пользователей к custom_field_data для каждого доступного настраиваемого поля.

1

можно потянуть данные с $.ajax() а также PHP, Создать файл data.php мы собираемся загрузить этот файл с ajax, В вашем data.php файл написать этот код.

$query = mysqli_query("select * from custom_field_data
inner join
user_data on user_data.user_id = custom_field_data.user_id
inner join
custom_fields on custom_field_data.custom_field_id = custom_fields.custom_field_id");
<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>

На вашей главной странице используйте button загрузить данные.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script type="text/javascript">
$(function(){
$('#load-data').click(function(){
$.post('data.php', function(data){
$('#results').html(data);
});
});
});
</script>

пример на sqlfiddle

1

На мой взгляд, лучше всего использовать соединение, потому что это уменьшит размер данных, передаваемых между БД и PHP

Вы можете использовать JSON, поскольку это легкий вес.

Вы можете скрыть вещи в скрытом поле и использовать их для дальнейшей фильтрации на стороне клиента.

Обновить —

Для сервера Sql я бы написал такой запрос

select
t1.user_name,
t1.first_name,
t1.last_name,
(select name from custom_fields
where custom_field_id= t2.custom_field_id)
from
user_data t1 left join
custom_field_data t2 on t1.user_id=t2.userid

Измените это согласно my-sql

-2
По вопросам рекламы [email protected]