Jump to content


Photo

Oracle NULL and PHP


  • Please log in to reply
2 replies to this topic

#1 mstap42

mstap42
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 27 January 2006 - 02:53 PM

I've run into a bit of a snag with querying Oracle in PHP and wondered if you had any ideas. The problem is null values. With my scripts, when data is sent to HTML tables or Excel, the nulls aren't recognized, and the value to the right is moved into that blank position. Otherwise, where all fields have some content, the data is displayed correctly.

Example:

SELECT last_name, first_name, middle_initial, profile
FROM mydb.operators

If middle_initial contains nothing, the profile value is written in the middle_initial column:
last_name first_name middle_initial profile
Simpson Homer admin

I would like to be able to substitute a blank value with something like a tab (\t). But tests with PHP's is_null() and empty() functions seem to show that these functions do not recognize the Oracle null at all. With error_reporting set to 2047 (E_ALL), they return nothing--neither Null nor Not Null, neither Empty nor Not Empty. Whether a field is populated or not, isset() shows the value to be Set.

So conditional statements like this fail:
[!--fonto:Courier New--][span style=\"font-family:Courier New\"][!--/fonto--]
if ((!isset($value)) || ($value == "")) {
$value = "\t";
}[!--fontc--][/span][!--/fontc--]

The conditions [!--fonto:Courier New--][span style=\"font-family:Courier New\"][!--/fonto--]is_null($value)[!--fontc--][/span][!--/fontc--] and [!--fonto:Courier New--][span style=\"font-family:Courier New\"][!--/fonto--]empty($value)[!--fontc--][/span][!--/fontc--] also fail.

Is there something about Oracle null values that PHP can't see? Is there some other function I could use to identify Oracle nulls for character substitution?

Many thanks!

#2 mandrews81

mandrews81
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 07 December 2006 - 02:58 AM

When fetching your row try using
while ($row = oci_fetch_array ($statement, OCI_ASSOC+OCI_RETURN_NULLS ))


The OCI_RETURN_NULLS will create empty elements for the NULL fields.

See http://us2.php.net/m...fetch-array.php for more info.

#3 rjgoulet

rjgoulet
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 27 February 2007 - 07:21 PM

You might also try using Oracle's NVL function.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users