Question about PDO mysql and local variables

[timgerr]

Hello all, I have a goofy question for ya, I have a PDO transaction to my mysql database that I an sending 2 queries. I get local variables in the first mysql statement then pass the variables to the second mysql statement.
Here is my problem I have at signs (@) in some returned object references and this screws up PHP.

Here are my queries:
Query 1:

$stmt = conn::getInstance()->prepare("SELECT @RES_TYPE := dnt_resource_type.resource_type,
					@GRP_NAME := dnt_groupname.grp_name,
					@RSS_Role := dnt_group_resource.role,	
					@CBS_ID := dnt_resource.res_resource 
				FROM dnt_resource INNER JOIN dnt_resource_type ON dnt_resource.res_type = dnt_resource_type.id
					 INNER JOIN dnt_groupname ON dnt_groupname.id = dnt_resource.groupname
					 INNER JOIN dnt_group_resource ON dnt_group_resource.resourceid = dnt_resource.id
					 INNER JOIN dnt_role_type ON dnt_group_resource.role = dnt_role_type.id
					 INNER JOIN dnt_groupmembers ON dnt_groupmembers.grp_mbr_name = dnt_groupname.id
					 INNER JOIN dnt_user ON dnt_groupmembers.userid = dnt_user.id
				WHERE dnt_user.id = ? AND ( dnt_role_type.id = '6' OR dnt_role_type.id = '3' )
						AND dnt_resource_type.resource_type = 'Bla'");
$stmt->execute(array_values($a));

Query 2:

$stmt = conn::getInstance()->prepare("SELECT node.CBSid, @RES_TYPE, @GRP_NAME,@RSS_Role, node.CRid, node.name, node.Description,  node.State, node.PARid, node.cbstreeroot
			FROM dnt_cbs_table AS node, dnt_cbs_table AS parent
			WHERE node.L BETWEEN parent.L AND parent.R
			AND parent.CBSid =  @CBS_ID
			AND parent.cbstreeroot = node.cbstreeroot
			ORDER BY node.L;");
			$stmt->execute();

the problem is that in my arrayobjects I have @ signs in them, here is a sample of output

 public 'CBSid' => string '645' (length=3)
      public '@RES_TYPE' => string 'Somthing' (length=7)
      public '@GRP_NAME' => string 'Somthing' (length=7)
      public '@RSS_Role' => string '6' (length=1)
      public 'CRid' => string '234' (length=3)
      public 'name' => string 'Default Root' (length=12)
      public 'Description' => string 'Computer Generated Root, Please rename' (length=38)
      public 'State' => string '1' (length=1)
      public 'PARid' => string '0' (length=1)
      public 'What' => string '645' (length=3)

How can I take the @ signs out within the mysql statements using PDO, I dont want to have to loop through the array objects every time to clean this up.

Thank you,
timgerr

-How important does a person have to be before they are considered assassinated instead of just murdered?
Congratulations!

 

[jpadie]

that's the way that mysql works. it returns the given column names, and in your case some column names are prepended with an ampersat because you're using local varables.

an ampersat is perfectly legal as an array key. it is not, however, a legitimate class property nor a legitimate simple variable name. i am really surprised that the object property names are being correctly assigned in your code.

so i guess the best answer is to return the row as an associative variable and then use this class to cleanse it

class rowConverter{
	public function __construct($row){
		foreach ($array as $key=>$val){
			$pattern = '/[^a-zA-Z_\x7f-\xff]/';
			$replace = '_';
			$key = preg_replace($pattern, $replace, $key);
			$this->$key = $val;
		}
	}
}
[/code

[code=sample use]
$stmt = conn::getInstance()->prepare("SELECT node.CBSid, @RES_TYPE, @GRP_NAME,@RSS_Role, node.CRid, node.name, node.Description,  node.State, node.PARid, node.cbstreeroot
            FROM dnt_cbs_table AS node, dnt_cbs_table AS parent
            WHERE node.L BETWEEN parent.L AND parent.R
            AND parent.CBSid =  @CBS_ID
            AND parent.cbstreeroot = node.cbstreeroot
            ORDER BY node.L;");
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
	$data[] = new rowConverter($row);
}

if you wanted to keep as an array, you could run get_object_vars() on it after the event. or even create an array filter/map to do it in one iteration.

for the former, something like this

extract(get_object_vars($row));

but best of all, why not leave as an array? or have i misunderstood?

you may also get an alternative view from the mysql forum not to mention other members of this forum.

 

[timgerr]

Thanks for the help, this rocks.

Timgerr

-How important does a person have to be before they are considered assassinated instead of just murdered?
Congratulations!

 

[jpadie]

note that the regex pattern supplied

$pattern = '/[^a-zA-Z_\x7f-\xff]/';

represents the panoply of permitted characters in a first character of a variable name. it may well not be what you want to use as a full replacement as it will also replace numbers. to avoid this add 0-9 inside the square brackets but then you should also check to make sure that the first character is not a number. perhaps add this as a second regex
$pattern = '/^([0-9])/';
$replace = '_$1';
preg_replace($pattern, $replace, $key);
[/code]