Solved

PHP - Export MySQL quiery to csv

Posted on 2007-11-21
2
1,091 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

777 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