I'm trying to export a table to a CSV file using php and it's working with the following code:
while($row = $results->fetchArray()) {
print '"' . stripslashes(implode('","',$row)) . "\"\n";
}
However, it creates duplicates of each field when I view the CSV file. I don't know how to limit them down to one per field.
This is because SQLite3Result::fetchArray by default fetches both a numeric and a named index for each column in the result. So $row might look like this:
array(
0 => 'Fred',
'firstname' => 'Fred',
1 => 'Jones',
'lastname' => 'Jones',
2 => '[email protected]',
'email' => '[email protected]'
)
The way around this is to specify other behaviour in the fetchArray call by passing a constant:
while ($row = $results->fetchArray(SQLITE3_ASSOC)) {
This means that only the names of the columns will be fetched. Equally, you could do SQLITE3_NUM: in your case it would make no difference.
Note also that a nicer way to output CSV data is with the fputcsv method, which handles enclosure and escaping as necessary. It requires a file handle: if you just want to echo the data, you can use the STDOUT constant:
fputcsv(STDOUT, $row);
fetchArray returns both associative and numeric indices by default. To return only numeric indices use:
while($row = $results->fetchArray( SQLITE3_NUM )) {
Or to return an associative array use:
while($row = $results->fetchArray( SQLITE3_ASSOC )) {
