How to I use PDO to pass variables into a MSSQL Stored Procedure?

Here's my stored procedure...

screenshot
I want to run it using a PDO dynamic and this is what I've got thus far:

<?php
require_once('projectpageclass.php');
$new_page= new ProjectPage;
$page_title="South Area Network Development Project List";

$start_date="01/01/2015";
$end_date="10/31/2015";

include('mssql_db_cred.php');
$mssql_pdo = new PDO("dblib:host=".$mssql_cred_data['server'].";dbname=".$mssql_cred_data['dbname'],$mssql_cred_data['user'],$mssql_cred_data['pw']); 
$mssql_pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
/*if(!$mssql_pdo)
{
	echo "no connection";
}
else
{
	echo "good connection";
}*/


$statement = $mssql_pdo->prepare("exec stp_Select_REPORT_DevelopeProjects_Gantt");
/*$statement->execute(array($start_date, $end_date);
$statement->execute(array($start_date, $end_date));
$statement_row=$statement->fetch(PDO::FETCH_ASSOC);*/


$body='<br><br>you\'re fine!';

$new_page->setBody($body);

echo $new_page->display();
?>

Open in new window


You'll notice on lines 23-25, they're commented out. That's where I was trying some different things to get my $start_date and $end_date accurately introduced into my stored proc.

It didn't work.

How do I pass my $start_date and $end_date into my stored proc so it's recognized as @DataDisplay_Start and @DataDisplay_End?

What do you think?
brucegustPHP DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zephyr_hex (Megan)DeveloperCommented:
  $statement = $mssql_pdo->prepare("exec stp_Select_REPORT_DevelopeProjects_Gantt ?, ?");
  $statement->bindParam(1, $start_date);
  $statement->bindParam(2, $end_date);
  $statement->execute();

Open in new window


Try using bindParam to bind your parameters to the stored procedure input params.
0
brucegustPHP DeveloperAuthor Commented:
zephyr!

I think we're close, but I want you to look at this:

Here's what I've got:

$statement = $mssql_pdo->prepare("exec stp_Select_REPORT_DevelopeProjects_Gantt ?, ?");
$statement->bindParam(1, $start_date);
$statement->bindParam(2, $end_date);
$statement->execute();        
$statement_row=$statement->fetch(PDO::FETCH_ASSOC);

echo $statement_row['str_Full_Name_Developer'];    

No errors, but no data.

The "str_Full_Name_Developer" is a legitimate column name, so I'm confident that we're good to go there, but nothing prints.

Thoughts?
0
zephyr_hex (Megan)DeveloperCommented:
enable PHP error reporting, use fetchAll instead of fetch, and iterate over the results:

error reporting (goes at the top of your php page)
//ERROR REPORTING
ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(E_ALL); 

Open in new window


$results = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach($results as $row) {
   var_dump($row);
}

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

brucegustPHP DeveloperAuthor Commented:
zephyr!

I appreciate you hanging in there with me, friend!

Tried this:

<?php
//ERROR REPORTING
ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(E_ALL);
                                         
require_once('projectpageclass.php');
$new_page= new ProjectPage;
$page_title="South Area Network Development Project List";

$start_date="01/01/2015";
$end_date="10/31/2015";

include('mssql_db_cred.php');

$mssql_pdo = new PDO("dblib:host=".$mssql_cred_data['server'].";dbname=".$mssql_cred_data['dbname'],$mssql_cred_data['user'],$mssql_cred_data['pw']);
$mssql_pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
if(!$mssql_pdo)
{
      echo "no connection";
}

$body="<br><br>here we go...<br><br>";

$statement = $mssql_pdo->prepare("exec stp_Select_REPORT_DevelopeProjects_Gantt ?, ?");
$statement->bindParam(1, $start_date);
$statement->bindParam(2, $end_date);
$statement->execute();

if(!$statement)
{
echo "it didn't work";
}      

$results = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach($results as $row) {
   var_dump($row);
}

No errors, but no data.

I wanted you to see the stored proc too, just to make sure you're getting a comprehensive snapshot of what I'm contending with.

Here it is:

