This class can generate a CSV file from MySQL query results.
It can connect to a given MySQL database and execute a SQL query.
The class can generate a CSV file from the query results.
The CSV file may have headers generated from the query result column names or use custom names.
The generated CSV file may be save to a file, served for download or displayed on a Web page.
class.select2csv.php
<?php
/*
Online PHP Examples with Source Code
website: http://4evertutorials.blogspot.in/
*/
/**
* select2csv
*
* @package MySQL Select CSV Dump
* @author Mark Berube
* @license Distributed under GNU/GPL
* @version 0.1
* @access public
*/
class select2csv{
var $result;
var $result_col_headers = array();
var $qry = false;
var $conn_to_db;
var $conn_array;
var $error = false;
var $debug = false;
var $use_first_line_header_row = true;
var $csv;
var $delim;
var $output_file;
var $delivery;// 'local_file', 'passthru', 'print'
/**
* select2csv::__construct()
* @desc object constructor
* @param array $conn_array
* @param bool $conn_to_db
* @return void
*/
function __construct($conn_array=false,$conn_to_db=false){
$this->conn_to_db = $conn_to_db;
$this->conn_array = $conn_array;
}
/**
* select2csv::set_qry()
* @desc Sets the MySQL qry to be executed
* @param string $qry
* @return void
*/
function set_qry($qry){
$this->qry = $qry;
}
/**
* select2csv::set_delim()
* @desc Sets the Delimiter for the CSV output
* @param string $delim
* @return void
*/
function set_delim($delim){
$this->delim = $delim;
}
/**
* select2csv::set_custom_headers()
* @desc Sets the Delimiter for the CSV output
* @param array $headers_arr
* @return void
*/
function set_custom_headers($headers_arr){
$this->result_col_headers = $headers_arr;
}
/**
* select2csv::first_line_headers()
* @desc Disables creating the first line headers row
* @return void
*/
function remove_header_line(){
$this->use_first_line_header_row = false;
}
/**
* select2csv::debug()
* @desc If function is called with no args it will echo errors if something is missing. Default is off
* @param bool $val
* @return void
*/
function debug($val=true){
$this->debug = $val;
}
/**
* select2csv::execute()
* @desc execute query object after all parameters set
* @param string $action
* @return array if action is select else true or false
*/
function execute($action='s'){
if($this->conn_to_db){
$conn_array = $this->conn_array;
$conn = mysql_connect($conn_array['host'], $conn_array['user'], $conn_array['pw'])or die("Unable to connect to MYSQL because: ".mysql_error());
$db_select = mysql_select_db($conn_array['db'],$conn) or die("Could not select '".$conn_array['db']."' database because: ".mysql_error());
}
//select will return array, either empty or populated
$this->result_val_type = 'array';
$this->result = $this->db_qry_get_rows();
if(empty($this->result_col_headers)){
$this->result_col_headers = $this->db_qry_get_headers();
}
$this->build_csv();
if($this->conn_to_db){
mysql_close($conn);
}
if($this->debug){
if($this->error){
echo $this->error;
return false;
}
}
}
function build_csv(){
//$result = $this->result;
$NumFields = count($this->result_col_headers);
$header_row = "";
$data_rows = "";
if($this->use_first_line_header_row){
///////////////////////
//build the headers from array keys
///////////////////////
$col = 1;
foreach ($this->result_col_headers as $header){
$header_row .= $header;
$header_row .= ($col < $NumFields) ? $this->delim : "\r\n";
$col++;
}
}
///////////////////////
//Now Get the data rows
///////////////////////
foreach($this->result as $row){
//print_r($row);
$col = 1;//reset
foreach ($row as $data){
$data_rows .= trim($data);
$data_rows .= ($col < $NumFields) ? $this->delim : "\r\n";
$col++;
}
}
$this->csv = $header_row . $data_rows;
unset($header_row,$data_rows);
}
/**
* select2csv::db_qry_get_rows()
* @desc Assembles 'select' qry results to a 2-dim assoc array. This function is called with the 's' action
* @return array if executed without error else false
*/
function db_qry_get_rows(){
$rows = array();
if($this->qry == false){
$this->error = "You must set a query (using set_qry()) before executing a 'select'";
return false;
}
$res = mysql_query($this->qry);
if($res){
$cnt = 0;
while($row = mysql_fetch_assoc($res)){
foreach ($row as $key => $val){
$rows[$cnt][$key] = $val;
}
$cnt++;
}
mysql_free_result($res);
}
return $rows;
}
/**
* select2csv::db_qry_get_headers()
* @desc Grabs this first record result and extracts the array keys for use as column headers...just for ease of access
* @return array
*/
function db_qry_get_headers(){
$headers = array();
if(is_array($this->result[0])){
if(empty($this->result[0])){
return $headers;
}else{
foreach ($this->result[0] as $key => $value) {
$headers[] = $key;
}
}
return $headers;
}
return false;
}
/**
* select2csv::output()
* @desc Delivers csv content in one of three ways: creation of local file, print to the browser window, or passthru for download
* @param string $delivery
* @param string $fname
* @param string $local_path
* @return void
*/
function output($delivery, $fname='', $local_path='.'){
switch($delivery){
case 'local_file':
file_put_contents($local_path.'/'.$fname,$this->csv);
break;
case 'passthru':
$tmp = 'Select2CSV_Dump.csv';
file_put_contents($tmp,$this->csv);
$file_size = filesize($tmp);
if ($fp = fopen($tmp, 'rb')) {
Header('Content-Disposition: attachment; filename="'.$fname.'"');
Header('Content-Length: '.$file_size);
fpassthru($fp);
} else {
echo "cannot find tmp file: check that the script has write permission to the containing folder";
}
break;
case 'print':
echo ''."\n".$this->csv.'
';
break;
}
}
}
//////////////////
// Example usage
//////////////////
ini_set('display_errors','On');
$db_creds = array(
'db' => "db_name",
'user' => "myusername",
'pw' => "mypassword",
'host' => "localhost"
);
$csv = new select2csv($db_creds,true);
$csv->set_qry("SELECT fname,lname,email,role FROM users");
//$csv->remove_header_line();//disables using a column header row at all
$csv->set_custom_headers(array('First','Last','EMail','Role'));//default is to use the field names from query as column headers
$csv->set_delim('|');
$csv->execute();
///////////////////
// 3 methods of delivery show below
///////////////////
//$csv->output('passthru','csvtest.csv');
$csv->output('print');
//$csv->output('local_file','csvtest.csv','./tmp');
?>
0 comments:
Post a Comment