Exporting 2 MySQL tables into an Excel Spreadsheet

[callingrohit]

Hi All,

I know how to export one table into an excel spreadsheet, but what I would like to do is export 2-3 tables in one spreadsheet instead of having sep spreadsheets for all my tables.

So suppose Table1 has 5 fields and Table2 has 3 fields.....each of them will have ID...not sure how to avoid that from coming in the spreadsheet for Table2 since ID will come from Table1 ???

Anyways, so on spreadsheet what I would like is this

//field1 to 5 are headers from Table1 & field-1-2-3 are from Table2

field1 field2 field3 field4 field5 field-1 field-2 field-3
data data data data data data data data

Is this possible ???

here is the code for exporting just one table into the spreadsheet...

[code]

<?php
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=spreadsheet.xls");
header("Pragma: no-cache");
header("Expires: 0");

define(db_host, "your_hostname");
define(db_user, "your_username");
define(db_pass, "your_password");
define(db_link, mysql_connect(db_host,db_user,db_pass));
define(db_name, "your_database_name");
mysql_select_db(db_name);

$select = "SELECT * FROM your_tablename";
$export = mysql_query($select);
$fields = mysql_num_fields($export);

for ($i = 0; $i < $fields; $i++) {
$header .= mysql_field_name($export, $i) . "\t";
}

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);

if ($data == "") {
$data = "\n(0) Records Found!\n";
}
// print the db
echo "$header \n $data" ;
?>

[/code]

cheers
rohit

[talreja_sapna]

wht abt the other columns are they same or diff can u tell me the schema of ur tables

[callingrohit]

hi talreja_sapna,

thanks for your reply. I'm not sure what you meant by schema of the tables ??? Did you mean the structure ??? both the tables 1 & 2 will have different stuff in them for example - Table 1 can contain a person's general information like name, address, city etc. and Table 2 can contain their hobbies and interests.

cheers
rohit

[talreja_sapna]

hi
wht u could do is use a query with join but u will have to specify field names in select statement

<?php
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=spreadsheet.xls");
header("Pragma: no-cache");
header("Expires: 0");

define(db_host, "localhost");
define(db_user, "root");
define(db_pass, "root");
define(db_link, mysql_connect(db_host,db_user,db_pass));
define(db_name, "person");
mysql_select_db(db_name);

$select = "SELECT general.id,name,address,hobbies,interest FROM general,interest where general.id=interest.id ";
$export = mysql_query($select);
$fields = mysql_num_fields($export);

for ($i = 0; $i < $fields; $i++) {
$header .= mysql_field_name($export, $i) . "\t";
}

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);

if ($data == "") {
$data = "\n(0) Records Found!\n";
}
// print the db
echo "$header \n $data" ;
?>