Solved

PHP - Export MySQL quiery to csv

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

This article discusses how to create an extensible mechanism for linked drop downs.
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.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

696 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