Export MySQL data to Excel in PHP

[netfrugal]

I've been able to export any data from MySQL to a csv file. But I've been having problems with stripping out all html code, and whenever there is a comma in the data, it sends all data to the next cell in excel. I included a str_replace function, but that is still not entirely working.

Let me show you what I've already done.


<?php if (!$HTTP_GET_VARS['submit']) { ?>

<?php
echo "Export and Save Customer Data onto your Local Machine";
echo '<form action="'. $phpself.'">';
echo '<input type="submit" value="Export" name="submit"></form>';
?>

<?php
}
else
{
$contents="Board Category,Questions,Answers\n";
$user_query = mysql_query('select
f.faqdesk_id
, c.categories_id
, f.faqdesk_question
, c.categories_name
, f.faqdesk_answer_short
from faqdesk_description as f
join faqdesk_to_categories as f2c
on f.faqdesk_id = f2c.faqdesk_id
join faqdesk_categories_description as c
on f2c.categories_id = c.categories_id');
while($row = mysql_fetch_array($user_query))
{
$contents.=$row[categories_name].",";
$contents.=$row[faqdesk_question].",";
$answer = str_replace(',', '\,', $row[faqdesk_answer_short]); // escape internalt commas
$contents.=$answer."\n";
}
$contents = strip_tags($contents); // remove html and php tags etc.
Header("Content-Disposition: attachment; filename=export.csv");
print $contents;

?>
It stipped out html tags, but it is still leaving in &nbsp;

I'm sure there is a fix or replacement function for that. However, the commas are still giving separation problems, even with the str_replace function.

You can see the excel results here: www.mastertheboard.com/board_topic_export3.php

It is a list of questions and answers for the military soldiers in my units.
There is also some other formmating issues having to do with numbered bullets. But I'm not sure how that is fixed.
Any ideas?

[IeD3vil]

$text = str_replace("&nbsp;"," ",$text);

that will replace all &nbsp; in $text to space.

[codeexploiter]

<?php
class database
{
private $db_handle;
private $user_name;
private $password;
private $data_base;
private $host_name;
private $sql;
private $results;

function __construct($host="localhost",$user,$passwd)
{
$this->db_handle = mysql_connect($host,$user,$passwd);
}

function dbSelect($db)
{
$this->data_base = $db;
if(!mysql_select_db($this->data_base, $this->db_handle))
{
error_log(mysql_error(), 3, "/phplog.err");
die("Error connecting to Database");
}
}

function executeSql($sql_stmt)
{
$this->sql = $sql_stmt;
$this->result = mysql_query($this->sql);
}
function returnResults()
{
return $this->result;
}
}

$user = "root";
$passwd = "";
$db = "newTest";
$sql = "SELECT * FROM EMP ORDER BY empname";

$dbObject = new database($host,$user,$passwd);
$dbObject->dbSelect($db);
$dbObject->executeSql($sql);


$res = $dbObject->returnResults();

$newFileName = "emp_names.csv";

$fpWrite = fopen("C:\\$newFileName", "w");

$nameStr = "";

while($record = mysql_fetch_object($res))
{
$name = $record->empname;

$nameArray = explode(",",$name);

if(count($nameArray) > 1)
{
$nameTemp = "";
for($i=0;$i < count($nameArray); $i++)
{
$nameTemp = $nameTemp . $nameArray[$i];

if($i != (count($nameArray) - 1))
$nameTemp = $nameTemp . "&sbquo;";
}
$name = $nameTemp;
}

$nameStr = $nameStr.$name.",";
}


fwrite($fpWrite,$nameStr);

echo "File operation has been completed successfully!<br><br>";
?>


Try the above code it needs optimization but the core thing works that mean it retrieves database fields from the DB and insert (in this case as only one record you can change it accoridng to your needs I just want to show how we can keep , within a db field)

[alansam007]

I want to design a site with form being send to my e-mail account and i want to be creating a profile for the visitor that send me an e-mail through the form....I was think of using MS Access as a Database since is just for 10-15 profile.

I want the MS Access to stand as the Database the will be check their username and password whether is correct...before they can log in into there profiles.

I was also looking for a script that i can use as a progress report which will look likes a plug in when they click an Hyper link in there profile(The script should display in percentage).

Thanks

[huzefa]

Hiiii,

I want to export mysql data to excel file with php

There are the things i want :
1. It should display the table on a page and we will be able to select the field with a radio button.
2. Then the selected fields should get exported to a excel file when we click on the submit button.
3. On the top of the excel file, it should display the field names and below it should show all the records present in the table.

Does anybody know how to do this ........i will be thankful if anybody will put the source code for this ...i need it ...its urgent.

[itw]

i have one drop down list which is dynamically generate reports.can someone help me out to generate excel report based on that drop down list?Thanks in advance.