We can use the function. GROUP_CONCAT()which allows for the removal of lists of each group received GROUP BY♪ Usually. GROUP_CONCAT() the name of the pole, but it'll take two meanings - date and gold♪ They can be combined with help. CONCAT() Some kind of unique divider, like a bar.SELECT
GROUP_CONCAT(CONCAT(data, "#", gold)) AS data_gold
FROM
gold
GROUP BY
name
+-------------------------------------------+
| data_gold |
+-------------------------------------------+
| 2016-08-27#10,2016-08-28#15,2016-08-29#20 |
| 2016-08-27#20,2016-08-28#25,2016-08-29#30 |
| 2016-08-27#30,2016-08-28#35,2016-08-29#40 |
| 2016-08-27#40,2016-08-28#45,2016-08-29#50 |
+-------------------------------------------+
In the PHP code, the line can be broken by comma (e.g. function) explode()And then every element of the mass that has been obtained is broken again by # GROUP_CONCAT allows for grading, and on the date received, you will always be able to determine which cell should be present.The following PHP code may be used to form the resulting table:<?php
try {
$pdo = new PDO(
'mysql:host=localhost;dbname=test',
'root',
'',
array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
function parse_gold($str) {
$elements = explode(',', $str);
$arr = array();
foreach($elements as $el) {
list($date, $gold) = explode('#', $el);
$arr[$date] = $gold;
}
return $arr;
}
$query = "SELECT
name,
GROUP_CONCAT(CONCAT(data, '#', gold)) AS data_gold
FROM
gold
GROUP BY
name";
$usr = $pdo->query($query);
$users = array();
while($user = $usr->fetch()) {
$users[$user['name']] = parse_gold($user['data_gold']);
}
$dates = array();
foreach($users as $user) {
$dates += array_keys($user);
}
echo '<table border="1">';
// Шапка
echo '<tr><td>Имя</td>';
foreach($dates as $date) {
echo "<td>$date</td>";
}
echo '</tr>';
// Содержимое таблицы
foreach($users as $name => $golds) {
echo '<tr>';
echo "<td>$name</td>";
foreach($dates as $date) {
if(array_key_exists($date, $golds)) {
echo "<td>{$golds[$date]}</td>";
} else {
echo "<td>-</td>";
}
}
echo '</tr>';
}
echo '</table>';
} catch (PDOException $e) {
echo "Ошибка выполнения запроса: " . $e->getMessage();
}