CREATE PROCEDURE [dbo].[stp_Select_REPORT_DevelopeProjects_Gantt]
(
	@DataDisplay_Start	date	,
	@DataDisplay_End	date	
)
AS
BEGIN

	SET NOCOUNT ON

	CREATE TABLE #tmp_Projects
	(
			id												int IDENTITY,
			int_DEVELOPERS_Project_Id						int,
			str_ProjectId									varchar(MAX),
			int_DEVELOPERS_Tool_Id							int,
			int_ProjectRank									int,
			int_DEVELOPERS_ProjectType_Id					int,
			str_ProjectName									varchar(MAX),
			int_Developer_Id								int,
			str_Description									varchar(MAX),
			int_DEVELOPERS_ProjectPriority_Id				int,
			bit_Is_Upcoming									bit,
			int_ProjectSubmitter_Id							int,
			str_DateSubmitted								varchar(MAX),
			dte_DateSubmitted								datetime,
			int_DEVELOPERS_DocumentationStatus_Id			int,
			int_DEVELOPERS_ProjectStatus_Id					int,
			int_DEVELOPERS_LevelOfEffort_Id					int,
			dec_PercentageCompleted							decimal(10,8),
			int_BenefitEquation								int,
			str_CurrentStatus								varchar(MAX),
			str_RequestorDueDate							varchar(MAX),
			str_DateStarted									varchar(MAX),
			str_DocumentationCompletionDate					varchar(MAX),
			str_TargetCompletionDate						varchar(MAX),
			str_DateCompleted								varchar(MAX),
			str_DateOnProduction							varchar(MAX),
			dte_RequestorDueDate							datetime,
			dte_DateStarted									datetime,
			dte_TargetCompletionDate						datetime,
			dte_DateCompleted								datetime,
			dte_DateOnProduction							datetime,
			str_Notes										varchar(MAX),
			bit_Is_Active									bit,
			int_Insert_User_Id								int,
			dte_Inserted_Date								datetime,
			int_Update_User_Id								int,
			dte_Updated_Date								datetime,
			str_Tool										varchar(MAX),
			str_ProjectPriority								varchar(MAX),
			str_ProjectType									varchar(MAX),
			str_DocumentationStatus							varchar(MAX),
			str_ProjectStatus								varchar(MAX),
			str_LevelOfEffort								varchar(MAX),
			str_Full_Name_Developer							varchar(MAX),
			str_Full_Name_Submitter							varchar(MAX),
			str_Full_Name_InsertUser						varchar(MAX),
			str_Full_Name_UpdateUser						varchar(MAX),
			str_Full_Name_Owner								varchar(MAX),
			DataDisplay_StartDate							datetime,
			DataDisplay_EndDate								datetime,
			DataDisplay_StartCell							int,
			DataDisplay_EndCell								int,
			TotalCellSpan									int,
			DataDisplay_StartWeek							int,
			DataDisplay_EndWeek								int,
			DataDisplay_Row									int

	)

	INSERT INTO #tmp_Projects
	(
			int_DEVELOPERS_Project_Id						,
			str_ProjectId									,
			int_DEVELOPERS_Tool_Id							,
			int_ProjectRank									,
			int_DEVELOPERS_ProjectType_Id					,
			str_ProjectName									,
			int_Developer_Id								,
			str_Description									,
			int_DEVELOPERS_ProjectPriority_Id				,
			bit_Is_Upcoming									,
			int_ProjectSubmitter_Id							,
			str_DateSubmitted								,
			dte_DateSubmitted								,
			int_DEVELOPERS_DocumentationStatus_Id			,
			int_DEVELOPERS_ProjectStatus_Id					,
			int_DEVELOPERS_LevelOfEffort_Id					,
			dec_PercentageCompleted							,
			int_BenefitEquation								,
			str_CurrentStatus								,
			str_RequestorDueDate							,
			str_DateStarted									,
			str_DocumentationCompletionDate					,
			str_TargetCompletionDate						,
			str_DateCompleted								,
			str_DateOnProduction							,
			dte_RequestorDueDate							,
			dte_DateStarted									,
			dte_TargetCompletionDate						,
			dte_DateCompleted								,
			dte_DateOnProduction							,
			str_Notes										,
			bit_Is_Active									,
			int_Insert_User_Id								,
			dte_Inserted_Date								,
			int_Update_User_Id								,
			dte_Updated_Date								,
			str_Tool										,
			str_ProjectPriority								,
			str_ProjectType									,
			str_DocumentationStatus							,
			str_ProjectStatus								,
			str_LevelOfEffort								,
			str_Full_Name_Developer							,
			str_Full_Name_Submitter							,
			str_Full_Name_InsertUser						,
			str_Full_Name_UpdateUser						,
			str_Full_Name_Owner								,
			DataDisplay_StartDate							,
			DataDisplay_EndDate								,
			DataDisplay_StartCell							,
			DataDisplay_EndCell								,
			TotalCellSpan									,
			DataDisplay_StartWeek							,
			DataDisplay_EndWeek								

	)
  		SELECT		DP.int_DEVELOPERS_Project_Id				,
					DP.str_ProjectId							,
					DP.int_DEVELOPERS_Tool_Id					,
					ISNULL(DP.int_ProjectRank, 999)	AS 	int_ProjectRank,
					DP.int_DEVELOPERS_ProjectType_Id			,
					DP.str_ProjectName							,
					DP.int_Developer_Id							,
					DP.str_Description							,
					DP.int_DEVELOPERS_ProjectPriority_Id		,
					DP.bit_Is_Upcoming							,
					DP.int_ProjectSubmitter_Id					,
					DP.str_DateSubmitted						,
					DP.dte_DateSubmitted						,
					DP.int_DEVELOPERS_DocumentationStatus_Id	,
					DP.int_DEVELOPERS_ProjectStatus_Id			,
					DP.int_DEVELOPERS_LevelOfEffort_Id			,
					DP.dec_PercentageCompleted					,
					DP.int_BenefitEquation						,
					DP.str_CurrentStatus						,
					DP.str_RequestorDueDate						,
					DP.str_DateStarted							,
					DP.str_DocumentationCompletionDate			,
					DP.str_TargetCompletionDate					,
					DP.str_DateCompleted						,
					DP.str_DateOnProduction						,
					DP.dte_RequestorDueDate						,
					DP.dte_DateStarted							,
					DP.dte_TargetCompletionDate					,
					DP.dte_DateCompleted						,
					DP.dte_DateOnProduction						,
					DP.str_Notes								,
					DP.bit_Is_Active							,
					DP.int_Insert_User_Id						,
					DP.dte_Inserted_Date						,
					DP.int_Update_User_Id						,
					DP.dte_Updated_Date							,

					DT.str_Tool									,
					DPP.str_ProjectPriority						,
					DPt.str_ProjectType							,
					DDS.str_DocumentationStatus					,
					DPS.str_ProjectStatus						,
					DLE.str_LevelOfEffort						,
		
					(CId.str_First_Name + ' ' + CId.str_Last_Name) AS str_Full_Name_Developer	,
					(CIs.str_First_Name + ' ' + CIs.str_Last_Name) AS str_Full_Name_Submitter	,
					(CIi.str_First_Name + ' ' + CIi.str_Last_Name) AS str_Full_Name_InsertUser	,
					(CIu.str_First_Name + ' ' + CIu.str_Last_Name) AS str_Full_Name_UpdateUser	,
					(CIo.str_First_Name + ' ' + CIo.str_Last_Name) AS str_Full_Name_Owner		,

					ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted) AS [DataDisplay_StartDate]														,
					ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate) AS [DataDisplay_EndDate]												,
					CASE WHEN (ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted) < @DataDisplay_Start) THEN 0 ELSE DateDiff(day, @DataDisplay_Start, ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted)) END AS [DataDisplay_StartCell]					,
					DateDiff(day, @DataDisplay_Start, ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate)) AS [DataDisplay_EndCell]			,
					DateDiff(day, ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted), ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate)) + 1 AS [TotalCellSpan]			,
					(DateDiff(day, @DataDisplay_Start, ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted))/7) AS [DataDisplay_StartWeek]				,
					(DateDiff(day, @DataDisplay_Start, ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate))/7) AS [DataDisplay_StartWeek]

		FROM		tbl_DEVELOPERS_Projects					DP
		LEFT JOIN	tbl_DEVELOPERS_Tools					DT	ON DP.int_DEVELOPERS_Tool_Id = DT.int_DEVELOPERS_Tool_Id AND DT.bit_Is_Active = 1
		LEFT JOIN	tbl_DEVELOPERS_ProjectPriorities		DPP	ON DP.int_DEVELOPERS_ProjectPriority_Id = DPP.int_DEVELOPERS_ProjectPriority_Id AND DPP.bit_Is_Active = 1
		LEFT JOIN	tbl_DEVELOPERS_DocumentationStatuses	DDS	ON DP.int_DEVELOPERS_DocumentationStatus_Id = DDS.int_DEVELOPERS_DocumentationStatus_Id AND DDS.bit_Is_Active = 1
		LEFT JOIN	tbl_DEVELOPERS_ProjectStatuses			DPS	ON DP.int_DEVELOPERS_ProjectStatus_Id = DPS.int_DEVELOPERS_ProjectStatus_Id AND DPS.bit_Is_Active = 1
		LEFT JOIN	tbl_DEVELOPERS_LevelOfEfforts			DLE	ON DP.int_DEVELOPERS_LevelOfEffort_Id = DLE.int_DEVELOPERS_LevelOfEffort_Id AND DLE.bit_Is_Active = 1
		LEFT JOIN	tbl_DEVELOPERS_ProjectTypes				DPT	ON DP.int_DEVELOPERS_ProjectType_Id = DPT.int_DEVELOPERS_ProjectType_Id AND DPT.bit_Is_Active = 1

		LEFT JOIN	tbl_CORE_Individuals					CId	ON DP.int_Developer_Id = CId.int_CORE_Individual_Id AND CId.bit_Is_Active = 1
		LEFT JOIN	tbl_CORE_Individuals					CIs	ON DP.int_ProjectSubmitter_Id = CIs.int_CORE_Individual_Id AND CIs.bit_Is_Active = 1
		LEFT JOIN	tbl_CORE_Individuals					CIi	ON DP.int_Insert_User_Id = CIi.int_CORE_Individual_Id AND CIi.bit_Is_Active = 1
		LEFT JOIN	tbl_CORE_Individuals					CIu	ON DP.int_Update_User_Id = CIu.int_CORE_Individual_Id AND CIu.bit_Is_Active = 1
		LEFT JOIN	tbl_CORE_Individuals					CIo	ON DT.int_Owner_Id = CIo.int_CORE_Individual_Id AND CIo.bit_Is_Active = 1

		WHERE		(ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted) between @DataDisplay_Start AND @DataDisplay_End)
		OR			(ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate) between @DataDisplay_Start AND @DataDisplay_End)
		OR			(ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted) < @DataDisplay_Start AND ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate) > @DataDisplay_End)
	ORDER BY [DataDisplay_StartCell], [DataDisplay_EndCell]


	DECLARE @rec_id int
	DECLARE @StartCell int
	DECLARE @EndCell int

	DECLARE proj_cursor CURSOR FOR 
	SELECT ID, ISNULL([DataDisplay_StartCell], 0), [DataDisplay_EndCell]
	FROM #tmp_Projects
	ORDER BY ID;

	OPEN proj_cursor

	FETCH NEXT FROM proj_cursor 
	INTO @rec_id, @StartCell, @EndCell

	WHILE @@FETCH_STATUS = 0
	BEGIN

		DECLARE @nextRecord int
		SET @nextRecord = (SELECT (ISNULL(MAX(DataDisplay_Row), 0) + 1) FROM #tmp_Projects WHERE [DataDisplay_EndCell] >= @StartCell AND DataDisplay_Row IS NOT NULL)

		DECLARE @nextFreeRecord int
		SET @nextFreeRecord = (SELECT TOP 1 DataDisplay_Row FROM #tmp_Projects WHERE DataDisplay_Row NOT IN (SELECT DataDisplay_Row FROM #tmp_Projects WHERE [DataDisplay_EndCell] >= @StartCell AND DataDisplay_Row IS NOT NULL) ORDER BY DataDisplay_Row)

		IF @nextFreeRecord IS NOT NULL
		BEGIN
			SET @nextRecord = @nextFreeRecord
		END

		UPDATE #tmp_Projects SET DataDisplay_Row = @nextRecord WHERE id = @rec_id

		FETCH NEXT FROM proj_cursor 
		INTO @rec_id, @StartCell, @EndCell

	END
	CLOSE proj_cursor;
	DEALLOCATE proj_cursor;

	SELECT 	int_DEVELOPERS_Project_Id						,
			str_ProjectId									,
			int_DEVELOPERS_Tool_Id							,
			int_ProjectRank									,
			int_DEVELOPERS_ProjectType_Id					,
			str_ProjectName									,
			int_Developer_Id								,
			str_Description									,
			int_DEVELOPERS_ProjectPriority_Id				,
			bit_Is_Upcoming									,
			int_ProjectSubmitter_Id							,
			str_DateSubmitted								,
			dte_DateSubmitted								,
			int_DEVELOPERS_DocumentationStatus_Id			,
			int_DEVELOPERS_ProjectStatus_Id					,
			int_DEVELOPERS_LevelOfEffort_Id					,
			dec_PercentageCompleted							,
			int_BenefitEquation								,
			str_CurrentStatus								,
			str_RequestorDueDate							,
			str_DateStarted									,
			str_DocumentationCompletionDate					,
			str_TargetCompletionDate						,
			str_DateCompleted								,
			str_DateOnProduction							,
			dte_RequestorDueDate							,
			dte_DateStarted									,
			dte_TargetCompletionDate						,
			dte_DateCompleted								,
			dte_DateOnProduction							,
			str_Notes										,
			bit_Is_Active									,
			int_Insert_User_Id								,
			dte_Inserted_Date								,
			int_Update_User_Id								,
			dte_Updated_Date								,
			str_Tool										,
			str_ProjectPriority								,
			str_ProjectType									,
			str_DocumentationStatus							,
			str_ProjectStatus								,
			str_LevelOfEffort								,
			str_Full_Name_Developer							,
			str_Full_Name_Submitter							,
			str_Full_Name_InsertUser						,
			str_Full_Name_UpdateUser						,
			str_Full_Name_Owner								,
			DataDisplay_StartDate							,
			DataDisplay_EndDate								,
			DataDisplay_StartCell							,
			DataDisplay_EndCell								,
			TotalCellSpan									,
			DataDisplay_StartWeek							,
			DataDisplay_EndWeek								,
			DataDisplay_Row
	FROM	#tmp_Projects
	ORDER BY DataDisplay_Row

	DROP TABLE #tmp_Projects

END

GO

Open in new window


I was able to do this as straight SQL:

$sql="CREATE TABLE #tmp_Projects 
	(
			id												int IDENTITY,
			int_DEVELOPERS_Project_Id						int,
			str_ProjectId									varchar(MAX),
			int_DEVELOPERS_Tool_Id							int,
			int_ProjectRank									int,
			int_DEVELOPERS_ProjectType_Id					int,
			str_ProjectName									varchar(MAX),
			int_Developer_Id								int,
			str_Description									varchar(MAX),
			int_DEVELOPERS_ProjectPriority_Id				int,
			bit_Is_Upcoming									bit,
			int_ProjectSubmitter_Id							int,
			str_DateSubmitted								varchar(MAX),
			dte_DateSubmitted								datetime,
			int_DEVELOPERS_DocumentationStatus_Id			int,
			int_DEVELOPERS_ProjectStatus_Id					int,
			int_DEVELOPERS_LevelOfEffort_Id					int,
			dec_PercentageCompleted							decimal(10,8),
			int_BenefitEquation								int,
			str_CurrentStatus								varchar(MAX),
			str_RequestorDueDate							varchar(MAX),
			str_DateStarted									varchar(MAX),
			str_DocumentationCompletionDate					varchar(MAX),
			str_TargetCompletionDate						varchar(MAX),
			str_DateCompleted								varchar(MAX),
			str_DateOnProduction							varchar(MAX),
			dte_RequestorDueDate							datetime,
			dte_DateStarted									datetime,
			dte_TargetCompletionDate						datetime,
			dte_DateCompleted								datetime,
			dte_DateOnProduction							datetime,
			str_Notes										varchar(MAX),
			bit_Is_Active									bit,
			int_Insert_User_Id								int,
			dte_Inserted_Date								datetime,
			int_Update_User_Id								int,
			dte_Updated_Date								datetime,
			str_Tool										varchar(MAX),
			str_ProjectPriority								varchar(MAX),
			str_ProjectType									varchar(MAX),
			str_DocumentationStatus							varchar(MAX),
			str_ProjectStatus								varchar(MAX),
			str_LevelOfEffort								varchar(MAX),
			str_Full_Name_Developer							varchar(MAX),
			str_Full_Name_Submitter							varchar(MAX),
			str_Full_Name_InsertUser						varchar(MAX),
			str_Full_Name_UpdateUser						varchar(MAX),
			str_Full_Name_Owner								varchar(MAX),
			DataDisplay_StartDate							datetime,
			DataDisplay_EndDate								datetime,
			DataDisplay_StartCell							int,
			DataDisplay_EndCell								int,
			TotalCellSpan									int,
			DataDisplay_StartWeek							int,
			DataDisplay_EndWeek								int,
			DataDisplay_Row									int

	)";
	$mssql_stmt_50=$mssql_pdo->query($sql);
if(!$mssql_stmt_50)
{
	echo "your table didn\'t happen.";
}
else
{
	echo "good to go";
}

Open in new window


So, it seems like my credentials are sufficient as far as setting up a table, etc. But, when I attempt to run the stored procedure, I get nothing.

What do you think?
0
brucegustPHP DeveloperAuthor Commented:
Tried this:

$statement = $mssql_pdo->prepare("EXEC stp_Select_REPORT_DevelopeProjects_Gantt '01/01/2015', '05/31/2015' ");
$statement->execute();
$results=$statement_row=$statement->fetch(PDO::FETCH_ASSOC);

foreach($results as $row) {
   var_dump($row);
}

...and I got this:

Warning: Invalid argument supplied for foreach() in /var/www/html/sasite/GANT/mssql_sandbox.php on line 30  

I'm just throwing stuff up against the wall trying to hit something.

What do you think?
0
brucegustPHP DeveloperAuthor Commented:
I can run this directly in MSSQL Studio and get data:

EXEC stp_Select_REPORT_DevelopeProjects_Gantt '01/01/2015', '05/31/2015'

It's been confirmed since my last post that my pdo connection does have the right permissions, so the snag is in the way I'm attempting to pass my variables into the stored proc.

Getting closer...
0
zephyr_hex (Megan)DeveloperCommented:
did you add in the php error reporting?  also, try var_dump($results) before the foreach loop.  what i'm looking for is to see if SQL is returning an error.  or php (which would show if you have the php error reporting enabled)
0
brucegustPHP DeveloperAuthor Commented:
I did. Here's the whole thing:

<?php
//ERROR REPORTING
ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(E_ALL); 
                                          
require_once('projectpageclass.php');
$new_page= new ProjectPage;
$page_title="South Area Network Development Project List";

$start_date="01/01/2015";
$end_date="10/31/2015";

include('mssql_db_cred.php');

$mssql_pdo = new PDO("dblib:host=".$mssql_cred_data['server'].";dbname=".$mssql_cred_data['dbname'],$mssql_cred_data['user'],$mssql_cred_data['pw']); 
$mssql_pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
if(!$mssql_pdo)
{
	echo "no connection";
}

$body="<br><br>here we go...<br><br>";


$statement = $mssql_pdo->prepare("EXEC stp_Select_REPORT_DevelopeProjects_Gantt '01/01/2015', '05/31/2015' ");
$statement->execute();
$results=$statement_row=$statement->fetch(PDO::FETCH_ASSOC);

foreach($results as $row) {
   var_dump($row);
}
/*
$statement = $mssql_pdo->prepare("exec stp_Select_REPORT_DevelopeProjects_Gantt ?, ?");
$statement->bindParam(1, $start_date);
$statement->bindParam(2, $end_date);
$statement->execute(); 

if(!$statement)
{
echo "it didn't work";
}	

$results = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach($results as $row) {
   var_dump($row);
}*/

                                          

/*while ($statement_row=$statement->fetch(PDO::FETCH_ASSOC))
{
	$body.=$statement_row['str_Full_Name_Developer	'].'<br>';
}*/


$new_page->setBody($body);

echo $new_page->display();
?>

Open in new window


...and here's the error as of this am...

Warning: Invalid argument supplied for foreach() in /var/www/html/sasite/GANT/mssql_sandbox.php on line 30
0
Ray PaseurCommented:
Most of the PDO functions set values that represent either success or failure, and you can often query these values by inspecting the return from the functions or by examining a property of the objects they create.  Usually you want to check things like PDO::errorInfo
http://php.net/manual/en/pdo.errorinfo.php
http://php.net/manual/en/class.pdo.php

Also, since your script sets PDO::ERRMODE_EXCEPTION I would expect you want to wrap all of these function calls in try{} catch{} blocks.
http://php.net/manual/en/class.pdostatement.php

What is the return value in $statement_row after line 28?
0
brucegustPHP DeveloperAuthor Commented:
Ray!

Here's where I'm at:

$statement = $mssql_pdo->prepare("EXEC stp_Select_REPORT_DevelopeProjects_Gantt '01/01/2015', '05/31/2015'");
if (!$statement) {
    echo "\nPDO::errorInfo():\n";
    print_r($mssql->errorInfo());
}
//$statement = $mssql_pdo->prepare("exec stp_Select_REPORT_SSRS_DeveloperTotalBenefitHours");
$statement->execute();
$statement_row=$statement->fetch(PDO::FETCH_ASSOC);

foreach($statement_row as $row)
{
echo $row['str_Tool'];
}

First off, I've confirmed my permissions with the MSSQL Server
Second, I can run the Stored Proc exactly as I've got it written in my code in MSSQL Studio, so that part of the code is sound
Third, I applied your suggestion as far as putting an "if" statement immediately after the $statement and I get no error

I still get "Warning: Invalid argument supplied for foreach() in /var/www/html/sasite/GANT/mssql_sandbox.php on line 29 " which refers to my foreach statement.

I can run the stored proc in MSSQL Studio, my permissions are valid...

What am I missing?
0
brucegustPHP DeveloperAuthor Commented:
Here's something that may amount to a helpful piece of info. When I do this:

  $sth = $mssql_pdo->prepare("EXEC stp_Select_REPORT_DevelopeProjects_Gantt ?, ?");
  $sth->bindParam(1, $start_date);
  $sth->bindParam(2, $end_date);
  $sth->execute();
var_dump($sth);

I get this:

object(PDOStatement)#2 (1) { ["queryString"]=> string(50) "EXEC stp_Select_REPORT_DevelopeProjects_Gantt ?, ?" }

Does that say anything to anybody?

When I do this:

$statement = $mssql_pdo->prepare("EXEC stp_Select_REPORT_DevelopeProjects_Gantt '01/01/2015', '05/31/2015'");
if (!$statement) {
    echo "\nPDO::errorInfo():\n";
    print_r($mssql->errorInfo());
}
$statement->execute();
var_dump($statement);
echo "<br>";
$statement_row=$statement->fetch(PDO::FETCH_ASSOC);
var_dump($statement_row);

I get this:

object(PDOStatement)#2 (1) { ["queryString"]=> string(72) "EXEC stp_Select_REPORT_DevelopeProjects_Gantt '01/01/2015', '05/31/2015'" }

bool(false)

What does that mean? What does it point to that needs to be fixed so I can get some data?
0
Ray PaseurCommented:
What is the output of var_dump($results) immediately after the $results variable is created?
0
brucegustPHP DeveloperAuthor Commented:
Here you go, Ray:

$statement = $mssql_pdo->prepare("EXEC stp_Select_REPORT_DevelopeProjects_Gantt '01/01/2015', '05/31/2015' ");
$statement->execute();
$results=$statement_row=$statement->fetch(PDO::FETCH_ASSOC);
var_dump($results);

The result...

bool(false)
0
brucegustPHP DeveloperAuthor Commented:
We have progress!

Here's what I've got:

$start = '01/01/2015';
$stop = '05/31/2015';

$query = "EXECUTE stp_Select_REPORT_DevelopeProjects_Gantt :DataDisplay_Start, :DataDisplay_End";
$stpro = $mssql_pdo->prepare($query);
$stpro->bindParam(':DataDisplay_Start', $start);
$stpro->bindParam(':DataDisplay_End', $stop);

// call the stored procedure
$stpro->execute();

$results = $stpro->fetchALL(PDO::FETCH_ASSOC);
var_dump($results);
foreach($results as $row)
        {
                echo $row['str_Tool'].'<br>';
        }

Open in new window


When I do a var_dump after the execute, I get bool(true)!

Yes!

But when I go to retrieve the data, I get:

array(0) { }

What do you think?
0
Ray PaseurCommented:
Looks like the query did not find any data that matched the WHERE conditions.
0
zephyr_hex (Megan)DeveloperCommented:
single quotes and double quotes are somewhat different in php.  just to test, try wrapping your date strings in double quotes instead of single.

$start = "01/01/2015";
$stop = "05/31/2015";

Open in new window


It's a longshot, but easy to try.
0
Ray PaseurCommented:
Might be a very "long shot."  Here is how quotes work in PHP:
http://www.experts-exchange.com/articles/12241/Quotation-Marks-in-PHP.html
0
brucegustPHP DeveloperAuthor Commented:
Guys, take a look at this:

I ran this:

$sql="SELECT DP.int_DEVELOPERS_Project_Id as project_id FROM tbl_DEVELOPERS_Projects DP WHERE DP.dte_DateStarted BETWEEN '2015-01-01' AND '2015-05-31'";
$query=$mssql_pdo->prepare($sql);
$query->execute();
$results=$query->fetchALL(PDO::FETCH_ASSOC);
//var_dump($results);
foreach($results as $row)
{
      echo $row['project_id'].'<br>';
}

...and I got:

yippee!
The thing that has me stoked is that this little piece of code demos the fact that I've got a sound connection to my database, there is data to be retrieved within the date parameters that I've been working with so that means the thing that is flawed with this whole operation is the way in which I'm passing my date parameters into the stored procedure.

Here's the stored procedure, which works and returns data between 01/01/2015 and 05/31/2015...

CREATE TABLE #tmp_Projects
	(
			id												int IDENTITY,
			int_DEVELOPERS_Project_Id						int,
			str_ProjectId									varchar(MAX),
			int_DEVELOPERS_Tool_Id							int,
			int_ProjectRank									int,
			int_DEVELOPERS_ProjectType_Id					int,
			str_ProjectName									varchar(MAX),
			int_Developer_Id								int,
			str_Description									varchar(MAX),
			int_DEVELOPERS_ProjectPriority_Id				int,
			bit_Is_Upcoming									bit,
			int_ProjectSubmitter_Id							int,
			str_DateSubmitted								varchar(MAX),
			dte_DateSubmitted								datetime,
			int_DEVELOPERS_DocumentationStatus_Id			int,
			int_DEVELOPERS_ProjectStatus_Id					int,
			int_DEVELOPERS_LevelOfEffort_Id					int,
			dec_PercentageCompleted							decimal(10,8),
			int_BenefitEquation								int,
			str_CurrentStatus								varchar(MAX),
			str_RequestorDueDate							varchar(MAX),
			str_DateStarted									varchar(MAX),
			str_DocumentationCompletionDate					varchar(MAX),
			str_TargetCompletionDate						varchar(MAX),
			str_DateCompleted								varchar(MAX),
			str_DateOnProduction							varchar(MAX),
			dte_RequestorDueDate							datetime,
			dte_DateStarted									datetime,
			dte_TargetCompletionDate						datetime,
			dte_DateCompleted								datetime,
			dte_DateOnProduction							datetime,
			str_Notes										varchar(MAX),
			bit_Is_Active									bit,
			int_Insert_User_Id								int,
			dte_Inserted_Date								datetime,
			int_Update_User_Id								int,
			dte_Updated_Date								datetime,
			str_Tool										varchar(MAX),
			str_ProjectPriority								varchar(MAX),
			str_ProjectType									varchar(MAX),
			str_DocumentationStatus							varchar(MAX),
			str_ProjectStatus								varchar(MAX),
			str_LevelOfEffort								varchar(MAX),
			str_Full_Name_Developer							varchar(MAX),
			str_Full_Name_Submitter							varchar(MAX),
			str_Full_Name_InsertUser						varchar(MAX),
			str_Full_Name_UpdateUser						varchar(MAX),
			str_Full_Name_Owner								varchar(MAX),
			DataDisplay_StartDate							datetime,
			DataDisplay_EndDate								datetime,
			DataDisplay_StartCell							int,
			DataDisplay_EndCell								int,
			TotalCellSpan									int,
			DataDisplay_StartWeek							int,
			DataDisplay_EndWeek								int,
			DataDisplay_Row									int

	)

	INSERT INTO #tmp_Projects
	(
			int_DEVELOPERS_Project_Id						,
			str_ProjectId									,
			int_DEVELOPERS_Tool_Id							,
			int_ProjectRank									,
			int_DEVELOPERS_ProjectType_Id					,
			str_ProjectName									,
			int_Developer_Id								,
			str_Description									,
			int_DEVELOPERS_ProjectPriority_Id				,
			bit_Is_Upcoming									,
			int_ProjectSubmitter_Id							,
			str_DateSubmitted								,
			dte_DateSubmitted								,
			int_DEVELOPERS_DocumentationStatus_Id			,
			int_DEVELOPERS_ProjectStatus_Id					,
			int_DEVELOPERS_LevelOfEffort_Id					,
			dec_PercentageCompleted							,
			int_BenefitEquation								,
			str_CurrentStatus								,
			str_RequestorDueDate							,
			str_DateStarted									,
			str_DocumentationCompletionDate					,
			str_TargetCompletionDate						,
			str_DateCompleted								,
			str_DateOnProduction							,
			dte_RequestorDueDate							,
			dte_DateStarted									,
			dte_TargetCompletionDate						,
			dte_DateCompleted								,
			dte_DateOnProduction							,
			str_Notes										,
			bit_Is_Active									,
			int_Insert_User_Id								,
			dte_Inserted_Date								,
			int_Update_User_Id								,
			dte_Updated_Date								,
			str_Tool										,
			str_ProjectPriority								,
			str_ProjectType									,
			str_DocumentationStatus							,
			str_ProjectStatus								,
			str_LevelOfEffort								,
			str_Full_Name_Developer							,
			str_Full_Name_Submitter							,
			str_Full_Name_InsertUser						,
			str_Full_Name_UpdateUser						,
			str_Full_Name_Owner								,
			DataDisplay_StartDate							,
			DataDisplay_EndDate								,
			DataDisplay_StartCell							,
			DataDisplay_EndCell								,
			TotalCellSpan									,
			DataDisplay_StartWeek							,
			DataDisplay_EndWeek								

	)
  		SELECT		DP.int_DEVELOPERS_Project_Id				,
					DP.str_ProjectId							,
					DP.int_DEVELOPERS_Tool_Id					,
					ISNULL(DP.int_ProjectRank, 999)	AS 	int_ProjectRank,
					DP.int_DEVELOPERS_ProjectType_Id			,
					DP.str_ProjectName							,
					DP.int_Developer_Id							,
					DP.str_Description							,
					DP.int_DEVELOPERS_ProjectPriority_Id		,
					DP.bit_Is_Upcoming							,
					DP.int_ProjectSubmitter_Id					,
					DP.str_DateSubmitted						,
					DP.dte_DateSubmitted						,
					DP.int_DEVELOPERS_DocumentationStatus_Id	,
					DP.int_DEVELOPERS_ProjectStatus_Id			,
					DP.int_DEVELOPERS_LevelOfEffort_Id			,
					DP.dec_PercentageCompleted					,
					DP.int_BenefitEquation						,
					DP.str_CurrentStatus						,
					DP.str_RequestorDueDate						,
					DP.str_DateStarted							,
					DP.str_DocumentationCompletionDate			,
					DP.str_TargetCompletionDate					,
					DP.str_DateCompleted						,
					DP.str_DateOnProduction						,
					DP.dte_RequestorDueDate						,
					DP.dte_DateStarted							,
					DP.dte_TargetCompletionDate					,
					DP.dte_DateCompleted						,
					DP.dte_DateOnProduction						,
					DP.str_Notes								,
					DP.bit_Is_Active							,
					DP.int_Insert_User_Id						,
					DP.dte_Inserted_Date						,
					DP.int_Update_User_Id						,
					DP.dte_Updated_Date							,

					DT.str_Tool									,
					DPP.str_ProjectPriority						,
					DPt.str_ProjectType							,
					DDS.str_DocumentationStatus					,
					DPS.str_ProjectStatus						,
					DLE.str_LevelOfEffort						,
		
					(CId.str_First_Name + ' ' + CId.str_Last_Name) AS str_Full_Name_Developer	,
					(CIs.str_First_Name + ' ' + CIs.str_Last_Name) AS str_Full_Name_Submitter	,
					(CIi.str_First_Name + ' ' + CIi.str_Last_Name) AS str_Full_Name_InsertUser	,
					(CIu.str_First_Name + ' ' + CIu.str_Last_Name) AS str_Full_Name_UpdateUser	,
					(CIo.str_First_Name + ' ' + CIo.str_Last_Name) AS str_Full_Name_Owner		,

					ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted) AS [DataDisplay_StartDate]														,
					ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate) AS [DataDisplay_EndDate]												,
					CASE WHEN (ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted) < @DataDisplay_Start) THEN 0 ELSE DateDiff(day, @DataDisplay_Start, ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted)) END AS [DataDisplay_StartCell]					,
					DateDiff(day, @DataDisplay_Start, ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate)) AS [DataDisplay_EndCell]			,
					DateDiff(day, ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted), ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate)) + 1 AS [TotalCellSpan]			,
					(DateDiff(day, @DataDisplay_Start, ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted))/7) AS [DataDisplay_StartWeek]				,
					(DateDiff(day, @DataDisplay_Start, ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate))/7) AS [DataDisplay_StartWeek]

		FROM		tbl_DEVELOPERS_Projects					DP
		LEFT JOIN	tbl_DEVELOPERS_Tools					DT	ON DP.int_DEVELOPERS_Tool_Id = DT.int_DEVELOPERS_Tool_Id AND DT.bit_Is_Active = 1
		LEFT JOIN	tbl_DEVELOPERS_ProjectPriorities		DPP	ON DP.int_DEVELOPERS_ProjectPriority_Id = DPP.int_DEVELOPERS_ProjectPriority_Id AND DPP.bit_Is_Active = 1
		LEFT JOIN	tbl_DEVELOPERS_DocumentationStatuses	DDS	ON DP.int_DEVELOPERS_DocumentationStatus_Id = DDS.int_DEVELOPERS_DocumentationStatus_Id AND DDS.bit_Is_Active = 1
		LEFT JOIN	tbl_DEVELOPERS_ProjectStatuses			DPS	ON DP.int_DEVELOPERS_ProjectStatus_Id = DPS.int_DEVELOPERS_ProjectStatus_Id AND DPS.bit_Is_Active = 1
		LEFT JOIN	tbl_DEVELOPERS_LevelOfEfforts			DLE	ON DP.int_DEVELOPERS_LevelOfEffort_Id = DLE.int_DEVELOPERS_LevelOfEffort_Id AND DLE.bit_Is_Active = 1
		LEFT JOIN	tbl_DEVELOPERS_ProjectTypes				DPT	ON DP.int_DEVELOPERS_ProjectType_Id = DPT.int_DEVELOPERS_ProjectType_Id AND DPT.bit_Is_Active = 1

		LEFT JOIN	tbl_CORE_Individuals					CId	ON DP.int_Developer_Id = CId.int_CORE_Individual_Id AND CId.bit_Is_Active = 1
		LEFT JOIN	tbl_CORE_Individuals					CIs	ON DP.int_ProjectSubmitter_Id = CIs.int_CORE_Individual_Id AND CIs.bit_Is_Active = 1
		LEFT JOIN	tbl_CORE_Individuals					CIi	ON DP.int_Insert_User_Id = CIi.int_CORE_Individual_Id AND CIi.bit_Is_Active = 1
		LEFT JOIN	tbl_CORE_Individuals					CIu	ON DP.int_Update_User_Id = CIu.int_CORE_Individual_Id AND CIu.bit_Is_Active = 1
		LEFT JOIN	tbl_CORE_Individuals					CIo	ON DT.int_Owner_Id = CIo.int_CORE_Individual_Id AND CIo.bit_Is_Active = 1

		WHERE		(ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted) between @DataDisplay_Start AND @DataDisplay_End)
		OR			(ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate) between @DataDisplay_Start AND @DataDisplay_End)
		OR			(ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted) < @DataDisplay_Start AND ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate) > @DataDisplay_End)
	ORDER BY [DataDisplay_StartCell], [DataDisplay_EndCell]


	DECLARE @rec_id int
	DECLARE @StartCell int
	DECLARE @EndCell int

	DECLARE proj_cursor CURSOR FOR 
	SELECT ID, ISNULL([DataDisplay_StartCell], 0), [DataDisplay_EndCell]
	FROM #tmp_Projects
	ORDER BY ID;

	OPEN proj_cursor

	FETCH NEXT FROM proj_cursor 
	INTO @rec_id, @StartCell, @EndCell

	WHILE @@FETCH_STATUS = 0
	BEGIN

		DECLARE @nextRecord int
		SET @nextRecord = (SELECT (ISNULL(MAX(DataDisplay_Row), 0) + 1) FROM #tmp_Projects WHERE [DataDisplay_EndCell] >= @StartCell AND DataDisplay_Row IS NOT NULL)

		DECLARE @nextFreeRecord int
		SET @nextFreeRecord = (SELECT TOP 1 DataDisplay_Row FROM #tmp_Projects WHERE DataDisplay_Row NOT IN (SELECT DataDisplay_Row FROM #tmp_Projects WHERE [DataDisplay_EndCell] >= @StartCell AND DataDisplay_Row IS NOT NULL) ORDER BY DataDisplay_Row)

		IF @nextFreeRecord IS NOT NULL
		BEGIN
			SET @nextRecord = @nextFreeRecord
		END

		UPDATE #tmp_Projects SET DataDisplay_Row = @nextRecord WHERE id = @rec_id

		FETCH NEXT FROM proj_cursor 
		INTO @rec_id, @StartCell, @EndCell

Open in new window


Here's how it appears in MSSQL Studio:

MSSQL Studio screenshot
Here's the way I'm calling it presently in my page:

$start = '01/01/2015';
$stop = '05/31/2015';

$query = "EXECUTE stp_Select_REPORT_DevelopeProjects_Gantt :DataDisplay_Start, :DataDisplay_End";
$stpro = $mssql_pdo->prepare($query);
$stpro->bindParam(':DataDisplay_Start', $start);
$stpro->bindParam(':DataDisplay_End', $stop);

// call the stored procedure
$stpro->execute();

$results = $stpro->fetchALL(PDO::FETCH_ASSOC);
var_dump($results);
foreach($results as $row)
{
	echo $row['str_Tool'].'<br>';
}

Open in new window


So, we've got:

a good connection
confirmed data to be retrieved
a successful select statement using the same criteria as the stored proc
data being accurately fetched and displayed

...so the only thing, by process of elimination, is the manner in which I'm passing my dates into the stored procedure.

I'm hoping that either Ray or zephyr - one of you two are looking at this and going "Ahh HAA! There's the problem...!"

What do you think?

BTW: The result of the code that's attempting to call the stored procedure is:

array(0) { }

Ray, you took that to mean an empty array. I wanted to chase that "lead" down to see if it didn't help clarify where the problem is and I think it has, but I don't know how to solve the problem.

Thoughts?
0
zephyr_hex (Megan)DeveloperCommented:
I originally thought this is an issue with the format of the date strings (which is what I was trying to get at with my last comment).  But now I'm wondering if you need to define a return parameter in your pdo command.  But before you go down that route, check to see if you have the stored procedure defined AS SET NOCOUNT ON.  If it's not, you might be getting multiple result sets, and in that case, try advancing to the next set.

If that doesn't fix the issue, you could run SQL Profiler while issuing the query from your php, and capture exactly what query is being run, and compare that against what happens when you run the stored procedure from SSMS.

If you find the queries are the same, try adding a return parameter.  Here's an example.Here's an example.
0
brucegustPHP DeveloperAuthor Commented:
OK, zephyr!

First off, this is a portion of an email I got from one of the .NET folks. She says:

"I added the “SET NOCOUNT OFF” so if that works, that would be awesome!"

I'm in uncharted territory, here, so I'm not sure how that affects your input, but I wanted you to see that.

Next, I ran this code:

$start = '01/01/2015';
$stop = '05/31/2015';

$query = "EXECUTE stp_Select_REPORT_DevelopeProjects_Gantt :DataDisplay_Start, :DataDisplay_End";
$stpro = $mssql_pdo->prepare($query);
$stpro->bindParam(':DataDisplay_Start', $start);
$stpro->bindParam(':DataDisplay_End', $stop);

// call the stored procedure
$stpro->execute();

$i = 1;
do {
    $rowset = $stpro->fetchAll(PDO::FETCH_NUM);
    if ($rowset) {
        printResultSet($rowset, $i);
    }
    $i++;
} while ($stpro->nextRowset());

No errors, just a blank screen.

//this is a note for me: http://php.net/manual/en/pdostatement.nextrowset.php

As far as SQL Profiler, I found this, but wanted to run it past you before I invest a lot of time into it: http://www.nusphere.com/products/php_sql_profiler.htm
0
brucegustPHP DeveloperAuthor Commented:
Never mind the above post. I think that one was doomed from the start, but I did try this:

$start = '01/01/2015';
$stop = '05/31/2015';

$statement = $mssql_pdo->prepare("exec stp_Select_REPORT_DevelopeProjects_Gantt");
$statement->execute(array('$start','$stop');
$results=$statement->fetchALL(PDO::FETCH_ASSOC);
var_dump($results);

...nothing.
0
brucegustPHP DeveloperAuthor Commented:
Tried this (at this point, I'm just throwing stuff up against the wall to see if anything works...)

$start = '01/01/2015';
$stop = '05/31/2015';

$statement = $mssql_pdo->prepare("CREATE TABLE #tmp_Projects
	(
			id												int IDENTITY,
			int_DEVELOPERS_Project_Id						int,
			str_ProjectId									varchar(MAX),
			int_DEVELOPERS_Tool_Id							int,
			int_ProjectRank									int,
			int_DEVELOPERS_ProjectType_Id					int,
			str_ProjectName									varchar(MAX),
			int_Developer_Id								int,
			str_Description									varchar(MAX),
			int_DEVELOPERS_ProjectPriority_Id				int,
			bit_Is_Upcoming									bit,
			int_ProjectSubmitter_Id							int,
			str_DateSubmitted								varchar(MAX),
			dte_DateSubmitted								datetime,
			int_DEVELOPERS_DocumentationStatus_Id			int,
			int_DEVELOPERS_ProjectStatus_Id					int,
			int_DEVELOPERS_LevelOfEffort_Id					int,
			dec_PercentageCompleted							decimal(10,8),
			int_BenefitEquation								int,
			str_CurrentStatus								varchar(MAX),
			str_RequestorDueDate							varchar(MAX),
			str_DateStarted									varchar(MAX),
			str_DocumentationCompletionDate					varchar(MAX),
			str_TargetCompletionDate						varchar(MAX),
			str_DateCompleted								varchar(MAX),
			str_DateOnProduction							varchar(MAX),
			dte_RequestorDueDate							datetime,
			dte_DateStarted									datetime,
			dte_TargetCompletionDate						datetime,
			dte_DateCompleted								datetime,
			dte_DateOnProduction							datetime,
			str_Notes										varchar(MAX),
			bit_Is_Active									bit,
			int_Insert_User_Id								int,
			dte_Inserted_Date								datetime,
			int_Update_User_Id								int,
			dte_Updated_Date								datetime,
			str_Tool										varchar(MAX),
			str_ProjectPriority								varchar(MAX),
			str_ProjectType									varchar(MAX),
			str_DocumentationStatus							varchar(MAX),
			str_ProjectStatus								varchar(MAX),
			str_LevelOfEffort								varchar(MAX),
			str_Full_Name_Developer							varchar(MAX),
			str_Full_Name_Submitter							varchar(MAX),
			str_Full_Name_InsertUser						varchar(MAX),
			str_Full_Name_UpdateUser						varchar(MAX),
			str_Full_Name_Owner								varchar(MAX),
			DataDisplay_StartDate							datetime,
			DataDisplay_EndDate								datetime,
			DataDisplay_StartCell							int,
			DataDisplay_EndCell								int,
			TotalCellSpan									int,
			DataDisplay_StartWeek							int,
			DataDisplay_EndWeek								int,
			DataDisplay_Row									int

	)

	INSERT INTO #tmp_Projects
	(
			int_DEVELOPERS_Project_Id						,
			str_ProjectId									,
			int_DEVELOPERS_Tool_Id							,
			int_ProjectRank									,
			int_DEVELOPERS_ProjectType_Id					,
			str_ProjectName									,
			int_Developer_Id								,
			str_Description									,
			int_DEVELOPERS_ProjectPriority_Id				,
			bit_Is_Upcoming									,
			int_ProjectSubmitter_Id							,
			str_DateSubmitted								,
			dte_DateSubmitted								,
			int_DEVELOPERS_DocumentationStatus_Id			,
			int_DEVELOPERS_ProjectStatus_Id					,
			int_DEVELOPERS_LevelOfEffort_Id					,
			dec_PercentageCompleted							,
			int_BenefitEquation								,
			str_CurrentStatus								,
			str_RequestorDueDate							,
			str_DateStarted									,
			str_DocumentationCompletionDate					,
			str_TargetCompletionDate						,
			str_DateCompleted								,
			str_DateOnProduction							,
			dte_RequestorDueDate							,
			dte_DateStarted									,
			dte_TargetCompletionDate						,
			dte_DateCompleted								,
			dte_DateOnProduction							,
			str_Notes										,
			bit_Is_Active									,
			int_Insert_User_Id								,
			dte_Inserted_Date								,
			int_Update_User_Id								,
			dte_Updated_Date								,
			str_Tool										,
			str_ProjectPriority								,
			str_ProjectType									,
			str_DocumentationStatus							,
			str_ProjectStatus								,
			str_LevelOfEffort								,
			str_Full_Name_Developer							,
			str_Full_Name_Submitter							,
			str_Full_Name_InsertUser						,
			str_Full_Name_UpdateUser						,
			str_Full_Name_Owner								,
			DataDisplay_StartDate							,
			DataDisplay_EndDate								,
			DataDisplay_StartCell							,
			DataDisplay_EndCell								,
			TotalCellSpan									,
			DataDisplay_StartWeek							,
			DataDisplay_EndWeek								

	)
  		SELECT		DP.int_DEVELOPERS_Project_Id				,
					DP.str_ProjectId							,
					DP.int_DEVELOPERS_Tool_Id					,
					ISNULL(DP.int_ProjectRank, 999)	AS 	int_ProjectRank,
					DP.int_DEVELOPERS_ProjectType_Id			,
					DP.str_ProjectName							,
					DP.int_Developer_Id							,
					DP.str_Description							,
					DP.int_DEVELOPERS_ProjectPriority_Id		,
					DP.bit_Is_Upcoming							,
					DP.int_ProjectSubmitter_Id					,
					DP.str_DateSubmitted						,
					DP.dte_DateSubmitted						,
					DP.int_DEVELOPERS_DocumentationStatus_Id	,
					DP.int_DEVELOPERS_ProjectStatus_Id			,
					DP.int_DEVELOPERS_LevelOfEffort_Id			,
					DP.dec_PercentageCompleted					,
					DP.int_BenefitEquation						,
					DP.str_CurrentStatus						,
					DP.str_RequestorDueDate						,
					DP.str_DateStarted							,
					DP.str_DocumentationCompletionDate			,
					DP.str_TargetCompletionDate					,
					DP.str_DateCompleted						,
					DP.str_DateOnProduction						,
					DP.dte_RequestorDueDate						,
					DP.dte_DateStarted							,
					DP.dte_TargetCompletionDate					,
					DP.dte_DateCompleted						,
					DP.dte_DateOnProduction						,
					DP.str_Notes								,
					DP.bit_Is_Active							,
					DP.int_Insert_User_Id						,
					DP.dte_Inserted_Date						,
					DP.int_Update_User_Id						,
					DP.dte_Updated_Date							,

					DT.str_Tool									,
					DPP.str_ProjectPriority						,
					DPt.str_ProjectType							,
					DDS.str_DocumentationStatus					,
					DPS.str_ProjectStatus						,
					DLE.str_LevelOfEffort						,
		
					(CId.str_First_Name + ' ' + CId.str_Last_Name) AS str_Full_Name_Developer	,
					(CIs.str_First_Name + ' ' + CIs.str_Last_Name) AS str_Full_Name_Submitter	,
					(CIi.str_First_Name + ' ' + CIi.str_Last_Name) AS str_Full_Name_InsertUser	,
					(CIu.str_First_Name + ' ' + CIu.str_Last_Name) AS str_Full_Name_UpdateUser	,
					(CIo.str_First_Name + ' ' + CIo.str_Last_Name) AS str_Full_Name_Owner		,

					ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted) AS [DataDisplay_StartDate]														,
					ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate) AS [DataDisplay_EndDate]												,
					CASE WHEN (ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted) < $start) THEN 0 ELSE DateDiff(day, $start, ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted)) END AS [DataDisplay_StartCell]					,
					DateDiff(day, $start, ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate)) AS [DataDisplay_EndCell]			,
					DateDiff(day, ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted), ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate)) + 1 AS [TotalCellSpan]			,
					(DateDiff(day, $start, ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted))/7) AS [DataDisplay_StartWeek]				,
					(DateDiff(day, $start, ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate))/7) AS [DataDisplay_StartWeek]

		FROM		tbl_DEVELOPERS_Projects					DP
		LEFT JOIN	tbl_DEVELOPERS_Tools					DT	ON DP.int_DEVELOPERS_Tool_Id = DT.int_DEVELOPERS_Tool_Id AND DT.bit_Is_Active = 1
		LEFT JOIN	tbl_DEVELOPERS_ProjectPriorities		DPP	ON DP.int_DEVELOPERS_ProjectPriority_Id = DPP.int_DEVELOPERS_ProjectPriority_Id AND DPP.bit_Is_Active = 1
		LEFT JOIN	tbl_DEVELOPERS_DocumentationStatuses	DDS	ON DP.int_DEVELOPERS_DocumentationStatus_Id = DDS.int_DEVELOPERS_DocumentationStatus_Id AND DDS.bit_Is_Active = 1
		LEFT JOIN	tbl_DEVELOPERS_ProjectStatuses			DPS	ON DP.int_DEVELOPERS_ProjectStatus_Id = DPS.int_DEVELOPERS_ProjectStatus_Id AND DPS.bit_Is_Active = 1
		LEFT JOIN	tbl_DEVELOPERS_LevelOfEfforts			DLE	ON DP.int_DEVELOPERS_LevelOfEffort_Id = DLE.int_DEVELOPERS_LevelOfEffort_Id AND DLE.bit_Is_Active = 1
		LEFT JOIN	tbl_DEVELOPERS_ProjectTypes				DPT	ON DP.int_DEVELOPERS_ProjectType_Id = DPT.int_DEVELOPERS_ProjectType_Id AND DPT.bit_Is_Active = 1

		LEFT JOIN	tbl_CORE_Individuals					CId	ON DP.int_Developer_Id = CId.int_CORE_Individual_Id AND CId.bit_Is_Active = 1
		LEFT JOIN	tbl_CORE_Individuals					CIs	ON DP.int_ProjectSubmitter_Id = CIs.int_CORE_Individual_Id AND CIs.bit_Is_Active = 1
		LEFT JOIN	tbl_CORE_Individuals					CIi	ON DP.int_Insert_User_Id = CIi.int_CORE_Individual_Id AND CIi.bit_Is_Active = 1
		LEFT JOIN	tbl_CORE_Individuals					CIu	ON DP.int_Update_User_Id = CIu.int_CORE_Individual_Id AND CIu.bit_Is_Active = 1
		LEFT JOIN	tbl_CORE_Individuals					CIo	ON DT.int_Owner_Id = CIo.int_CORE_Individual_Id AND CIo.bit_Is_Active = 1

		WHERE		(ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted) between $start AND $stop)
		OR			(ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate) between $start AND $stop)
		OR			(ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted) < $start AND ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate) > $stop)
	ORDER BY [DataDisplay_StartCell], [DataDisplay_EndCell]");
