Как распарсить результат запроса MS SQL?

415

14 февраля 2017, 21:46

Есть сервер MS SQL, есть библиотека для работы с данным СУБД - sqlsrv. посылаю запрос:

SELECT * 
FROM (
    SELECT [name], 
           [date], 
           [status], 
           row_number() OVER (PARTITION BY [name] ORDER BY [date] DESC) AS rownum
    FROM [script_monitoring].[dbo].[script_status]
    ) [script_monitoring].[dbo].[script_status] 
WHERE rownum <= 10`

Получаю ответ:

Awayalogs    2017-02-09 23:59:17.363    1    1
Awayalogs    2017-02-08 23:59:15.117    1    2
Awayalogs    2017-02-07 23:59:12.660    1    3
Awayalogs    2017-02-06 23:59:12.580    1    4
Awayalogs    2017-02-05 23:59:16.870    1    5
Awayalogs    2017-02-04 23:59:18.237    1    6
Awayalogs    2017-02-03 23:59:11.577    1    7
Awayalogs    2017-02-02 23:59:16.237    1    8
Awayalogs    2017-02-01 23:59:12.047    1    9
Awayalogs    2017-01-31 23:59:13.300    1    10
DeleteOldBackups    2017-01-31 19:02:20.140    1    1
DeleteOldBackups    2017-01-13 16:39:02.307    0    2

И т.д. (96 строк)

Собственно вопрос как парсить этот ответ, так чтобы на основании каждого уникального имени(awayalogs, Deleteoldbackups) сформировать таблицу типа:

             /awayalogs/                      /DeleteOldBackups/
     awayalogs 2017-02-09 23:59:17.363    1     DeleteOldBackups 2017-01-31 19:02:20.140    1
     awayalogs 2017-02-08 23:59:15.117    1     DeleteOldBackups 2017-01-13 16:39:02.307    0
     awayalogs 2017-02-06 23:59:12.580    1
     awayalogs 2017-02-06 23:59:12.580    1

Вот так я доставал данные, когда мне нужно было все подряд без фильтрации:

$count_rows = sqlsrv_num_rows($stmt);
    echo "<table align=\"center\" border=\"1\" cellpadding=\"7\" cellspacing=\"1\"><tr><th>Имя</th><th>Описание</th><th>Дата</th><th>Статус завершения</th></tr>";
    $n = 0;
    while ($n < $count_rows) {
        echo "<tr>";
        echo "<td>" . sqlsrv_get_field($stmt, 0) . "</td>";
        echo "<td>" . sqlsrv_get_field($stmt, 1) . "</td>";
        echo "<td>" . sqlsrv_get_field($stmt, 2, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR)) . "</td>";
        if (sqlsrv_get_field($stmt, 3) === 0) {
            echo "<td bgcolor=\"#FF0000\">" . sqlsrv_get_field($stmt, 3) . "</td>";
        } else {
            echo "<td bgcolor=\"#CCFF99\">" . sqlsrv_get_field($stmt, 3) . "</td>";
        }
        echo "</tr>";
        $n++;
        $row = sqlsrv_fetch($stmt, SQLSRV_SCROLL_NEXT);
    }
    echo "</table>";

(только не закидывайте минусами я просто не опытен в этом деле не знаю как правильно сформировать вопрос)

Answer 1

$summary = array();
while ($n < $count_rows) {
    $summary[sqlsrv_get_field($stmt, 0)][] = array(
         'first_field' => sqlsrv_get_field($stmt, 1),
         'second_field' => sqlsrv_get_field($stmt, 2),
         ..... 
    );
}
foreach($summary as $name => $values){
    //заголовок таблицы. $name - ваши уникальные имена
    foreach($values as $key => $value){
        ?>
        <tr>
              <td><?php echo $value['first_field'];?></td>
              .......
        </tr>
        <?php
    }
}

Answer 2

$sql_query = "SELECT * FROM(SELECT   [name]
                                    ,[date]
                                        ,[status]
                                        ,row_number() OVER (PARTITION BY [name] ORDER BY [date] DESC) AS rownum
                                        FROM [script_monitoring].[dbo].[script_status]) [script_monitoring].[dbo].[script_status]
                                        where rownum <= 10";
$sql_query2 = "SELECT distinct [name] from [script_monitoring].[dbo].[script_status]";
$params = array();
$options = array("Scrollable" => SQLSRV_CURSOR_KEYSET);
$stmt = sqlsrv_query($conn, $sql_query, $params, $options);
$stmt2 = sqlsrv_query($conn, $sql_query2, $params, $options);
if ($stmt === false) {
    die(print_r(sqlsrv_errors(), true));
}
if (sqlsrv_fetch($stmt) === false) {
    die(print_r(sqlsrv_errors(), true));
}
$names = []; // = 0
$count_rows = sqlsrv_num_rows($stmt); // = ~96
$count_rows2 = sqlsrv_num_rows($stmt2); // = 4
while ($n < $count_rows2) {
    $names[$n] = sqlsrv_get_field($stmt2, 0);
    $row = sqlsrv_fetch($stmt, SQLSRV_SCROLL_NEXT);
    $n++;
}
echo "<ul  class=\"\main_table\"\>";
while ($n < $count_rows2) {
    echo "<li><table id=\"" + $names[$n] + "\"\ class=\"\script_table\"\>";
    echo "<thead>";
    echo "<tr>";
    echo "<th colspan=\"4\">" + $names[$n] + "<span class=\"\close_table_button\"\><span class=\"\ui-icon ui-icon-close\"\></span></span></th>";
    echo "</tr>";
    echo "<tr>";
    echo "<th>Имя</th>";
    echo "<th>Описание</th>";
    echo "<th>Дата</th>";
    echo "<th>Статус</th>";
    echo "</tr>";
    echo "</thead>";
    echo "<tbody>";
    while ($n2 < $count_rows) {
        if ($names[$n] === sqlsrv_get_field($stmt, 0)) {
            echo "<tr>";
            echo "<td>" . sqlsrv_get_field($stmt, 0) . "</td>";
            echo "<td>" . sqlsrv_get_field($stmt, 1) . "</td>";
            echo "<td>" . sqlsrv_get_field($stmt, 2, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR)) . "</td>";
            if (sqlsrv_get_field($stmt, 3) === 0) {
                echo "<td bgcolor=\"#FF0000\">" . sqlsrv_get_field($stmt, 3) . "</td>";
            } else {
                echo "<td bgcolor=\"#CCFF99\">" . sqlsrv_get_field($stmt, 3) . "</td>";
            }
            echo "</tr>";
            $row = sqlsrv_fetch($stmt, SQLSRV_SCROLL_NEXT);
            $n2++;
        }
    }
    echo "</tbody>";
    echo "</li>";
    echo "</ul>";
    $n++;
}