Using a temporary table with sqlsrv_query function in PHP yields no results

Question

I have a long query for SQL Server 2005 that I'm attempting to find the result set for using the sqlsrv_query function in PHP. Here's my example query. I've removed the variable declarations and SET statements as well as most of the WHEN conditions in the CASE statements for brevity.

DECLARE @TotalTable TABLE (
    SourceName varchar(50),
        GrossRevenue float,
        TotalOrderCount int,
        FBAOrderCount int,
        PostageExpense float,
        MarketplaceFees float,
        PickupExpense float,
        COGS float,
        AvgOrder float
    );

INSERT @TotalTable(SourceName,GrossRevenue,TotalOrderCount,FBAOrderCount,PostageExpense,MarketplaceFees,PickupExpense,COGS,AvgOrder)
SELECT 
    cl.Name,
    SUM(oi.Price) + SUM(oi.NativeShippingFee),
    COUNT(*) AS 'Order Count',
    SUM(CASE WHEN o.ActualPostage = '0.00' THEN 1 ELSE 0 END) AS 'FBA Orders',
    SUM(o.ActualPostage) AS 'Postage Expense',
    CAST(SUM(CASE 
        WHEN o.ActualPostage = '0.00' AND mcd.VisibleName = 'Amazon US' THEN --FBA order on Amazon
            (pt.weight * @FBAWeightCost) + @FBAPickPackCost + (oi.Price * @AmzComm) + @AmzVcf
        ELSE 
            0
        END) AS DECIMAL(10,2)) AS 'Marketplace Fees',
    CASE
        WHEN cl.Name = 'Source - Monroe County Recycler' THEN 
            (@PickupMonroeCounty * @TimesToMonroeCounty) * @JohnPerMile
        ELSE
            0
    END AS 'Pickup Expense',
    CASE 
        WHEN cl.Name = 'Source - Monroe County Recycler' THEN 
            @TotalCostMonroeCounty
        ELSE 
            0
    END AS 'COGS',
    CAST(ROUND(SUM(oi.Price) / COUNT(*),2) AS DECIMAL(10,2)) AS 'Avg Order'
FROM 
    [Order] o
JOIN [OrderItem] oi ON oi.OrderId = o.Id
JOIN [InventoryItem] ii ON ii.Id = oi.InventoryItemId
JOIN [ProductsTraits] pt ON pt.ASIN = ii.ASIN
JOIN [Classifier] clr ON clr.InventoryItemId = ii.Id
JOIN [Classification] cl ON cl.Id = clr.ClassificationId
JOIN [MarketConfigurationData] mcd ON mcd.MarketHandle = o.MarketHandle
WHERE cl.Name LIKE 'Source - %' AND o.Status = '2'
GROUP BY cl.Name
ORDER BY SUM(oi.Price) + SUM(oi.NativeShippingFee) DESC

SELECT
    SourceName,
    GrossRevenue,
    TotalOrderCount,
    FBAOrderCount,
    PostageExpense,
    MarketplaceFees,
    COGS,
    PickupExpense,
    AvgOrder,
    GrossRevenue - PostageExpense - MarketplaceFees - COGS - PickupExpense AS 'Profit'
FROM @TotalTable
ORDER BY 'Profit' DESC

This query when run in SQL Server Management Studio display 19 rows of data as expected. However, when run via this PHP code I get no results.

$sSql = <<<EOD
THIS IS WHERE THAT LONG QUERY STRING GOES
EOD;

$oConn = connectDb(SERVER,USER,PASSWORD);
$result = sqlsrv_query($oConn,$sSql) or die(print_r(sqlsrv_errors(), true));
print_r(sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC));

I've confirmed I can get test results to display when running simple queries via this method so it has something to do with how PHP is passing the query to SQL.

Any thoughts?

JL Martel · Accepted Answer · 2013-08-08 06:17:36Z

8

It's been a while but in case someone is still having this problem: all you have to do is to call SET NOCOUNT ON at the beginning of the stored procedure. This will prevent the "rows affected" messages to be sent to the client and be mistaken for the output of the procedure.

