Advanced Search

Export data to CSV

/ Published in: PHP
Save to your folder(s)
The best part about this script is that any table you put in the query on line 14 will be the headers and data in the CSV. No need to hand type out all the table headers unless you want to comment that section out and put in your own. So if you have a table full of users that includes: name, email, website, phone you could create a query like:

SELECT name, email, website, phone FROM users

This would pull only those fields to the CSV if you had other data like passwords, nicknames or instant messenger addresses in the same table.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. <?php
  2. $db = mysql_connect('localhost', 'username', 'password'); // Connect to the database
  3. $link = mysql_select_db('database name', $db); // Select the database name
  4.  
  5. function parseCSVComments($comments) {
  6.   $comments = str_replace('"', '""', $comments); // First off escape all " and make them ""
  7.   if(eregi(",", $comments) or eregi("\n", $comments)) { // Check if I have any commas or new lines
  8.     return '"'.$comments.'"'; // If I have new lines or commas escape them
  9.   } else {
  10.     return $comments; // If no new lines or commas just return the value
  11.   }
  12. }
  13.  
  14. $sql = mysql_query("SELECT * FROM tableName"); // Start our query of the database
  15. $numberFields = mysql_num_fields($sql); // Find out how many fields we are fetching
  16.  
  17. if($numberFields) { // Check if we need to output anything
  18. 	for($i=0; $i<$numberFields; $i++) {
  19. 		$head[] = mysql_field_name($sql, $i); // Create the headers for each column, this is the field name in the database
  20. 	}
  21. 	$headers = join(',', $head)."\n"; // Make our first row in the CSV
  22.         $data = "";
  23. 	while($info = mysql_fetch_object($sql)) {
  24. 		foreach($head as $fieldName) { // Loop through the array of headers as we fetch the data
  25. 			$row[] = parseCSVComments($info->$fieldName);
  26. 		} // End loop
  27. 		$data .= join(',', $row)."\n"; // Create a new row of data and append it to the last row
  28. 		$row = ''; // Clear the contents of the $row variable to start a new row
  29. 	}
  30. 	// Start our output of the CSV
  31. 	header("Content-type: application/x-msdownload");
  32. 	header("Content-Disposition: attachment; filename=log.csv");
  33. 	header("Pragma: no-cache");
  34. 	header("Expires: 0");
  35. 	echo $headers.$data;
  36. } else {
  37. 	// Nothing needed to be output. Put an error message here or something.
  38. 	echo 'No data available for this CSV.';
  39. }
  40. ?>

URL: http://www.wlscripting.com/tutorial/37

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.