$statement->execute();
$results=$statement->fetchALL(PDO::FETCH_ASSOC);
var_dump($results);

Open in new window


This is a portion of the stored procedure. No errors, just "array(0) { } "
0
zephyr_hex (Megan)DeveloperCommented:
SQL Profiler is part of the SQL installation.  Here's how you access it.  Although if you're not familiar with how to use it, you'll probably want to work through a tutorial for how to run it and filter the results.  SQL Profiler will let you see what query is being run (which would tell you if the problem is with the parameters being passed in).

With your last attempt where you're executing the stored procedure code from php -- have you tried formatting your date variables to '2015-01-01' format?  That would be an easy thing to try, and if it works, you could reformat your dates in php and pass them in YYYY-MM-DD format.


And regarding 'SET NOCOUNT ON' : when you run an update, or insert, or delete in T-SQL, you get back a message like '2 records deleted'.  SET NOCOUNT ON suppresses those messages that tell you how many records were involved.  ANY output from your stored procedure will be returned to call from php, and you don't want record counts returned so it's best to suppress those messages.
0
brucegustPHP DeveloperAuthor Commented:
zephyr - tried reformatting the dates, nothing. Blank page.

Here's something else that I wanted you to see...I did this:

$query = "EXECUTE stp_Select_REPORT_DevelopeProjects_Gantt :DataDisplay_Start, :DataDisplay_End";
$stpro = $mssql_pdo->prepare($query);
$stpro->bindParam(':DataDisplay_Start', $start);
$stpro->bindParam(':DataDisplay_End', $stop);

