Good day, I ask for help, the situation is this: there is a table in MySql: MySql table

task: bring it to the page in the form: The final view on the php page

I created the table header as follows:

<thead> <tr> <th> </th> <?php if ($mysqli->connect_errno) { printf("Соединение не удалось: %s\n", $mysqli->connect_error); exit(); } $sqltableclandate = "SELECT * FROM `nametable` GROUP BY Date"; $sqltableclanResultdate = $mysqli->query($sqltableclandate); while($tableclandate = $sqltableclanResultdate->fetch_assoc()) {?> <th><?php echo $tableclandate['Date'];?></th> <? }?> </tr> </thead>

But further plugging ....

Tell me where to dig and where to look)

I forgot to add yesterday: Addendum: if there is no information for a certain date, then the cell should be 0 or N / A

Adding all table code at the moment:

 <?php require_once("includes/connection_new.php"); ?> <div class="content"> <div id="TableClan"> <h2>Статистика:</h2> <table class="tablesorter" id="myTable"> <thead> <tr> <th>Дата</th> <?php $query_dateclan = "SELECT * FROM `DBname`.`stats` GROUP BY Date"; if($resultclandate = $mysqli->query($query_dateclan)){ $rowclandate=$resultclandate->fetch_assoc(); $kol_rowsclandate = $resultclandate->num_rows; for($i=0; $i<$kol_rowsclandate; $i++){?> <th> <? echo $rowclandate['Date']; ?> </th> <? }}?> </tr> </thead> <tbody> <tr> <td><? echo $rowclandate['name'] ?></td> </tr> </tbody> </table> </div> <!-- тело любых страниц.php --> </div>

base view: table: stat (as you can see name5 appeared later) enter image description here

    2 answers 2

    You can use the GROUP_CONCAT() function, which allows you to display lists of each of the groups received by GROUP BY . Usually in GROUP_CONCAT() transfer the name of the column, but here you need two values ​​- date and gold . They can be combined with CONCAT() with some unique separator, for example, a grid.

     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 PHP code, you can first split a string by a comma (for example, by explode() function), and then each element of the resulting array can be broken again into #. GROUP_CONCAT allows sorting, and based on the received date you can always determine which cell the current item should belong to.

    To generate the resulting table, you can use the following PHP code:

     <?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(); }
    • Could you give an example of this breakdown regarding my case, unfortunately my knowledge is not enough to understand without an example ( - Cheshire
    • @Cheshire added the answer. - cheops
    • unfortunately, when executing, swears on line 7 of your code: "Parse error: syntax error, unexpected '['" - Cheshire
    • @Cheshire probably doesn’t have the latest PHP version that doesn’t work with the new array syntax. Corrected the answer, using the old syntax. - cheops
    • everything worked, only in the query after the FROM table name: test . Thank you very much, can you tell me what to read on this topic? - Cheshire

    pull the data from the database and save it to the array, and then through the usual foreach you build the table, this is an example, if you don’t know how, throw the whole file I will help

     <?php foreach ($category as $cat): ?> <tr> <td><?php echo $cat['id']; ?></td> <td><?php echo $cat['name']; ?></td> <td><?php echo $cat['sort_order']; ?></td> </tr> <?php endforeach; ?>
    • Which file to throw, the full code or type of database? - Cheshire
    • all that is throw - Dima
    • added to main message - Cheshire