export mysql data, using php

leaven

Hi!

I have a website that works with a MySQL database through PHP.

I'd like to enable visitors of this website to save data from this database to their computer in Excel format.

Is this possible? Please, PHP examples...

Thanks!

amansabharwal

yes this is very much possible

write a script which will fetch the data from the database and

echo all the data in the following format

"field1","field2",field2"
"field1","field2",field2"

make sure that before you do all this change the content disposition of the file using header function and give the filename as "file.csv"

leaven

Great, thanks!

MytchE

You'll need to set the content-type and filename headers too. Just search google for PHP header content-type.

leaven

Yes, will do. I think I'll get this thing working

In fact I'm a starter in PHP and doing searches in Google, and other searchengines, for the things I want to know is how I learned most of my PHP until now.

It's a funny thing when you see that all you want to do is make a simple thing but before you know it it will become more and more complicated.
Or maybe it's just me and my perfectionism 🙂 Either way it's fun to learn new things as I go.
This forum is a great way to learn also!

danielle

This is an example of what you need. Be sure there is nothing before the "<?header..." - not even a space. The echo statement before the while puts column names in the first row of the excel file.

<?header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: inline; filename=\"contacts.xls\"");

//pconnect file
include "access.php";

$q="select contact_id, companyname, address from contacts";
$r=mysql_query($q);

echo ("Contact Id". "\t" ."Company Name". "\t" ."Address"."\n");

while ($a=mysql_fetch_array($r)) {

    echo ($a["contact_id"]. "\t" .$a["companyname"]. "\t"
	.$a["address"]. "\n");

}//end while

vtb

I believe you can also ouput the data as an HTML table, and as long as you have the right content-type header, the browser, if capable, will invoke Excel which will convert html to its native format on the fly.

It appears you can also create formulas as well as literal values:

<?header("Content-Type: application/vnd.ms-excel");?>
<table>
<tr>
    <td>Something</td>
    <td>17</td>
</tr>
<tr>
    <td>Something else</td>
    <td>32</td>
</tr>
<tr>
    <td>TOTAL:</td>
    <td>=SUM(B1:B2)</td>
</tr>
</table>