Fw: 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"
To: "Dani Oderbolz"
Cc:
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

Re: Case insensitivity in column and table names

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.p hp

Works for me with Firebird, PostGre and MySQL

/Jona

----- Original Message -----
From: "David Busby"
To:
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 cod=
e?
> Or are is it when you do something like `pg_fetch_array($resource);` th=
at
it
> becomes an issue?
>
> /B
>
>
>
>
> ----- Original Message -----
> From: "Patrick Gibson"
> To: "Dani Oderbolz"
> Cc:
> 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 wi=
th
> the
> >> exact same tables.
> >>
> > I don=B4t 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 pa=
st
> (with MySQL) we've always been able to rely on the database returning k=
eys
> 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 th=
e
> 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.or=
g
>


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)