How to get database table header information into an CSV File.

Question

I am trying to connect to the database and get current state of a table and update that information into csv file, with below mentioned piece of code, am able to get data information into csv file but am not able to get header information from database table into csv file.

So my questions is How can I get Database Table Header information into an CSV File ?

$config['database'] = 'sakila';
$config['host'] = 'localhost';
$config['username'] = 'root';
$config['password'] = '';

$d = new PDO('mysql:dbname='.$config['database'].';host='.$config['host'], $config['username'], $config['password']);
$query = "SELECT * FROM actor";
$stmt = $d->prepare($query);

// Execute the statement
$stmt->execute();

var_dump($stmt->fetch(PDO::FETCH_ASSOC));

$data = fopen('file.csv', 'w');

while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
    echo "Hi";
    // Export every row to a file
    fputcsv($data, $row);
}

Header information meaning:

Vehicle     Build   Model
 car        2009    Toyota
 jeep       2007    Mahindra

So header information for this would be Vehicle Build Model

Any guidance would be highly appreciated.

Answer 1

Using SHOW COLUMNS FROM will get you the column names from . You then can write the first line of your CSV with them:

// Do this before adding the rows from the database
$query = "SHOW COLUMNS FROM some_table";
$stmt = $d->prepare($query);
$stmt->execute();
$columns = '';
while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
    $columns .= $row['Field'] . ',';
}
$columns = substr($columns, 0, -1);
fputcsv($data, $columns);

Answer 2

Use mysqldump:

mysqldump -u user -p --fields-terminated-by=',' --tab=/tmp mydatabase mytable

The above command will create 2 files in /tmp. mytable.sql and mytable.txt. The sql file will contain the table creation schema and the txt file will contain the records of the mytable table with fields delimited by a comma.

Answer 3

Just get the keys from the array $row: array_keys(). Now you have all the columnnames and you can put them in your csv-file.

======= EDIT =======

<?php

$header = array();

while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
    if(empty($header)){ // do it only once!
      $header = array_keys($row); // get the columnnames
      fputcsv($data, $header); // put them in csv
    }

    echo "Hi";
    // Export every row to a file
    fputcsv($data, $row);
}
?>

Answer 4

You already have it in PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set

EDIT: One way to get and write the names of columns is (put it before your while lopp)

fputcsv($data, array_keys($stmt->fetch(PDO::FETCH_ASSOC)));

Read about php arrays and associative arrays in particular and on function on arrays (such as array_keys). Answers to particular questions can not replace understanding of basic concepts such as arrays.