PHP not getting all query results from Microsoft sql server

need to see the query + code that shows how you are consuming it...

Given that it's a rather arbitrary number (203, not something like 0, 50, 100, 200, etc), it's probably one of three things:

1. You're not connecting to the same database / server (e.g. PHP is connecting to one and you're running it via the other tool on another).

2. The query is not truly the same (e.g. the PHP one is adding some kind of condition that the other tool doesn't have). You'd have to echo the query immediately before running it in PHP to see exactly what's running.

3. The data is changing between executions (less likely, since I assume you would've ruled that out already, but possible).

I'm guessing #2.

Thanks, I'm calling a stored procedure and returning the results. Here is the code:

USE [XXX]
GO
/****** Object: StoredProcedure [dbo].[spr_gpActualCostsForDaysPast] Script Date: 2/8/2021 12:54:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






ALTER PROCEDURE [dbo].[spr_gpActualCostsForDaysPast]
@DAYS smallint,
@DEFAULT smallint,
@ORIGIN smallint


AS BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;


SET @DEFAULT = COALESCE(@DEFAULT,1);
SET @ORIGIN = COALESCE(@ORIGIN,0);


IF object_id('#actualcosts', 'U') is not null
TRUNCATE TABLE dbo.#actualcosts
ELSE
CREATE TABLE dbo.#actualcosts (
"Work Order #" VARCHAR(6),
"Date Posted" DATE,
"Description" TEXT,
"Document" TEXT,
"GP ID" VARCHAR(16),
"Vendor" VARCHAR(100),
"Cost Element" SMALLINT,
"Actual Labor Cost" MONEY,
"Actual Material Cost" MONEY,
"Actual Equipt Rental Cost" MONEY,
"Actual Subcontractor Cost" MONEY,
"Actual Other Cost" MONEY,
"Actual Travel Cost" MONEY,
"Actual Ops Overhead Cost" MONEY,
"Actual Shipping Cost" MONEY,
"Unaccounted Cost" MONEY,
"Start Date" DATE,
"End Date" DATE,
"Date Modified" DATE,
"Days" SMALLINT,
"Default" SMALLINT,
"Origin" SMALLINT
)


INSERT INTO #actualcosts (
"Work Order #",
"Date Posted",
"Description",
"Document",
"GP ID",
"Vendor",
"Cost Element",
"Actual Labor Cost",
"Actual Material Cost",
"Actual Equipt Rental Cost",
"Actual Subcontractor Cost",
"Actual Other Cost",
"Actual Travel Cost",
"Actual Ops Overhead Cost",
"Actual Shipping Cost",
"Unaccounted Cost",
"Start Date",
"End Date",
"Date Modified",
"Days",
"Default",
"Origin"
)


(SELECT
stuff(WS_Job_Number, charindex('F', WS_Job_Number), 1, '') AS [Work Order #],
GLPOSTDT AS [Date Posted],
TRXDSCRN AS [Description],
DOCNUMBR AS [Document],
DEX_ROW_ID AS [GP ID],
VENDORID AS [Vendor],
Cost_Element AS [Cost Element],
CASE
WHEN Cost_Element IN (1) THEN Cost_Code_Act_Cost_TTD
ELSE 0.0
END AS [Actual Labor Cost],
CASE
WHEN Cost_Element IN (2) THEN Cost_Code_Act_Cost_TTD
ELSE 0.0
END AS [Actual Material Cost],
CASE
WHEN Cost_Element IN (3) THEN Cost_Code_Act_Cost_TTD
ELSE 0.0
END AS [Actual Equipt Rental Cost],
CASE
WHEN Cost_Element IN (4) THEN Cost_Code_Act_Cost_TTD
ELSE 0.0
END AS [Actual Subcontractor Cost],
CASE
WHEN Cost_Element IN (5) THEN Cost_Code_Act_Cost_TTD
ELSE 0.0
END AS [Actual Other Cost],
CASE
WHEN Cost_Element IN (6) THEN Cost_Code_Act_Cost_TTD
ELSE 0.0
END AS [Actual Travel Cost],
CASE
WHEN Cost_Element IN (7) THEN Cost_Code_Act_Cost_TTD
ELSE 0.0
END AS [Actual Ops Overhead Cost],
CASE
WHEN Cost_Element IN (8) THEN Cost_Code_Act_Cost_TTD
ELSE 0.0
END AS [Actual Shipping Cost],
CASE
WHEN Cost_Element NOT IN(1,2,3,4,5,6,7,8) THEN Cost_Code_Act_Cost_TTD
ELSE 0.0
END AS [Unaccounted Cost],
DATEADD(d, - COALESCE(@DAYS, @DEFAULT), GETDATE()) AS [Start Date],
DATEADD(d, - @ORIGIN, GETDATE()) AS [End Date],
MODIFDT AS [Date Modified],
@DAYS AS [Days],
@DEFAULT AS [Default],
@ORIGIN AS [Origin]


FROM dbo.JC20001 AS JC2


WHERE 1=1
AND (JC2.MODIFDT BETWEEN DATEADD(d, - COALESCE(@DAYS, @DEFAULT) - 1, GETDATE()) AND DATEADD(d, - @ORIGIN, GETDATE()))
AND stuff(WS_Job_Number, charindex('F', WS_Job_Number), 1, '') IS NOT NULL
-- next line prevents non-numeric work order #'s from breaking the run
AND isnumeric(stuff(WS_Job_Number, charindex('F', WS_Job_Number), 1, '')) = 1


UNION


SELECT
stuff(WS_Job_Number, charindex('F', WS_Job_Number), 1, '') AS [Work Order #],
GLPOSTDT AS [Date Posted],
TRXDSCRN AS [Description],
DOCNUMBR AS [Document],
DEX_ROW_ID AS [GP ID],
VENDORID AS [Vendor],
Cost_Element AS [Cost Element],
CASE
WHEN Cost_Element IN (1) THEN Cost_Code_Act_Cost_TTD
ELSE 0.0
END AS [Actual Labor Cost],
CASE
WHEN Cost_Element IN (2) THEN Cost_Code_Act_Cost_TTD
ELSE 0.0
END AS [Actual Material Cost],
CASE
WHEN Cost_Element IN (3) THEN Cost_Code_Act_Cost_TTD
ELSE 0.0
END AS [Actual Equipt Rental Cost],
CASE
WHEN Cost_Element IN (4) THEN Cost_Code_Act_Cost_TTD
ELSE 0.0
END AS [Actual Subcontractor Cost],
CASE
WHEN Cost_Element IN (5) THEN Cost_Code_Act_Cost_TTD
ELSE 0.0
END AS [Actual Other Cost],
CASE
WHEN Cost_Element IN (6) THEN Cost_Code_Act_Cost_TTD
ELSE 0.0
END AS [Actual Travel Cost],
CASE
WHEN Cost_Element IN (7) THEN Cost_Code_Act_Cost_TTD
ELSE 0.0
END AS [Actual Ops Overhead Cost],
CASE
WHEN Cost_Element IN (8) THEN Cost_Code_Act_Cost_TTD
ELSE 0.0
END AS [Actual Shipping Cost],
CASE
WHEN Cost_Element NOT IN(1,2,3,4,5,6,7,8) THEN Cost_Code_Act_Cost_TTD
ELSE 0.0
END AS [Unaccounted Cost],
DATEADD(d, - COALESCE(@DAYS, @DEFAULT), GETDATE()) AS [Start Date],
DATEADD(d, - @ORIGIN, GETDATE()) AS [End Date],
MODIFDT AS [Date Modified],
@DAYS AS [Days],
@DEFAULT AS [Default],
@ORIGIN AS [Origin]


FROM dbo.JC30201 AS JC3


WHERE 1=1
AND (JC3.MODIFDT BETWEEN DATEADD(d, - COALESCE(@DAYS, @DEFAULT) - 1, GETDATE()) AND DATEADD(d, - @ORIGIN, GETDATE()))
AND stuff(WS_Job_Number, charindex('F', WS_Job_Number), 1, '') IS NOT NULL
-- next line prevents non-numeric work order #'s from breaking the run
AND isnumeric(stuff(WS_Job_Number, charindex('F', WS_Job_Number), 1, '')) = 1
)


SELECT * FROM #actualcosts;


If @@ERROR <> 0 GoTo ErrorHandler
SET NOCOUNT OFF
Return(0)


ErrorHandler:
Return(@@ERROR)
END

Select allOpen in new window

Here is the PHP:

$org = isset($_REQUEST["org"]) && $_REQUEST["org"] >= 0 ? $_REQUEST["org"] : 0;
$days = isset($_REQUEST["days"]) && $_REQUEST["days"] >= 0 ? $_REQUEST["days"] : 0;

// we need to modify code to retrieve the $rid for each work order herein
$rid = null;

/**
* get actual costs for n days past
*/
try {
$hostname = "xxxxx";
$dbname = "xxxxx";
$username = "xxxxx";
$password = "xxxxx";

$dbh = new PDO("sqlsrv:server=$hostname;Database=$dbname", $username, $password);
} catch (PDOException $e) {
die($e->getMessage());
}

$stmt = $dbh->prepare("EXEC dbo.spr_gpActualCostsForDaysPast :org, :days")
or die("unable to prepare statement");

$stmt->bindParam(":org", $org)
or die("unable to bind org past");

$stmt->bindParam(":days", $days)
or die("unable to bind days past");

$success = $stmt->execute()
or die("unable to execute statement");

if ($success) {
/**
* fetchAll should only be used for small amounts of data,
* so we will fetch individual records one at a time
* 
* $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
*/
$data = [];

$stmt->setFetchMode(PDO::FETCH_INTO, $data);
$data = $stmt->fetch();

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$data[] = $row;
}
echo(print_r($data));
/**
* if work order exists, but no data, create zero-filled record for QB
*/
if (empty($data) && isset($wko) && $wko > "") {
unset($data);
$data = [];
$data[0]["Work Order #"] = $wko;
$data[0]["Related Work Order"] = $rid;
$data[0]["Date Posted"] = "";
$data[0]["Description"] = "";
$data[0]["Document"] = "";
$data[0]["GP ID"] = "";
$data[0]["Vendor"] = "";
$data[0]["Cost Element"] = "";
$data[0]["Actual Labor Cost"] = "0.00";
$data[0]["Actual Material Cost"] = "0.00";
$data[0]["Actual Equipt Rental Cost"] = "0.00";
$data[0]["Actual Subcontractor Cost"] = "0.00";
$data[0]["Actual Other Cost"] = "0.00";
$data[0]["Actual Travel Cost"] = "0.00";
$data[0]["Actual Ops Overhead Cost"] = "0.00";
$data[0]["Actual Shipping Cost"] = "0.00";
}

echo json_encode($data);
}

unset($dbh); unset($stmt);

Select allOpen in new window

Also, when I dump the results, the last entry has a bunch of "NULL" values that don't appear as 'NULL" on the server. 

I'm not sure why you have both FETCH_INTO and FETCH_ASSOC fetch modes involved here - that seems a little odd. Perhaps there's a conflict with using both?

What's the row count immediately after execution?

$success = $stmt->execute() or die("unable to execute statement");
echo $stmt->rowCount(); die();

Hello, I get an accurate row count until I enter 6 days for the number of days and then he gives me -1, when is should be 369 rows. I cannot figure out why anything over a couple of days gives me an inaccurate row count.

I get an accurate row count until I enter 6 days for the number of days and then he gives me -1, when is should be 369 rows.

what are the parameters you pass
I see there are 3 params but you pass only two...

ALTER PROCEDURE [dbo].[spr_gpActualCostsForDaysPast]
@DAYS    smallint,
@DEFAULT smallint,
@ORIGIN  smallint

Select allOpen in new window

$stmt = $dbh->prepare("EXEC dbo.spr_gpActualCostsForDaysPast :org, :days")

Select allOpen in new window

also this part looks very complicated...
what are you trying to do here?

   AND stuff(WS_Job_Number, charindex('F', WS_Job_Number), 1, '') IS NOT NULL
       -- next line prevents non-numeric work order #'s from breaking the run
   AND isnumeric(stuff(WS_Job_Number, charindex('F', WS_Job_Number), 1, '')) = 1

Select allOpen in new window

is this not enough?

   AND isNumeric(replace(WS_Job_Number,'F',''))=1

Select allOpen in new window

Thanks for the input...making those changes now, the default was used for a previous version, it should have been removed. 

The above changes still yield incorrect row count if I run the query for more than a few days. I still get -1 rather than 369. What could possibly be causing that? Thanks.

The above changes still yield incorrect row count if I run the query for more than a few days. I still get -1 rather than 369. What could possibly be causing that?

what parameters you use?
and what does getting "-1" means?

this is your code, simplified...
it requires 3 parameters...
are you using this code?
what are you passing and what are you getting / what you should get?
I see all parameters are used in the code, so you cannot pass empty/null..

ALTER PROCEDURE [dbo].[spr_gpActualCostsForDaysPast]
@DAYS    smallint,
@DEFAULT smallint,
@ORIGIN  smallint

AS

SET @DEFAULT = COALESCE(@DEFAULT,1);
SET @ORIGIN = COALESCE(@ORIGIN,0);

IF object_id('#actualcosts', 'U') is not null
TRUNCATE TABLE dbo.#actualcosts
ELSE
CREATE TABLE dbo.#actualcosts (...)

INSERT INTO #actualcosts (...)
(
SELECT ...
  FROM dbo.JC20001 AS JC2
 WHERE 1=1
   AND (JC2.MODIFDT BETWEEN DATEADD(d, - COALESCE(@DAYS, @DEFAULT) - 1, GETDATE()) AND DATEADD(d, - @ORIGIN, GETDATE()))
   AND stuff(WS_Job_Number, charindex('F', WS_Job_Number), 1, '') IS NOT NULL
       -- next line prevents non-numeric work order #'s from breaking the run
   AND isnumeric(stuff(WS_Job_Number, charindex('F', WS_Job_Number), 1, '')) = 1

UNION

SELECT ...
  FROM dbo.JC30201 AS JC3
 WHERE 1=1
   AND (JC3.MODIFDT BETWEEN DATEADD(d, - COALESCE(@DAYS, @DEFAULT) - 1, GETDATE()) AND DATEADD(d, - @ORIGIN, GETDATE()))
   AND stuff(WS_Job_Number, charindex('F', WS_Job_Number), 1, '') IS NOT NULL
       -- next line prevents non-numeric work order #'s from breaking the run
   AND isnumeric(stuff(WS_Job_Number, charindex('F', WS_Job_Number), 1, '')) = 1
);

SELECT * FROM #actualcosts;

Select allOpen in new window

The -1 is the row count that I receive when I echo it back. The parameters are origin = 0 and days = 6 the row count returned is -1, whereas when I use any origin or day  less than 6 I get the correct row count. For example origin =1 and days = 1 gives me 113 row count.

I removed the default parameter, so now the code only requires origin and days.

The query works fine when I run it on the server but something in the PHP is not allowing all of the rows to be returned whenever I use a parameter of more than 5 days

so this is your final code

ALTER PROCEDURE [dbo].[spr_gpActualCostsForDaysPast]
@DAYS    smallint,
@ORIGIN  smallint

AS

SET @ORIGIN = COALESCE(@ORIGIN,0);

IF object_id('#actualcosts', 'U') is not null
TRUNCATE TABLE dbo.#actualcosts
ELSE
CREATE TABLE dbo.#actualcosts (...)

INSERT INTO #actualcosts (...)
(
SELECT ...
  FROM dbo.JC20001 AS JC2
 WHERE 1=1
   AND (JC2.MODIFDT BETWEEN DATEADD(d, - @DAYS - 1, GETDATE()) AND DATEADD(d, - @ORIGIN, GETDATE()))
   AND stuff(WS_Job_Number, charindex('F', WS_Job_Number), 1, '') IS NOT NULL
       -- next line prevents non-numeric work order #'s from breaking the run
   AND isnumeric(stuff(WS_Job_Number, charindex('F', WS_Job_Number), 1, '')) = 1

UNION

SELECT ...
  FROM dbo.JC30201 AS JC3
 WHERE 1=1
   AND (JC3.MODIFDT BETWEEN DATEADD(d, - @DAYS - 1, GETDATE()) AND DATEADD(d, - @ORIGIN, GETDATE()))
   AND stuff(WS_Job_Number, charindex('F', WS_Job_Number), 1, '') IS NOT NULL
       -- next line prevents non-numeric work order #'s from breaking the run
   AND isnumeric(stuff(WS_Job_Number, charindex('F', WS_Job_Number), 1, '')) = 1
);

SELECT * FROM #actualcosts;

Select allOpen in new window

and when you call this in db

call spr_gpActualCostsForDaysPast 6,0

what do you get?

also, why do you need temp table here?
this should do the same thing!

ALTER PROCEDURE [dbo].[spr_gpActualCostsForDaysPast]
@DAYS    smallint,
@ORIGIN  smallint

AS

SET @ORIGIN = COALESCE(@ORIGIN,0);

SELECT ...
  FROM dbo.JC20001 AS JC2
 WHERE 1=1
   AND (JC2.MODIFDT BETWEEN DATEADD(d, - @DAYS - 1, GETDATE()) AND DATEADD(d, - @ORIGIN, GETDATE()))
   AND isNumeric(replace(WS_Job_Number,'F',''))=1

UNION

SELECT ...
  FROM dbo.JC30201 AS JC3
 WHERE 1=1
   AND (JC3.MODIFDT BETWEEN DATEADD(d, - @DAYS - 1, GETDATE()) AND DATEADD(d, - @ORIGIN, GETDATE()))
   AND isNumeric(replace(WS_Job_Number,'F',''))=1

Select allOpen in new window

I dont see any issue with parameters or query
see what happens with date parameters if I use 12,0...

declare @days int = 12;
declare @origin int;

SET @ORIGIN = COALESCE(@ORIGIN,0);

select DATEADD(d, - @DAYS - 1, GETDATE()), DATEADD(d, - @ORIGIN, GETDATE())

Select allOpen in new window

OK, so I've been eliminating fields from the Query to see if one of those were causing the issue, when I remove the Description and Document fields (both are text fields) it works perfectly. So there is something about the data in those fields that is causing the problem.  When I search for 6 days I get the expected 369 rows returned, but when I add them back my row count goes back to -1.

1. did you remove usage of temp table?
2. when you run it in db, do you get full results?

3. what columns did you remove?
are they these columns
"Description" TEXT,
"Document" TEXT,

4. when you get -1, do you get any exception on php side?

Yes those are the two columns that I removed from the query. When the Description and Document columns are not returned then the results are correct. As soon as I add those columns, I get a result of -1 when I run the PHP app. So far I have not been able to determine how to fix this issue. 

Are you checking the row count using the method I suggested?

@gr8gonzo...yes that is how I am checking the row count. I get -1 when the two text columns are included but I get the correct count when those columns are omitted.

Hmmm. I wonder if it's PHP / PDO's method of iterating through the result set. Can you try the CURSOR_SCROLL method (just add the below bolded piece to your prepare statement):

$stmt = $dbh->prepare("EXEC dbo.spr_gpActualCostsForDaysPast :org, :days", array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)) or die("unable to prepare statement");

