PHP - Export MySQL quiery to csv

I'm looking for a php function that will use a MySQL query, produce a CSV file and present it as a download.

Would anybody be able to supply me with such a function?

Thank you
LVL 3
Mr_SplashAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
steelseth12Connect With a Mentor Commented:

header('Content-type: text/csv');
 
header('Content-Disposition: attachment; filename="file.csv"');
 
 
$file = sql2csv("SELECT * FROM categories");
 
print $file;
 
function sql2csv($query) {
 
	$q = mysql_query($query);
	
		$str = "";
		
	while($row = mysql_fetch_row($q)) {
 
		for($i=0;$i<count($row);$i++) {
		 
			$str .= $row[$i].",";
		
		
		}
			$str = substr($str,0,strlen($str)-1);
			$str .= "\r\n";
 
	}
	
	return $str;
 
}

Open in new window

0
 
simonkinCommented:
Hi,

Try this...

Just set the query to your own records

Working example:


<?php
 
	// Define the lines array
	$lines = array();
	
	// Define the csv filename - Set this to the name of the file you want to output.
	$csvfile 	= "filename.csv";
 
	// Write the query
	$query = "SELECT * FROM table";
	$result = mysql_query($query);
	
	// Count the number of rows
	$numRows = mysql_num_rows($result);
	
	// Build an array to hold the rows from the query
	$records = array();
	
	// Loop through the results, populate the array
	while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
	
		$id = $row["id"]; // If "id" is the name of an key field in your db, otherwise substitute this...
		$records[$id]["id"] = $row["id"];
		$records[$id]["field1"] = $row["field1"]; // Substitute field name with your field and copy as required...
		$records[$id]["field2"] = $row["field2"]; // Substitute field name with your field and copy as required...
		$records[$id]["field3"] = $row["field3"]; // Substitute field name with your field and copy as required...
		$records[$id]["field4"] = $row["field4"]; // Substitute field name with your field and copy as required...
		$records[$id]["field5"] = $row["field5"]; // Substitute field name with your field and copy as required...
	
	}
		
	// Add a header row, set this to the amount of fields to you have, there must be a comma , delimiter between each field
	$lines[] = "Field1,Field2,Field3,Field4,Field5";
	
	// Set the loop
	$i = 1;
	
	// Iterate through the rows
	while ($i <= $numRows) {
 
	// Loop through the array, and build the line
	foreach ($records as $record) {
 
		// Copy the following structure for the amount of fields required
		$line = $record["field1"];	// Start off the line
		$line = $line . "," . $record["field2"]; // Append the line with each field, adding a comma delimiter
		$line = $line . "," . $record["field3"]; // Append the line with each field, adding a comma delimiter
		$line = $line . "," . $record["field4"]; // Append the line with each field, adding a comma delimiter
		$line = $line . "," . $record["field5"]; // Append the line with each field, adding a comma delimiter
		
		// Add this line to the lines array
		$lines[] = $line;
		
		// Increment the loop
		$i++;
	
	}
 
	// Write the csv file   
	if (!$handle = fopen($csvfile, 'w')) {
		print "Cannot open file ($csvfile)";
		exit;
	}
	
	// Iterate through the lines array
	foreach ($lines as $l) {
	
		if (!fwrite($handle, ($l . "\n"))) {
			print "Cannot write to file ($csvfile)";
			exit;
		}
	}            
	
	// Close the csv file !important!
	fclose($handle);
 
 
?>

Open in new window

0
All Courses

From novice to tech pro — start learning today.