Solved

How to export MySQL Recordset Results to Excel

Posted on 2010-08-28
8
1,711 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to count occurrences of each item in an array.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

932 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

15 Experts available now in Live!

Get 1:1 Help Now