[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

How do I do a live XLS export of a MySQL table from a web page?

Hi experts,

I was wondering if there is a relatively simple solution for me to do a MySQL export of a particular table for client download.  This is not for me as the developer, but for any person who is browsing the site to be able to download a live export from a particular table.  So far, I've been simply doing an export on my own using Navicat, and then posting the resulting XLS file as a download on the page.  But this is obviously not ideal, as the client updates the database constantly, which makes the XLS file go farther out of date as time goes by.

Any solutions free or otherwise to make this happen for the client?

Thanks in advance,

Karey
0
ImagineItDigital
Asked:
ImagineItDigital
1 Solution
 
ad4m1Commented:
There's serveral ways of doing this but the easiest one would be to write a php script and host it on the server somwhere that your clients can access it.
They would then nagivate to the script which would then give them a CSV download of the current contents of the table in your database. CSV files can then be opened using Excel.
If your after a almost-live version then perhaps outputing the data as a HTML table and using the Excel's Data - > Web-Based Query engine to periodically pull in the data into an Excel spreadsheet to do this. I've set similar thing up before for clients wishing to have the latest Exchange rate for their finance tables in Excel. I pulled the data in from XE.net's website using this tool.
Either way your script will look something similar to this:
<?php
header("content-type: text/csv");
$strsql="SELECT * FROM mytable";
$rs=mysql_query($strsql);
while($data=@mysql_fetch_array($strsql))
   {
   foreach($data as $value)
      {
      print("$value,");
      }
   }
?>
The above example is for your CSV export but if you want the HTML table output version you'll need this:
<?php
$strsql="SELECT * FROM mytable";
$rs=mysql_query($strsql);
print("<table>");
while($data=@mysql_fetch_array($strsql))
   {
   print("<tr>");
   foreach($data as $value)
      {
      print("<td>$value</td>");
      }
   print("</tr>");
   }
print("</table>");
?>
Hope this helps.
0
 
Ray PaseurCommented:
Here is my teaching example of how to do what you want.  

A thoughtful addition might be to add the Date/Time information into the file name that is created near line 50.  With another script, you could look for the most recent version of the CSV file, and if it were more than X hours old, you could recreate a current version.

HTH, and best wishes for the new year, ~Ray
<?php // RAY_fputcsv_from_mysql.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


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

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = 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, $db_connection))
{
   $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



// CONSTRUCT AND EXECUTE THE QUERY
$sql = "SELECT field1, field2, field3 FROM my_table ORDER BY field1 DESC";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS



// OPEN THE CSV FILE
$csv = 'RAY_temp_csv.txt';
$fp  = fopen($csv, 'w');



// ITERATE OVER THE DATA SET.  MAN PAGE http://us3.php.net/manual/en/function.mysql-fetch-row.php
while ($row = mysql_fetch_row($res))
{

// OPTIONAL - SKIP OVER ROWS THAT HAVE EMPTY FIELDS
    if ( empty($row[0]) && empty($row[1]) ) continue;

// WRITE THE COMMA-SEPARATED VALUES.  MAN PAGE http://us3.php.net/manual/en/function.fputcsv.php
    if (!fputcsv($fp, $row)) die('CATASTROPHE');
}



// ALL DONE
fclose($fp);

// SHOW A LINK
echo "<p><a href=\"$csv\">$csv</a></p>\n";

Open in new window

0
 
ImagineItDigitalAuthor Commented:
I really appreciate the help.  This worked very well and was easy to implement.  It's now live for the client.

Thanks to ad4m1 one as well.  I just couldn't quite figure out your solution as quickly, though I'm sure it was spot on.  I use Dreamweaver and ADDT, and then edit the scripts as I understand them.  

Thanks again.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now