Solved

How to export MySQL Recordset Results to Excel

Posted on 2010-08-28
8
1,702 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
 
LVL 108

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 108

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to count occurrences of each item in an array.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

744 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

11 Experts available now in Live!

Get 1:1 Help Now