// call the stored procedure
$stpro->execute();

$results = $stpro->fetchALL(PDO::FETCH_ASSOC);
var_dump($results);
foreach($results as $row)
{
      echo $row['str_Tool'].'<br>';
}

The big thing being that I didn't define $start or $end and I got...

array(0) { }

In my mind, that's huge, big WRONG. There should be errors jumping out from all over because I'm looking for a $start and $stop that's not been defined. And yet, we get an empty array(0) {}.

Pointless.

I'm getting close to giving up. I've been at this for weeks off and on and I can't find the answer. I'll give your SQL Profiler a shot...
0
zephyr_hex (Megan)DeveloperCommented:
you might try the return parameter, as well.  also, you have PHP errors enabled, right?  if not, put this at the top of your php file:

//ERROR REPORTING
ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(E_ALL); 

Open in new window

0
brucegustPHP DeveloperAuthor Commented:
Here's my code as it exists right now in its entirety. You mentioned "return parameter" and I missed that. What is that and where does it belong in relation to my code?

I'll let you know what the SQL Profiler reveals after I have a chance to run through that with my counterpart this morning.

Here's the code:

date_default_timezone_set('America/Chicago');
//ERROR REPORTING
ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(E_ALL); 

include('mssql_db_cred.php');

$mssql_pdo = new PDO("dblib:host=".$mssql_cred_data['server'].";dbname=".$mssql_cred_data['dbname'],$mssql_cred_data['user'],$mssql_cred_data['pw']); 
$mssql_pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
if(!$mssql_pdo)
{
	echo "no connection";
}

