Solved

PHP - Export MySQL quiery to csv

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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 …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

911 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

25 Experts available now in Live!

Get 1:1 Help Now