Export sqlite table using php

Question

Hi,

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 way that would be great. Cheers

Answer 1

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

Answer 2

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.