Solved

PHP - Export MySQL quiery to csv

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now