//lines 19-35 work, as far as a sound query, but there appears to be no data, so...


$query = "EXECUTE stp_Select_REPORT_DevelopeProjects_Gantt :DataDisplay_Start, :DataDisplay_End";
$stpro = $mssql_pdo->prepare($query);
$stpro->bindParam(':DataDisplay_Start', $start);
$stpro->bindParam(':DataDisplay_End', $stop);

// call the stored procedure
$stpro->execute();

$results = $stpro->fetchALL(PDO::FETCH_ASSOC);
var_dump($results);
foreach($results as $row)
{
	echo $row['str_Tool'].'<br>';
}
?>

Open in new window

0
brucegustPHP DeveloperAuthor Commented:
Here's the result of the profiler...

Profile screenshot
So, the stored procedure is firing correctly and the parameters are being passed into the stored proc correctly.

Sweet!

So the issue, then, is displaying the data. I should say, too, that you can take "EXECUTE stp_Select_REPORT_DevelopeProjects_Gannt '01/01/2015', '05/31/2015'" as is and run it in MSSQL Studio and you get 55 rows.

So, what then is wrong with:

$results = $stpro->fetchALL(PDO::FETCH_ASSOC);
var_dump($results);
foreach($results as $row)
{
      echo $row['str_Tool'].'<br>';
}

