asked on

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?

SOLUTION

Insoftservice inso

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Scott Fell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bruce Gust

ASKER

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!

Scott Fell

What do you get when you print_r($arr_1)?

Insoftservice inso

Did you tried my suggestion

Bruce Gust

ASKER

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:

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

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 Gust

ASKER

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

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.