?
Solved

How to export MySQL Recordset Results to Excel

Posted on 2010-08-28
8
Medium Priority
?
1,774 Views
Last Modified: 2013-12-13
How can I enable the end-user to export the results from a RecordSet to Excel?  My site uses PHP and a MySQL database.  The Results page in a table format that is dynamic.  The user will need to be able to click a button or link to save off an Excel file of the table’s results.  I have no idea how to start this.  I can only find extensions that take a form and insert the data into an Excel file.
Thanks
0
Comment
Question by:napsternova
[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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 3

Expert Comment

by:flubbard
ID: 33551331
You could start with a select into outfile command into a temporary folder.  Then provide a linik for the file to export.  You can choose in the syntax of the select statement to make it tab or comma deliniated so that it can be imported as a text file into excel.

hth - flub
0
 
LVL 70

Assisted Solution

by:Jason C. Levine
Jason C. Levine earned 400 total points
ID: 33551833
Hi napsternova,

Are you looking for a DW extension that does this?  There are several but none free.

If you are willing to play around in code for a bit, there are several free scripts out there:

http://www.phpsimple.net/tutorials/mysql_to_excel/
http://www.phpclasses.org/package/2038-PHP-Export-data-from-a-MySQL-database-to-Excel-files.html

for example.
0
 

Author Comment

by:napsternova
ID: 33552053
Awesome solutions, how about those extensions?  Just for fun
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 33552320
PHP has some built-in capabilities to help you with this.  Check the man page here:
http://us.php.net/manual/en/function.fputcsv.php#94088

Most Windows installations will automatically associate CSV files with Excel.  So a good way to do something like this is to write the CSV file on your server and give the client an HTML link to the file.  When they click the link, the Excel program will usually start.
0
 

Author Comment

by:napsternova
ID: 33552997
I am not too sure what to do with this, sorry.
So I can make a new php document and create a recordset?
Then enter the array info?
This info appears in 3 rows and 3 columns, how to I display my info that will always be say 3 columns but depending on how many items are in the repeating region could have 100 rows.  This example is static.  Can you help with this part?

<?php

$list = array (
    array('aaa', 'bbb', 'ccc', 'dddd'),
    array('123', '456', '789'),
    array('"aaa"', '"bbb"')
);

$fp = fopen('file.csv', 'w');

foreach ($list as $fields) {
    fputcsv($fp, $fields);
}

fclose($fp);
?>
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1600 total points
ID: 33553271
CSV files typically have the same number of columns in each row.  Your record set would have N columns and any number of rows, depending on the response to the query.

Here is a teaching example that shows how to get all the columns of a table into a CSV.  It is long, but not too complicated.  The iterator near line 150 is important.  Please look it over and post back with any questions.

Best, ~Ray
<?php // RAY_mysql_to_csv.php
error_reporting(E_ALL);

// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php
// MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR DBA / HOSTING PROVIDER
$db_user = "??";
$db_word = "??";



// ESTABLISH A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$dbcx = mysql_connect("$db_host", "$db_user", "$db_word"))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB CONNECTION: ";
   echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $dbcx))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB SELECTION: ";
   echo "<br/> $errmsg <br/>";
   die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES




// THE TABLE NAME IS NOT SPECIFIED - ASK CLIENT
if (empty($_GET["q"]))
{
// RETURN A LIST OF TABLES
   $sql = "SHOW TABLES";
   $res = mysql_query($sql);
   if (!$res)
   {
      $errmsg = mysql_errno() . ' ' . mysql_error();
      echo "<br/>QUERY FAIL: ";
      echo "<br/>$sql <br/>";
      die($errmsg);
   }

// DETERMINE HOW MANY ROWS OF RESULTS WE GOT (ONE FOR EACH TABLE)
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-num-rows.php
   $num = mysql_num_rows($res);
   if (!$num)
   {
      echo "<br/>$sql ";
      die("QUERY FOUND NO DATA");
   }
   else
   {
      echo "<br/>$sql ";
      echo "QUERY FOUND $num ROWS OF DATA ";
   }

// ITERATE OVER THE RESULTS SET TO COLLECT THE TABLE NAMES
   $show_tables = array();
   while ($show_tables = mysql_fetch_array($res))
   {
      $my_tables[] = $show_tables[0];
   }
// VISUALIZE THE LIST OF TABLES
   echo "<br/><pre>\n";
   print_r($my_tables);
   echo "</pre>\n";

// SHOW A FORM - ASK CLIENT TO CHOOSE THE TABLE
?>

<form>
ENTER TABLE NAME:
<input name="q" />
<input type="submit" />
</form>

<?php // CLOSE OFF THE IF STATEMENT
    die('CHOOSE NOW, PLEASE');
}



// THE CLIENT HAS SPECIFIED THE TABLE NAME IN THE FORM OR URL GET ARGUMENT
else
{
   $nom = trim($_GET["q"]);
}



// CONSTRUCT THE QUERY USING THE TABLE NAME IN THE URL GET STRING q=
$sql  = "SELECT * FROM ";
$sql .= "`" . mysql_real_escape_string($nom) . "`";

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
// MAN PAGE:http://us2.php.net/manual/en/function.mysql-query.php
if (!$res = mysql_query($sql, $dbcx))
{
    // MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql ";
    // MAN PAGE: http://us.php.net/manual/en/function.mysql-errno.php
    if (mysql_errno() == 1146) echo "<br/>TABLE NAMES ARE CASE SENSITIVE ";
    die("<br/>" . $errmsg);
}

// HOW MANY ROWS DID WE GET?
// MAN PAGE: http://us3.php.net/manual/en/function.mysql-num-rows.php
$num = mysql_num_rows($res);
if (!$num) die("SQL $sql RETURNED NO ROWS");




// NOW THAT WE KNOW WE HAVE DATA, ESTABLISH THE PLACE TO PUT THE DATA!
// USE A FILE NAME WITH TABLE NAME, DATE AND TIME TO AVOID CONFUSION
$fname = $nom . date('Ymd\THis') . '.csv';
$fpc   = fopen($fname, 'w');
if (!$fpc) die("FOPEN FAILED FOR $fname");




// GET THE FIRST ROW OF THE RESULTS SET AND GET THE FIELD NAMES
$row = mysql_fetch_assoc($res);
$col = array_keys($row);
// MAN PAGE: http://us3.php.net/manual/en/function.fputcsv.php
fputcsv($fpc, $col);

// WRITE THE FIRST ROW OF THE DATA
fputcsv($fpc, $row);




// ITERATE OVER THE REMAINING DATA
while ($row = mysql_fetch_assoc($res))
{
   fputcsv($fpc, $row);
}



// ALL DONE
fclose($fpc);



// SHOW THE LINK TO THE BACKUP FILE
echo "<br/>CSV OF $nom CREATED HERE WITH COLUMN TITLES PLUS $num ROWS OF DATA:\n";
echo "<br/><a href=\"$fname\">$fname</a>\n";

Open in new window

0
 

Author Closing Comment

by:napsternova
ID: 33553464
Thank you
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 33555124
napsternova,

You've already closed out, but here are two extensions that will automate this:

http://linecraft.com/dreamweaver-export-to-excel/extension.php
http://www.dmxzone.com/go?16350
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Suggested Courses

771 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