Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Export MySQL Data to Excel from a PHP page

Posted on 2008-10-07
4
Medium Priority
?
1,016 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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.
Suggested Courses

597 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