Save to .csv file

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

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 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

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

Re: [northernpenguin] Save to .csv file

By Chris - June 24, 2010

Hi Ragi,

Are you saying that your HTML search result page has a link to download a CSV of the results you're currently looking at -- but that the CSV returns all the records?

If so, you'll need to pass the query forward from your search results page to the CSV request. Try replacing your CSV link with this:

<a href="csv.php?<?php $r = $_REQUEST; unset( $r['page'] ); echo http_build_query($r); ?>">
Download a CSV of these results
</a>


If that isn't what's happening, please provide more details on your setup: either a link or attaching your PHP source code would be helpful. :)
All the best,
Chris