PDOStatement::fetch() and duplicate field names

Question

I'm working on a web application using MySQL and PHP 5.3.8. We have a mechanism to translate a simplified query instruction into a complete query string, including joins.

Since I cannot know what (normalized) tables there will be joined and what their fields are called, there may be duplicate field names. When executing PDOStatement::fetch(PDO::FETCH_ASSOC), I get an associated array of field names:

$test = $this->DBConnection->prepare("SELECT `events`.`Title`, `persons`.`Title` FROM `events` JOIN `persons` ON `events`.`HostID` = `persons`.`ID`;");
$test->execute();
$test->fetch();

But I have no way of distinguishing repeating field names, such as "title". Worse, duplicates overwrite each other:

array('Title' => 'Frodo Baggins');

In the bad old days, I ran mysql_fetch_field() on each field to get the table for each field. Please, tell me there is a better way than prefixing the fields (SELECT events.Title AS eventsTitle;).

Your help is greatly appreciated!

Answer 1

Give them aliases in the query so they won't be duplicates:

SELECT events.Title AS eTitle, persons.Title AS pTitle FROM ...

Then the row will be:

array('eTitle' => 'Hobbit Meeting', 'pTitle' => 'Frodo Baggins');

The alternative is to fetch the result as an indexed array rather than associative:

$test->fetch(PDO::FETCH_NUM);

Then you'll get:

array('Hobbit Meeting', 'Frodo Baggins');

and you can access them as $row[0] and $row[1].