What is it about that code that fails to deliver the results that are being retrieved?
0
zephyr_hex (Megan)DeveloperCommented:
I'm glad you were able to confirm the query execution using profiler.  that narrows down the problem.

Try using :
$mssql_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  //enable errors
$stpro->execute();
$result = $stpro->fetchAll();  //omit the setting and use lower case L's (not fetchALL)
var_dump($result);

Open in new window

0
brucegustPHP DeveloperAuthor Commented:
I did this:

$mssql_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  //enable errors
$stpro->execute();
$result = $stpro->fetchAll();  //omit the setting and use lower case L's (not fetchALL)
var_dump($result);

...and got this:

array(0) { }

Same thing.

Is it possible that the results are coming from an entity that is not an array? Would that explain an empty array?
0
zephyr_hex (Megan)DeveloperCommented:
no.  fetchAll creates an array out of all result sets.  if it's empty, that means no results were returned.  

here's an example that shows another way to get multiple result sets.
0
brucegustPHP DeveloperAuthor Commented:
Hey, zephyr...

Here's what I did:

$mssql_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  //enable errors

$query = "{:retval=CALL stp_Select_REPORT_DevelopeProjects_Gantt (@DataDisplay_Start=:start, :@DataDisplay_End=:stop)}";
$stmt = $mssql_pdo->prepare($query);

