Thread: Fw: Case insensitivity in column and table names

Fw: Case insensitivity in column and table names

From
"David Busby"
Date:
Patrick,
    Could you simply place some strtolower() or strtoupper() in the code?
Or are is it when you do something like `pg_fetch_array($resource);` that it
becomes an issue?

/B




----- Original Message -----
From: "Patrick Gibson" <postgres_list@patrickg.com>
To: "Dani Oderbolz" <oderbolz@ecologic.de>
Cc: <pgsql-php@postgresql.org>
Sent: Thursday, May 15, 2003 10:47
Subject: Re: [PHP] Case insensitivity in column and table names


On 5/15/03 5:56, Dani Oderbolz wrote:

>> We have encountered a big problem when doing the same thing in Postgres,
as
>> Postgres seems to lowercase all the column names. Since associative
arrays
>> in PHP *are* case-sensitive, all of our existing code thus breaks with
the
>> exact same tables.
>>
> I don´t quite get your point.
> In Postgres 7.3.2 I can do this:
>
> Select MyColumn from MyTable;
> --Works
>
> as well as
> Select mycolumn from mytable;
> --Works as well
>
> Its just that internally postgres stores your object names in lowercase -
> which should not matter to you.

It does matter because when it comes back to PHP, the keys in the array are
lowercase, even though the query specified them as uppercase. In the past
(with MySQL) we've always been able to rely on the database returning keys
in the same case as we specify -- PHP arrays are case sensitive, which is
where the problem comes in.

If we have the query, 'select ID, USERNAME from USERS' with a MySQL
database, we'd get an associate array with 'ID' and 'USERNAME' being the
keys. The same query with Postgres will give us keys of 'id' and 'username',
which is different.

The goal of what we want to do is to be able to use the same code no matter
which database we are using. We ultimately will switch to a lowercase
convention to accommodate Postgres, but I'm trying to find out if that
really is the only option. The quoting technique does not work either
because MySQL handles it differently.

Patrick


---
 patrick gibson
 http://patrickgibson.com/


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


Re: Case insensitivity in column and table names

From
"Jona"
Date:
You could change the key case in the array after having called
pg_fetch_array($resource)
Use the buildin PHP function: array_change_key_case
http://dk.php.net/manual/en/function.array-change-key-case.php

Works for me with Firebird, PostGre and MySQL

/Jona

----- Original Message -----
From: "David Busby" <busby@pnts.com>
To: <pgsql-php@postgresql.org>
Sent: Thursday, May 15, 2003 9:54 PM
Subject: Fw: [PHP] Case insensitivity in column and table names


> Patrick,
>     Could you simply place some strtolower() or strtoupper() in the code?
> Or are is it when you do something like `pg_fetch_array($resource);` that
it
> becomes an issue?
>
> /B
>
>
>
>
> ----- Original Message -----
> From: "Patrick Gibson" <postgres_list@patrickg.com>
> To: "Dani Oderbolz" <oderbolz@ecologic.de>
> Cc: <pgsql-php@postgresql.org>
> Sent: Thursday, May 15, 2003 10:47
> Subject: Re: [PHP] Case insensitivity in column and table names
>
>
> On 5/15/03 5:56, Dani Oderbolz wrote:
>
> >> We have encountered a big problem when doing the same thing in
Postgres,
> as
> >> Postgres seems to lowercase all the column names. Since associative
> arrays
> >> in PHP *are* case-sensitive, all of our existing code thus breaks with
> the
> >> exact same tables.
> >>
> > I don´t quite get your point.
> > In Postgres 7.3.2 I can do this:
> >
> > Select MyColumn from MyTable;
> > --Works
> >
> > as well as
> > Select mycolumn from mytable;
> > --Works as well
> >
> > Its just that internally postgres stores your object names in
lowercase -
> > which should not matter to you.
>
> It does matter because when it comes back to PHP, the keys in the array
are
> lowercase, even though the query specified them as uppercase. In the past
> (with MySQL) we've always been able to rely on the database returning keys
> in the same case as we specify -- PHP arrays are case sensitive, which is
> where the problem comes in.
>
> If we have the query, 'select ID, USERNAME from USERS' with a MySQL
> database, we'd get an associate array with 'ID' and 'USERNAME' being the
> keys. The same query with Postgres will give us keys of 'id' and
'username',
> which is different.
>
> The goal of what we want to do is to be able to use the same code no
matter
> which database we are using. We ultimately will switch to a lowercase
> convention to accommodate Postgres, but I'm trying to find out if that
> really is the only option. The quoting technique does not work either
> because MySQL handles it differently.
>
> Patrick
>
>
> ---
>  patrick gibson
>  http://patrickgibson.com/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>