PDO Prepared Statement Not Returning Expected Values

General PHP Help
#1

I am trying to query my database to get a message to display to the user. However it triggers the unknown error line – if($ErrMsgRes === FALSE) instead of returning the value expected.

($db_conn is initiated as a PDO object in the included file.)

	include_once __DIR__ . '/resources/system/system.db_conn.php';
		
		for($f = 1; $f<=$_GET['lfec']; $f++){
			
			try{
				//Query to determine is user is authorized.
				$ErrMsgQry = 'Select A.process_id, A.error_id, A.msg_status, A.msg_effective_date, A.error_msg_heading, A.error_msg_body from systbl_SYS_standard_error_messages A Where A.process_id=:pid AND A.error_id=:eid AND A.msg_status = \'A\' Limit 1';
				
				//Query Parameter )
				$ErrMsgParam = ['pid'=>$_GET['p'.$f-1], 'eid'=>$_GET['m'.$f-1]];

				//Prepare PDO Query to accept parameters
				$ErrMsgTest = $db_conn->prepare($ErrMsgQry);

				//Execute PDO Query with parameters
				$ErrMsgTest -> execute($ErrMsgParam);
				
				//Results of the query
				$ErrMsgRes = $ErrMsgTest->fetch();
			
			if($ErrMsgRes === FALSE){
				array_push($errorMsg, "An unknown error occured. Please try again. ");
		}else{
					$M9638527412222 = '<strong>' ;
					$M9638527412222 .=  $ErrMsgRes['error_msg_heading'] ;
					$M9638527412222 .= '</strong> &mdash; ' ;
					$M9638527412222 .=  $ErrMsgRes['error_msg_body'] ;
					$M9638527412222 .=  '  <em>(' . $ErrMsgRes['process_id']. '\\' .  $ErrMsgRes['error_id'] . ')</em>';

					array_push($errorMsg, $M9638527412222);
			}

I tried to query the database with the actual data that I was passing via binds, which results in this SQL:

Select A.process_id, A.error_id, A.msg_status, A.msg_effective_date, A.error_msg_heading, A.error_msg_body from systbl_SYS_standard_error_messages A Where A.process_id='sys.login.validate' AND A.error_id='userid2bad' AND A.msg_status = 'A' Limit 1

Which returns the expected result.

What am I doing wrong?

#2

You will want error reporting turned on for PDO, it is off by default.

You aren’t using the correct placeholder names.
‘pid’ is not the same as ‘:pid’

I’d suggest using bindParam, or because there are so few variables, don’t even use the named placeholders to begin with.

You can also use double quotes for the query string, so you don’t have to escape the string’s single quote.

1 Like
#3

It is not an “Error” it is just not returning the expected record.

I updated the query parameters with adding the :

However the issue still remains.

I also tried the bindParams method you suggested… no change.

I ran the PDO::debugDumpParams() and got the following output:

SQL: [223] Select A.process_id, A.error_id, A.msg_status, A.msg_effective_date, A.error_msg_heading, A.error_msg_body from systbl_SYS_standard_error_messages A Where A.process_id=:pid AND A.error_id=:eid AND A.msg_status = 'A' Limit 1
Params:  2
Key: Name: [4] :pid
paramno=0
name=[4] ":pid"
is_param=1
param_type=2
Key: Name: [4] :eid
paramno=1
name=[4] ":eid"
is_param=1
param_type=2
SQL: [223] Select A.process_id, A.error_id, A.msg_status, A.msg_effective_date, A.error_msg_heading, A.error_msg_body from systbl_SYS_standard_error_messages A Where A.process_id=:pid AND A.error_id=:eid AND A.msg_status = 'A' Limit 1
Params:  2
Key: Name: [4] :pid
paramno=0
name=[4] ":pid"
is_param=1
param_type=2
Key: Name: [4] :eid
paramno=1
name=[4] ":eid"
is_param=1
param_type=2
SQL: [223] Select A.process_id, A.error_id, A.msg_status, A.msg_effective_date, A.error_msg_heading, A.error_msg_body from systbl_SYS_standard_error_messages A Where A.process_id=:pid AND A.error_id=:eid AND A.msg_status = 'A' Limit 1
Params:  2
Key: Name: [4] :pid
paramno=0
name=[4] ":pid"
is_param=1
param_type=2
Key: Name: [4] :eid
paramno=1
name=[4] ":eid"
is_param=1
param_type=2
#4

Your input data probably contains some non-printing or url encoded characters. What does adding the following show -

var_dump($_GET);
#5

Try printing the query then.

This will help with it,

<?php

include_once __DIR__ . '/resources/system/system.db_conn.php';
$debug = true;		
for($f = 1; $f<=$_GET['lfec']; $f++){
			
	try{
		//Query to determine is user is authorized.
		$ErrMsgQry = 'Select A.process_id, A.error_id, A.msg_status, A.msg_effective_date, A.error_msg_heading, A.error_msg_body from systbl_SYS_standard_error_messages A Where A.process_id=:pid AND A.error_id=:eid AND A.msg_status = \'A\' Limit 1';
				
		//Query Parameter )
		$ErrMsgParam = [':pid'=>$_GET['p'.$f-1], ':eid'=>$_GET['m'.$f-1]];

		//Prepare PDO Query to accept parameters
		$ErrMsgTest = $db_conn->prepare($ErrMsgQry);


		if($debug)
		{
			$placeholders = [
				':pid',
				':eid'
			];
				
			$values = [
				$_GET['p'.$f-1],
				$_GET['m'.$f-1],
			];	
			echo str_replace($placeholders, $values, $ErrMsgQuery);
			echo "<br>";
		}

		//Execute PDO Query with parameters
		$ErrMsgTest -> execute($ErrMsgParam);
				
		//Results of the query
		$ErrMsgRes = $ErrMsgTest->fetch();
			
		if($ErrMsgRes === FALSE){
			array_push($errorMsg, "An unknown error occured. Please try again. ");
		}else{
			$M9638527412222 = '<strong>' ;
			$M9638527412222 .=  $ErrMsgRes['error_msg_heading'] ;
			$M9638527412222 .= '</strong> &mdash; ' ;
			$M9638527412222 .=  $ErrMsgRes['error_msg_body'] ;
			$M9638527412222 .=  '  <em>(' . $ErrMsgRes['process_id']. '\\' .  $ErrMsgRes['error_id'] . ')</em>';

			array_push($errorMsg, $M9638527412222);
		}	
	} catch (Exception $e) {
		echo $e->getMessage();
	
	}	
}
#6

Hi all – it looked to be the way that I was populating the binds. pulling elements from the $_get array.

I refactored that process and the query worked like a charm.

Thanks for your assistance.

1 Like
Sponsor our Newsletter | Privacy Policy | Terms of Service