sqlsrv_query executing not returning result, but result exists when code executed in sql manager

Question

I'm trying to execute the code below, but for some reason, it is not returning any result, it is not reporting any errors, and I can't figure out why. When I paste the query directly to SQL Manager, it is executed, and I get the desired result.

I assume it's something with sqlsrv_query that is interpreting differently, but I can't figure out why.

It's a first time; I'm trying to do it with PHP so I'm struggling and I would appreciate if anyone can point me in the right direction.

 $sql = "DECLARE @YearsToPass INT
    SET @YearsToPass = 10;
    WITH cte AS
    (
    SELECT DATEPART(YY, GETDATE())- @YearsToPass + 1 as Years
    UNION ALL
    SELECT Years + 1 as Years
    FROM cte
    WHERE Years + 1 <= YEAR(GETDATE())
    )

    SELECT cte.Years, DATEFROMPARTS(cte.Years, 12, 31),  SUM(Inv.[Nabavna vrijednost])
    FROM cte
    left join  [Drezga01].[dbo].[BI_Inventory01] Inv on Inv.[Datum knjiženja] <= DATEFROMPARTS(cte.Years, 12, 31)
    group by cte.Years, DATEFROMPARTS(cte.Years, 12, 31)
    ORDER BY cte.Years DESC";
 $query = sqlsrv_query($conn_bi,$sql);
 //error handling
 if( $query === false ) {
    if( ($errors = sqlsrv_errors() ) != null) {
     foreach( $errors as $error ) {
     echo "SQLSTATE: ".$error[ 'SQLSTATE']."<br />";
     echo "code: ".$error[ 'code']."<br />";
     echo "message: ".$error[ 'message']."<br />";
     }
   }
  }
while($row = sqlsrv_fetch_array( $query, SQLSRV_FETCH_ASSOC)){
$zaliha_array[] = array('godina' => $row['godina'],
                        'zaliha' => abs($row['nabava']));
}

 debugVar($zaliha_array);

Array is empty, but no errors reported. Result exists and is returned when query executed in SQL Management Studio. Any ideas?

Can you make a small test and execute your script with this error checking: $query = sqlsrv_query($conn_bi, $sql); if ($query === false ) {print_r(sqlsrv_errors(), true); exit;}. Thanks. — Zhorov
– Zhorov, Commented Apr 19, 2019 at 20:01

Zhorov · Accepted Answer · 2019-04-19 20:32:34Z

1

You generate statement, that has no column aliases for DATEFROMPARTS(cte.Years, 12, 31) and SUM(Inv.[Nabavna vrijednost]) and using sqlsrv_fetch_array() with SQLSRV_FETCH_ASSOC is one possible reason for your unexpected results. Try to fetch data with SQLSRV_FETCH_NUMERIC option to retrieve each row of a result set as a numerically indexed array:

<?php
$sql = "
    DECLARE @YearsToPass INT
    SET @YearsToPass = 10;

    WITH cte AS
    (
        SELECT DATEPART(YY, GETDATE())- @YearsToPass + 1 as Years
        UNION ALL
        SELECT Years + 1 as Years
        FROM cte
        WHERE Years + 1 <= YEAR(GETDATE())
    )

    SELECT cte.Years, DATEFROMPARTS(cte.Years, 12, 31),  SUM(Inv.[Nabavna vrijednost])
    FROM cte
    left join  [Drezga01].[dbo].[BI_Inventory01] Inv on Inv.[Datum knjiženja] <= DATEFROMPARTS(cte.Years, 12, 31)
    group by cte.Years, DATEFROMPARTS(cte.Years, 12, 31)
    ORDER BY cte.Years DESC
";

$query = sqlsrv_query($conn_bi, $sql);
if ($query === false ) {
    print_r(sqlsrv_errors(), true);
    exit;
}

while($row = sqlsrv_fetch_array( $query, SQLSRV_FETCH_NUMERIC)){
    $zaliha_array[] = array(
        'godina' => $row[0],
        'zaliha' => abs($row[2])
    );
}
debugVar($zaliha_array);
?>

answered Apr 19, 2019 at 20:32

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Budimir Skrtic Over a year ago

Spot on! I just added aliases for columns and it worked like a charm. Can't believe it was so simple and I spent hours trying to figure out what is going on. Thank you very much @Zhorov!

Zhorov Over a year ago

@BudimirSkrtic I'm glad to help. Good luck!

Tom Lima · Accepted Answer · 2019-04-19 19:59:01Z

0

You have some requirements for this communication to work properly:

You need to check the operating system requirements, sql server version and php version. Then you download the driver for your version of php. This link can help you with this step System requirements for the microsoft drivers
Download and install the ODBC driver. You can download ODBC Driver here
Make reference of the drivers that you downloaded in php.ini where the extensions are as this picture shows

Obs: Do not forget to take the ';' before the word extension

Obs: Do not forget that the driver files need to be in the php / ext directory which is where the php drivers are
Finally use the phpinfo() function and verify that the pdo_sqlsrv extension has been loaded. he should stay like this

I hope this helps to solve the problem o/

answered Apr 19, 2019 at 19:59

Tom Lima

1,0661 gold badge10 silver badges26 bronze badges

1 Comment

Budimir Skrtic Over a year ago

I'm sorry if my question is misleading, but my connection with MS SQL is OK. I'm able to perform SELECT, UPDATE, INSERT, DELETE queries. Only the above query is a problem for me, that one is not returning anything.

Collectives™ on Stack Overflow

sqlsrv_query executing not returning result, but result exists when code executed in sql manager

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related