Bruce Gust
asked on
Is there a difference between a loop against a MySQL database and a loop against a MSSQL database?
I have a body of code that works great when I'm running it up against a MySQL database.
I've got the code below, but basically what it does is start by grouping a series of rows according to the "tool id."
The database, while several hundred rows, has about 14 unique tool ids, and I've got that going just fine.
The problem happens at row 27. The moment I start querying the initial recordset, it will loop through the first record and then stop. So I'll get all of the rows that correspond to tool id#1 and then instead of continuing through tool id numbers 2-14, it stops.
No errors, just no data.
That's what's happening on the MSSQL side. You can see the attached screenshot to see what it looks like:
Now, the same code running on a MySQL server produces this:
I've spent several hours playing with this and I'm beginning to wonder if there aren't some nuances with MSSQL that I'm overlooking.
Why does my loop stop after tool id #1? How can I keep the loop going?
Here's my code:
I've got the code below, but basically what it does is start by grouping a series of rows according to the "tool id."
The database, while several hundred rows, has about 14 unique tool ids, and I've got that going just fine.
The problem happens at row 27. The moment I start querying the initial recordset, it will loop through the first record and then stop. So I'll get all of the rows that correspond to tool id#1 and then instead of continuing through tool id numbers 2-14, it stops.
No errors, just no data.
That's what's happening on the MSSQL side. You can see the attached screenshot to see what it looks like:
Now, the same code running on a MySQL server produces this:
I've spent several hours playing with this and I'm beginning to wonder if there aren't some nuances with MSSQL that I'm overlooking.
Why does my loop stop after tool id #1? How can I keep the loop going?
Here's my code:
$sql="select DP.int_DEVELOPERS_Tool_Id as tool_id
FROM net_BudgetTools..tbl_DEVELOPERS_Projects DP
WHERE
DP.int_DEVELOPERS_Tool_Id IS NOT NULL
AND DP.int_DEVELOPERS_Tool_Id<>0
GROUP BY
DP.int_DEVELOPERS_Tool_Id";
$mssql_stmt=$mssql_pdo->query($sql);
if (!$mssql_stmt) {
echo "\nPDO::errorInfo():\n";
print_r($mssql_pdo->errorInfo());
}
$current_tool_id="";
$current_str_tool="";
while($row=$mssql_stmt->fetch(PDO::FETCH_ASSOC))
{
echo $row['tool_id'];
if($current_tool_id<>$row['tool_id'])
{
$body .='<tr><td style="background-image:url(images/stone_header.jpg); background-repeat:no-repeat; width:1230px; height:32px; color:#ffffff;"> ';
$sql_1="select DP.int_DEVELOPERS_ProjectPriority_Id as priority_id,
DP.str_description as the_description,
DT.str_Tool as 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 ='$row[tool_id]'
AND
DT.bit_Is_Active = 1
ORDER BY DP.int_DEVELOPERS_ProjectPriority_Id";
$mssql_stmt_1=$mssql_pdo->query($sql_1);
while($row_1=$mssql_stmt_1->fetch(PDO::FETCH_ASSOC))
{
if($current_str_tool<>$row_1['str_tool'])
{
$body .= stripslashes($row_1['str_tool']);
$body .='</td></tr><tr><td style="background-image:url(images/black_glass.jpg); background-repeat:no-repeat; width1230px; height:34px;"> </td></tr>';
}
$body .='<tr><td>';
$body .=$row_1['first_name'];
$body .='</td></tr>';
$current_str_tool=$row_1['str_tool'];
}
}
echo $row['tool_id'];
$current_tool_id=$row['tool_id'];
}
$body .='</table>';
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.
ASKER
Arnold, that did it! And Aaron, I'm giving you some points as well. I revised my code to use "DISTINCT" rather than "GROUP." Seems like that refines the code somewhat.
Thanks!
Thanks!
ASKER