PDO include table name prefixes in FETCH_ASSOC

Many of my MySQL tables have columns with the same name. I want to have
PDO include table names in named result sets. For example:

$sth = $dbh->prepare("SELECT * FROM table0, table1");
$result = $sth->fetchAll(PDO::FETCH_ASSOC);


I want $result to be organized like:

echo $result["table0.column0"];
echo $result["table0.column1"];
echo $result["table1.column0"];
echo $result["table1.column1"];


Or, alternatively:

echo $result["table0"]["column0"];
echo $result["table0"]["column1"];
echo $result["table1"]["column0"];
echo $result["table1"]["column1"];


Any ideas? Thanks!


--Aaron


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Aaron Paetznick [ Mi, 24 März 2010 17:05 ] [ ID #2036498 ]

Re: PDO include table name prefixes in FETCH_ASSOC

> Many of my MySQL tables have columns with the same name. I want to have
> PDO include table names in named result sets. For example:
>
> $sth = $dbh->prepare("SELECT * FROM table0, table1");
> $result = $sth->fetchAll(PDO::FETCH_ASSOC);
>
>
> I want $result to be organized like:
>
> echo $result["table0.column0"];
> echo $result["table0.column1"];
> echo $result["table1.column0"];
> echo $result["table1.column1"];
>
>
> Or, alternatively:
>
> echo $result["table0"]["column0"];
> echo $result["table0"]["column1"];
> echo $result["table1"]["column0"];
> echo $result["table1"]["column1"];
>
>
> Any ideas? Thanks!

Sounds like you want to UNION two SELECTs
http://dev.mysql.com/doc/refman/5.0/en/union.html

(SELECT col1, col2, col4 FROM table1 WHERE ... ORDER BY ...)
UNION
(SELECT col1, col2, col4 FROM table2 WHERE ... ORDER BY ...)


> --Aaron
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php

--
Niel Archer
niel.archer (at) blueyonder.co.uk



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Niel Archer [ Mi, 24 März 2010 17:28 ] [ ID #2036499 ]

Re: PDO include table name prefixes in FETCH_ASSOC

On 24 March 2010 16:05, Aaron Paetznick <aaronp [at] critd.com> wrote:
> Many of my MySQL tables have columns with the same name. =C2=A0I want to =
have PDO
> include table names in named result sets. =C2=A0For example:
>
> =C2=A0$sth =3D $dbh->prepare("SELECT * FROM table0, table1");
> =C2=A0$result =3D $sth->fetchAll(PDO::FETCH_ASSOC);
>
>
> I want $result to be organized like:
>
> =C2=A0echo $result["table0.column0"];
> =C2=A0echo $result["table0.column1"];
> =C2=A0echo $result["table1.column0"];
> =C2=A0echo $result["table1.column1"];
>
>
> Or, alternatively:
>
> =C2=A0echo $result["table0"]["column0"];
> =C2=A0echo $result["table0"]["column1"];
> =C2=A0echo $result["table1"]["column0"];
> =C2=A0echo $result["table1"]["column1"];
>
>
> Any ideas? =C2=A0Thanks!
>
>
> --Aaron
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

The data coming from the server dictates the column names, not PDO.

If you need to identify the names, then use aliases in the SQL.





--
-----
Richard Quadling
"Standing on the shoulders of some very clever giants!"
EE : http://www.experts-exchange.com/M_248814.html
EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp
Zend Certified Engineer : http://zend.com/zce.php?c=3DZEND002498&r=3D213474=
731
ZOPA : http://uk.zopa.com/member/RQuadling

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Richard Quadling [ Mi, 24 März 2010 17:18 ] [ ID #2036500 ]

Re: PDO include table name prefixes in FETCH_ASSOC

Thanks for the advise. I wanted a more automatic method of prefixing
column names, but I ended up just aliasing them all.

I know I could always just issue multiple SELECTs, but I wish MySQL
would support this sort of functionality natively. I'd really like to
reference my results as $result["table0"]["column0"] from a single SELECT.

Anyways, thanks all.


--Aaron


On 3/24/2010 11:28 AM, Niel Archer wrote:
>> Many of my MySQL tables have columns with the same name. I want to have
>> PDO include table names in named result sets. For example:
>>
>> $sth = $dbh->prepare("SELECT * FROM table0, table1");
>> $result = $sth->fetchAll(PDO::FETCH_ASSOC);
>>
>>
>> I want $result to be organized like:
>>
>> echo $result["table0.column0"];
>> echo $result["table0.column1"];
>> echo $result["table1.column0"];
>> echo $result["table1.column1"];
>>
>>
>> Or, alternatively:
>>
>> echo $result["table0"]["column0"];
>> echo $result["table0"]["column1"];
>> echo $result["table1"]["column0"];
>> echo $result["table1"]["column1"];
>>
>>
>> Any ideas? Thanks!
>
> Sounds like you want to UNION two SELECTs
> http://dev.mysql.com/doc/refman/5.0/en/union.html
>
> (SELECT col1, col2, col4 FROM table1 WHERE ... ORDER BY ...)
> UNION
> (SELECT col1, col2, col4 FROM table2 WHERE ... ORDER BY ...)
>
>
>> --Aaron
>>
>>
>> --
>> PHP Database Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>
> --
> Niel Archer
> niel.archer (at) blueyonder.co.uk
>
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Aaron Paetznick [ Mi, 24 März 2010 19:04 ] [ ID #2036501 ]

Re: PDO include table name prefixes in FETCH_ASSOC

On 24 March 2010 18:04, Aaron Paetznick <aaronp [at] critd.com> wrote:
> Thanks for the advise. =C2=A0I wanted a more automatic method of prefixin=
g column
> names, but I ended up just aliasing them all.
>
> I know I could always just issue multiple SELECTs, but I wish MySQL would
> support this sort of functionality natively. =C2=A0I'd really like to ref=
erence
> my results as $result["table0"]["column0"] from a single SELECT.
>
> Anyways, thanks all.
>
>
> --Aaron
>
>
> On 3/24/2010 11:28 AM, Niel Archer wrote:
>>>
>>> Many of my MySQL tables have columns with the same name. =C2=A0I want t=
o have
>>> PDO include table names in named result sets. =C2=A0For example:
>>>
>>> =C2=A0 =C2=A0$sth =3D $dbh->prepare("SELECT * FROM table0, table1");
>>> =C2=A0 =C2=A0$result =3D $sth->fetchAll(PDO::FETCH_ASSOC);
>>>
>>>
>>> I want $result to be organized like:
>>>
>>> =C2=A0 =C2=A0echo $result["table0.column0"];
>>> =C2=A0 =C2=A0echo $result["table0.column1"];
>>> =C2=A0 =C2=A0echo $result["table1.column0"];
>>> =C2=A0 =C2=A0echo $result["table1.column1"];
>>>
>>>
>>> Or, alternatively:
>>>
>>> =C2=A0 =C2=A0echo $result["table0"]["column0"];
>>> =C2=A0 =C2=A0echo $result["table0"]["column1"];
>>> =C2=A0 =C2=A0echo $result["table1"]["column0"];
>>> =C2=A0 =C2=A0echo $result["table1"]["column1"];
>>>
>>>
>>> Any ideas? =C2=A0Thanks!
>>
>> Sounds like you want to UNION two SELECTs
>> http://dev.mysql.com/doc/refman/5.0/en/union.html
>>
>> (SELECT col1, col2, col4 FROM table1 WHERE ... ORDER BY ...)
>> UNION
>> (SELECT col1, col2, col4 FROM table2 WHERE ... ORDER BY ...)
>>
>>
>>> --Aaron
>>>
>>>
>>> --
>>> PHP Database Mailing List (http://www.php.net/)
>>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>> --
>> Niel Archer
>> niel.archer (at) blueyonder.co.uk
>>
>>
>>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

I use MS SQL and have used other DBs. I've never seen the table name
as part of the column name in a result set on any of them.


--
-----
Richard Quadling
"Standing on the shoulders of some very clever giants!"
EE : http://www.experts-exchange.com/M_248814.html
EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp
Zend Certified Engineer : http://zend.com/zce.php?c=3DZEND002498&r=3D213474=
731
ZOPA : http://uk.zopa.com/member/RQuadling

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Richard Quadling [ Do, 25 März 2010 09:51 ] [ ID #2036643 ]
PHP » gmane.comp.php.database » PDO include table name prefixes in FETCH_ASSOC

Vorheriges Thema: Ldap sasl bind(gssapi problem)
Nächstes Thema: Building indexes

[ Startseite ] [ Administrator ] [ Suche ] [ Hinweise ] [ Impressum ]

Powered by FudForum