Export sqlite table using php

Im just getting started with PHP, and I need to export a table from my sqlite database to a CSV or ideally XLS. I have found an example using mysql, but i cant convert it to work with sqlite.

Here is what i have so far:

<?php
$db     = new PDO('sqlite:../ccm.sqlite');
$query  = $db->query('SELECT * FROM Emails');
$export = sqlite_query ($query);
$fields = sqlite_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ ){
    $header .= sqlite_field_name( $export , $i ) . "\t";
}

while( $row = sqlite_fetch_row( $export ) ){
//sqlite_fetch_row doesnt actually exist...
    $line = '';
    foreach( $row as $value ){                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) ){
            $value = "\t";
        }else{
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}
$data = str_replace( "\r" , "" , $data );

if ( $data == "" ){
    $data = "\n(0) Records Found!\n";                        
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=emails.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
?>

Can anyone help me out with this? or if there is a simpler w开发者_如何学Cay that would be great. Cheers

---

You are mixing PDO methods with sqlite_* functions; try using one or the other:

$db = new PDO("sqlite:../ccm.sqlite");
$query = $db->query("select * from emails");
$first_row = true;
while ($row = $query->fetch(PDO::FETCH_ASSOC))
{
  if ($first_row)
  {
    // I'm not sure how to get the field names using a PDO method but
    // we can use the first row's (or any row's) key values as these
    // are the field names.
    $first_row = false;
    $number_of_fields = count($row);
    $field_names = array_keys($row);
    $first_field_name = $field_names[0];
  }
  // do stuff here with the row
  print_r($row);
}

or

$db = sqlite_open("../cmm.sqlite");
$query = sqlite_query($db, "select * from emails");
$number_of_fields = sqlite_num_fields($query);
$first_field_name = sqlite_field_name($query, 0);
while ($row = sqlite_fetch_array($query))
{
    // do stuff here with the row.
    print_r($row);
}

I'm not 100% sure but I think PDO works with sqlite3 databases and sqlite_* functions works with sqlite2 databases.

---

If I need to quickly get data out of a sqlite3 database as CSV files, I use the sqlite3 CLI:

$ sqlite3 ccm.sqlite
sqlite> .mode csv
sqlite> .output emails.csv
sqlite> .headers on
sqlite> select * from emails
sqlite> .output stdout
sqlite> .quit
$ cat emails.csv

This starts the sqlite3 CLI opening the ccm.sqlite database, sets the output mode to csv (the format of select statements), sets output to the file named emails.csv, turns select column headers on (optional), selects all the data in the emails table, sets output to standard out (closing the emails.csv file), quits the CLI and checks the output by sending it to standard out.

There are other formats you can output, type .help at the sqlite3 CLI prompt:

.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements

---

I've improved upon Stacey's answer, and thought I'll share it here. I've added headers to make the browser download the output as a CSV file.

<?
// Set headers to make the browser download the results as a csv file
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=filename.csv");
header("Pragma: no-cache");
header("Expires: 0");

// Connect to DB
$conn = new PDO('sqlite:db_name.db');

// Query
$query = $conn->query("SELECT * FROM some_table");

// Fetch the first row
$row = $query->fetch(PDO::FETCH_ASSOC);

// If no results are found, echo a message and stop
if ($row == false){
    echo "No results";
    exit;
}

// Print the titles using the first line
print_titles($row);
// Iterate over the results and print each one in a line
while ($row != false) {
    // Print the line
  echo implode(array_values($row), ",") . "\n";
    // Fetch the next line
  $row = $query->fetch(PDO::FETCH_ASSOC);
}

// Prints the column names
function print_titles($row){
    echo implode(array_keys($row), ",") . "\n";
}

---

If you need to send a CSV file directly to the browser, without writing in an external file, you can open the output and use fputcsv on it.

    <?php
    $out = fopen('php://output', 'w');
// print column header
    fputcsv($out, array_keys($row)));
//or print content directly
    fputcsv($out, array_values($row)));
    fclose($out);
    ?>