Oracle->not same result from php and from sql*plus

[Thierry B.]

Hi,

Here is my query, ran from oracle sql*plus:

SELECT mrbs_room.id, start_time, end_time, name, mrbs_entry.id, type
FROM mrbs_entry, mrbs_room
WHERE mrbs_entry.room _id = mrbs_room.id
AND area_id = 101
AND start_time <= 1076068800
AND end_time > 1076065200

Here is the correct result :
SQL> Connected.

ID START_TIME END_TIME NAME ID T
---------- ---------- ---------- ---- -- -
82 1076065200 1076068800 test 41 I

Ran from php, I got this :

ID START_TIME END_TIME NAME T
---------- ---------- ---------- ---- -- -
41 1076065200 1076068800 test I

the two ID fields are named with their table name, but php keep the second
value and display it as the first one.

Thierry Bothorel

--
Click here to answer / cliquez ci dessous pour me repondre

http://cerbermail.com/?7O7SOrggJg

[Pedro Graca]

Re: Oracle-&gt;not same result from php and from sql*plus

Thierry B. wrote:[color=blue]
> the two ID fields are named with their table name, but php keep the second
> value and display it as the first one.[/color]

Rename the ID fields, so that they have different names for PHP.

select table1.id, ..., table2.id as id2, ...


Or maybe you can use integer indexing on the result;
instead of $res['id'] use $res[0] and $res[4]
--
--= my mail box only accepts =--
--= Content-Type: text/plain =--
--= Size below 10001 bytes =--

[Thierry B.]

Re: Oracle-&gt;not same result from php and from sql*plus

Thanks,
I will use alias, but I already used integer indexing, the buffer returned
by oracle simply miss one column


--
--
Click here to answer / cliquez ci dessous pour me repondre

http://cerbermail.com/?7O7SOrggJg

"Pedro Graca" <hexkid@hotpop. com> a écrit dans le message de news:
c058s8$12dc36$1 @ID-203069.news.uni-berlin.de...[color=blue]
> Thierry B. wrote:[color=green]
> > the two ID fields are named with their table name, but php keep the[/color][/color]
second[color=blue][color=green]
> > value and display it as the first one.[/color]
>
> Rename the ID fields, so that they have different names for PHP.
>
> select table1.id, ..., table2.id as id2, ...
>
>
> Or maybe you can use integer indexing on the result;
> instead of $res['id'] use $res[0] and $res[4]
> --
> --= my mail box only accepts =--
> --= Content-Type: text/plain =--
> --= Size below 10001 bytes =--[/color]

[Andy Hassall]

Re: Oracle-&gt;not same result from php and from sql*plus

On Sun, 8 Feb 2004 10:46:15 -0400, "Thierry B." <nospam@nospam. com> wrote:
[color=blue]
>I will use alias, but I already used integer indexing, the buffer returned
>by oracle simply miss one column[/color]

Unique identifiers for each column within a result set is certainly the way to
go, but just testing out whether it does lose data:

<pre>
<?php
$conn = OCILogon('test' , 'test', 'dev92lnx');

$stmt = OCIParse($conn, 'select 1 id, 2 id from dual');
OCIExecute($stm t);
OCIFetch($stmt) ;

echo "OCIFetch:\ n";
echo OCIResult($stmt , 1) . "\n";
echo OCIResult($stmt , 2) . "\n";
echo OCIResult($stmt , "ID") . "\n";

echo "\nOCIFetchInto :\n";
OCIExecute($stm t);
OCIFetchInto($s tmt, $row, OCI_ASSOC+OCI_N UM);
var_dump($row)
?>
</pre>

Outputs:

OCIFetch:
1
2
1

OCIFetchInto:
array(3) {
[0]=>
string(1) "1"
["ID"]=>
string(1) "2"
[1]=>
string(1) "2"
}

The second identically named column isn't lost. Most of the PHP OCI8 functions
work using bind by name, rather than position, but the data is still there.

If you fetch into an associative array by name, clearly it can only keep one
of the values. (Although this could be better documented - can't spot a
reference from a quick read through). And the code above shows an inconsistency
between OCIResult and OCIFetchInto as to which one it picks in the case of
identical identifiers (which is almost certainly a bug).

--
Andy Hassall <andy@andyh.co. uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk > / <http://www.andyhsoftwa re.co.uk/space>

[Thierry B.]

Re: Oracle-&gt;not same result from php and from sql*plus

Thanks,

using OCI_ASSOC, I just need to write :

SELECT mytable1.id AS id, mytable2.id AS id2

instead of

SELECT mytable1.id, mytable2.id

Thierry Bothorel
--
Click here to answer / cliquez ci dessous pour me repondre

http://cerbermail.com/?7O7SOrggJg

"Andy Hassall" <andy@andyh.co. uk> a écrit dans le message de news:
etkc205dn5fdh56 lckn2940ocvoood d6di@4ax.com...
[color=blue]
> Unique identifiers for each column within a result set is certainly the[/color]
way to[color=blue]
> go, but just testing out whether it does lose data:
>
>
> The second identically named column isn't lost. Most of the PHP OCI8[/color]
functions[color=blue]
> work using bind by name, rather than position, but the data is still[/color]
there.[color=blue]
>
> If you fetch into an associative array by name, clearly it can only keep[/color]
one[color=blue]
> of the values. (Although this could be better documented - can't spot a
> reference from a quick read through). And the code above shows an[/color]
inconsistency[color=blue]
> between OCIResult and OCIFetchInto as to which one it picks in the case of
> identical identifiers (which is almost certainly a bug).
>
> --
> Andy Hassall <andy@andyh.co. uk> / Space: disk usage analysis tool
> <http://www.andyh.co.uk > / <http://www.andyhsoftwa re.co.uk/space>[/color]