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
Solved

How to export my MySQL query results into a formated Excell spreadsheet?

Posted on 2009-07-07
14
344 Views
Last Modified: 2013-12-13
Experts,

I have a complex query that prints query results to a page.  I would like to allow my users to download the query results in a formatted Excell Spreadsheet.  Is this possible?

My query is below.  EVM
<?php
				 if($_GET['cmd']=="getsums"){
				 mysql_connect("localhost", "user", "password")or die("cannot connect");
				 mysql_select_db("dsadb2")or die("cannot select DB");
		  
		 		 $sql2="SELECT @rownum:=@rownum + 1 'rank', a.NUID,sum(a.Steps) as total_steps, sum(a.Miles) as total_miles
						FROM walk_2009_stats a, (SELECT @rownum:=0) b
						GROUP BY NUID ORDER BY total_miles DESC";  
						
						$data2 = mysql_query($sql2) or die ("Could not run query: " . $sql2 . "<br />\n" . mysql_error () );
						
						echo'<h3 align="left">Total Milage To Date | Download Here</h3><br/>';
						
						echo "<table border='0' cellpadding='0' cellspacing='1'>";
						echo "
						<tr>
						<td style='text-align:center'><b><u>Rank</b></u></td>
						<td style='text-align:center'><b><u>NUID</b></u></td>
						<td style='text-align:center'><b><u>First Name</b></u></td>
						<td style='text-align:center'><b><u>Last Name</b></u></td>
						<td style='text-align:center'><b><u>Site</b></u></td>
						<td style='text-align:center'><b><u>Milage</b></u></td>
						</tr>";
						
						// THIS SETS UP THE COLOR STYLE FOR EACH ROW, INSIDE THE WHILE LOOP WE WILL ASK THE COLORS TO ALTERNATE
						$style = 0;
						
						// Start rank
						$rank = 1;
						
						while ($results2 = mysql_fetch_assoc($data2)){
						
							//inside the loop change the class of the row/column 
							//(based on a style sheet). you could also directly specify 
							//a background colour here
							if($style == 1) {
							$tclass = "row1";
							$style = 0;
							} else {
							$tclass = "row2";
							$style = 1;
							}
						
						$NUID = $results2['NUID'];
						$Total_Miles = $results2['total_miles'];
						$sql3="SELECT * FROM walk_2009_members WHERE NUID='$NUID' ";
						$data3 = mysql_query($sql3) or die ("Could not run query: " . $sql3 . "<br />\n" . mysql_error () );
						while ($results3 = mysql_fetch_assoc($data3)){
						echo 
						'<tr class="'.$tclass.'" >
						<td style="text-align:center">'.($rank++).'</td>
						<td style="text-align:center">'.$results3['NUID'].'</td>
						<td style="text-align:center">'.$results3['FirstName'].'</td>
						<td style="text-align:center">'.$results3['LastName'].'</td>
						<td style="text-align:center">'.$results3['Site'].'</td>
						<td style="text-align:center">'.$results2['total_miles'].'</td>
						</tr>';
						}
 
						
						}
						echo "</table>";
						 
				  }// END OF GETSUMS IF STATEMENT
				 ?>

Open in new window

0
Comment
Question by:evibesmusic
  • 7
  • 5
14 Comments
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24799454
Yes, it is possible. There are libraries available, for instance:

http://pear.php.net/package/Spreadsheet_Excel_Writer
0
 

Author Comment

by:evibesmusic
ID: 24807949
@cxr:

I appreciate the link and the pear package looks nice but, I can't install anything on the host server.

Any other possible ways this might be done via a php script?

EVM
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24809893
What do you mean by "formatted", what kind of formatting is needed?

Excel recognize different formats, the simplest method is to write a TAB separated CSV file: separate each field with a TAB character, and separate each row with a newline character. No particular formatting is possible with this method, but excel will right-align numbers and left-align text. Something like this:
<?php
 
mysql_connect('localhost','user','pass');
mysql_select_db('dbname');
$result = mysql_query('select * from MyTable');
 
