Link to home
Start Free TrialLog in
Avatar of Bruce Gust
Bruce GustFlag for United States of America

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:

User generated image
Now, the same code running on a MySQL server produces this:

User generated image
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

Avatar of Bruce Gust
Bruce Gust
Flag of United States of America image

ASKER

Actually the trouble begins at 47, when I actually run the query...
SOLUTION
Avatar of Aaron Tomosky
Aaron Tomosky
Flag of United States of America image

Link to home
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!