Handling case of field names in multiple db's

[Michael Flanagan]

(Bottom line: I think what I'm looking for is an easy way of changing
the case of key values in an array.)

I've got code that I'm trying to make agnostic about the underlying
database system I'm using. That is, I want the code to work whether
the underlying db is MS SQL, MySQL, Oracle, etc. I'm using the PEAR DB
package, and it's great for what I'm trying to do...except in one
area. I get the returned row fields in an associative array, where the
element key is the field name and the element value is the field
value. The problem is that MS SQL returns field names in mixed case
(actually, it returns the field names in the case the user used to
define the field names in MS SQL in the first place), while Oracle
returns the field names all caps. So, if I have the same db on MS SQL
and on Oracle, I can't easily access the field values.
For example:

$row*=*$result->fetch_row(DB_FETCHMODE_ASSOC);

Now, if I print_r($row), I see something like:

Array*(
***[My_Id]*=*1;
***[Name]*=*"Fred Flintstone";
)

If I run this under an Oracle db, I might get this, instead:

Array*(
***[MY_ID]*=*1;
***[NAME]*=*"Fred Flintstone";
)

So my code cannot say something such as:

$id*=*$row["My_Id"];

That would work for the MS SQL version, but not for the Oracle
version. I certainly don't want to say something like:

if*($IsMsSql)*$id*=*$row["My_Id"];
else*$id*=*$row["MY_ID"];

I really don't have control of how the users define the case of their
field names, otherwise I could tell them to upper-case them all the
time. But that might not work, if a different set of db routines that
DB uses returns field names in all lower case.

I could do the following, but I'm not sure I want to take the
efficiency hit (maybe it isn't so bad; I don't know):

foreach*($row*as*$key*=>*$value)*{
***$row*[strtoupper($key)]*=*$value;
***unset($row[$key]);
}


Any ideas? Thanks.
Michael

[Pedro Graca]

Michael Flanagan wrote:

> I could do the following, but I'm not sure I want to take the
> efficiency hit (maybe it isn't so bad; I don't know):
>
> foreach ($row as $key => $value) {
> $row [strtoupper($key)] = $value;
> unset($row[$key]);
> }

Don't know about the efficiency stuff, but you don't want to go through
that with $row['DATA'] !

$row2 = array();
foreach ($row as $key => $value) {
$row2[strtoupper($key)] = $value;
}
$row = $row2;
unset($row2);
--
--= my mail box only accepts =--
--= Content-Type: text/plain =--
--= Size below 10001 bytes =--

[Michael Flanagan]

Good point; thanks.

I've looked at constants, and so now I'm doing something like:

define("USE_MSSQL", TRUE);

if (defined("USE_MSSQL") && USE_MSSQL) {
define("MY_ID", "My_Id");
define("NAME", "Name");
}
else if (defined("USE_ORACLE") && USE_ORACLE) {
define("MY_ID", "MY_ID");
define("NAME", "NAME");
}
else {
die("No db defined");
}

....

$id = $row[MY_ID];

Still, am I missing something easier still?

Michael

Pedro Graca <hexkid@hotpop.com> wrote in message news:<bqdubh$21gvdd$1@ID-203069.news.uni-berlin.de>...

> Michael Flanagan wrote:

> > I could do the following, but I'm not sure I want to take the
> > efficiency hit (maybe it isn't so bad; I don't know):
> >
> > foreach ($row as $key => $value) {
> > $row [strtoupper($key)] = $value;
> > unset($row[$key]);
> > }

>
> Don't know about the efficiency stuff, but you don't want to go through
> that with $row['DATA'] !
>
> $row2 = array();
> foreach ($row as $key => $value) {
> $row2[strtoupper($key)] = $value;
> }
> $row = $row2;
> unset($row2);

[Pedro Graca]

Michael Flanagan wrote:

> I've looked at constants, and so now I'm doing something like:

[snip]

> $id = $row[MY_ID];
>
> Still, am I missing something easier still?

Well ... I've only ever used MySQL (and M$SQL for a while)
and I never used mysql_fetch_assoc() (or mysql_fetch_array() -- UGH!).

With mysql_fetch_row() (or mssql_fetch_row()) I get numerical
indexes only, and I do:

<?php
// ...
$id = $row[0];
$name = $row[1];
// ...
?>
--
--= my mail box only accepts =--
--= Content-Type: text/plain =--
--= Size below 10001 bytes =--

[Michael Flanagan]

Right. But then the problem becomes one of having to mod your code if
you add a new field, or if the order of fields changes. I could
mention each field by name in the SELECT statement, but...

Thanks for your suggestions.

Michael

Pedro Graca <hexkid@hotpop.com> wrote in message news:<bqgon2$20mtur$1@ID-203069.news.uni-berlin.de>...

> Michael Flanagan wrote:

> > I've looked at constants, and so now I'm doing something like:

> [snip]

> > $id = $row[MY_ID];
> >
> > Still, am I missing something easier still?

>
> Well ... I've only ever used MySQL (and M$SQL for a while)
> and I never used mysql_fetch_assoc() (or mysql_fetch_array() -- UGH!).
>
> With mysql_fetch_row() (or mssql_fetch_row()) I get numerical
> indexes only, and I do:
>
> <?php
> // ...
> $id = $row[0];
> $name = $row[1];
> // ...
> ?>