troubleshooting Question

Why does this function work one way in MSSQL Studio and another when accessed via PDO?

Avatar of Bruce Gust
Bruce GustFlag for United States of America asked on
PHP
3 Comments1 Solution145 ViewsLast Modified:
I'm trying to troubleshoot a function that's part of a SELECT statement. I built a little sandbox so I could better manipulate / view the code in a PHP environment. But when I went to fire the code, I got some different results and I don't understand why.

Here's the SQL:

SELECT
                ac.AccountID,
                clt.ClientName,
                clt.ClientTetrisID,
                clt.ClientID,
                cast(dbo.ufn_ClientPreference(clt.ClientID, 'StatementRollUp') as int) as StatementRollUp
              

            from Statement st with (nolock)
                                                Join Account ac with (nolock) on st.Accountid = ac.AccountID

                join Patient pat with (nolock) on pat.AccountID = ac.AccountID
                join Practice prt with (nolock) on ac.PracticeID = prt.PracticeID
                join Client clt with (nolock) on prt.ClientID = clt.ClientID
                left join
                (select pmtplanamount, pmtplanday, primarypatientid, startingplanbalance from PaymentPlan
                  where planendeddate is null
                  and iscurrentplan = 1) pp
                  on pp.primarypatientid = pat.patientid
            where ac.accountid=10093227

When you fire that in MSSQL Studio, you get this:

results screenshot
Notice the value you're getting for "StatementRollUp."

Now when I try to run the same SELECT using a PDO dynamic, I get this:

PDO screenshot
...notice the value of "StatementRollUp."

In the Studio, StatementRollUp is "1." When I run it via PDO, it's "0" and I don't understand why.

Here's my PDO code:

<?php
date_default_timezone_set('America/Chicago');

$mssql_pdo=new PDO("");
$mssql_pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
if(!$mssql_pdo)
{
	echo "no connection";
}
		
	function test(){
		
	global $mssql_pdo;		
				
		//$sql="select TOP 5 patientcode from txn where accountid=10001124";
		$sql="
            SELECT
                ac.AccountID,
                clt.ClientName,
                clt.ClientTetrisID,
                clt.ClientID,
                cast(dbo.ufn_ClientPreference(clt.ClientID, 'StatementRollUp') as int) as StatementRollUp
              

            from Statement st with (nolock)
                                                Join Account ac with (nolock) on st.Accountid = ac.AccountID

                join Patient pat with (nolock) on pat.AccountID = ac.AccountID
                join Practice prt with (nolock) on ac.PracticeID = prt.PracticeID
                join Client clt with (nolock) on prt.ClientID = clt.ClientID
                left join
                (select pmtplanamount, pmtplanday, primarypatientid, startingplanbalance from PaymentPlan
                  where planendeddate is null
                  and iscurrentplan = 1) pp
                  on pp.primarypatientid = pat.patientid
            where ac.accountid=10093227";
			//echo $sql;
		$query=$mssql_pdo->query($sql);
		if(!$query){
			echo "nope";
		}
		
		$result_array=array();

	while($row=$query->fetch(PDO::FETCH_ASSOC) )
		{
			$result_array[]=$row;
		}
	return $result_array;
	}		


//$test_sql=getOldTransactions();
?>

<!DOCTYPE html>
<html lang="en">
<head>
<title>Patient Focus | SQL Machine</title>

<script language="JavaScript"><!--
function MM_jumpMenu(targ,selObj,restore){ //v3.0
  window.open(selObj.options[selObj.selectedIndex].value, "_blank"); 
  if (restore) selObj.selectedIndex=0;
}
// -->
</script>
<script type="text/javascript">
<!--
    function toggle_visibility(id) {
       var e = document.getElementById(id);
       if(e.style.display == 'none')
          e.style.display = 'block';
       else
          e.style.display = 'none';
    }
//-->
</script>

<style>
body {
font-family:Arial;
size:12pt;
color:#000000;
}

.content {
margin:auto;
width:900px;
height:auto;
border:1px solid #cccccc;
border-radius:10px;
}

.wysiwyg {
white-space: pre-line;
padding-left:10px;
padding-right:10px;
}

.code_container {
border:1px solid #000000; 
width:807px; 
height:250px; 
overflow:auto; 
background-color: #013c76;
font-family:Arial; 
color:#ffffff; 
font-size:10pt; 
margin:auto;
white-space:pre;
}

ol {
	white-space: normal;
	word-wrap: break-word;
	width:750px;
	margin:auto;
	background-color:#036; background-repeat:repeat-y;background-position:center;
	font-family:Helevtica, Arial;
	font-size:10pt;
	color:#ffffff;
}
ol	li {
	padding:6px;
	background-color:#ffc;
	color:#ffffff;
	}
	
ol li span{ 
color:#000000; }

.blue {
	color:blue;	
}
</style>

</head>

<body>

<br><br>
<div class="content">
	<div class="wysiwyg">
	
	<table border="1" cellspacing="5" cellpadding="5" width="100%">
		<tr>
			<td>Account ID</td>
			<td>Client Name</td>
			<td>Client Tetris ID</td>
			<td>Client ID</td>
			<td>Statement Roll Up</td>
		</tr>
	<?php
	$test_stuff=test();
	//echo $test_stuff;
	
	if($test_stuff){
	//echo "yes";
	}	

	foreach($test_stuff as $row)
	{
		echo "<tr><td>";
		echo $row['AccountID'];
		echo "</td><td>";
		echo $row['ClientName'];
		echo "</td><td>";
		echo $row['ClientTetrisID'];
		echo "</td><td>";
		echo $row['ClientID'];
		echo "</td><td>";
		echo $row['StatementRollUp'];
		echo "</td></tr>";
	}
	?>
	</table>

	</div>
</div>

</body>
</html>

What am I missing?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros