Solved

Export MySQL Data to Excel from a PHP page

Posted on 2008-10-07
4
1,006 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
[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
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

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

687 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