$retval = null;
$start='01/01/2015';
$stop='05/31/2015';

$stmt->bindParam('retval', $retval, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 4);
$stmt->bindParam('start', $start, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 50);
$stmt->bindParam('stop', $stop, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 50);

$stmt->execute();

$results = array();

do 
{
    $results []= $stmt->fetchAll();
} 

while ($stmt->nextRowset());
echo '<pre>';
print_r($retval);echo "\n"; // the return value: 5
print_r($start);echo "\n"; // 
print_r($results);echo "\n"; // all record sets
echo '</pre>';

$stmt->closeCursor();
unset($stmt);

Open in new window


Not sound like a teacup, but I'm in some high weeds right now and I don't even know where to start to correct the error which is:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 102 General SQL Server error: Check messages from the SQL Server [102] (severity 15) [(null)]' in /var/www/html/sasite/GANT/mssql_sandbox.php:32 Stack trace: #0 /var/www/html/sasite/GANT/mssql_sandbox.php(32): PDOStatement->execute() #1 {main} thrown in /var/www/html/sasite/GANT/mssql_sandbox.php on line 32

Line 32 is $stmt->execute();

What do you think?

I wasn't sure about the manner in which I need to bind my parameters. I googled it and was able to determine that you pass a date as a string. I figured you might be able to confirm or correct that.
0
zephyr_hex (Megan)DeveloperCommented:
Yes, you want to pass the dates as strings.

Is you stored procedure using a return parameter?  You would see something like this at the end:

SELECT @variable

If it's not using a return parameter, we're barking up the wrong tree.
0
brucegustPHP DeveloperAuthor Commented:
Here's the stored procedure...

BTW: Thanks for taking the time to weigh in on this. I just concluded an hour long webex meeting with one of the .NET administrators and everybody is baffled...

Here you go:

