Solved

How to export MySQL Recordset Results to Excel

Posted on 2010-08-28
8
1,721 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Left trim cells in column A Excel vba 2 32
And OR formula 5 23
Return Column Number based on a specific value 25 26
vba autofilter in row 4 6 11
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

803 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