<?php /* mySQL2CSV functions - updated 2nd oct 2009:::::::

These functions were patched together by Ciaran O'Kelly at blog.crondesign.com. 
There may be bugs/problems as these functions were not heavily tested.
No copyright restrictions whatsoever - Use them however you see fit!
Please let me know ([email protected]) if you improve on this stuff! 

//////////////////////////EXPORT CSV::::::::::::::::::::::::::
// EXPLANATION OF PARAMETERS WITH SAMPLE VALUES:

//A standard mqSQL query that returns the data to be saved (REQUIRED)
$values_query="SELECT addinID,'-1' AS instanceID, name, friendlyname, description, type, value, longvalue, dependantname, dependantvalue, editable, required, orderindex, defaultvalue FROM addins WHERE addinID='1';"; 

//Include column names in first row of CSV? If so, specify lookup query (good for excluding some fields from export)
$col_names_query="SHOW COLUMNS FROM addins WHERE Field!='id' AND Field!='authorID' AND Field!='publishdate';";

//How many columns to include in CSV?(REQUIRED if $col_names_query=0)
$col_count=14; 

//Use ',' for Excel but ';' is generally safer
$fieldseparator=';';

//should the file be downloaded from browser or saved to the server?
$download_file=0; 

//contains name of download file or (depending on $download_file) the path to save csv file without .csv file extension.
$filename = "settings";
*/
function exportCSV($values_query, $col_names_query=0, $col_count=1, $fieldseparator=';', $filename='exportedCSV', $download_file=0){
	$lineseparator = "\n";
	
	//GET COLUMNS::::::
	if($col_names_query){
		$result = mysql_query($col_names_query) or die($col_names_query."<br />".mysql_error());
		$i = 0;
		if (mysql_num_rows($result) > 0) {
			while ($row = mysql_fetch_assoc($result)) {
				$csv_output .= $row['Field'].$fieldseparator." ";
				$i++;
			}
		}
		$csv_output .= "\n";
	}else{
		$i=$col_count;
	}
	
	//GET VALUES:::::::::::::::
	$values = mysql_query($values_query) or die($values_query."<br />".mysql_error());
	while ($rowr = mysql_fetch_row($values)) {
		for ($j=0;$j<$i;$j++) {
			$csv_output .= $rowr[$j].$fieldseparator." ";
		}
		//$csv_output=trim($csv_output,$fieldseparator." "); //trim trailing ; from end of line (disrupts import functions
		$csv_output .= $lineseparator;
	}
	
	
	//OUTPUT RESULTS::::::::::::::
	if($download_file){ //DOWNLOAD:
		header("Content-type: application/vnd.ms-excel");
		header("Content-disposition: csv" . date("Y-m-d") . ".csv");
		header( "Content-disposition: filename=".$filename.".csv");
		echo $csv_output;
	}else{//SAVE TO SERVER:
		$fh = fopen($filename.'.csv', 'w') or die("can't open file");
		fwrite($fh, $csv_output);
		fclose($fh);
		if(file_exists($filename.'.csv')){return (1);}else{return(0);}
	}

}



/*////////////////////////IMPORT CSV:::::::::::::::::::::::::
//EXPLANATION OF PARAMETERS WITH SAMPLE VALUES:

//existing table to import the CSV data to (REQUIRED)
$import_to_table = "news"; 

//Use ',' for Excel but ';' is generally safer
$fieldseparator=';';

//Name of the file on the server to import (use $_FILES["uploadfile"]["tmp_name"] for uploaded file) (REQUIRED)
$importfilename = "newnews.csv";

// 1 if the first row of your CSV contains column names. 0 if it does not. 
$has_field_names = 1;

//Merge existing rows with duplicates?	(CSV file must contain at least one index for this to work)				
$merge_on_duplicate = 1; 

//offset the imported values if first column in sql table is an auto increment column (auto ID number). Should only be used with $has_field_names = 0
$skip_first_col = 0;

//should the csv file be removed from the server after import?
$delete_after_import=1;

//save all generated queries to a file after import? to what filename?
$outputfile="output.sql"; 

*/

function importCSV($importfilename, $fieldseparator=';', $import_to_table, $has_field_names=0, $merge_on_duplicate=1, $skip_first_col=1, $delete_after_import=1, $outputfile=0){
	$lineseparator = "\n";
	if(!file_exists($importfilename)) {
		echo "ERROR: File not found. Make sure you specified the correct path.\n";
		exit;
	}
	$file = fopen($importfilename,"r");
	if(!$file) {
		echo "ERROR: opening data file.\n";
		exit;
	}
	$size = filesize($importfilename);
	if(!$size) {
		echo "ERROR: File is empty.\n";
		exit;
	}
	$csvcontent = fread($file,$size);
	fclose($file);
	$csvcontent=trim($csvcontent,$lineseparator);
	
	$lines = 0;
	$queries = "";
	$linearray = array();
	$fieldnames= "";
	
	foreach(split($lineseparator,$csvcontent) as $line) {
		$lines++;
		$line = trim($line," \t");
		$line = rtrim($line,$fieldseparator);
		$line = str_replace("\r","",$line);
		$line = str_replace("'","\'",$line); //escapes the special character. remove it if entries are already escaped in the csv file
		$linearray = explode($fieldseparator,$line);
		array_walk($linearray, 'trim_value'); //trims the array
		$linemysql=substr(implode("','",$linearray),0,-3);		
		
		if($has_field_names && $lines==1){//1ST ROW: 
			$fieldnames="(`".substr(implode("`,`",$linearray),0,-2).")";
			if($merge_on_duplicate){ //get index of distinct column name
				$fieldnamesarray=$linearray; //save column names for all future queries
			}
		}else{//ALL OTHER ROWS:
			if($merge_on_duplicate){
				if(!$fieldnamesarray){ //if field names are not in CSV, get them from table:
					$fieldnamesarray=mysql_fetch_array(mysql_query("SHOW COLUMNS FROM $import_to_table;"));
				}
				$v="";
				foreach($linearray as $index => $val){
					if($fieldnamesarray[$index]){ 
						if($skip_first_col && $index==0){}//If not skipping the first column:
						else{$v.="`".trim($fieldnamesarray[$index])."`='".$val."',";} //add this field=value pair to the $updatequery statement
					}
				}
				$v=trim($v,',');
				$updatequery= "ON duplicate KEY UPDATE $v";
			}else{
				$updatequery="";
			}
			if($skip_first_col){
				$query= "INSERT INTO $import_to_table $fieldnames VALUES('','$linemysql') $updatequery;";
			}else{
				$query = "INSERT INTO $import_to_table $fieldnames VALUES('$linemysql') $updatequery;";
			}
			$queries .= $query . "\n";
			mysql_query($query) or die($query.'<br />'.mysql_error());
		}		
	}
	//echo "<br /><br />",$queries; //for testing
	
	if($outputfile){ //save queries to a file:
		if(!is_writable($outputfile)) {
			echo "File is not writable, check permissions.\n";
		}else{
			$file2 = fopen($outputfile,"w");
			if(!$file2) {
				echo "Error writing to the output file.\n";
			}else{
				fwrite($file2,$queries);
				fclose($file2);
			}
		}
	}
	if($delete_after_import){@unlink($importfilename);}
	return (1);
}
//internal function:
function trim_value(&$value){ 
    $value = trim($value); 
}




?>