[SOLVED] PHP, Export SQL Table to CSV or XLS

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Michael.Santangelo
Forum Newbie
Posts: 7
Joined: Fri Jul 28, 2006 1:21 pm

[SOLVED] PHP, Export SQL Table to CSV or XLS

Post by Michael.Santangelo »

Hello all,

I'm working on a PHP page to export some SQL data in a date range to a XLS or CSV file... However, I get

Warning: Cannot modify header information - headers already sent by (output started at C:\Server\Apache2\htdocs\csstat\admin\xlsexport.php:11) in C:\Server\Apache2\htdocs\csstat\admin\xlsexport.php on line 81

Warning: Cannot modify header information - headers already sent by (output started at C:\Server\Apache2\htdocs\csstat\admin\xlsexport.php:11) in C:\Server\Apache2\htdocs\csstat\admin\xlsexport.php on line 82

Warning: Cannot modify header information - headers already sent by (output started at C:\Server\Apache2\htdocs\csstat\admin\xlsexport.php:11) in C:\Server\Apache2\htdocs\csstat\admin\xlsexport.php on line 83

Warning: Cannot modify header information - headers already sent by (output started at C:\Server\Apache2\htdocs\csstat\admin\xlsexport.php:11) in C:\Server\Apache2\htdocs\csstat\admin\xlsexport.php on line 84
followed by the output of the SQL table...

I used a PHP export script I found which DOES work, --- http://169hamilton.kicks-ass.net:8088/c ... export.php

and
MY specific page is

Code: Select all

<!DOCTYPE html 
     PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>LCC Customer Service Statistics - XLS Exporter</title>
<!--<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/>-->
</head>

<body>
<?php
	if (empty($_POST))
	{	?>
		<div class="main">
			<form action="<?= $_SERVER['PHP_SELF'] ?>" method="post">
				<p><strong>Export Range Start:</strong><input type="text" name="exprs" size="20" maxlength="10" /></p>
				<p><strong>Export Range&nbsp;End:</strong><input type="text" name="expre" size="20" maxlength="10" /></p>
				<p>You <strong>MUST</strong> enter dates as yyyy/mm/dd or it will not work!</p>
				<input type="submit" value="Submit" /> <input type="reset" value="Reset" /></p>
			</form>
			<!-----------Bottom Of Page----------->
			<hr>
			<p>Last Updated: <?php print date("F d, Y.  g:i a", getlastmod()); ?>, msantang<br></p>
		</div>
	<?php 
	}
	else {
		$numPost = count($_POST);
		if ($numPost != 1) {
			if(isset($_POST['exprs']) && isset($_POST['expre'])) {
				//EDIT YOUR MySQL Connection Info:
				$DB_Server = "localhost";		//your MySQL Server 
				$DB_Username = "root";				 //your MySQL User Name 
				$DB_Password = "ncc1701D";				//your MySQL Password 
				$DB_DBName = "csstat";				//your MySQL Database Name 
				$DB_TBLName = "data_02";				//your MySQL Table Name 
				//$DB_TBLName,  $DB_DBName, may also be commented out & passed to the browser
				//as parameters in a query string, so that this code may be easily reused for
				//any MySQL table or any MySQL database on your server

				//DEFINE SQL QUERY:
				//you can use just about ANY kind of select statement you want - 
				//edit this to suit your needs!
				$sql = "Select * from $DB_TBLName";

				//Optional: print out title to top of Excel or Word file with Timestamp
				//for when file was generated:
				//set $Use_Titel = 1 to generate title, 0 not to use title
				$Use_Title = 1;
				//define date for title: EDIT this to create the time-format you need
				$now_date = date('m-d-Y H:i');
				//define title for .doc or .xls file: EDIT this if you want
				$title = "Dump For Table $DB_TBLName from Database $DB_DBName on $now_date";
				/*

				Leave the connection info below as it is:
				just edit the above.

				(Editing of code past this point recommended only for advanced users.)
				*/
				//create MySQL connection
				$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
				//select database
				$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());
				//execute query
				$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());

				//if this parameter is included ($w=1), file returned will be in word format ('.doc')
				//if parameter is not included, file returned will be in excel format ('.xls')
				if (isset($w) && ($w==1))
				{
					$file_type = "msword";
					$file_ending = "doc";
				}
				else {
					$file_type = "vnd.ms-excel";
					$file_ending = "xls";
				}
				
				//header info for browser: determines file type ('.doc' or '.xls')
				header("Content-Type: application/$file_type");
				header("Content-Disposition: attachment; filename=database_dump.$file_ending");
				header("Pragma: no-cache");
				header("Expires: 0");
				
				/*	Start of Formatting for Word or Excel	*/
				
				if (isset($w) && ($w==1)) //check for $w again
				{
					/*	FORMATTING FOR WORD DOCUMENTS ('.doc')   */
					//create title with timestamp:
					if ($Use_Title == 1)
					{
						echo("$title\n\n");
					}
					//define separator (defines columns in excel & tabs in word)
					$sep = "\n"; //new line character

					while($row = mysql_fetch_row($result))
					{
						//set_time_limit(60); // HaRa
						$schema_insert = "";
						for($j=0; $j<mysql_num_fields($result);$j++)
						{
							//define field names
							$field_name = mysql_field_name($result,$j);
							//will show name of fields
							$schema_insert .= "$field_name:\t";
							if(!isset($row[$j])) {
								$schema_insert .= "NULL".$sep;
							}
							elseif ($row[$j] != "") {
								$schema_insert .= "$row[$j]".$sep;
							}
							else {
								$schema_insert .= "".$sep;
							}
						}
						$schema_insert = str_replace($sep."$", "", $schema_insert);
						$schema_insert .= "\t";
						print(trim($schema_insert));
						//end of each mysql row
						//creates line to separate data from each MySQL table row
						print "\n----------------------------------------------------\n";
					}
				}
				else{
					/*	FORMATTING FOR EXCEL DOCUMENTS ('.xls')   */
					//create title with timestamp:
					if ($Use_Title == 1)
					{
						echo("$title\n");
					}
					//define separator (defines columns in excel & tabs in word)
					$sep = "\t"; //tabbed character
				
					//start of printing column names as names of MySQL fields
					for ($i = 0; $i < mysql_num_fields($result); $i++)
					{
						echo mysql_field_name($result,$i) . "\t";
					}
					print("\n");
					//end of printing column names
				
					//start while loop to get data
					while($row = mysql_fetch_row($result))
					{
						//set_time_limit(60); // HaRa
						$schema_insert = "";
						for($j=0; $j<mysql_num_fields($result);$j++)
						{
							if(!isset($row[$j]))
								$schema_insert .= "NULL".$sep;
							elseif ($row[$j] != "")
								$schema_insert .= "$row[$j]".$sep;
							else
								$schema_insert .= "".$sep;
						}
						$schema_insert = str_replace($sep."$", "", $schema_insert);
						//following fix suggested by Josue (thanks, Josue!)
						//this corrects output in excel when table fields contain \n or \r
						//these two characters are now replaced with a space
						$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
						$schema_insert .= "\t";
						print(trim($schema_insert));
						print "\n";
					}
				}
			}
		}
	}
