Save to .csv file

20 posts by 4 authors in: Forums > CMS Builder
Last Post: November 30, 2010   (RSS)

By eduran582 - September 7, 2009

Dave, et al,

My client wants to have the ability to save the mySQL database data to a MS .csv file format (for import to an Excel worksheet). I found the following script (and reworked it a little) that works fine. It even appends the date and time to the file name (very cool). Problem: I'm not sue how to specify what fields to save (as opposed to all fields). They only want a few of the fields to review. Any suggestions? [unsure]

<?
$host = '<your_host>';
$user = '<your_user>';
$pass = '<password>';
$db = '<db_name>';
$table = '<table_name>';
$file = 'output_file_name';

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field'].",";
$i++;
}
}
$csv_output .= "\n";
//$values = mysql_query("SELECT * FROM ".$table."");
$values = mysql_query("SELECT * FROM $table WHERE paid = '1'");
while ($rowr = mysql_fetch_row($values)) {

for ($j=0;$j<$i-1;$j++) {

$csv_output .=$rowr[$j].",";
}
$csv_output .=$rowr[$j].",";
$csv_output .= "\n";
}

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
?>

Thanks!

Eric

Re: [eduran582] Save to .csv file

By Chris - September 8, 2009 - edited: September 9, 2009

Hi Eric,

The script you found doesn't seem to encode special characters (if one of your fields has a comma in it, you're in big trouble!) I think it may also have another bug where it doubles up the last field?

Here's a script I made which uses CMS Builder's getRecords() function instead of doing raw SQL:

<?php
require_once "C:/wamp/www/cmsbuilder_1_34_build1/cmsAdmin/lib/viewer_functions.php";

// load records
list($records,) = getRecords(array(
'tableName' => 'blog',
'where' => "paid = '1'"
));

// specify fields to output
$fields = array('title', 'category', 'content');

$filename = "blog_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");

// output csv header row
print(join(',', $fields) . "\n");

// loop over records, outputting a row for each
foreach ($records as $record) {
$row = array();
foreach ($fields as $field) {
$value = $record[$field];
// if this value contains a special character, quote and escape it
if ( preg_match('/[," \t\n]/', $value) ) {
$value = '"' . preg_replace('/"/', '""', $value) . '"';
}
array_push($row, $value);
}
echo(join(',', $row) . "\n");
}

?>


You'd need to replace the parts in red with your own information and you should be good to go! Hope this helps!
All the best,
Chris

Re: [chris] Save to .csv file

By eduran582 - September 8, 2009

Chris,

Thanks for the excellent script! And thanks for pointing out my error in forgetting about 'special characters'. I tried it out and it works great (and quick too)!

Keep up the great work! [;)]

Eric

Re: [chris] Save to .csv file

By Kenny - September 9, 2009

How would you go about using a search form with this?

My guess is to use the search form to display the results on the page. Then click a link to export those results to csv.

Just not sure how to go about it.

Re: [sagentic] Save to .csv file

By Chris - September 9, 2009 - edited: September 12, 2009

Hi sagentic,

Since this code is using getRecords() to load the records, any search functionality that works with a regular list viewer page will work with this one.

In fact, you could use one PHP file to produce both your HTML-formatted results and CSV results. The following example would only work with a "get" request, so if your search form has method="post", you'll want to change that.

<?php
require_once "C:/wamp/www/cmsbuilder_1_34_build1/cmsAdmin/lib/viewer_functions.php";

// load records
list($blogRecords,) = getRecords(array(
'tableName' => 'blog',
'where' => "paid = '1'"
));

// if the user has supplied "as_csv" in query string
if (@$_REQUEST['as_csv']) {

// specify fields to output
$fields = array('title', 'category', 'content');

$filename = "blog_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");

// output csv header row
print(join(',', $fields) . "\n");

// loop over records, outputting a row for each
foreach ($blogRecords as $record) {
$row = array();
foreach ($fields as $field) {
$value = $record[$field];
// if this value contains a special character, quote and escape it
if ( preg_match('/[," \t\n]/', $value) ) {
$value = '"' . preg_replace('/"/', '""', $value) . '"';
}
array_push($row, $value);
}
echo(join(',', $row) . "\n");
}

// exit -- we are finished with the page
exit;
}

?>

... (a regular list page) ...

<a href="?as_csv=1&<?php echo @$_SERVER['QUERY_STRING'] ?>">Download these results in CSV format</a>

All the best,
Chris

Re: [chris] Save to .csv file

By Kenny - September 11, 2009

Ok - almost there.

I am getting an error that I'm not sure how to work out:

Notice: Undefined index: as_csv in /home/csoccer/public_html/schedules/local2.php on line 9
Notice: Undefined variable: local_schedulesRecords in /home/csoccer/public_html/schedules/local2.php on line 76 Warning: Invalid argument supplied for foreach() in /home/csoccer/public_html/schedules/local2.php on line 76


I have attached my search file and list page.


Kenny
Attachments:

search_001.php 2K

local2.php 5K

Re: [sagentic] Save to .csv file

By Chris - September 12, 2009 - edited: September 12, 2009

Hi Kenny,

Oops! I fixed my above post to correct the error you got (added the two @ characters.) I also marked off the places where you'll need to customize things in red. You ran into the second bug because we used different names for our record variables. I had originally called mine $records, which was easy to miss. It's now red and called $blogRecords, which you'll want to change to $local_schedulesRecords.

Hope this helps! :D
All the best,
Chris

Re: [chris] Save to .csv file

By Kenny - September 14, 2009

Forgot to tell you that this worked great! Thanks!

Kenny

Re: [chris] Save to .csv file

By northernpenguin - June 24, 2010

Chris: This is a very useful code which works really well for what I want to do. Unfortunately, I am having one problem. The way I have it setup, the user runs the search, and the search result is displayed in a separate file (php/html). When I use your code it works, but it sends all the records to the csv, not just the output of the search.

How can I narrow the output to just the output of the search?

Thanx........... Ragi
--
northernpenguin
Northern Penguin Technologies

"Any sufficiently advanced technology
is indistinguishable from magic."
........Arthur C. Clarke