<?php
date_default_timezone_set("asia/tehran");
include_once'config.php';

$result1 = mysqli_query($connect, "SELECT DISTINCT `api_name`, `result` FROM `request` WHERE DATE(`datetime`) >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK) Order BY `api_name`, `result`;");

$sql = 'SELECT DATE(`datetime`) AS "date", ';
while ($row = mysqli_fetch_assoc($result1)) {
    $sql .= 'SUM(CONCAT_WS(":", `api_name`, `result`) = "' . $row['api_name'] . ':' . $row['result'] .'" OR 0) AS "' . $row['api_name'] . ':' . $row['result'] . '", ';
}
$sql .= '"" FROM `request` 
WHERE DATE(`datetime`) >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)
GROUP BY DATE(`datetime`)
Order BY DATE(`datetime`) desc';

//var_dump($sql);

$result2 = mysqli_query($connect, $sql);

$rows = [];
while ($row = mysqli_fetch_assoc($result2)) {
    $rows []= $row;
}

$thead = "<tr><th>" . implode("</th><th>", array_keys($rows[0])) . "</th></tr>";
$tbody = array_reduce($rows, function($a, $b){return $a.="<tr><td>".implode("</td><td>",$b)."</td></tr>";});
echo "<table border='1' cellspacing='0' width='100%'>\n$thead\n$tbody\n</table>";
