I've got the code below which is distributed for free. And I've set up a MySQL database with 4 columns (field names) in it... (see attached screen grab).

 

However, when I put my details in below code (username, database name, password, hostname), I do get a XLS file download, but with no info in it (the "no matching records found" comes up).

 

I know my details are correct and that it is possible to connect to my database as I have done so with another script (that one takes the info from the php file and displays in an a html file.

 

any ideas as what I could be doing wrong?

 

Thanks,

James.

 

<?php
//Written by Dan Zarrella. Some additional tweaks provided by JP Honeywell
//pear excel package has support for fonts and formulas etc.. more complicated
//this is good for quick table dumps (deliverables)

include('DB_connection.php');
$result = mysql_query('select * from excel_test', $linkID);
$count = mysql_num_fields($result);

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

while($row = mysql_fetch_row($result)){
 $line = '';
 foreach($row as $value){
   if(!isset($value) || $value == ""){
     $value = "\t";
   }else{
# important to escape any quotes to preserve them in the data.
     $value = str_replace('"', '""', $value);
# needed to encapsulate data in quotes because some data might be multi line.
# the good news is that numbers remain numbers in Excel even though quoted.
     $value = '"' . $value . '"' . "\t";
   }
   $line .= $value;
 }
 $data .= trim($line)."\n";
}
# this line is needed because returns embedded in the data have "\r"
# and this looks like a "box character" in Excel
 $data = str_replace("\r", "", $data);


# Nice to let someone know that the search came up empty.
# Otherwise only the column name headers will be output to Excel.
if ($data == "") {
 $data = "\nno matching records found\n";
}

# This line will stream the file to the user rather than spray it across the screen
header("Content-type: application/octet-stream");

# replace excelfile.xls with whatever you want the filename to default to
header("Content-Disposition: attachment; filename=excelfile.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo $header."\n".$data; 
?>

I'm assuming $linkID is provided in your 'DB_connection.php' script?

 

Try this - it may determine a problem in the query:

 

$result = mysql_query('select * from excel_test', $linkID) or die(mysql_error());

 

 

What happens when you echo out $line ?

 

If you get nothing when echoing $line try echoing $value ?

 

 

Its very probable that you'd tried all of the above - let us know how you get on.

arh! That maybe it then.... the $linkID is not declared in my script. So this makes up part of the MySQL query? Any ideas what I might put... I'm only learning all this now. i just want to get all the info out of the database.

Is your table empty? or do you have data in it?

Sorry, I learning as fast as I can, been set a project..

 

the error (obviously) was all my fault... a couple of lines from that code had the terminating ; left off the end.

 

It now works and creates an excel document with field names of the mySQL columns running across the top, then with each record below.

 

However, my mySQL database is just one giant table with lots of columns. For presentation, I'd like to be able to be able to break down the table, so one Excel document has a table for with columns 1-10, then the next one has a table for columns 11-20 from mySQL table etc etc.

 

I see the PHP code I have displayed above, uses a for and a while loop to run through the whole mySQL table and add the values it finds into a variable. But if I don't want to run through the whole table, how do I go about that? Could I create an array with PHP, then populate that with the info from the database table and then splice that and then echo only that?

 

Hope I making some sense. Thanks for the help so far.