$filename = 'excelfile.xls';
 
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=$filename");
header("Pragma: no-cache");
header("Expires: 0");
 
$quote = create_function('$item',
  'return str_replace(array("\r",\'"\'),array("",\'""\'), $item);');
 
$firstrow = true;
while($row = mysql_fetch_assoc($result)) {
  if($firstrow) {
    echo implode("\t",array_map($quote,array_keys($row)))."\n";
    $firstrow = false;
  }
  echo implode("\t",array_map($quote,$row))."\n";
}
 
?> 

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:evibesmusic
ID: 24842601
@cxr:

Sorry for using the word 'formatted', I just basically want the information placed into a spreadsheet according to column heading within the database.  If what you have described above will do this then I am set.  Thank you for your attention to this question.

EVM
0
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 500 total points
ID: 24843720
Try this, then:
<?php
 
mysql_connect('localhost','user','pass');
mysql_select_db('dsadb2');
 
mysql_query('SET @rownum:=0');
 
$sql = "SELECT 
    @rownum:=@rownum + 1 'Rank',
    a.NUID, FirstName 'First Name', LastName 'Last Name', Site, 
    sum(a.Miles) as Mileage
  FROM walk_2009_stats a, walk_2009_members b
  WHERE a.NUID=b.NUID
  GROUP BY a.NUID, FirstName, LastName, Site
  ORDER BY Mileage DESC";
$result = mysql_query($sql);
if(!$result) die(mysql_error());
 
$filename = 'Mileage_'.date('Y-m-d').'.xls';
 
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=$filename");
header("Pragma: no-cache");
header("Expires: 0");
 
$quote = create_function('$item',
  'return str_replace(array("\r",\'"\'),array("",\'""\'), $item);');
 
$firstrow = true;
while($row = mysql_fetch_assoc($result)) {
  if($firstrow) {
    echo implode("\t",array_map($quote,array_keys($row)))."\n";
    $firstrow = false;
  }
  echo implode("\t",array_map($quote,$row))."\n";
}
 
?>

Open in new window

0
 

Author Comment

by:evibesmusic
ID: 24930302
@cxr:

Sorry for the long delay.  I've placed your code inside of my page and it produces all of the results of the query but, prints them to the screen.

Can you offer any other suggestions on how to get the results of the query to populate an Excell spreadsheet?
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24930340
>> I've placed your code inside of my page

That is wrong, it should be saved as a separate php script. When this script is executed, it should load in excel automatically.
0
 

Author Comment

by:evibesmusic
ID: 24930607
@cxr:

Thank you for correcting my implementation of your script.  The script now works but, all of the information in the db is placed into the contents of one cell in the spreadsheet.  Is it possible to have all of the db contents populate the spreadsheet just like in the db itself (with column headers, followed by the data)?
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24930756
That is what it was supposed to do, and it does so on my computer...

Try modifying the file extension, change this:

$filename = 'Mileage_'.date('Y-m-d').'.xls';

...into this:

$filename = 'Mileage_'.date('Y-m-d').'.csv';
0
 

Author Comment

by:evibesmusic
ID: 24930973
@cxr:

When I changed the coding per your last post, it placed each row of db values into one cell.  It did not seperate the results into different colums.

Would the version of Excell have any affect on how the information is displayed?  I am using MS Excell 2003.  Hmmm....

I have also modified the name of the file that is downloaded as you can see by the files below.
Total-Mileage-DB-As-of2009-07-23.csv
Mileage-2009-07-23.xls
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24931134
I found the code below at the start of the files. It should not be there, it is not produced by the code I posted in http:#a24843720

You should use my code as is, without inserting HTML headers at the beginning.
<!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=utf-8" />
<title>Untitled Document</title>
</head>
 
<body>

Open in new window

0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 25105614
My answer http:#a24843720 was correct, the asker inserted html headers, which brakes it. I explained that in http:#a24931134.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
question about excel functionality 3 33
how to do this Excel search box Soundex Search 6 27
Clear a Text Box 7 25
vba copy paste previous line 3 15
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

860 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