Solved

PHP - Export MySQL quiery to csv

Posted on 2007-11-21
2
1,095 Views
Last Modified: 2013-12-12
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
0
Comment
Question by:Mr_Splash
2 Comments
 
LVL 4

Expert Comment

by:simonkin
ID: 20327781
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
 
LVL 20

Accepted Solution

by:
steelseth12 earned 500 total points
ID: 20327800

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question