Returning 0 for level in hierarchy query

[salesmr]

I have a query that works fine in Oracle, but I can't seem to get it to work in PHP. The code is:

$query = "SELECT lpad(' ', (level - 1) * 4) || emp_name, level ";
$query .= "from emp_table";
$query .= "CONNECT BY manager_id = prior emp_id ";
$query .= "START WITH emp_id = '1234567' ";
 
$result = odbc_do($connection, $query);
 
while(odbc_fetch_into($result,&$fields))
{
    $empname = $fields[0];
                $level = $fields[1];
 
    $indent = (($level - 1) * 4);
    for($x=0;$x<$indent;$x++)
    {
    print("&nbsp;");
    }
    print("$empname<br>");
}

[Please use CODE tags when posting source code. Thanks! --pbmods]

I am trying to return an indented hierarchy. It works if I create a view and select the data from there, and also when I print the query and run it directly in Oracle, but always returns "0" as $level in PHP.

Could you please advise?

Thank you in advance for your assistance,
Michelle

[ak1dnar]

<?php 
$query = "SELECT lpad(' ', (level - 1) * 4) || emp_name, level ";
$query .= "from emp_table";
$query .= "CONNECT BY manager_id = prior emp_id ";
$query .= "START WITH emp_id = '1234567' ";
 
echo $query;
?>
 

outputs:
Refer to the bold area of the output.It has merged each other.

SELECT lpad(' ', (level - 1) * 4) || emp_name, level from emp_tableCONNECT BY manager_id = prior emp_id START WITH emp_id = '1234567'

change this line

$query .= "from emp_table";

with

$query .= "from emp_table ";

it will work.

[ak1dnar]

I hope you might read Posting Guidelines before your next post.

You are welcome to TSDN.
best regards,
-Ajaxrand

[salesmr]

I actually had it right in my code, just when I copied it over and took out some unneeded stuff to post it, I accidentally removed the space at the end. It still is returning "0" for level.

Would appreciate any info you can give. (By the way, this is my 1st post, so sorry for any inconvenience.)

Thanks

[salesmr]

Mr. Moderator,
Can you please advise? I am stuck on this. I know that "level" is a field/pseudocolumn in Oracle that I can use to tell me what level my recursive query results are at. This works great in Oracle, but when I try it in PHP, it always returns "0" as my level rather than 1,2,3,4, etc. I would appreciate any assistance that you can give. I'm stumped.

Thanks,
Michelle

[ak1dnar]

$query = "SELECT lpad(' ', (level - 1) * 4) || emp_name, level ";
$query .= "from emp_table";
$query .= "CONNECT BY manager_id = prior emp_id ";
$query .= "START WITH emp_id = '1234567' ";
 
$result = odbc_do($connection, $query);
 
while(odbc_fetch_into($result,&$fields))// HAVE YOU NOTICED THIS "&" IN YOUR odbc_fetch_into, MAY I KNOW WHAT IS IT ?
{
$empname = $fields[0];
$level = $fields[1];
 
$indent = (($level - 1) * 4);
for($x=0;$x<$indent;$x++)
{
print("&nbsp;");
}
print("$empname<br>");
}

[salesmr]

Honestly, I've never even noticed it before, and I've been copying that same general code from application to application. I removed it and it still behaved the same way.....not sure.

[ak1dnar]

Honestly, I've never even noticed it before, and I've been copying that same general code from application to application. I removed it and it still behaved the same way.....not sure.

I am not familiar with oracle odbc, better you refer to these sample application and functions under php.net
Thanks,
-Ajaxrand