How to parse the result of an MS SQL query?

Question

There is a MS SQL server, there is a library for working with this DBMS - sqlsrv. send request:

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`

I get the answer:

 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

Etc. (96 lines)

Actually the question is how to parse this answer, so that on the basis of each unique name (awayalogs, Deleteoldbackups) form a table of the type:

  /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

That's how I got the data when I needed everything without filtering:

 $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>";

(just don’t throw in minuses I’m just not experienced in this business I don’t know how to form a question correctly)

You apparently did not understand, as a result of the request, you get a response like the last 10 lines for each "name" there are many of them, where 1 line looks like / name / date / status / line number /
I request the filtered data, and I want to scatter them into different tables using php, and here in general, what kind of data is there?
The request is arranged, it also displays the data as it should be shown, I just do not understand how to generate it in HTML and how to organize cycles for output.
The cycle should be displayed 1 table, then another, then I draw them up with cascading style sheets as necessary

rjhdby rjhdby 12.7k four 22 45 · Accepted Answer · 2017-02-13T13:26:06

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

Look at the code below, besides the fact that it is an ugly code (I admit it), can this work?
more detailed source codes here github.com/Vankalif/script_mon

Answer 2 · 2017-02-13T14:53:04

 $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++; }

In theory, it should not work, since sqlsrv_fetch will scroll to the end in the very first iteration of the outer loop.
And how to change the condition that the cycle selects only the necessary values?
And in your case, after the first while (which is from the full file) and inside the loop after n++ , you must reset the result set pointer to the first line sqlsrv_fetch($stmt, SQLSRV_SCROLL_FIRST)
@rjhdby look above, I added the whole so that the whole picture of actions was visible.

How to parse the result of an MS SQL query?

2 answers 2

More articles: