Solved

Export MySQL Data to Excel from a PHP page

Posted on 2008-10-07
4
1,001 Views
Last Modified: 2013-12-12
Hi!

I have a MySQL database that I need to export table data out of and into an excel spreadsheet.

The scenerio...
-3 tables volunteer, volunteerC & volunteerD
-the volunteer table holds data pertinent to the volunteer i.e. name address etc
-the volunteerC table holds data regarding comments, i.e. comment reply date
-the volunteerD holds the position and days applied for
We need to pull all record from volunteer and corrosponding records from volunteerD & volunteerC table and export the data to an excel spreadsheat downloadable by the viewer

Schema...
Database name: home
Tables:
-volunteer
   -Fields: ID_vol (primary key auto inc), name_vol, surname_vol, address_vol, city_vol, province_vol, postal_vol, phone_vol, phonew_vol, phonec_vol, phoneo_vol, email_vol, club_vol, level_vol, experienceE1_vol, experienceP1_vol, experienceE2_vol, experienceP2_vol, experienceE3_vol, experienceP3_vol

 -volunteerD
   -Fields: ID_vod(primary key auto inc), ID_vol_vod(primary key of volunteer table) , position1_vod , position2_vod, position3_vod, position4_vod, position5_vod, position6_vod, position7_vod, position8_vod , day1_vod, day2_vod, day3_vod, day4_vod, day5_vod , day6_vod, day7_vod, day8_vod day9_vod , day10_vod, comment_vod, date_vod

 -volunteerC
   -Fields: ID_voc(primary key auto inc), ID_vol_voc(primary key of volunteer table), comment_voc, replied_voc, date_voc

I can get the results for each table seperately
But how do I get it all on one spreadsheet?? Any suggestions??
<?php
mysql_select_db($database_home_conn, $home_conn);
$query_rsVolunteer = "SELECT * FROM volunteer";
$rsVolunteer = mysql_query($query_rsVolunteer, $home_conn) or die(mysql_error());
$row_rsVolunteer = mysql_fetch_assoc($rsVolunteer);
$totalRows_rsVolunteer = mysql_num_rows($rsVolunteer);
 
//Export to Excel Server Behavior
if (isset($_POST['vold'])&&($_POST['vold']=="2")){
	$delim="";
	$delim_replace="";
	if($delim==""){
		$lang=(strpos($_SERVER['HTTP_ACCEPT_LANGUAGE'],",")===false)?$_SERVER['HTTP_ACCEPT_LANGUAGE']:substr($_SERVER['HTTP_ACCEPT_LANGUAGE'],0,strpos($_SERVER['HTTP_ACCEPT_LANGUAGE'],","));
		$semi_array=array("af","zh-hk","zh-mo","zh-cn","zh-sg","zh-tw","fr-ch","de-li","de-ch","it-ch","ja","ko","es-do","es-sv","es-gt","es-hn","es-mx","es-ni","es-pa","es-pe","es-pr","sw");
		$delim=(in_array($lang,$semi_array) || substr_count($lang,"en")>0)?",":";";
	}
	$output="";
	$include_hdr="1";
	if($include_hdr=="1"){
		$totalColumns_rsVolunteer=mysql_num_fields($rsVolunteer);
		for ($x=0; $x<$totalColumns_rsVolunteer; $x++) {
			if($x==$totalColumns_rsVolunteer-1){$comma="";}else{$comma=$delim;}
			$output = $output.(ereg_replace("_", " ",mysql_field_name($rsVolunteer, $x))).$comma;
		}
		$output = $output."\r\n";
	}
 
	do{$fixcomma=array();
    		foreach($row_rsVolunteer as $r){array_push($fixcomma,ereg_replace($delim,$delim_replace,$r));}
		$line = join($delim,$fixcomma);
    		$line=ereg_replace("\r\n", " ",$line);
    		$line = "$line\n";
    		$output=$output.$line;}while($row_rsVolunteer = mysql_fetch_assoc($rsVolunteer));
	header("Content-Type: application/xls");
	header("Content-Disposition: attachment; filename=report.csv");
	header("Content-Type: application/force-download");
	header("Cache-Control: post-check=0, pre-check=0", false);
	echo $output;
	die();
}
?>
 
volunteerD table...
<?php
mysql_select_db($database_home_conn, $home_conn);
$query_rsVolunteer = "SELECT ID_vol_vod AS Volunteer_ID, position1_vod AS Position_1, position2_vod AS Position_2, position3_vod AS Position_3, position4_vod AS Position_4, position5_vod AS Position_5, position6_vod AS Position_6, position7_vod AS Position_7, position8_vod AS Position_8, day1_vod AS Day_1, day2_vod AS Day_2, day3_vod AS Day_3, day4_vod AS Day_4, day5_vod AS Day_5, day6_vod AS Day_6, day7_vod AS Day_7, day8_vod AS Day_8, day9_vod AS Day_9, day10_vod AS Day_10, comment_vod AS Comment, date_vod AS Submitted_On FROM volunteerD";
$rsVolunteer = mysql_query($query_rsVolunteer, $home_conn) or die(mysql_error());
$row_rsVolunteer = mysql_fetch_assoc($rsVolunteer);
$totalRows_rsVolunteer = mysql_num_rows($rsVolunteer);
 
