Why does this loop quit?

Here's my code:

<table border="1">
<?php
date_default_timezone_set('America/Chicago');
ini_set('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);

//this one is good to go - $sql = "select CID.str_First_Name from net_BudgetTools..tbl_CORE_Individuals CID where CID.bit_Is_Active=1";

$sql="select DP.int_DEVELOPERS_Tool_Id as tool_id
FROM net_BudgetTools..tbl_DEVELOPERS_Projects DP
WHERE DP.int_DEVELOPERS_Tool_Id<>''
GROUP BY int_DEVELOPERS_Tool_Id";

$mssql_stmt=$mssql_pdo->query($sql);

if (!$mssql_stmt) {
    echo "\nPDO::errorInfo():\n";
    print_r($dbh->errorInfo());
}

while($arr=$mssql_stmt->fetch(PDO::FETCH_ASSOC))
{
?>
	<tr>
		<td>
		<?php 
		//echo stripslashes($arr['first_name'].' '.$arr['last_name']).' - '.$arr['str_ProjectName'].' | '.$arr['str_Tool']; 
		echo $arr['tool_id']; 
		?>
		</td>
		<td>
		</td>
	</tr>
<?php
}
?>
</table>

Open in new window


It gives me this:

1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
14  

Now, when I run this code (pay special attention to lines 36-42:

<table border="1">
<?php
date_default_timezone_set('America/Chicago');
ini_set('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);

//this one is good to go - $sql = "select CID.str_First_Name from net_BudgetTools..tbl_CORE_Individuals CID where CID.bit_Is_Active=1";

$sql="select DP.int_DEVELOPERS_Tool_Id as tool_id
FROM net_BudgetTools..tbl_DEVELOPERS_Projects DP
WHERE DP.int_DEVELOPERS_Tool_Id<>''
GROUP BY int_DEVELOPERS_Tool_Id";

$mssql_stmt=$mssql_pdo->query($sql);

if (!$mssql_stmt) {
    echo "\nPDO::errorInfo():\n";
    print_r($dbh->errorInfo());
}

while($arr=$mssql_stmt->fetch(PDO::FETCH_ASSOC))
{
?>
	<tr>
		<td>
		<?php 
		//echo stripslashes($arr['first_name'].' '.$arr['last_name']).' - '.$arr['str_ProjectName'].' | '.$arr['str_Tool']; 
		echo $arr['tool_id']; 
		?>
		</td>
		<td>
		<?php
		$sql_1="select str_Tool
		FROM net_BudgetTools..tbl_DEVELOPERS_Tools
		WHERE int_DEVELOPERS_Tool_Id='$arr[tool_id]'";

		$mssql_stmt_1=$mssql_pdo->query($sql_1);
		$arr_1=$mssql_stmt_1->fetch(PDO::FETCH_ASSOC);
		echo $arr_1['str_Tool'];
		?>		
		</td>
	</tr>
<?php
}
?>
</table>

Open in new window


I get this:

1  Capital Budget Tool  

...and that's accurate! And, I've gone through and spot checked the other values to make sure there's a coinciding "str_Tool," but it does the first one , then stops.

No idea why.

Any suggestions?
Bruce GustPHP DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

InsoftserviceCommented:
Please provide the db structure. As it would easy to provide correct sql query.
Please try below query and revert

select dt.strTool, DP.int_DEVELOPERS_tool_Id as toolid
from net_Budget_Tools..tbl_DEVELOPERS_Projects DP,net_BudgetTools..tbl_DEVELOPERS_Tools dt
where  dt.int_DEVELOPERS_Tool_Id=DP.int_DEVELOPERS_Tool_Id
and DP.int_DEVELOPERS_Tool_Id<>''
GROUP BY dp.int_DEVELOPERS_Tool_Id
 
 
I have removed
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
<?php
		$sql_1="select str_Tool
		FROM net_BudgetTools..tbl_DEVELOPERS_Tools
		WHERE int_DEVELOPERS_Tool_Id='$arr[tool_id]'";

		//$mssql_stmt_1=$mssql_pdo->query($sql_1);
		//$arr_1=$mssql_stmt_1->fetch(PDO::FETCH_ASSOC);
		//echo $arr_1['str_Tool'];

               if ($sql_1->execute()) {
                       while ($arr_1 = $sql_1->fetch(PDO::FETCH_ASSOC)) {
                           echo   $arr_1['str_Tool']."<br>";                       
 
                   }
           }


?>

Open in new window

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
Bruce GustPHP DeveloperAuthor Commented:
Scott, I see the logic of what you're suggesting. I gave it a whirl and I get the exact same result.

Any other suggestions?

This is killing me!
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
What do you get when you print_r($arr_1)?
InsoftserviceCommented:
Did you tried my suggestion
Bruce GustPHP DeveloperAuthor Commented:
Guys, I've got some progress that incorporates some of your logic, as well as some additional discoveries, but still no complete list.

First off, here's my code that corresponds to the first screen shot which is also attached:

$body = '<br><br><span class="title_text">'.$page_title.'</span><br><br>
Welcome to the South Area Network Development Project List!<br><br>
<table style="margin:auto; width:1230px;">';

$sql="select DP.int_DEVELOPERS_Tool_Id as tool_id, 
DP.int_DEVELOPERS_ProjectPriority_Id as priority_id,
DT.str_Tool,
CID.str_First_name as first_name,
CID.str_Last_name as last_name
FROM net_BudgetTools..tbl_DEVELOPERS_Projects DP

LEFT JOIN
net_BudgetTools..tbl_DEVELOPERS_Tools DT
ON
DP.int_DEVELOPERS_Tool_Id=DT.int_DEVELOPERS_Tool_Id

LEFT JOIN
net_BudgetTools..tbl_CORE_Individuals CID
ON
DT.int_Owner_Id = CID.int_CORE_Individual_Id

WHERE 
DP.int_DEVELOPERS_Tool_Id IS NOT NULL 
AND
DT.bit_Is_Active = 1
ORDER BY 
tool_id, DP.int_DEVELOPERS_ProjectPriority_Id";

$mssql_stmt=$mssql_pdo->query($sql);

if (!$mssql_stmt) {
    echo "\nPDO::errorInfo():\n";
    print_r($mssql_pdo->errorInfo());
}

$current_tool_id="";

while($arr=$mssql_stmt->fetch(PDO::FETCH_ASSOC))
{
	if($current_tool_id<>$arr['tool_id'])
	{
		echo "yes";
	$body .='<tr><td style="background-image:url(images/stone_header.jpg); background-repeat:no-repeat; width:1230px; height:32px; color:#ffffff;">&nbsp;&nbsp;';
	$body .= stripslashes($arr['str_Tool']. '- '.$arr['first_name'].' '.$arr['last_name'].' - '.$arr['tool_id']);
	$body .='</td></tr><tr><td style="background-image:url(images/black_glass.jpg); background-repeat:no-repeat; width1230px; height:34px;">&nbsp;</td></tr>';
	$sql_1="select * from net_BudgetTools..tbl_DEVELOPERS_Projects where int_DEVELOPERS_Tool_Id='$arr[tool_id]'";
	$body .='<tr><td>';
	$body.=$sql_1;
	}
	//$body.='here\'s where I\'m going to put my stuff';
$current_tool_id=$arr['tool_id'];
}


$body .='</table>';

Open in new window


And here's the screenshot:

screenshot
You'll notice that I've to several rows with the tool_id printed to the right of every Tool name (line 44). I've also echo-ed out a "yes" every time the tool id changes. This is significant because watch what happens when I add "$mssql_stmt_1=$mssql_pdo->query($sql_1)" on line 50.

Here's the code:

$body = '<br><br><span class="title_text">'.$page_title.'</span><br><br>
Welcome to the South Area Network Development Project List!<br><br>
<table style="margin:auto; width:1230px;">';

$sql="select DP.int_DEVELOPERS_Tool_Id as tool_id, 
DP.int_DEVELOPERS_ProjectPriority_Id as priority_id,
DT.str_Tool,
CID.str_First_name as first_name,
CID.str_Last_name as last_name
FROM net_BudgetTools..tbl_DEVELOPERS_Projects DP

LEFT JOIN
net_BudgetTools..tbl_DEVELOPERS_Tools DT
ON
DP.int_DEVELOPERS_Tool_Id=DT.int_DEVELOPERS_Tool_Id

LEFT JOIN
net_BudgetTools..tbl_CORE_Individuals CID
ON
DT.int_Owner_Id = CID.int_CORE_Individual_Id

WHERE 
DP.int_DEVELOPERS_Tool_Id IS NOT NULL 
AND
DT.bit_Is_Active = 1
ORDER BY 
tool_id, DP.int_DEVELOPERS_ProjectPriority_Id";

$mssql_stmt=$mssql_pdo->query($sql);

if (!$mssql_stmt) {
    echo "\nPDO::errorInfo():\n";
    print_r($mssql_pdo->errorInfo());
}

$current_tool_id="";

while($arr=$mssql_stmt->fetch(PDO::FETCH_ASSOC))
{
	if($current_tool_id<>$arr['tool_id'])
	{
		echo "yes";
	$body .='<tr><td style="background-image:url(images/stone_header.jpg); background-repeat:no-repeat; width:1230px; height:32px; color:#ffffff;">&nbsp;&nbsp;';
	$body .= stripslashes($arr['str_Tool']. '- '.$arr['first_name'].' '.$arr['last_name'].' - '.$arr['tool_id']);
	$body .='</td></tr><tr><td style="background-image:url(images/black_glass.jpg); background-repeat:no-repeat; width1230px; height:34px;">&nbsp;</td></tr>';
	$sql_1="select * from net_BudgetTools..tbl_DEVELOPERS_Projects where int_DEVELOPERS_Tool_Id='$arr[tool_id]'";
	$body .='<tr><td>';
	$body.=$sql_1;
	$body.='</td></tr>';
	$mssql_stmt_1=$mssql_pdo->query($sql_1);
	}
	//$body.='here\'s where I\'m going to put my stuff';
$current_tool_id=$arr['tool_id'];
}


$body .='</table>';

Open in new window


...and here's the "mess..."

another screenshot
The first record runs fine, but then you can see that instead of "Expense Budget Tool - Jennifer Newton - 2" it says "1 - 2 31 -2," and then come the the third row, there's no data at all.

In the upper left corner, instead of there being 14 "yes-es," there are only three.

So, there's something foul going on, as though the criteria for the select statement is getting polluted or something.

What do you think? What am I doing wrong or what can I do better?
Bruce GustPHP DeveloperAuthor Commented:
Hey, guys!

I was able to get it to work! Turns out that in the midst of running a query while another query is happening using the same connection - at least in a MSSQL environment - you run into problems. To remedy my situation, I prefaced my inner loop by opening up another connection and querying the database as a separate transaction and everything worked!

Never heard of such a thing, but chalking it up to becoming more sensitive / aware of the MSSQL environment.

Thanks again!
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I use PHP with MSSQL as well and there are some quirks.  However, I think there may be a better way to do what you need.  Maybe post another question with just the basic part of your code for others to look at.

You should be able to hit your db that creates an array of results for whatever you are placing in the light gray header bar.  

Then loop through that array and hit the related table submitting the matching key.
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.