asked on

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

ad4m1

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.

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ImagineItDigital

ASKER

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.