a program that creates a CSV file

[glens1234]

Hi.

 

Basically, i have a script which gets all people from a database who speak a certain language(s).

 

It then prints the results to the screen. So it is a very standard php/mysql script!

 

However, i want the results as a CSV file! Is this possible?

 

For example, i believe you can slap a statement in to phpMyAdmin and download a CSV file containing the results.

 

Im very new to php. Is this sort of thing do-able or rocket science? 

 

cheers!

 

 

[discomatt]

http://www.google.ca/search?q=php+mysql+to+csv

[glens1234]

thanks for the quick reply!

 

Ok...

 

i had a scout around and looked at a few examples and now have a revised (frankenstein) script.

It almost works!!!

Now when the i select a language from the dropdown box the csv file opens/downloads.

However, for some reason the csv file contains the current (unstyled) html page.

 

Then when i scroll down the page i see the comma deliminated results that i want! whey!

 

So any ideas why it is displaying the entire html page as opposed to just the comma deliminated fields?

 

Here is the code

<?php
$lang_imploded = implode(",", $lang_spoken);
$query1 = "SELECT name, languages_spoken FROM jos_chronoforms_1 WHERE  languages_spoken LIKE '$lang_imploded%'";
$out = ''; 

    $database->setQuery($query1);
    $rows = $database->loadObjectList();
    foreach ( $rows as $row ) {
    $out .= ''.$row->name.',';
$out .= ''.$row->languages_spoken.',';
}

// Open file export.csv.
$f = fopen ('export.csv','w');

// Put all values from $out to export.csv.
fputs($f, $out);
fclose($f);

header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="export.csv"');
readfile('export.csv');	

?>

 

 

Thanks!

 

[JustinK101]

I found this script and modified it to be a nice neat function, let me know if it works for you:

 

function sql2csv($sql, $filename) {
	$export = mysql_query($sql) or die(mysql_error());
	$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)) || ($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 = "\nNo Records...\n";
	}

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

 

 

[glens1234]

Yessss!

ive managed to copy and paste my way out!

 

These guys have made a cool function which does it all for you!

 

http://www.bin-co.com/php/scripts/csv_import_export/

 

Thanks all

 

 

 

 

 

 

[JustinK101]

oK, for me it simply prints the text to the page, doesnt prompt me to download the csv, any ideas?

[JustinK101]

The problem seems to be IE7, it simply displays the text, does not prompt to download. Works fine in Mozilla, is there a fix, workaround to get IE7 to work?

[glens1234]

i left them a comment about that. im using firefox anyway so im cool!

 

[JustinK101]

Here is fixed version, put column headers and doesnt put a space before each record and doesnt double quote each record. Still has the IE 7 bug though, anybody know how to fix it?

 

function sql2csv($sql, $filename) {
	$result = mysql_query($sql) or die(mysql_error());

	header("Content-type:text/octect-stream");
	header("Content-Disposition:attachment;filename=" . $filename);

	for ($i = 0; $i < mysql_num_fields($result); $i++) {
	   $fields[$i] = mysql_field_name($result, $i);
	}

	print stripslashes(implode(',', $fields)) . "\n";

	while($row = mysql_fetch_row($result)) {
		print stripslashes(implode(',', $row)) . "\n";
	}
	exit;
}

[JustinK101]

FINAL VERSION. TESTED AND WORKDS IN FIREFOX 2, IE 7, AND SAFARI.

 

function sql2csv($sql, $filename) {
	$result = mysql_query($sql) or die(mysql_error());

	session_cache_limiter('public');
	header("Content-type:text/octect-stream");
	header("Content-Disposition:attachment;filename=" . $filename);
	header("Pragma: no-cache");
	header("Expires: 0");

	for ($i = 0; $i < mysql_num_fields($result); $i++) {
	   $fields[$i] = mysql_field_name($result, $i);
	}

	print stripslashes(implode(',', $fields)) . "\n";

	while($row = mysql_fetch_row($result)) {
		print stripslashes(implode(',', $row)) . "\n";
	}
	exit;
}