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:

screenshot
Now, the same code running on a MySQL server produces this:

another screenshot
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;">&nbsp;&nbsp;';
	$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;">&nbsp;</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>';

Open in new window

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.

Bruce GustPHP DeveloperAuthor Commented:
Actually the trouble begins at 47, when I actually run the query...
Aaron TomoskyDirector of Solutions ConsultingCommented:
Instead of the big group by, why don't you just select distinct? The point of group by is to sum it count or max or something some other column grouped by the first one.

I'd suggest you use Ms ssms to run the query in a gui and see what comes back
arnoldCommented:
Looks as though you are running a query within the connection of data.

Try this output a message to your system whenever $mssql_stmt_1=$mssql_pdo->query($sql_1); is run, line 47 of your code.
You are using the same connection.
That might be your issue when using MSSQL.

Try using a separate connection to the MS SQL DB for the internal query to see whether that resolves your issue.

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:
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!
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
Microsoft SQL Server

From novice to tech pro — start learning today.