did you try

UNION ALL

instead of UNION?

+ also did you try not using the temp table but just return a simple select
as I mentioned here

https://www.experts-exchange.com/questions/29207488/PHP-not-getting-all-query-results-from-Microsoft-sql-server.html#a43240120

also, you can try nvarchar(4000) instead of TEXT in your temp table...

Yes I removed the temporary table, still no luck after trying all of the above selections it seems that PHP has some limit set to the number of results allowed. I'm stumped.

Okay if that didn't work, then my next assumption is that it's an issue with the max buffer size. Including Document and Description and going back farther is including data that is too large to be handled in the return in the default buffer size. So you can try adjusting PDO::MYSQL_ATTR_MAX_BUFFER_SIZE before querying:

// Set buffer size to 100 MB (probably overkill, but just for testing purposes)
$dbh->setAttribute(PDO::MYSQL_ATTR_MAX_BUFFER_SIZE, 1024*1024*100);

Select allOpen in new window

I would assume HainKurt's latest comment about using LEFT() would probably also work if data size is the root problem, since it would be dramatically limiting the data returned.

EDIT: I just realized that's a MySQL attribute, not a SQL Server one. I'll check to see if there's a MSSQL version.

Thank you both so very much, your comments/suggestions lead me to a resolution, I absolutely needed to address the buffer size, that got be about half-way there, I was still losing data, so I looked at the field types in the tables and noticed that the  two text fields were set to char(31) and char(21). Once I trimmed these fields in the query, I was able to get the correct result set, no matter how many days I ran the query for. Is there a way to mark both as solutions? Thanks.