CODEWITHSUNDEEP
phpmysqlarrayscsv
Tyler
Tyler

Reputation: 85

PHP MySQL table to CSV with column names

I have the following php function that ouputs a table to a CSV file. I also need the column names in the first row. It works however it has a column with the name, then an identical column but instead of the name it is the column number. I've tried alot of solutions to output to CSV and this one works the best for me. But for the life of me i cannot figure out why I am getting the duplicate columns. I like this solution as it is clean and simple. There are other solutions on here. I am wondering can i get this to work?

I tried using a query, and using "UNION ALL" but it just wasn't going to work that well for me:

SELECT * INTO OUTFILE "c:/mydata4.csv" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' FROM tickets

my function that works the best is:

public function tickets_to_csv() {

    $query = $this -> db -> prepare("SELECT * FROM tickets");

    try {
        $query -> execute();
        $data = $query -> fetchAll();
        $fp = fopen('report.csv', 'w');
        $column_names = array_keys($data[0]);
        fputcsv($fp, $column_names);
        foreach ($data as $row) {
            fputcsv($fp, $row);
        }
        fclose($fp);
    } catch(PDOException $e) {
        die($e -> getMessage());
    }

}

It creats a file that looks like this

Column_1  |      0      | Column_2  |      1     |
Val_1-1   |    Val_1-1  | Val_2-1   |  Val_2-1   |
Val_1-2   |    Val_1-2  | Val_2-2   |  Val_2-2   |  
Val_1-3   |    Val_1-3  | Val_2-3   |  Val_2-3   |

To me this should work fine.

EDIT:

Here is my solution to @Jessicas answer, Change to:

$data = $query -> fetchAll(PDO::FETCH_ASSOC);

Also, I edited this part to create a new line for each row. Otherwise looks fine in excel, but if opened in notepad, it was all one line. Here is the solution:

        $query -> execute();
        $new_line = "\r\n";
        $data = $query -> fetchAll(PDO::FETCH_ASSOC);
        $fp = fopen('report.csv', 'w');
        $column_names = array_keys($data[0]);
        $column_names[] = $new_line;
        fputcsv($fp, $column_names);
        foreach ($data as $row) {
            $row[] = $new_line; 
            fputcsv($fp, $row);
        }

Upvotes: 1

Views: 1838

Answers (1)

Jessica
Jessica

Reputation: 7005

You need to set the fetch mode to be just associative, not both.

http://www.php.net/manual/en/pdostatement.setfetchmode.php

PDO::FETCH_ASSOC (integer) Specifies that the fetch method shall return each row as an array indexed by column name as returned in the corresponding result set. If the result set contains multiple columns with the same name, PDO::FETCH_ASSOC returns only a single value per column name. http://www.php.net/manual/en/pdo.constants.php

Upvotes: 4

Related Questions