469,138 Members | 1,239 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,138 developers. It's quick & easy.

Get Mysql to export rows in excel format

142 100+
Hi,

I want my clients to login, press a button and then MySQL export the assigned fields into a excel doc for them to download...

Where can I get a script to convert MySQL fields to excel format or is there a simple way to do something in PHP?

Kind Regards
Webandwe
Sep 20 '07 #1
4 2944
pbmods
5,821 Expert 4TB
Heya, Webandwe.

There's a few threads on this site about exporting to a CSV file, which Excel can import, but there is very little support for exporting in Excel format.
Sep 20 '07 #2
webandwe
142 100+
Hi, thanks.

I found a script on the net but had to fix it a but but it is still not working, if you run the file it wants to save the php file and does not run and create a excel file, can someone please take a look at it and maybe see if you can find any faults with the script.

thank-you:

[PHP]

<?php
/**************
This PHP script Extracts MySQL table and downloads into an Excel Spreadsheet.
Script by Jeff Johns, for a full explanation and tutorial on this, see: http://www.phpfreaks.com/tutorials/114/0.php
**************
CONFIGURATION:

YOUR DATABASE HOST = (ex. localhost)
USERNAME = username used to connect to host
PASSWORD = password used to connect to host
DB_NAME = your database name
TABLE_NAME = table in the database used for extraction
**************
To extract specific fields and not the whole table, simply replace
the * in the $select variable with the fields you want
**************/
define(db_host, "__MUNGED__");
define(db_user, "__MUNGED__");
define(db_pass, "__MUNGED__");
define(db_link, mysql_connect(db_host,db_user,db_pass));
define(db_name, "companion");
mysql_select_db(db_name);
/*************
Build query, call it, and find the number of fields
/*************/
$select = "SELECT * FROM brandmetlogin";
$export = mysql_query($select);
$count = mysql_num_fields($export);
/************
Extract field names and write them to the $header variable
/***********/
for ($i = 0; $i < $count; $i++) {
$header .= mysql_field_name($export, $i)."t";
}
/***********
Extract all data, format it, and assign to the $data variable
/**********/
while($row = mysql_fetch_row($export)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "t";
}
$line .= $value;
}
$data .= trim($line)."n";
}
$data = str_replace("r", "", $data);
/************
Set the default message for zero records
/************/
if ($data == "") {
$data = "n(0) Records Found!\n";
}
/************
Set the automatic download section
/************/
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=spreadsheet.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$headern$data";
?>


[/PHP]

Later
Webandwe
Sep 21 '07 #3
pbmods
5,821 Expert 4TB
Heya, Webandwe.

For your protection, I have removed your MySQL connection info from your code.
Sep 21 '07 #4
webandwe
142 100+
thanks, did not even see it there...
Sep 25 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Damien | last post: by
11 posts views Thread by Mike MacSween | last post: by
2 posts views Thread by PerryC | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.