?>
</body>
</html>
which is available http://169hamilton.kicks-ass.net:8088/c ... export.php

I'm really at a loss. I basically copied the export.php and plugged it into xlsexport.php. xlsexport.php gives an error but export.php does not...

Any ideas?
Last edited by Michael.Santangelo on Wed Aug 02, 2006 6:22 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Wow, that is a lot of code...

Header sent errors come from one of a few things. 1) Trying to set a cookie after output is sent to the browser, 2) calling the header()function after output is sent to the browser (without using output buffering), and 3) calling session_start() after sending output to the browser.

These are the more common causes of this error. A search of the error message would probably reveal that to you as well.
Michael.Santangelo
Forum Newbie
Posts: 7
Joined: Fri Jul 28, 2006 1:21 pm

Post by Michael.Santangelo »

OK so I see that the problem is because I was doing output at some point before doing the headers. I followed the tips at viewtopic.php?t=1157 and rewrote some code...

Code: Select all

<html>
<body>
<?php
  if (isset($_POST['exprs']) && isset($_POST['expre'])) {
  // Header info for browser: Determines file type (xls here).
  header("Content-Type: application/vnd.ms-excel");
  header("Content-Disposition: attachment; filename=database_dump.xls");
  header("Pragma: no-cache");
  header ("Expires: 0");

  // Rest of page code here
And I'm still getting the error... It's beginning to get VERY frustrating as there is NO output before the lines that set the header...

Any ideas?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Code: Select all

<html> 
<body> 
<?php 
...
?>
See the output HTML? That is what is killing it.
Michael.Santangelo
Forum Newbie
Posts: 7
Joined: Fri Jul 28, 2006 1:21 pm

Post by Michael.Santangelo »

Everah, you are my savior. I never woulda thought to take out the <html> tag... :oops:

This is my first PHP project greater than a simple "Hello World" or email form... I'm so used to needing a <html> tag at the beginning...

Thanks a ton! :)
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You got it bud. Glad I could help.
Post Reply