?
Solved

PHP - Export MySQL quiery to csv

Posted on 2007-11-21
2
Medium Priority
?
1,104 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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

WordPress Tutorial 2: Terminology

An important part of learning any new piece of software is understanding the terminology it uses. Thankfully WordPress uses fairly simple names for everything that make it easy to start using the software.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to count occurrences of each item in an array.
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.
Suggested Courses

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