如何使用Ajax从MSSQL Query for DataTable中获取数据 [英] How to fetch data from MSSQL Query for DataTables using Ajax

问题描述

这是从 DataTables儿童行获得的代码

$(document).ready(function() {
    var table = $('#example').DataTable( {
        "ajax": "../ajax/data/objects.txt", //here
        "columns": [
            {
                "className":      'details-control',
                "orderable":      false,
                "data":           null,
                "defaultContent": ''
            }, //and here to fetch the data below
            { "data": "name" }, 
            { "data": "position" }, 
            { "data": "office" },
            { "data": "salary" }
        ],
        "order": [[1, 'asc']]
    } );

我想使用ajax从SQL查询获取数据，这是我的SQL查询：

I would like to get the data from an SQL Query by using ajax. This is my SQL Query :

$tsql = 
"SELECT *
FROM [dbo].[ITEM_MASTER] A
INNER JOIN
[dbo].[STOCK] B
ON
B.ItemId = A.ItemId
";
$result = sqlsrv_query($conn, $tsql, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));
if (!$result) {
 die("Query to show fields from table failed");
}

while($row=sqlsrv_fetch_array($result))
{
    $ItmId = $row['ItemId'];
    $ItmName = $row['ItemName'];
    $ItmType = $row['ItemType'];
    $ItmGroup = $row['ItemGroup'];
    $ItmClass = $row['ItemClass'];
    $ItmSerialNum = $row['ItemSerialNum'];
    $ItmUOM = $row['ItemUOM'];
    $StkQty = $row['StockQuantity'];
    $StkId = $row['StockId'];
 }

在ajax部分，我只是调用变量的名称，如 $ ItmId 或者我已经在我的while循环中说过了。
有可能吗？如果是这样，怎么办？因为我对AJAX没有任何知识

And on the ajax part, i just call the name of the variable like $ItmId or like i've stated in my while loop. Is it possible? If so, how? Since i don't have any knowledge on AJAX

更新

数据被推送到第二个参数，并没有显示 ItmId ？而不管我是否更改 $ data：ItmName 它只是根据数组显示并显示其他内容？

The data were pushed to the second parameter and is not showing the ItmId ? And no matter if i change $data : ItmName it just displays based on the arrays and display something else?

$(document).ready(function() {
    var table = $('#table').DataTable( {
        "ajax": {
            "url": "table_data.php",
            "type": "POST"
            },
        "columns": [
            {
                "class":          'details-control',
                "orderable":      false,
                "data":           null,
                "defaultContent": ''
            },
            { "$data": "ItmId"  },
            { "$data": "ItmName" },
            { "$data": "ItmGroup"},
            { "$data": "ItmClass"}
    ],
        "order": [[1, 'asc']]
    } );

推荐答案

首先，您需要更改此行：

Firstly, you need to change this line:

"ajax": "../ajax/data/objects.txt", //here

指向将运行sql查询的实际文件，以获取数据：即这样：

to point to the actual file that will run the sql query to fetch the data: i.e. like this:

"ajax": {"url": "path/to/phpfile.php", "type": "POST"}

您需要使 code>循环如下：

You need to make your while loop like this:

$data = array();
while($row=sqlsrv_fetch_array($result))
{
    $ItmId = $row['ItemId'];
    $ItmName = $row['ItemName'];
    $ItmType = $row['ItemType'];
    $ItmGroup = $row['ItemGroup'];
    $ItmClass = $row['ItemClass'];
    $ItmSerialNum = $row['ItemSerialNum'];
    $ItmUOM = $row['ItemUOM'];
    $StkQty = $row['StockQuantity'];
    $StkId = $row['StockId'];

    $data['data'][] = array($ItmId, $ItmName, $ItmType,....etc);
 }
 echo json_encode($data);

您应该注意，您需要实际表（html）中的列数。
另外，你的json应该是这样的：

You should note, you require the exactly amount of columns in your actual table (html). Also, your json should look like this:

data:
    array(
        ItmId,
        ItmName,
        ..etc
    ),
    array(
        ItmId,
        ItmName,
        ..etc
    ),

基本上有一列数组。

这篇关于如何使用Ajax从MSSQL Query for DataTable中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！