CREATE PROCEDURE [dbo].[stp_Select_REPORT_DevelopeProjects_Gantt]
(
	@DataDisplay_Start	date	,
	@DataDisplay_End	date	
)
AS
BEGIN

	SET NOCOUNT ON

	CREATE TABLE #tmp_Projects
	(
			id												int IDENTITY,
			int_DEVELOPERS_Project_Id						int,
			str_ProjectId									varchar(MAX),
			int_DEVELOPERS_Tool_Id							int,
			int_ProjectRank									int,
			int_DEVELOPERS_ProjectType_Id					int,
			str_ProjectName									varchar(MAX),
			int_Developer_Id								int,
			str_Description									varchar(MAX),
			int_DEVELOPERS_ProjectPriority_Id				int,
			bit_Is_Upcoming									bit,
			int_ProjectSubmitter_Id							int,
			str_DateSubmitted								varchar(MAX),
			dte_DateSubmitted								datetime,
			int_DEVELOPERS_DocumentationStatus_Id			int,
			int_DEVELOPERS_ProjectStatus_Id					int,
			int_DEVELOPERS_LevelOfEffort_Id					int,
			dec_PercentageCompleted							decimal(10,8),
			int_BenefitEquation								int,
			str_CurrentStatus								varchar(MAX),
			str_RequestorDueDate							varchar(MAX),
			str_DateStarted									varchar(MAX),
			str_DocumentationCompletionDate					varchar(MAX),
			str_TargetCompletionDate						varchar(MAX),
			str_DateCompleted								varchar(MAX),
			str_DateOnProduction							varchar(MAX),
			dte_RequestorDueDate							datetime,
			dte_DateStarted									datetime,
			dte_TargetCompletionDate						datetime,
			dte_DateCompleted								datetime,
			dte_DateOnProduction							datetime,
			str_Notes										varchar(MAX),
			bit_Is_Active									bit,
			int_Insert_User_Id								int,
			dte_Inserted_Date								datetime,
			int_Update_User_Id								int,
			dte_Updated_Date								datetime,
			str_Tool										varchar(MAX),
			str_ProjectPriority								varchar(MAX),
			str_ProjectType									varchar(MAX),
			str_DocumentationStatus							varchar(MAX),
			str_ProjectStatus								varchar(MAX),
			str_LevelOfEffort								varchar(MAX),
			str_Full_Name_Developer							varchar(MAX),
			str_Full_Name_Submitter							varchar(MAX),
			str_Full_Name_InsertUser						varchar(MAX),
			str_Full_Name_UpdateUser						varchar(MAX),
			str_Full_Name_Owner								varchar(MAX),
			DataDisplay_StartDate							datetime,
			DataDisplay_EndDate								datetime,
			DataDisplay_StartCell							int,
			DataDisplay_EndCell								int,
			TotalCellSpan									int,
			DataDisplay_StartWeek							int,
			DataDisplay_EndWeek								int,
			DataDisplay_Row									int

	)

	INSERT INTO #tmp_Projects
	(
			int_DEVELOPERS_Project_Id						,
			str_ProjectId									,
			int_DEVELOPERS_Tool_Id							,
			int_ProjectRank									,
			int_DEVELOPERS_ProjectType_Id					,
			str_ProjectName									,
			int_Developer_Id								,
			str_Description									,
			int_DEVELOPERS_ProjectPriority_Id				,
			bit_Is_Upcoming									,
			int_ProjectSubmitter_Id							,
			str_DateSubmitted								,
			dte_DateSubmitted								,
			int_DEVELOPERS_DocumentationStatus_Id			,
			int_DEVELOPERS_ProjectStatus_Id					,
			int_DEVELOPERS_LevelOfEffort_Id					,
			dec_PercentageCompleted							,
			int_BenefitEquation								,
			str_CurrentStatus								,
			str_RequestorDueDate							,
			str_DateStarted									,
			str_DocumentationCompletionDate					,
			str_TargetCompletionDate						,
			str_DateCompleted								,
			str_DateOnProduction							,
			dte_RequestorDueDate							,
			dte_DateStarted									,
			dte_TargetCompletionDate						,
			dte_DateCompleted								,
			dte_DateOnProduction							,
			str_Notes										,
			bit_Is_Active									,
			int_Insert_User_Id								,
			dte_Inserted_Date								,
			int_Update_User_Id								,
			dte_Updated_Date								,
			str_Tool										,
			str_ProjectPriority								,
			str_ProjectType									,
			str_DocumentationStatus							,
			str_ProjectStatus								,
			str_LevelOfEffort								,
			str_Full_Name_Developer							,
			str_Full_Name_Submitter							,
			str_Full_Name_InsertUser						,
			str_Full_Name_UpdateUser						,
			str_Full_Name_Owner								,
			DataDisplay_StartDate							,
			DataDisplay_EndDate								,
			DataDisplay_StartCell							,
			DataDisplay_EndCell								,
			TotalCellSpan									,
			DataDisplay_StartWeek							,
			DataDisplay_EndWeek								

	)
  		SELECT		DP.int_DEVELOPERS_Project_Id				,
					DP.str_ProjectId							,
					DP.int_DEVELOPERS_Tool_Id					,
					ISNULL(DP.int_ProjectRank, 999)	AS 	int_ProjectRank,
					DP.int_DEVELOPERS_ProjectType_Id			,
					DP.str_ProjectName							,
					DP.int_Developer_Id							,
					DP.str_Description							,
					DP.int_DEVELOPERS_ProjectPriority_Id		,
					DP.bit_Is_Upcoming							,
					DP.int_ProjectSubmitter_Id					,
					DP.str_DateSubmitted						,
					DP.dte_DateSubmitted						,
					DP.int_DEVELOPERS_DocumentationStatus_Id	,
					DP.int_DEVELOPERS_ProjectStatus_Id			,
					DP.int_DEVELOPERS_LevelOfEffort_Id			,
					DP.dec_PercentageCompleted					,
					DP.int_BenefitEquation						,
					DP.str_CurrentStatus						,
					DP.str_RequestorDueDate						,
					DP.str_DateStarted							,
					DP.str_DocumentationCompletionDate			,
					DP.str_TargetCompletionDate					,
					DP.str_DateCompleted						,
					DP.str_DateOnProduction						,
					DP.dte_RequestorDueDate						,
					DP.dte_DateStarted							,
					DP.dte_TargetCompletionDate					,
					DP.dte_DateCompleted						,
					DP.dte_DateOnProduction						,
					DP.str_Notes								,
					DP.bit_Is_Active							,
					DP.int_Insert_User_Id						,
					DP.dte_Inserted_Date						,
					DP.int_Update_User_Id						,
					DP.dte_Updated_Date							,

					DT.str_Tool									,
					DPP.str_ProjectPriority						,
					DPt.str_ProjectType							,
					DDS.str_DocumentationStatus					,
					DPS.str_ProjectStatus						,
					DLE.str_LevelOfEffort						,
		
					(CId.str_First_Name + ' ' + CId.str_Last_Name) AS str_Full_Name_Developer	,
					(CIs.str_First_Name + ' ' + CIs.str_Last_Name) AS str_Full_Name_Submitter	,
					(CIi.str_First_Name + ' ' + CIi.str_Last_Name) AS str_Full_Name_InsertUser	,
					(CIu.str_First_Name + ' ' + CIu.str_Last_Name) AS str_Full_Name_UpdateUser	,
					(CIo.str_First_Name + ' ' + CIo.str_Last_Name) AS str_Full_Name_Owner		,

					ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted) AS [DataDisplay_StartDate]														,
					ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate) AS [DataDisplay_EndDate]												,
					CASE WHEN (ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted) < @DataDisplay_Start) THEN 0 ELSE DateDiff(day, @DataDisplay_Start, ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted)) END AS [DataDisplay_StartCell]					,
					DateDiff(day, @DataDisplay_Start, ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate)) AS [DataDisplay_EndCell]			,
					DateDiff(day, ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted), ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate)) + 1 AS [TotalCellSpan]			,
					(DateDiff(day, @DataDisplay_Start, ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted))/7) AS [DataDisplay_StartWeek]				,
					(DateDiff(day, @DataDisplay_Start, ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate))/7) AS [DataDisplay_StartWeek]

		FROM		tbl_DEVELOPERS_Projects					DP
		LEFT JOIN	tbl_DEVELOPERS_Tools					DT	ON DP.int_DEVELOPERS_Tool_Id = DT.int_DEVELOPERS_Tool_Id AND DT.bit_Is_Active = 1
		LEFT JOIN	tbl_DEVELOPERS_ProjectPriorities		DPP	ON DP.int_DEVELOPERS_ProjectPriority_Id = DPP.int_DEVELOPERS_ProjectPriority_Id AND DPP.bit_Is_Active = 1
		LEFT JOIN	tbl_DEVELOPERS_DocumentationStatuses	DDS	ON DP.int_DEVELOPERS_DocumentationStatus_Id = DDS.int_DEVELOPERS_DocumentationStatus_Id AND DDS.bit_Is_Active = 1
		LEFT JOIN	tbl_DEVELOPERS_ProjectStatuses			DPS	ON DP.int_DEVELOPERS_ProjectStatus_Id = DPS.int_DEVELOPERS_ProjectStatus_Id AND DPS.bit_Is_Active = 1
		LEFT JOIN	tbl_DEVELOPERS_LevelOfEfforts			DLE	ON DP.int_DEVELOPERS_LevelOfEffort_Id = DLE.int_DEVELOPERS_LevelOfEffort_Id AND DLE.bit_Is_Active = 1
		LEFT JOIN	tbl_DEVELOPERS_ProjectTypes				DPT	ON DP.int_DEVELOPERS_ProjectType_Id = DPT.int_DEVELOPERS_ProjectType_Id AND DPT.bit_Is_Active = 1

		LEFT JOIN	tbl_CORE_Individuals					CId	ON DP.int_Developer_Id = CId.int_CORE_Individual_Id AND CId.bit_Is_Active = 1
		LEFT JOIN	tbl_CORE_Individuals					CIs	ON DP.int_ProjectSubmitter_Id = CIs.int_CORE_Individual_Id AND CIs.bit_Is_Active = 1
		LEFT JOIN	tbl_CORE_Individuals					CIi	ON DP.int_Insert_User_Id = CIi.int_CORE_Individual_Id AND CIi.bit_Is_Active = 1
		LEFT JOIN	tbl_CORE_Individuals					CIu	ON DP.int_Update_User_Id = CIu.int_CORE_Individual_Id AND CIu.bit_Is_Active = 1
		LEFT JOIN	tbl_CORE_Individuals					CIo	ON DT.int_Owner_Id = CIo.int_CORE_Individual_Id AND CIo.bit_Is_Active = 1

		WHERE		(ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted) between @DataDisplay_Start AND @DataDisplay_End)
		OR			(ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate) between @DataDisplay_Start AND @DataDisplay_End)
		OR			(ISNULL(ISNULL(dte_DateStarted, dte_DateOnProduction), dte_DateCompleted) < @DataDisplay_Start AND ISNULL(ISNULL(dte_DateOnProduction, dte_DateCompleted), dte_TargetCompletionDate) > @DataDisplay_End)
	ORDER BY [DataDisplay_StartCell], [DataDisplay_EndCell]


	DECLARE @rec_id int
	DECLARE @StartCell int
	DECLARE @EndCell int

	DECLARE proj_cursor CURSOR FOR 
	SELECT ID, ISNULL([DataDisplay_StartCell], 0), [DataDisplay_EndCell]
	FROM #tmp_Projects
	ORDER BY ID;

	OPEN proj_cursor

	FETCH NEXT FROM proj_cursor 
	INTO @rec_id, @StartCell, @EndCell

	WHILE @@FETCH_STATUS = 0
	BEGIN

		DECLARE @nextRecord int
		SET @nextRecord = (SELECT (ISNULL(MAX(DataDisplay_Row), 0) + 1) FROM #tmp_Projects WHERE [DataDisplay_EndCell] >= @StartCell AND DataDisplay_Row IS NOT NULL)

		DECLARE @nextFreeRecord int
		SET @nextFreeRecord = (SELECT TOP 1 DataDisplay_Row FROM #tmp_Projects WHERE DataDisplay_Row NOT IN (SELECT DataDisplay_Row FROM #tmp_Projects WHERE [DataDisplay_EndCell] >= @StartCell AND DataDisplay_Row IS NOT NULL) ORDER BY DataDisplay_Row)

		IF @nextFreeRecord IS NOT NULL
		BEGIN
			SET @nextRecord = @nextFreeRecord
		END

		UPDATE #tmp_Projects SET DataDisplay_Row = @nextRecord WHERE id = @rec_id

		FETCH NEXT FROM proj_cursor 
		INTO @rec_id, @StartCell, @EndCell

	END
	CLOSE proj_cursor;
	DEALLOCATE proj_cursor;

	SET NOCOUNT OFF

	SELECT 	int_DEVELOPERS_Project_Id						,
			str_ProjectId									,
			int_DEVELOPERS_Tool_Id							,
			int_ProjectRank									,
			int_DEVELOPERS_ProjectType_Id					,
			str_ProjectName									,
			int_Developer_Id								,
			str_Description									,
			int_DEVELOPERS_ProjectPriority_Id				,
			bit_Is_Upcoming									,
			int_ProjectSubmitter_Id							,
			str_DateSubmitted								,
			dte_DateSubmitted								,
			int_DEVELOPERS_DocumentationStatus_Id			,
			int_DEVELOPERS_ProjectStatus_Id					,
			int_DEVELOPERS_LevelOfEffort_Id					,
			dec_PercentageCompleted							,
			int_BenefitEquation								,
			str_CurrentStatus								,
			str_RequestorDueDate							,
			str_DateStarted									,
			str_DocumentationCompletionDate					,
			str_TargetCompletionDate						,
			str_DateCompleted								,
			str_DateOnProduction							,
			dte_RequestorDueDate							,
			dte_DateStarted									,
			dte_TargetCompletionDate						,
			dte_DateCompleted								,
			dte_DateOnProduction							,
			str_Notes										,
			bit_Is_Active									,
			int_Insert_User_Id								,
			dte_Inserted_Date								,
			int_Update_User_Id								,
			dte_Updated_Date								,
			str_Tool										,
			str_ProjectPriority								,
			str_ProjectType									,
			str_DocumentationStatus							,
			str_ProjectStatus								,
			str_LevelOfEffort								,
			str_Full_Name_Developer							,
			str_Full_Name_Submitter							,
			str_Full_Name_InsertUser						,
			str_Full_Name_UpdateUser						,
			str_Full_Name_Owner								,
			DataDisplay_StartDate							,
			DataDisplay_EndDate								,
			DataDisplay_StartCell							,
			DataDisplay_EndCell								,
			TotalCellSpan									,
			DataDisplay_StartWeek							,
			DataDisplay_EndWeek								,
			DataDisplay_Row
	FROM	#tmp_Projects
	ORDER BY DataDisplay_Row

	DROP TABLE #tmp_Projects

END

GO

Open in new window

0
zephyr_hex (Megan)DeveloperCommented:
OK, there's no return variable.  I would recommend that the DROP TABLE #tmp_Projects is commented out -- it shouldn't be the cause of the problem, but it's also not necessary because SQL will dispose of the temp table.

Try this, and if it doesn't work, I'd start looking at the pdo driver version.  Or try executing a very basic stored procedure to see if you get results.

//PHP ERROR REPORTING, at the top of your page
ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting(E_ALL); 

//other stuff

$mssql_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  //enable errors

$stmt = $mssql_pdo->prepare("EXEC stp_Select_REPORT_DevelopeProjects_Gantt ?,?");

$start='01/01/2015';
$stop='05/31/2015';

$stmt->bindParam(1, $start, PDO::PARAM_STR);
$stmt->bindParam(2, $stop, PDO::PARAM_STR);

$stmt->execute();
$results = array();
$i = 0;
do {
	$results []= $stmt->fetchAll();
        $i++;
} while ($stmt->nextRowset());

var_dump($results);
var_dump($i);  //see how many time it looped

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brucegustPHP DeveloperAuthor Commented:
zephyr...wait for it...the problem has been solved!

Are you ready for this?

I don't know if this resonates with you at all, but what my team wound up doing was to deconstruct things to the point where it became evident that the temp table was the problem. What made it difficult to see is that the SQL could fail within the Stored Procedure and PHP wouldn't return an error.

Once the temp table was replaced with a permanent table, it worked just fine.

Thanks for you time, friend! This one was a long time coming!
0
zephyr_hex (Megan)DeveloperCommented:
Wahoo!  Glad you were able to figure it out.  Sometimes problems like this mean you need to start super simple and add in components until you can pinpoint the issue.
0
brucegustPHP DeveloperAuthor Commented:
Gentlemen!

Thanks so much for the help. Good news! The witch is dead! We changed the store proc so instead of #tmp_Projects, it was reconfigured to @tmp_Projects. When that change was made, it fired correctly.

I've got another question at http://www.experts-exchange.com/questions/28824339/How-can-I-pass-this-date-value-as-a-string-into-a-MSSQL-Stored-Procedure.html. Feel free to weigh in on that when you have a moment.

Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.