Creating a CSV file from a MySQL database

[gatekat]

Hello,

I need a quarry to send to a MySQL database va PHP that will create a CSV file of the database contents (and not structure) and download them to the user's computer with a Save As like function.

I am trying to make this happen with a single click on a link, but a forced download on a separate page would be acceptable. I'm not sure how to do that either, however.

Any assistance would be appreciated.

--
Rauhnee

[bastien]

This code creates a table dump, it should point you in the right direction

Note:script code is not tested

<?//copy data
require ("dbconn.php");  //db connection

$handle = fopen("dump.csv","w");  //local file

$sql = "select * from users";
$result = mysql_query($sql,$conn);
if ($result){
	$sql = "select * from ads";
	$result = mysql_query($sql,$conn);
	$count = mysql_num_fields($result);
	   while ($rows = mysql_fetch_array($result)){

     for ($i=0;$i<$count;$i++){
       echo $rows[$i].", ";  //data
     }
     echo "\n";  //new line
  }
}
fclose($handle);
//force download
header("Cache-Control: no-store, no-cache, must-revalidate"); // HTTP/1.1
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache"); // HTTP/1.0
header("Cache-control: private"); header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=dump.csv");
header("Content-Type: application/octet-stream"); 
header("Content-Transfer-Encoding: binary"); 


?>

[gatekat]

Thank you very much, Bastien. It works great after a touch of tweaking.

But it insists on putting a .php at the end of the downloaded file name. So instead of downloading IBDdata.csv it downloads IBDdata.csv.php

Any idea how to fix that? My code is below.

The file it is in (all by itself) is download.php if that matters.

//////////////////////////////////////////////

$connID = connect_to_db();

header("Cache-Control: no-store, no-cache, must-revalidate"); // HTTP/1.1
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache"); // HTTP/1.0
header("Cache-control: private");
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=IBDdata.csv");
header("Content-Type: application/octet-stream");
header("Content-Transfer-Encoding: binary");

$LocalFile = FOPEN("IBDdata.csv","w"); //open the local file for writing

$query_select = "SELECT date_format(entryDate , '%M %e, %Y'), entrySubject, entryAuthorFirst, entryAuthorLast, entryComments FROM tblData ORDER BY entrySubject";
$result = mysql_query($query_select, $connID);

IF ($result) // Makes sure there is data to get.
{
$query_select = "SELECT date_format(entryDate , '%M %e, %Y'), entrySubject, entryAuthorFirst, entryAuthorLast, entryComments FROM tblData ORDER BY entrySubject";
$result = mysql_query($query_select, $connID);
$count = mysql_num_fields($result);
WHILE($rows = mysql_fetch_array($result))
{
FOR($i=0; $i < $count; $i++)
{
ECHO $rows[$i].", "; //Writes the data line by line
}
ECHO "\n"; //new line
}
}
FCLOSE($LocalFile); //force the download

---

Many thanks,
Rauhnee

[bastien]

i don't know looking quickly

but i did notice a mistake in my code, my bad....

ECHO $rows[$i].", "; //Writes the data line by line

should be

fwrite $rows[$i].",";

it may be that the two are related