Bruce Gust
asked on
Why does this loop quit?
Here's my code:
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:
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?
<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>
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>
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What do you get when you print_r($arr_1)?
Did you tried my suggestion
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:
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($sq l_1)" on line 50.
Here's the code:
...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?
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;"> ';
$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;"> </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>';
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-
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;"> ';
$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;"> </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>';
...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?
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!
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!
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.
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.
ASKER
Any other suggestions?
This is killing me!