Description:
------------
This request summarises the exchanges that took place on php.pecl.dev between 13-19 June 2005 under the topic
"PDO question: any way to resolve duplicate column names with PDO_FETCH_ASSOC?"


**********************************************************
Original request from Matthew Peters:
**********************************************************
I really like the PDO_FETCH_ASSOC behaviour where I get back an associative array indexed by column name, but I have a problem when there are two columns with the same name in the result set: for example when I join two tables that both contain a column called 'id', or 'name', for example.
Experiment shows that one of them hides the other.

I would really like if there were a way to get both back and to know which was from which table. The simplest solution to my mind would be if there were a way to get the keys in the associative array to be qualified with table name: 'table1.id' and 'table2.id', for example. Does anyone know of a way of doing this or something similar?

**********************************************************
Wez's reply:
**********************************************************
Hi Matthew,

The problem is that not all of the underlying database libraries offer up information about the table names, so we can't guarantee that they'll be there.

For those that do have the info, we can add an attribute that you'd pass in when you prepare the query, that would cause the table names to be included in the keys.  For drivers that don't support that, an error would be raised.

I suspect that you're also looking for a workable alternative when the driver doesn't support it; one possible suggestion is have a fetch mode that allows multiple columns with the same name to come back; we could represent this in PHP script space something like this:

$row = array('price' => 100, 'id' => array(1 /* from first table */, 2 /* from second */));

You're still faced with the problem of having to figure out which version of id belongs to which table, but now you'd have a way to determine when you need to start poking around the SQL (and which names to look for), so that you can avoid doing it on every query.

How does that sound?

**********************************************************
Final exchange: followup from Matthew with Wez's answer
**********************************************************
Hi Matthew,

> What you propose when the underlying library supports returning table
> names - adding an attribute on the prepare - would be fine. I wondered why
> you wouldn't want to put it on the fetch, like PDO_FETCH_ASSOC_QUALIFIED,
> but maybe it's extra work to collect the information in the first place?

Yes
 
> names of the tables and columns. I would need to know that the values in the
> arrays were in some guaranteed order i.e. that you had copied them from the
> result in a certain order, so that at least I knew id[0] appeared before
> id[1] in the SQL statement.

This should always be the case, as PDO will walk the columns left-to-right.

Would you mind opening up a bug report for this issue?
Although I do flag mail for follow up, it tends to be mostly minor stuff, so will likely forget to check back on this; the bug report will group it together with other PDO stuff so that I see it when I'm next looking.