//Export to Excel Server Behavior
if (isset($_POST['export'])&&($_POST['export']=="1")){
	$delim="";
	$delim_replace="";
	if($delim==""){
		$lang=(strpos($_SERVER['HTTP_ACCEPT_LANGUAGE'],",")===false)?$_SERVER['HTTP_ACCEPT_LANGUAGE']:substr($_SERVER['HTTP_ACCEPT_LANGUAGE'],0,strpos($_SERVER['HTTP_ACCEPT_LANGUAGE'],","));
		$semi_array=array("af","zh-hk","zh-mo","zh-cn","zh-sg","zh-tw","fr-ch","de-li","de-ch","it-ch","ja","ko","es-do","es-sv","es-gt","es-hn","es-mx","es-ni","es-pa","es-pe","es-pr","sw");
		$delim=(in_array($lang,$semi_array) || substr_count($lang,"en")>0)?",":";";
	}
	$output="";
	$include_hdr="1";
	if($include_hdr=="1"){
		$totalColumns_rsVolunteer=mysql_num_fields($rsVolunteer);
		for ($x=0; $x<$totalColumns_rsVolunteer; $x++) {
			if($x==$totalColumns_rsVolunteer-1){$comma="";}else{$comma=$delim;}
			$output = $output.(ereg_replace("_", " ",mysql_field_name($rsVolunteer, $x))).$comma;
		}
		$output = $output."\r\n";
	}
 
	do{$fixcomma=array();
    		foreach($row_rsVolunteer as $r){array_push($fixcomma,ereg_replace($delim,$delim_replace,$r));}
		$line = join($delim,$fixcomma);
    		$line=ereg_replace("\r\n", " ",$line);
    		$line = "$line\n";
    		$output=$output.$line;}while($row_rsVolunteer = mysql_fetch_assoc($rsVolunteer));
	header("Content-Type: application/xls");
	header("Content-Disposition: attachment; filename=volunteer_report.csv");
	header("Content-Type: application/force-download");
	header("Cache-Control: post-check=0, pre-check=0", false);
	echo $output;
	die();
}
?>
 
And volunteerC table....
<?php
mysql_select_db($database_home_conn, $home_conn);
$query_rsVolc = "SELECT * FROM volunteerC";
$rsVolc = mysql_query($query_rsVolc, $home_conn) or die(mysql_error());
$row_rsVolc = mysql_fetch_assoc($rsVolc);
$totalRows_rsVolc = mysql_num_rows($rsVolc);
 
//Export to Excel Server Behavior
if (isset($_POST['volc'])&&($_POST['volc']=="3")){
	$delim="";
	$delim_replace="";
	if($delim==""){
		$lang=(strpos($_SERVER['HTTP_ACCEPT_LANGUAGE'],",")===false)?$_SERVER['HTTP_ACCEPT_LANGUAGE']:substr($_SERVER['HTTP_ACCEPT_LANGUAGE'],0,strpos($_SERVER['HTTP_ACCEPT_LANGUAGE'],","));
		$semi_array=array("af","zh-hk","zh-mo","zh-cn","zh-sg","zh-tw","fr-ch","de-li","de-ch","it-ch","ja","ko","es-do","es-sv","es-gt","es-hn","es-mx","es-ni","es-pa","es-pe","es-pr","sw");
		$delim=(in_array($lang,$semi_array) || substr_count($lang,"en")>0)?",":";";
	}
	$output="";
	$include_hdr="1";
	if($include_hdr=="1"){
		$totalColumns_rsVolc=mysql_num_fields($rsVolc);
		for ($x=0; $x<$totalColumns_rsVolc; $x++) {
			if($x==$totalColumns_rsVolc-1){$comma="";}else{$comma=$delim;}
			$output = $output.(ereg_replace("_", " ",mysql_field_name($rsVolc, $x))).$comma;
		}
		$output = $output."\r\n";
	}
 
	do{$fixcomma=array();
    		foreach($row_rsVolc as $r){array_push($fixcomma,ereg_replace($delim,$delim_replace,$r));}
		$line = join($delim,$fixcomma);
    		$line=ereg_replace("\r\n", " ",$line);
    		$line = "$line\n";
    		$output=$output.$line;}while($row_rsVolc = mysql_fetch_assoc($rsVolc));
	header("Content-Type: application/xls");
	header("Content-Disposition: attachment; filename=report.csv");
	header("Content-Type: application/force-download");
	header("Cache-Control: post-check=0, pre-check=0", false);
	echo $output;
	die();
}
?>

Open in new window

0
Comment
Question by:vstoneman1
4 Comments
 
LVL 2

Accepted Solution

by:
AdiF earned 500 total points
ID: 22664948
Not sure I understand you correctly but perhaps you are looking for a MySQL solution which would be more feasible and efficient.  You can change your SQL query to include UNION and/or JOIN clauses:

http://dev.mysql.com/doc/refman/5.0/en/join.html is used to output columns from multiple tables as though the result came from one table (in a nutshell).

http://dev.mysql.com/doc/refman/5.0/en/union.html is used to incorporate row results from multiple tables (in a nutshell).
0
 
LVL 4

Expert Comment

by:imgriff
ID: 22664963
You can change the header of the document to an excel type

Just make sure you don't output the normal document headers first. You have to do either one or the other. An IF statement would do the trick

Don't output lines below if you want to export
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
if($export==true){
	header("Content-type: application/x-msdownload");
	header("Content-Disposition: attachment; filename=\"Product_Category_Matrix.xls\"");
	header("Pragma: no-cache");
	header("Expires: 0"); 
}

Open in new window

0
 
LVL 1

Expert Comment

by:ShinyDarkStone
ID: 22668562
try this class
http://www.phpclasses.org/browse/package/1590.html

its in Portuguese, but i think that ull be able to handle it
0
 

Author Closing Comment

by:vstoneman1
ID: 31504035
Hi!
Thanks a bunch... workin like a lucky charm
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

825 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