answered Aug 8, 2013 at 6:17

JL Martel

961 silver badge2 bronze badges

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

1 Comment

mrjamesmyers Over a year ago

I migrated from PHP 5.6 to 7 (in the process of) and in doing so from mysql_connect/FreeTDS to sqlsrv and some queries that did work but now don't. In particular, queries which had multiple statements or select/inserting into #TABLES and the querying them. This fixed a lot of issues.

siride · Accepted Answer · 2012-11-14 02:05:21Z

2

If it's anything like mysql_query(), you can't execute multiple queries in a single string with sqlsrv_query(). See the last post of this thread: http://sqlsrvphp.codeplex.com/discussions/35511

So you should first try executing the queries in three different sqlsrv_query() statements.

answered Nov 14, 2012 at 2:05

siride

212k5 gold badges43 silver badges63 bronze badges

Comments

Developer Cng · Accepted Answer · 2015-11-04 17:34:02Z

write before set nocount on; like

    SET NOCOUNT ON; DECLARE @TotalTable TABLE (
    SourceName varchar(50),
        GrossRevenue float,
        TotalOrderCount int,
        FBAOrderCount int,
        PostageExpense float,
        MarketplaceFees float,
        PickupExpense float,
        COGS float,
        AvgOrder float
    );

INSERT @TotalTable(SourceName,GrossRevenue,TotalOrderCount,FBAOrderCount,PostageExpense,MarketplaceFees,PickupExpense,COGS,AvgOrder)
SELECT 
    cl.Name,
    SUM(oi.Price) + SUM(oi.NativeShippingFee),
    COUNT(*) AS 'Order Count',
    SUM(CASE WHEN o.ActualPostage = '0.00' THEN 1 ELSE 0 END) AS 'FBA Orders',
    SUM(o.ActualPostage) AS 'Postage Expense',
    CAST(SUM(CASE 
        WHEN o.ActualPostage = '0.00' AND mcd.VisibleName = 'Amazon US' THEN --FBA order on Amazon
            (pt.weight * @FBAWeightCost) + @FBAPickPackCost + (oi.Price * @AmzComm) + @AmzVcf
        ELSE 
            0
        END) AS DECIMAL(10,2)) AS 'Marketplace Fees',
    CASE
        WHEN cl.Name = 'Source - Monroe County Recycler' THEN 
            (@PickupMonroeCounty * @TimesToMonroeCounty) * @JohnPerMile
        ELSE
            0
    END AS 'Pickup Expense',
    CASE 
        WHEN cl.Name = 'Source - Monroe County Recycler' THEN 
            @TotalCostMonroeCounty
        ELSE 
            0
    END AS 'COGS',
    CAST(ROUND(SUM(oi.Price) / COUNT(*),2) AS DECIMAL(10,2)) AS 'Avg Order'
FROM 
    [Order] o
JOIN [OrderItem] oi ON oi.OrderId = o.Id
JOIN [InventoryItem] ii ON ii.Id = oi.InventoryItemId
JOIN [ProductsTraits] pt ON pt.ASIN = ii.ASIN
JOIN [Classifier] clr ON clr.InventoryItemId = ii.Id
JOIN [Classification] cl ON cl.Id = clr.ClassificationId
JOIN [MarketConfigurationData] mcd ON mcd.MarketHandle = o.MarketHandle
WHERE cl.Name LIKE 'Source - %' AND o.Status = '2'
GROUP BY cl.Name
ORDER BY SUM(oi.Price) + SUM(oi.NativeShippingFee) DESC

SELECT
    SourceName,
    GrossRevenue,
    TotalOrderCount,
    FBAOrderCount,
    PostageExpense,
    MarketplaceFees,
    COGS,
    PickupExpense,
    AvgOrder,
    GrossRevenue - PostageExpense - MarketplaceFees - COGS - PickupExpense AS 'Profit'
FROM @TotalTable
ORDER BY 'Profit' DESC

Collectives™ on Stack Overflow

Using a temporary table with sqlsrv_query function in PHP yields no results

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related