PHP 使用 sqlsrv_query 显示 T-SQL 语句

Question

只是想知道是否有人可以帮助我理解为什么我的代码不输出数据。我在 Microsoft SQL Server 2008 中创建了一个 T-SQL 查询。该查询运行良好，并在 SQL Management Studio 中显示所有正确的数据。下面是数据在 SQL Server Management studio 中的外观：

block sequence|number of ties|percent_of_q4|number of q3 ties|percent of q3 ties| quality
0 1108  11.34296 37  3.33935 1
1 1094  31.11517  66 6.032907  1
2 1109  21.633 53  4.77908 1

当我尝试使用完全相同的查询在简单的 PHP 脚本中输出数据时，没有显示任何数据。

发生这种情况是因为 sqlsrv_query 不喜欢我的查询吗？ sqlsrv_fetch_array 不像我的查询中的“IS NOT NULL”吗？我已经完全不知道该尝试什么了...任何人都可以提供任何建议来解释为什么这个相当简单的脚本似乎不起作用吗？任何答复将不胜感激！

如果有任何不清楚的地方，请告诉我。

干杯， 尼尔

PHP 脚本如下：

       <?php

        /*data base connection */



        $serverName = ".\SQLEXPRESS";

        $connectionOptions = array("Database"=>"V6_HOLLTS479_20101015_subset",

         "UID"=>"username",

         "PWD" => "password");



        /* Connect using Windows Authentication */       

        $conn = sqlsrv_connect($serverName, $connectionOptions);



        /* Check whether connnection is established */

        if($conn === false)

        {

            die(print_r(sqlsrv_errors(), true));

        }



        /* SQL query */

$tsql = "

DECLARE @block_size AS real



  DECLARE @threshold_1 AS real

 DECLARE @threshold_2 AS real

  DECLARE @threshold_3 AS real

 DECLARE @threshold_4 AS real







   --Set variables



    SET @block_size = 200.0



    SET @threshold_1 = 50  -- GISCat4HighThresh

    SET @threshold_2 = 75  -- GISCat3HighThresh

    SET @threshold_3 = 25  -- GISCat4LowThresh

    SET @threshold_4 = 50  -- GISCat3LowThresh



 SELECT

  sub_t.block_sequence ,

  sub_t.number_of_ties,

   tie_q_4.number_of_ties AS number_of_q4_ties,

   (CAST(tie_q_4.number_of_ties AS real)/CAST(sub_t.number_of_ties AS real))*100.0 AS percent_of_q4_ties,

       tie_q_3.number_of_ties AS number_of_q3_ties,

    (CAST(tie_q_3.number_of_ties AS real)/CAST(sub_t.number_of_ties AS real))*100.0 AS percent_of_q3_ties,



       --The next column shows the block quality.  This is currently 4,3,1 based on the threshold rules.

    CASE WHEN (

        (CAST(tie_q_4.number_of_ties AS real)/CAST(sub_t.number_of_ties AS real))*100.0 > @threshold_1

            OR

        (CAST(tie_q_3.number_of_ties AS real)/CAST(sub_t.number_of_ties AS real))*100.0 > @threshold_2

            ) THEN 4

        WHEN (

        (CAST(tie_q_4.number_of_ties AS real)/CAST(sub_t.number_of_ties AS real))*100.0 > @threshold_3

            OR

        (CAST(tie_q_3.number_of_ties AS real)/CAST(sub_t.number_of_ties AS real))*100.0 > @threshold_4

            ) THEN 3

        ELSE 1



                    END AS quality



        FROM (

        SELECT

            FLOOR(CAST(image_sequence AS real)/@block_size) AS block_sequence ,

            COUNT(image_sequence) AS number_of_ties

            FROM database

            GROUP BY 

                FLOOR(CAST(image_sequence AS real)/@block_size)

            ) AS SUB_T



        LEFT JOIN (

        SELECT FLOOR(CAST(image_sequence AS real)/@block_size) AS block_sequence ,

            COUNT(image_sequence) AS number_of_ties

            FROM database

            WHERE 

                quality = 4

            GROUP BY 

            FLOOR(CAST(image_sequence AS real)/@block_size)

        ) AS tie_q_4 ON sub_t.block_sequence = tie_q_4.block_sequence



        LEFT JOIN (

        SELECT

            FLOOR(CAST(image_sequence AS real)/@block_size) AS block_sequence ,

            COUNT(image_sequence) AS number_of_ties

            FROM  database

            WHERE 

                quality = 3

            GROUP BY 

            FLOOR(CAST(image_sequence AS real)/@block_size)

        ) AS tie_q_3 ON sub_t.block_sequence = tie_q_3.block_sequence


        WHERE sub_t.block_sequence IS NOT NULL
         ORDER BY block_sequence

";




$result = sqlsrv_query($conn, $tsql);



$row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC);

print("<pre>");

print_r($row);

print("</pre>");

Answer 1

我遇到了类似的问题，我尝试了各种方法，然后又回到了基础。如果你尝试

$query=sqlsrv_query($conn, $tsql);
$result=sqlsrv_fetch_array($query);
print_r($result); 

应该能成功