Solved

Export MySQL Data to Excel from a PHP page

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article discusses four methods for overlaying images in a container on a web page
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 …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

707 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

16 Experts available now in Live!

Get 1:1 Help Now