Solved

Export MySQL Data to Excel from a PHP page

Posted on 2008-10-07
4
999 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

932 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now