Extract from MySQL to XLS (code included)

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

User avatar
$var
Forum Contributor
Posts: 317
Joined: Thu Aug 18, 2005 8:30 pm
Location: Toronto

Extract from MySQL to XLS (code included)

Post by $var »

good moring phpdn!

i have a question this morning about extracting from a MySQL database, and exporting it to an .xls file.
i have the database all set up, and the form is putting all the info in the right fields and such and such...
but with my code, there is a no go.

i have never even worked with excel, let alone tried to extract data for it... i was wondering if anyone had any experience with it, or at the very least, how to extract to a text file... as a last resort.

the code below is in xls.inc.php, and is below the INSERT statement, nested in the if('bpress'='add') tag.
let's see if it's in the include, or if it is probably the way i have it on the page.
here's what i got:

Code: Select all

<? $select = "SELECT * FROM surveys WHERE Mem_ID =".$_COOKIE["ID02"];                
		$export = mysql_query($select);
		$fields = mysql_num_fields($export);
			for ($i = 0; $i < $fields; $i++) {
		    $header .= mysql_field_name($export, $i) . "\t";
			while($row = mysql_fetch_row($export)) {
				$line = '';
				foreach($row as $value) {                                            
					if ((!isset($value)) OR ($value == "")) {
						$value = "\t";
					} else {
						$value = str_replace('"', '""', $value);
						$value = '"' . $value . '"' . "\t";
					}
					$line .= $value;
				}
				$data .= trim($line)."\n";
			}
			$data = str_replace("\r","",$data); 
			
			if ($data == "") {
		    $data = "\n(0) Records Found!\n";                        
			} 
			header("Content-type: application/x-msdownload");
			header("Content-Disposition: attachment; filename=extract.xls");		
			header("Pragma: no-cache");
			header("Expires: 0");
			print "$header\n$data"; 
?>

Good Morning!
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

What problem exactly are you experiencing :?:
User avatar
$var
Forum Contributor
Posts: 317
Joined: Thu Aug 18, 2005 8:30 pm
Location: Toronto

Post by $var »

ah, touche....

Parse error: parse error in xls.php on line 30
Line 30 is the ?> close bracket...

So, I don't know what that means... it usually means something is out of whack in the code above...
That doesn't help much hey?

I'll fiddle some more and see what comes up...
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

OK, you are missing closing brace for the for loop
User avatar
$var
Forum Contributor
Posts: 317
Joined: Thu Aug 18, 2005 8:30 pm
Location: Toronto

Post by $var »

where?

i think that i see and can match up all the close brackets...
which line is it to go after, and which loop is it closing?
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post by Skara »

If you can't find an unmatched bracket, take out the mess:

Code: Select all

for { // <= here
            while {
                foreach {                                            
                    if {}
                    else {}
                }
            }
            if {}
Charles256
DevNet Resident
Posts: 1375
Joined: Fri Sep 16, 2005 9:06 pm

Post by Charles256 »

heh.he got ya, you didn't close the for loop in order of precedence.but now exactly where you meant to close things you'll have to decide.i didn't look that close;) but a small tip, if you ever get an error on the line after your code ends or the line of,you probably didn't close a bracket somewhere.
User avatar
$var
Forum Contributor
Posts: 317
Joined: Thu Aug 18, 2005 8:30 pm
Location: Toronto

Post by $var »

Thank you Charles256 and Skara!

Those are both excellent trouble shooting tips.
And once the bracket was in place, it passed (of course it would!)

Thanks again!

And the file extracts fine to my computer as well, which is cool...
The only problem that I see here is the way that the data is output....

All of the info, both the field names, and the answers are all dumped into the
A1 cell of the spreadsheet... It's not really readable.

Code: Select all

Survey_ID Mem_ID Mem_Name Mem_FName Mem_LName Mem_Title Mem_URL Mem_Email Mem_City Mem_Country Mem_Postal Survey_1 Survey_2 Survey_3 Survey_3_2 Survey_4 Survey_4_2 Survey_5 Survey_5_2 Survey_6_1 Survey_6_2 Survey_6_3 Survey_6_4 Survey_6_5 Survey_6_6 Survey_6_7 Survey_6_8 Survey_6_9 Survey_6_10 Survey_7 Survey_8 "1" "7" "Honeycomb Worldwide Group of Companies" "HR-Guest" "Guest" "Ph.D" "http://www.advantageboard.com" "1" "asd" "asd" "Yes" "(Optional: Please describe if you can)" "Yes" "(Optional: Please describe if you can)" "Yes" "(Optional: Please describe if you can)" "7" "7" "Honeycomb Worldwide Group of Companies" "HR-Guest" "Guest" "Ph.D" "http://www.advantageboard.com" "1" "asddddddddddd" "Yes" "(Optional: Please describe if you can)" "Yes" "(Optional: Please describe if you can)" "Yes" "(Optional: Please describe if you can)" "6" "7" "Honeycomb Worldwide Group of Companies" "HR-Guest" "Guest" "Ph.D" "http://www.advantageboard.com" "1" "asdadfa" "dfasdfas" "Yes" "(Optional: Please describe if you can)" "Yes" "(Optional: Please describe if you can)" "Yes" "(Optional: Please describe if you can)"

I thought that that is what the counting the fields was for?
In this fragment of code we are starting a loop and extracting all the field names from the database. In the first line of code $i is 0. Then it checks to see if $i is less than the number of fields, and finally it adds 1 to $i.

Code: Select all

for ($i = 0; $i < $fields; $i++) {
    $header .= mysql_field_name($export, $i) . "\t";
}
I sort of thought that that meant that it would be in an excel chart...
Has anyone does this before? Were you able to get the data into corrolating rows and columns?
Last edited by $var on Fri Oct 07, 2005 2:55 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 »

your script doesn't save the file it generates.. it's simply passing it back to the requesting browser.
User avatar
$var
Forum Contributor
Posts: 317
Joined: Thu Aug 18, 2005 8:30 pm
Location: Toronto

Post by $var »

right, and then the browser promts to download, and when you do... it's all in one cell...
spitfire_esquive
Forum Commoner
Posts: 37
Joined: Sun Nov 06, 2005 6:46 am

Post by spitfire_esquive »

almost same code but there is no prompt appearing in my browser (IE 6.0.28). maybe there is something turned "off" in my browser?
spitfire_esquive
Forum Commoner
Posts: 37
Joined: Sun Nov 06, 2005 6:46 am

Post by spitfire_esquive »

:( all in one cell also :( i wonder why....
User avatar
$var
Forum Contributor
Posts: 317
Joined: Thu Aug 18, 2005 8:30 pm
Location: Toronto

Post by $var »

what i ended up doing for this is export it to text file, and copy and paste it into xls.
it works fine that way.

i don't know why it couldn't just do it the other way, difficulites.
spitfire_esquive
Forum Commoner
Posts: 37
Joined: Sun Nov 06, 2005 6:46 am

Post by spitfire_esquive »

did you use an fwrite to export it to a .txt or you just changed the header information to download it onto a .txt file?

thanks! :D
User avatar
$var
Forum Contributor
Posts: 317
Joined: Thu Aug 18, 2005 8:30 pm
Location: Toronto

Post by $var »

I ended up with this:

Code: Select all

<? $select = "SELECT * FROM surveys WHERE Mem_ID =".$ID;                 
		$file = "surveys.txt";
		$export = mysql_query($select);
		$fields = mysql_num_fields($export);
			for ($i = 0; $i < $fields; $i++) {
		    $header .= mysql_field_name($export, $i) . "\t";
			while($row = mysql_fetch_row($export)) {
				$line = '';
				foreach($row as $value) {                                            
					if ((!isset($value)) OR ($value == "")) {
						$value = "\t";
					} else {
						$value = str_replace('"', '""', $value);
						$value = '"' . $value . '"' . "\t";
					}
					$line .= $value;
				}
				$data .= trim($line)."\n";
			}
			}
			$data = str_replace("\r","",$data); 
			
			if ($data == "") {
		    $data = "\n(0) Records Found!\n";                        
			} 
			if (!$file_handle = fopen($file,"a")) { echo "Cannot open file"; }  
			if (!fwrite($file_handle, $data)) { echo "Cannot write to file"; }  
			header("Location: http://www.redirectpath.com/whatever.php");  
			fclose($file_handle);  
?>
Post Reply