Solved

How to export MySQL Recordset Results to Excel

Posted on 2010-08-28
8
1,733 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
  • 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 100 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 109

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 109

Accepted Solution

by:
Ray Paseur earned 400 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

821 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