troubleshooting Question

Is the result of this an array?

Avatar of Bruce Gust
Bruce GustFlag for United States of America asked on
PHP
8 Comments2 Solutions115 ViewsLast Modified:
I'm working on an app where most of the SELECT statements are constructed on the fly and then processed using this:

		$glob = array('sql' => $sql);
		$error_message = "\n---------------------------".date('Y-m-d h:i:s')."--------------------------------------\n";
		$error_message .= "\nStackTrace:\n";
		$backtrace = debug_backtrace();
			//krumo($backtrace);
			if(is_array($backtrace)){
				foreach($backtrace as $bf){
					if(isset($bf['file'])){
					$error_message .= $bf['file'].": Line ".$bf['line'].": Function ".$bf['function']." -->"."\n";
					}
				}
			}
    		ini_set('mssql.charset', 'UTF-8');
		$sqlhost = SQL_HOST;

		$sdb = $db;

		$conn = @mssql_connect($sqlhost,SQL_USER,SQL_PASSWORD);

		if(!$conn){

			$error_message .= date('Y:m:d h:i:s').' :: Issue Connecting to '.$db."\n".". Unable to connect to server named '".SQL_HOST."'\n".". Query Attempted: ".$sql."\n"."Stacktrace::";
			$backtrace = debug_backtrace();
			//krumo($backtrace);
			if(is_array($backtrace)){
				foreach($backtrace as $bf){
					if(isset($bf['file'])){
					$error_message .= $bf['file'].": Line ".$bf['line'].": Function ".$bf['function']." -->"."\n";
					}
				}
			}
			$error_file = '';
			if (defined('ENVIRONMENT'))
			{
			  switch (ENVIRONMENT)
			  {
				case 'staging':
				  $error_file = '/var/tmp/digdev_db_error';
				break;
				case 'production':
				  $error_file = '/var/tmp/dig_db_error';
				break;
				case 'local':
				  $error_file = '/var/tmp/dig_db_error';
				break;
			  }
			}
		    if(ENVIRONMENT != 'development'){
				error_log($error_message,3,$error_file);
		    }
			if($debug === true){
			echo $error_message;
			}

		}
		$dbconnect = @mssql_select_db($sdb,$conn);
		if(!$dbconnect){
			$error_message .= 'Issue Connecting to '.$db." Database\n".". Trying default connection to PCAR_Data\n".". Query Attempted: ".$sql."\n"."StackTrace::";
			$error_file = '';
			if (defined('ENVIRONMENT'))
				{
				  switch (ENVIRONMENT)
				  {
					case 'staging':
					  $error_file = '/var/tmp/digdev_db_error';
					break;
					case 'production':
					  $error_file = '/var/tmp/dig_db_error';
					break;
				  }
				}
			if(ENVIRONMENT != 'development'){
				error_log($error_message,3,$error_file);
			} else {
				echo '<pre>'.$error_message.'</pre>';
				exit();
			}
			if($debug === true){
			echo $error_message;
			}
		}
		$tsql= $sql;
		if(!$rQuery = @mssql_query($tsql, $conn))
		{
			$sMssql_get_last_message = mssql_get_last_message();
			$sQuery_added  = "BEGIN TRY\n";
			$sQuery_added .= "\t".$tsql."\n";
			$sQuery_added .= "END TRY\n";
			$sQuery_added .= "BEGIN CATCH\n";
			$sQuery_added .= "\tSELECT 'Error: '  + ERROR_MESSAGE()\n";
			$sQuery_added .= "END CATCH";
			$rRun2= @mssql_query($sQuery_added, $hDb_conn);
			$aReturn = @mssql_fetch_assoc($rRun2);
			if(empty($aReturn))
			{
				$error_message .=  "\r\nMSSQL returned: ".$sMssql_get_last_message."\r\nExecuted query:\r\n";
				//$error_message .= '<pre>';
				$error_message .= nl2br($tsql)."\r\n";
				//$error_message .= '</pre>';
			}
			elseif(isset($aReturn['computed']))
			{
				$error_message .= "\r\nMSSQL returned: ".$aReturn['computed']."\r\nExecuted query:\r\n";
				//$error_message .= '<pre>';
				$error_message .= nl2br($tsql)."\r\n";
				//$error_message .= '</pre>';
			}

      			$error_file = '';
			if (defined('ENVIRONMENT'))
				{
				  switch (ENVIRONMENT)
				  {
					case 'staging':
					  $error_file = '/var/tmp/digdev_db_error';
					break;
					case 'production':
					  $error_file = '/var/tmp/dig_db_error';
					break;
				  }
				}
			if(ENVIRONMENT != 'development'){
				error_log(strip_tags($error_message),3,$error_file);
				sendEmailError($error_message,$tsql);
      			}
			if($debug === true){
				echo $error_message;
			}

        	if(STORE_QUERIES) $GLOBALS['queries'][] = array('sql'=>$glob['sql'],'result'=>false);
			return FALSE;
		}
		else
		{

		    if(is_bool($rQuery)){
		    	$data = true;
		    }elseif(mssql_num_rows($rQuery) > 1 || $multi == true){
	        	$data = array();
				while($rows = mssql_fetch_assoc($rQuery)){
					$data[] = $rows;
				}
			}elseif(mssql_num_fields($rQuery)  >=1 ){
	        	//mssql_data_seek($rQuery,0);
				$data = mssql_fetch_assoc($rQuery);
			}else{
	        	mssql_next_result($rQuery);
	        	$data = mssql_fetch_assoc($rQuery);
	        }
	        if(isset($data)){

	        	if(STORE_QUERIES){
			      	//Phillip suggested that storing this data all the time could eat up too much
			      	//memory. I'll store a constant to manage when this should happen.
			      	$glob['result']=$data;
			      	$GLOBALS['queries'][] = $glob;
			    }

				return $data;
	        }else{

	        	if(STORE_QUERIES) $GLOBALS['queries'][] = array('sql'=>$glob['sql'],'result'=>false);
	      		return false;
	        }

			mssql_close($conn);
		}

	}

