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]

Thierry B. wrote:

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

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

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" <he****@hotpop.com> a écrit dans le message de news:
c0*************@ID-203069.news.uni-berlin.de...

Thierry B. wrote:

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

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

[Andy Hassall]

On Sun, 8 Feb 2004 10:46:15 -0400, "Thierry B." <no****@nospam.com> wrote:

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

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($stmt);
OCIFetch($stmt);

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

echo "\nOCIFetchInto:\n";
OCIExecute($stmt);
OCIFetchInto($stmt, $row, OCI_ASSOC+OCI_NUM);
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 <an**@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>

[Thierry B.]

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" <an**@andyh.co.uk> a écrit dans le message de news:
et********************************@4ax.com...

Unique identifiers for each column within a result set is certainly the way to go, but just testing out whether it does lose data:
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 <an**@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>