How do I re-arrange table data?
How do I re-arrange table data?
Hiebs915
Posts: 14Questions: 3Answers: 0
Description of problem:
I have a table that is produced using data echoed from a PHP backend. The table currently shows all the data returned from the server and displays it in the format it comes in. I'd like to limit which columns it displays (I was able to accomplish this already), re-arrange the data, and assign row names to the table. This is my first time doing a lot of this work but I'm trying to learn. If there's a better method for showing you my code, please let me know. I'd like to make this as easy as possible for you guys to help me. I can provide screenshots, code, or files.
Here's my jQuery to produce the table:
$(function sendYearMonthToServer() {
$("span").click(function(event) {
let $target = $(event.target);
let month = ($target.closest("span").html()).substring(5,7);
let year = ($target.closest("span").html()).substring(0,4);
let queryString = window.location.search;
let urlParams = new URLSearchParams(queryString);
let term = urlParams.get('rptType');
let monthYearTermArray = [month,year,term];
let myJSON = JSON.stringify(monthYearTermArray);
$("#clickedTable").dataTable({
"paging": false,
"ordering": false,
"info": false,
"searching": false,
"ajax": {
"url": "monthlyProductionReports_backend.php",
"type": "POST",
"data": {"monthYearTerm": monthYearTermArray}},
"columns": [
{ "data": "RptKey"},
{ "data": "RptTypeKey"},
{ "data": "SumItemTypeKey"},
{ "data": "MthlyTot"},
{ "data": "YearToDateTot"},
{ "data": "CumTot"},
{ "data": "ActiveWells"}
]
});
});
});
Here's the PHP code that gets the data from our SQL Server:
<?php
include 'oilgas/includes/MSSQLTestServerConnection.php';
if (empty($_POST["monthYearTerm"])) {
echo "Nothing was entered in the input field";
} else {
$month['startMonth'] = $_POST["monthYearTerm"][0];
$year['startYear'] = $_POST["monthYearTerm"][1];
$term['rptType'] = $_POST["monthYearTerm"][2];
}
// Parameters passed into SQL SP.
$sql = "EXEC dbo.spOG_GetMonthlyProductionReports @startMonth = ?, @startYear = ?, @rptType = ?";
$params = array(
array($month['startMonth'], SQLSRV_PARAM_IN),
array($year['startYear'], SQLSRV_PARAM_IN),
array($term['rptType'], SQLSRV_PARAM_IN)
);
$stmt = sqlsrv_prepare($conn, $sql, $params);
// Check if any errors occur during the preparation and execution of the SQL.
if ($stmt === false) {
echo "Statement Error"."<br>";
PrintErrors();
} elseif ( sqlsrv_execute( $stmt ) === false ) {
echo "Execution Error"."<br>";
PrintErrors();
} else {
while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_BOTH)) {
$dataArray['data'][] = array(
"RptKey" => (string)$row['RptKey'],
"RptTypeKey" => (string)$row['RptTypeKey'],
"SumItemTypeKey" => (string)$row['SumItemTypeKey'],
"MthlyTot" => (string)$row['MthlyTot'],
"YearToDateTot" => (string)$row['YearToDateTot'],
"CumTot" => (string)$row['CumTot'],
"ActiveWells" => (string)$row['ActiveWells']
);
}
$jsonData = json_encode($dataArray, JSON_PRETTY_PRINT);
// echo "<pre>$jsonData</pre>";
echo $jsonData;
}
?>
Here's the table I've been able to produce so far:
<table id="clickedTable">
<thead>
<tr>
<th>Product Type</th>
<th>Month</th>
<th>Year-to-Date</th>
<th>Cumulative</th>
<th>YearToDateTot</th>
<th>CumTot</th>
<th>ActiveWells</th></tr>
</thead>
<tbody>
<tr class="odd">
<td>weljun2002</td>
<td>1</td>
<td>1</td>
<td></td>
<td></td>
<td>666640</td>
<td></td>
</tr>
<tr class="even">
<td>weljun2002</td>
<td>1</td>
<td>2</td>
<td></td>
<td></td>
<td>2885870</td>
<td></td>
</tr>
<tr class="odd">
<td>weljun2002</td>
<td>1</td>
<td>3</td>
<td></td>
<td></td>
<td>2134626</td>
<td></td>
</tr>
<tr class="even">
<td>weljun2002</td>
<td>1</td>
<td>4</td>
<td>1122391</td>
<td>7033421</td>
<td>833885534</td>
<td></td>
</tr>
<tr class="odd">
<td>weljun2002</td>
<td>1</td>
<td>5</td>
<td>23781391</td>
<td>146416094</td>
<td>6497035044</td>
<td></td>
</tr>
<tr class="even">
<td>weljun2002</td>
<td>1</td>
<td>6</td>
<td>10649088</td>
<td>67580340</td>
<td>3503991476</td>
<td></td>
</tr>
<tr class="odd">
<td>weljun2002</td>
<td>1</td>
<td>7</td>
<td></td>
<td></td>
<td></td>
<td>1982</td>
</tr>
<tr class="even">
<td>weljun2002</td>
<td>1</td>
<td>8</td>
<td></td>
<td></td>
<td></td>
<td>2809</td>
</tr>
<tr class="odd">
<td>weljun2002</td>
<td>1</td>
<td>9</td>
<td></td>
<td></td>
<td></td>
<td>638</td>
</tr>
<tr class="even">
<td>weljun2002</td>
<td>1</td>
<td>10</td>
<td></td>
<td></td>
<td></td>
<td>502</td>
</tr>
<tr class="odd"><td>weljun2002</td>
<td>1</td>
<td>11</td>
<td></td>
<td></td>
<td></td>
<td>220</td>
</tr>
<tr class="even">
<td>weljun2002</td>
<td>1</td>
<td>12</td>
<td></td>
<td></td>
<td></td>
<td>6143</td>
</tr>
</tbody>
</table>
Here's what I would like to produce:
<table>
<thead>
<tr>
<th colspan="4">State Totals</th>
</tr>
</thead>
<tbody>
<tr>
<td>Product Type</td>
<td>Month</td>
<td>Year-To-Date</td>
<td>Cumulative</td>
</tr>
<tr>
<td>Oil (BBL)</td>
<td>1240096</td>
<td>1240096</td>
<td>830223244</td>
</tr>
<tr>
<td>Gas (MCF)</td>
<td>25316403</td>
<td>25316403</td>
<td>6384793016</td>
</tr>
<tr>
<td>Water (BBL)</td>
<td>12293439</td>
<td>12293439</td>
<td>3469580223</td>
</tr>
<tr>
<td colspan="4">Active Wells</td>
</tr>
<tr>
<td>Producing Oil</td>
<td colspan="3">1936</td>
</tr>
<tr>
<td>Producing Gas</td>
<td colspan="3">2679</td>
</tr>
<tr>
<td>Shut-In Oil</td>
<td colspan="3">687</td>
</tr>
<tr>
<td>Shut-In Gas</td>
<td colspan="3">476</td>
</tr>
<tr>
<td>Temporarily-Abandoned</td>
<td colspan="3">225</td>
</tr>
<tr>
<td>Total</td>
<td colspan="3">5996</td>
</tr>
<tr>
<td>Generate the entire PDF report:</td>
<td colspan="3">PDF Generated Here</td>
</tr>
</tbody>
</table>
Here's a screenshot showing my current table and the data that's being passed to the table.
This is what I'd like to display:
This question has an accepted answers - jump to answer
Answers
We'll need some more information to help, such as how will you limit the columns? Is that using
columns.visible? Likewise, how do you mean re-arrange the data and assign row names?We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.
Cheers,
Colin
I can limit which columns are displayed by not listed them in the ajax call. For example, if I didn't want to show the "RptKey", I could comment it out in the ajax call.
I'll see if I can put together a JSFiddle. Did you see the two images I attached? The first is the current table I'm able to produce, the second is the table I'd like to have. You'll notice the second table has row names on the left (Producing Oil, Producing Gas, etc.) while the first one doesn't. There are quite a few differences between the two tables.
You can have
colspanandrowspanin thethead. See this example for the details.Datatables doesn't support
colspanandrowspanin thetbody. See the HTML requirements docs. The RowGroup extension might do some of what you want. But Datatables doesn't support different numbers of displayed columns in the rows.Kevin
I think I almost got it working while I was putting together the jsfiddle. I'm going to make a new post with a much more simple example case of the problem I was running into.
@kthorngren Thanks. After some trial and error, I was suspecting that the dataTables didn't support colspan and rowspan in the tbody.