putcsv formatting wrong when result is from pdo

Question

I am trying to import data from a db via pdo and output the results to a csv file. I am able to output to a screen correctly but the formatting in the csv is wild, double names and no '\n'

<?php
require_once('auth.php');
$conn = new PDO("mysql:host=localhost;dbname=$dbname", $username, $pw);

if (($handle = fopen("nameList2.txt", "r")) !== FALSE) {
        while (($data = fgetcsv($handle, 1000, "        ")) !== FALSE) {
                $firstname = $data[0];
                $lastname = $data[1];
                $stmt = $conn->prepare("SELECT * FROM list WHERE FName = :firstname AND LName = :lastname");
                $stmt->bindParam(':firstname', $firstname);
                $stmt->bindParam(':lastname', $lastname);
                $stmt->execute();
                $result = $stmt->fetchAll();
                //var_dump($firstname);
                //var_dump($lastname);
                //var_dump($result);
                $fp = fopen('file.csv', 'w');
                foreach($result as $chunk){
                        echo $chunk[4]."        ".$chunk[6]."   ".$chunk[7]."   ".$chunk[10]."  ".$chunk[11]."".$chunk[12]."  ".$chunk[13]."  ".$chunk[18]."  ".$chunk[19]."  ".$chunk[20]."<br />";
                        fputcsv($fp, $chunk);
                }
                fclose($fp);
        }
        fclose($handle);
        //fclose($fp);
}

?>

enter image description here

nni6 · Answer 1 · 2014-04-11T19:36:31.270

0

Your problem with double names is because you doesn't use the method fetchAll() right:
you get the names twice in the $result. Use that:

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

To fix the problem with \n try

ini_set('auto_detect_line_endings', true);

edited Apr 11 '14 at 19:36

answered Apr 11 '14 at 19:08

nni6

990
6
13

PDO::FETCH_ASSOC seams to blow out my query all together -> Notice: Undefined offset: 4 in /blah/query2.php on line 19 – brad Apr 11 '14 at 19:50
yes, it borks my line 19 which is no big deal, I just commented it out. But actually PDO::FETCH_ASSOC does similar to the recommendation above, outputs the first row but nothing else. It seems to lose the rest of the query. http://i.imgur.com/6NSMEBP.png <- as you can see from a var_dump, the data is there, but never reaches the csv – brad Apr 11 '14 at 19:59
oh, change $fp = fopen('file.csv', 'w'); to $fp = fopen('file.csv', 'a'); – nni6 Apr 11 '14 at 20:18
fopen('file.csv', 'a'); fixed it – brad Apr 11 '14 at 20:29

score 0 · Answer 2 · answered Apr 11 '14 at 19:14

You are feeding fputcsv bad data, so it's giving you bad output. Specifically, fetchAll retrieves each row as an array with both numeric and string keys, so each value appears twice.

Fix this by setting the fetch mode appropriately, for example

$result = $stmt->fetchAll(PDO::FETCH_NUM);

It's unclear what the problem with the line endings is -- you don't say, and I can't tell from the screenshot. What is certain is that fputcsv writes a single line feed as the line termination character. While the vast majority of programs will correctly detect and handle these Unix-style line endings, there are some others (e.g. Notepad) that won't.

Thank you, that cleaned it up a lot. a little too much actually. It outputs the first row correctly and that's all. Only one, not the rest. :/ — brad, Apr 11 '14 at 19:38

putcsv formatting wrong when result is from pdo

2 Answers2