By I am learning on

Hi,

In a function I wrote:

$select_query = "SELECT * INTO OUTFILE 'mytable.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM {my_table};";
db_query($select_query);

It works fine and creates the file in my XAMPP c:\xampp\mysql\data\mywebsite

1. Is there a better way doing this in Drupal?

2. How do I specify path e.g. /sites/default/files so that it is created there.

3. How do I start a file download the moment it is ready?

Regards

⋅ Categories: Drupal 6.x

Comments

saepl’s picture

saepl commented

Here is a standalone page I created to download an excel sheet of a mysql database table. You will have to change it a bit for use in drupal but perhaps it will help you out a bit.

<?php 
header("Content-type: application/vnd.ms-excel"); 
header("Content-Disposition: attachment; filename=registrations.xls"); 
header("Pragma: no-cache"); 
header("Expires: 0"); 
//print "$header\n$data"; 

define(db_host, "localhost"); 
define(db_user, "*****"); 
define(db_pass, "*****"); 
define(db_link, mysql_connect(db_host,db_user,db_pass)); 
define(db_name, "*****"); 
mysql_select_db(db_name); 

$select = "SELECT * FROM table_name"; 
$export = mysql_query($select); 
$fields = mysql_num_fields($export); 

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

while($row = mysql_fetch_row($export)) { 
$line = ''; 
foreach($row as $value) { 
if ((!isset($value)) OR ($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"; 
} 
// print the db 
echo "$header \n $data" ; 
?>
I am learning’s picture

I am learning commented

Thanks saepl,

I too know plain PHP implementation of this and looping and adding record by record. But dumping by select * into .... is much faster then looping a recordset especially when you have bulk data (as in my case, almost a million records in a table).

I'm searching for a Drupal implementation of the same, I'm studying table_export (although a drupal 5 module, I need code for drupal 6) for the same, this is also looping the recordset.

Regards

saepl’s picture

saepl commented

maybe use the header information in my code and use the inject module to insert the header into your theme?

http://drupal.org/project/inject