4
function readDB($db,$event)
{
    try {
        $rows = array();
        $sql = "SELECT \"Red Score\", \"Blue Score\", red1, red2, red3, blu1, blu2, blu3 FROM Matches WHERE Event='$event' AND Type='Q' ORDER BY Number;";
        foreach($db->query($sql) as $row)
        {
            $rows[] = $row;
            echo count($row) . "<br/>";
        }
        printArray($rows);
    } catch(PDOException $e) {
        $rows = 'aids';
        echo $e->getMessage();
    }

    $db = null;
}

Here's the relevant function. It runs the query and puts the results into a 2D array. Changing the query to select only one field with no conditions still returns two things.

The code returns the right data, just each record is duplicated, ie a row of eight is turned into a row of 16. The query when run in the SQLite3 console returns each thing once.
Sample:
PHP: 

51    51    27    27    836    836    435    435    1102    1102    245    245    88    88    1293    1293  
33    33    30    30    401    401    3489   3489   415     415     3475   3475   4722  4722  2655    2655

SQLite3: 

51    27    836    435    1102    245    88    1293  
33    30    401    3489   415     3475   4722  2655

Can anybody explain why this is happening?

EDIT: My apologies. I've replaced the pastes with embedded code. Should've thought to do that.

EDIT EDIT: Solution: Set the default fetch mode with PDOStatement::setAttribute(); it defaults to FETCH_BOTH which is causing this issue. I looked in the wrong place for a solution.
Docs are here, for more information: http://www.php.net/manual/en/pdostatement.fetch.php

matt
  • 161
  • 8
  • 1
    Show us some of the code here please, not just on fiddle – Hanky Panky Mar 19 '13 at 04:29
  • Please paste your code directly into the question, rather than posting to an outside service like pastebin. The external code can go away at any time, and there is no version control over it like with SO questions. Therefore, future readers will probably not be able to see the external code and thus this question will be useless or not make much sense. **StackOverflow is as much about future reference for others as it is about you getting your question answered today.** – Andy Lester Mar 19 '13 at 04:40
  • You can also show us what the echo is printing.. – MatRt Mar 19 '13 at 04:42
  • I'd suggest that when using PDO, do it the correct way, and use parametrized queries! – ppeterka Mar 19 '13 at 12:43
  • 2
    What fetch mode are you using? By default PDO will fetch an array of results indexed by column name **and** by column numbers (ie. the values from the DB appear in the array twice, with two different keys). So if you're using the default fetch mode, you're probably cycling through both of those representations of the result set. EDIT: looks like you figured this out on your own right before I posted this! – Liv Mar 19 '13 at 12:50

1 Answers1

11

'By default PDO will fetch an array of results indexed by column name and by column numbers (ie. the values from the DB appear in the array twice, with two different keys). So if you're using the default fetch mode, you're probably cycling through both of those representations of the result set.' Thank you, Liv, this explanation helps.

Solution: Set the default fetch mode with PDOStatement::setAttribute(); it defaults to FETCH_BOTH which is causing this issue. I looked in the wrong place for a solution. Docs are here, for more information: http://www.php.net/manual/en/pdostatement.fetch.php

matt
  • 161
  • 8