SQL Server 查询在 PHP 中不返回任何结果，但在 Management Studio 中运行良好 - SQL Server query returning no results in PHP, but runs fine in Management Studio

SQL Server query returning no results in PHP, but runs fine in Management Studio

我有一个相当大的查询，在Microsoft SQL Server Management Studio中运行良好。但是，当我尝试在 PHP 中运行相同的查询时，我没有得到任何结果。这是我的代码，我知道这是连接到数据库的最糟糕的方式。

Public function GetClockedHours() {
    $conn = odbc_connect('easydo', '', '');
    if (!$conn) {
        exit("Connection Failed: " . $conn);
    }
    $sql = "WITH ByDays AS ( -- Number the entry register in each day
SELECT 
EventTm AS T,
CONVERT(VARCHAR(10),EventTm,102) AS Day,
FLOOR(CONVERT(FLOAT,EventTm)) DayNumber,
ROW_NUMBER() OVER(PARTITION BY FLOOR(CONVERT(FLOAT,EventTm)) ORDER BY EventTm) InDay 
FROM CHINA_VISION_DorEvents
Where DorCtrls_Ref = '16' AND CardCode = '000006f1' AND CONVERT(Date,EventTm) >  dateadd(day, -7, getdate())
)
,Diffs AS (
SELECT 
E.Day,
E.T ET, O.T OT, O.T-E.T Diff, 
DATEDIFF(S,E.T,O.T) DiffSeconds -- difference in seconds
 FROM 
(SELECT BE.T, BE.Day, BE.InDay 
 FROM ByDays BE 
 WHERE BE.InDay % 2 = 1) E -- Even rows
INNER JOIN
    (SELECT BO.T, BO.Day, BO.InDay 
 FROM ByDays BO 
 WHERE BO.InDay % 2 = 0) O -- Odd rows
ON E.InDay + 1 = O.InDay -- Join rows (1,2), (3,4) and so on
AND E.Day = O.Day --  in the same day
)

SELECT Day, 
SUM(DiffSeconds) Seconds, 
CONVERT(VARCHAR(8), 
(DATEADD(S, SUM(DiffSeconds), '1900-01-01T00:00:00')),
108) TotalHHMMSS -- The same, formatted as HH:MM:SS
FROM Diffs GROUP BY Day
ORDER BY Day desc";
    $rs = odbc_exec($conn, $sql);
    if (!$rs) {
        exit("Error in SQL");
    }
    $array = array();
    $i = 1;
    while ($row = odbc_fetch_array($rs, $i)) {
        foreach ($row AS $key => $value) {
            $array[$i][$key] = $row[$key];
        }
        $i++;
    }
    var_dump($array);
    return $array;
}

预期结果将是

一系列结果，结果将是：

   Date    Seconds   hours
2015.01.27  18055   05:00:55
2015.01.26  33491   09:18:11
2015.01.23  32649   09:04:09
2015.01.22  31554   08:45:54
2015.01.21  31889   08:51:29

然而，得到的是一无所有。

你得到多少个结果集？如果大于 1，则需要选择正确的结果集：

bool odbc_next_result ( resource $result_id )

http://php.net/manual/en/function.odbc-next-result.php

我也有同样的问题。就我而言，我像这样执行

$sql = "SELECT * FROM table1";
$resultSet = odbc_exec($sqllink, $sql);
while ($data = odbc_fetch_array($resultSet)) {
    $sql = "SELECT * FROM table2";
    $resultSet2 = odbc_exec($sqllink, $sql);//failed here
    while ($data2 = odbc_fetch_array($resultSet2)) {
           //something here
    }
}

我像这样改变了，它奏效了

$sql = "SELECT * FROM table1";
$resultSet = odbc_exec($sqllink, $sql);
// Create an array and store the results
$queryResult = array();
while ($data = odbc_fetch_array($resultSet)) {
    // push the required content into the array
    $queryResult[$data['id']]['name'] = $data[name];
}
foreach($queryResult as $row) {
    $sql = "SELECT * FROM table2";
    $resultSet2 = odbc_exec($sqllink, $sql);
    while ($data2 = odbc_fetch_array($resultSet2)) {
        // something here
    }
}