It's called pconnect. What I'm trying to do is determine if that particular function not only facilitates the SQL, but also stores the results in an array. I'm thinking it does, but the syntax, not only is it somewhat convoluted, it also involves some code I'm not familiar with. It looks like this:

So, I'll have a function that looks like this:

  function getBadge($accountid){
    $sql = "
    OPEN SYMMETRIC KEY PCAR_Key
      DECRYPTION BY CERTIFICATE PCAR_Certificate

              select top 1
              patientfname as firstname,
              patientlname as lastname,
              practicepatientid as patientid,
              account.accountid,
              account.practiceid,
              dbo.ufn_AccountCurrentWorkingBalance($accountid) as currbal,
              dbo.ufn_AccountCurrentBalance($accountid) as totalbal,
              patientdob,
              CONVERT(varchar(100), DecryptByKey(eSSN)) as patientssn,
              case patientgender
        when 'F' then 'Female'
                when 'M' then 'Male'
              end gender,
              maddress1 as address1,
              maddress2 as address2,
              mcity + ' ' + mstate + ', ' + mzip as address3,
              case
                when phonenumber is null
                  then case
                    when mobilephone is not null
                      then mobilephone
                    when homephone is not null
                      then homephone
                    when workphone is not null
                      then workphone
                  end
                when phonenumber is not null
                  then phonenumber
                else null
              end phonenumber
                
            from account (nolock)

            left outer join patient (nolock) on patient.accountid = account.accountid
            left outer join (select phonenumber, account.accountid, createddate as callmade from call2 (nolock) join account (nolock) on phonenumber in (mobilephone, workphone, homephone) where account.accountid = $accountid) as tmp on tmp.accountid = account.accountid
            where account.accountid = '$accountid'
            order by callmade desc
    ";
    return pconnectdb($sql);
  }

...notice the "return pconnect($sql)." So, I'm passing the SELECT statement as a variable into the pconnect function. Got that. But is the result of that function an array?

Throughout this application, there's a methodology that's being incorporated where the SELECT statement is constructed on the fly via a number of different functions. The final query is packaged as a $sql variable that's passed into the pconnect function. The results of the pconnect function are often stored (I believe) in an array.

Like this:

$data = pf::getAppData('/app/accounttimeline'); //getAppData is a function where a SELECT statement that is then sent to pconnect. "$data" is now the array that was generated by the pconnect function, correct?

A few lines down, you'll see this:

$data['timeline']=AccountTimelines::getAccountTimeline($accounted)

I'm thinking I've just made my $data array into a multi-dimensional array with one of the indices being "timeline" that holds the result of the getAccountTimeline function, correct?

I'm thinking all of the above questions are answered with a resounding "Yes" provided that the pconnect function returns an array.

Please raise your hand if you have the answer...
SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 8 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 2 Answers and 8 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