Extra (double) array fields in return value when using PDO result database

Question

I'm accessing a MySQL database from a PHP script using the PDO library, which I haven't used before (I am more familiar with MySQLi). I'm seeing extra, unexpected fields in the result. They aren't interfering with my code, but I'd like to know where they've come from.

Here is my database call:

SELECT 
    author.author_id AS author_id,
    author.name_last AS name_last,
    author.name_first AS name_first,
    author.detail AS detail     
FROM join_play_author
LEFT JOIN author
ON join_play_author.author_id = author.author_id
WHERE join_play_author.play_id = :play_id
ORDER BY author.name_last

I then bind and execute successfully, and my results contain all of the fields I've requested, with the appropriate labels. However, each field occurs twice in the result set: once with the label I requested, and once with an extra auto-incremented value. For example, one result set (printed using print_r()) looks like this:

Array 
(
    [author_id] => 41 
    [0] => 41 
    [name_last] => Dekker 
    [1] => Dekker 
    [name_first] => Thomas 
    [2] => Thomas 
    [detail] => 0 
    [3] => 0 
)

These double fields aren't actively interfering with my code, but they are annoying during debug and I worry that they could affect performance for large result sets, which will be a concern on the production site.

Removing the AS tags doesn't affect the result array.

Any ideas? Is this just a feature of PDO--and if so, is there any way to turn it off? I don't ever remember seeing these extra fields in MySQLi results, although it's possible I missed them.

Joshua Bixler · Accepted Answer · 2014-10-23 19:54:36Z

5

The PHP library is probably setting PDO::FETCH_BOTH as the result. If you don't want the extra keys set in your array you would need to change it to PDO::FETCH_ASSOC. Most libraries allow you to change this without modifying code though. You will have to read the documentation on that.

Documentation on the PDO statement fetch options. http://www.php.net/manual/en/pdostatement.fetch.php

edited Oct 23, 2014 at 19:54

answered Apr 9, 2014 at 17:13

Joshua Bixler

5312 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chapka Over a year ago

Thanks; that did it! $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); got rid of the extra rows.

lorem monkey · Accepted Answer · 2014-10-23 16:16:34Z

2

As stated by chapka in his comment - use setAttribute to clear the results from double results:

 $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

answered Oct 23, 2014 at 16:16

lorem monkey

4,0023 gold badges37 silver badges49 bronze badges

Collectives™ on Stack Overflow

Extra (double) array fields in return value when using PDO result database

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related