Is the result of this an array?

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);
		}

	}

Open in new window


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);
  }

Open in new window


...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...
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.

Ray PaseurCommented:
OK, I can stipulate that $data is an array.  You can find out what it contains with var_dump() or if you want to use the data, with var_export().

There is this line in the code:

return pconnectdb($sql);

And there is also discussion of pconnect

Are these the same or different?
0
Dave BaldwinFixer of ProblemsCommented:
You have '@mssql_connect' in your code.  '@' supresses the Reporting of errors, not the making of them.  And what version of PHP and OS are you using that even supports the 'mssql' driver?
1
Ray PaseurCommented:
Further to DaveBaldwin's note about mssql_connect()... This function is far from future-proof, since it has been removed in the current release of PHP, at PHP 7.  But with the @ error control (suppression) operator, you run the risk that an otherwise harmless upgrade to PHP 7 will cause a silent failure in your script.  It will just stop working.  No message, nothing, just failure and embarrassment.  We don't write programming like that, ever.

PHP @ error suppression is like dog poop.   It is brown sadness programming.  It is out of place, no matter where you find it.  So just don't do that.  Go through your code base and remove all of the @-signs.  Correct any condition that causes a message to arise after you've removed the error suppressor.  If you don't do that you won't be able to sleep at night, wondering what might have failed while you weren't looking.
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Learn More!
Bruce GustPHP DeveloperAuthor Commented:
"Brown sadness programming..."

I'm going to use that in a complete sentence before the end of the day. That is awesome!
0
Bruce GustPHP DeveloperAuthor Commented:
And Ray, to your question:

There is this line in the code:

return pconnectdb($sql);

 And there is also discussion of pconnect

Are they the same...

I know that "pconnect" is a way in which PHP can connect to a database (http://php.net/manual/en/function.mysql-pconnect.php). As a matter of fact, I think you weighed in on that on another question, so that much is clear.  In this app, however, the programmer used "$pconnectdb" as the name of the function that runs the SQL code that's been generated.

The flow looks like this:

You'll have a method that's "assembling" the various parts of a SELECT statement. The end result being packed in a variable called "$sql." At the end of that method you see this:

return StatementImage::runQuery($sql);

"runQuery" looks like this:

    protected static function runQuery($sql) {
        return pconnectdb($sql, DEFAULT_DB, true);
    }

Open in new window


"pconnectdb" is referring to file in the "helpers" directory (this is a Code Igniter framework) and the script for that is the very first block of code I included at the top of this question.

I know, it's a ball of snot, but...

If I can confirm that "pconnectdb" is, not only facilitating connectivity to the database, but is also returning an array, that will validate some of the other conclusions that I've made pertaining to other parts of the code.

So, is "return pconnectdb($sql, DEFAULT_DB, true);" returning an array?

Thanks!
0
Ray PaseurCommented:
So, is "return pconnectdb($sql, DEFAULT_DB, true);" returning an array?
That's a great question and one of the sorts of questions that must be answered to understand the code.  But we cannot answer it here in E-E land.  We do not have access to the code on a server; we can only guess.  

You can answer it.  You can call the runQuery() function with various inputs in the $sql variable.  I would try some simple things that work and some bogus things that should be expected to fail.  Run a few tests with simple things like those shown below, and after each test use var_dump()** to print out the return value from the function.

Here's the problem you're up against.  This is an old application and its design may predate common sense.  

Today a sensible return value might be a response object.  The object would contain the query string, the return value from the query, the count of rows in the results set, and the results set from the query in the form of an array of objects, where each object represents one row of the results set.  It's easy to write programming that deals with a response object like this - just test the return value to see if the query worked.  Then test the count to see if there is any data that needs to be processed.

But considering that this is an old application here are some of the kinds of things I've seen returned from database methods.

1. Boolean FALSE (the query failed)
2. Boolean FALSE (the query worked, but found no data matching the WHERE constraints
3. Boolean FALSE (the database server went away)
4. Boolean FALSE (there were missing inputs in other methods)
5. Array (one row of the results set)
6. Array (array of rows of the results set)
7. Boolean TRUE (update was successful)
8. Integer (update was successful, affected row count)
9. Etc, etc...

If you write your programming to assume there is an array in the return value, you may be writing programming that cannot deal with the other kinds of things that can be returned.  So the question "Is it returning an array" may be too limited in scope.  The smarter question is "What does it return and under what circumstances should I expect to find different return values?"

Some test queries (add any others that might be helpful):
SELECT 2+2 AS total
SELECT foo FROM bar
Gefizzlesnot
SHOW TABLES
DROP TABLE table_that_does_not_exist

Open in new window

** The reason I keep trying to emphasize var_dump() is because it will make you more independent when you get in the habit of looking at the data in PHP variables.  That is all PHP does -- transform one kind of data into another.  We don't control rocket trajectories or biosphere chemistry or detect toxic gas with PHP.  We just move and copy and transform data.  Once you get used to seeing the effect of PHP code on data structures, you'll make progress faster, I promise!
0

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:
Got it!

I'll do a var_dump and see what's there. Although, even simple tests like that turn out to be a challenge in and of themselves because of all the "tentacles" that surround the query and blah, blah, blah.

We'll get it done.

Thanks, Ray!
0
Ray PaseurCommented:
You have my sympathy about the "tentacles."  There are many, many complexities that are unnecessary in PHP frameworks.  That's one of the reasons that Ruby is so popular for prototyping.  Laravel (with its own set of complexities) tries to get past all the configuration stuff and just do things by convention.  It's a big step in the right direction!
0
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
PHP

From novice to tech pro — start learning today.