how to create a button to export a mysql table to an excel file.

[jbrill]

Ive seen it done, now i need to know how its done.

 

Does anyone have the code that creates a link, and on click, takes a specified table in my database and exports and downloads an excel file?

[cooldude832]

excel or csv? csv is easier and imports perfectly

 

<?php
$q = mysql_query(SELECT * FROM TABLE) or die (mysql_error());
$row = mysql_fetch_array($q);
$rows = implode("\n",$row);
$data = implode(","$rows);
fwrite("File.csv",w);
echo "<a href=\"file.csv">Click here</a>";
?>

sorta i think

[jbrill]

hmmmmm didnt work...

 

[cooldude832]

I'm not sure if the implodes are right I just took a stab at it

try

<?php
while($row = mysql_rech_array($q)){
foreach($row as $value) {
$data[] = implode(",",$value);
}
}
$data = implode("\n",$data);
print_r($data);
$file = fopen("file.csv","w");
if(fwrite($file,$data)){
echo "Success";
}
else {
echo "Failure";
}
?>

[jbrill]

still no worky 

 

[cooldude832]

this worked for me: (check permissions on the subfolder)

<?php
$q = mysql_query("select * from myTable") or die(mysql_error());
while($row = mysql_fetch_array($q)){
$data[] = implode(",",$row);
}
$data = implode("\n",$data);
$file = fopen("file.csv","w");
if(fwrite($file,$data)){
echo "Success<br/>
<a href=\"file.csv\">Here</a>";
}
else {
echo "Failure";
}
?>

[jbrill]

i put this code in:

<?php
$q = mysql_query("select * from jobs") or die(mysql_error());
while($row = mysql_fetch_array($q)){
$data[] = implode(",",$row);
}
$data = implode("\n",$data);
$file = fopen("file.csv","w");
if(fwrite($file,$data)){
echo "Success<br/>
<a href=\"file.csv\">Here</a>";
}
else {
echo "Failure";
}
?>

 

and got this error:

 

 

Parse error: syntax error, unexpected T_STRING in /home/morow/public_html/admin/admin_options.php on line 44

 

line 44 is:

 

$q = mysql_query("select * from jobs") or die(mysql_error());

 

what permissions, on what folder?

 

[cooldude832]

one sec, i'm making it more useful

[cooldude832]

lets try this it shows all table you pick a table and then it csv it to a file called $table_unixtimestamp.csv

<?php

$sql = "SHOW TABLES FROM pira00_mysql";
$result = mysql_query($sql);
echo "<form action=\"csv.php\" method=\"post\">
<select name=\"table\">";
while ($row = mysql_fetch_row($result)) {
    echo "<option value=\"".$row[0]."\">".$row[0]."</option>";
}
echo "</select><input type=\"submit\" value=\"submit\">";
if (ISSET($_POST['table'])){
$table = $_POST['table'];
$q = mysql_query("select * from $table") or die(mysql_error());
while($row = mysql_fetch_array($q)){
$data[] = implode(",",$row);
}
$filename = $table;
$filename .= "_";
$filename .= date("U");
$filename .= ".csv";
$data = implode("\n",$data);
$file = fopen($filename,"w");
if(fwrite($file,$data)){
echo "<br/>Success<br/>
<a href=\"".$filename."\">".$filename."</a>";
}
else {
echo "Failure";
}
}
?>

[cooldude832]

It seems to be duplicated results for some reason I'll figure out why

[cooldude832]

okay this works perfectly for me (reason it doubled was mysql_fetch_array draws associtive keys and indexed keys so i changed it to assoc and we are good) feel free to share but give me credit

<?php
/*
By Cooldude_832: ([email protected])
USE OF THIS WITHOUT THIS IS CONSIDERED STOLEN WORK!!!!!
this will allow you to see all your tables in a given mysql database (make connection first) 
and then select a database by giving $database a value.
Pick a value from the dropdown and hit submit.
Enojy.
Cooldude_832
*/
$database = "";
$sql = "SHOW TABLES FROM $database";
$result = mysql_query($sql);
echo "<form action=\"csv.php\" method=\"post\">
<select name=\"table\">";
while ($row = mysql_fetch_row($result)) {
    echo "<option value=\"".$row[0]."\">".$row[0]."</option>";
}
echo "</select><input type=\"submit\" value=\"submit\"></form>";

if (ISSET($_POST['table'])){
$table = $_POST['table'];
$q = mysql_query("SELECT * from $table") or die(mysql_error());
while($row3 = mysql_fetch_assoc($q)){
$data[] = implode(",",$row3);
}
echo "<br/><br/><br/><br/><br/>";
$data = implode("\n",$data);

$filename = $table;
$filename .= "_";
$filename .= date("U");
$filename .= ".csv";
echo $filename;
$file = fopen($filename,"w");
if(fwrite($file,$data)){
echo "<br/>Success<br/>
<a href=\"".$filename."\">".$filename."</a>";
}
else {
echo "Failure";
}
}
?>

[cooldude832]

It needs 1 more bit added to deal with strings that have commas in it.  I'm trying to figure out a way right now any suggestions be great

[cooldude832]

Okay I'm working with excel 2003 and i've tried different delimeters and it only likes the delimter of commas and \n so this version strips all , from the data which isn't great, but it will work until you rebuild it into a proper excel format.

<?php
/*
By Cooldude_832: ([email protected])
USE OF THIS WITHOUT THIS IS CONSIDERED STOLEN WORK!!!!!
this will allow you to see all your tables in a given mysql database (make connection first) 
and then select a database by giving $database a value.
Pick a value from the dropdown and hit submit.
Enojy.
Cooldude_832
*/
$database = "";
$sql = "SHOW TABLES FROM $databasel";
$result = mysql_query($sql);
echo "<form action=\"csv.php\" method=\"post\">
<select name=\"table\">";
while ($row = mysql_fetch_row($result)) {
    echo "<option value=\"".$row[0]."\">".$row[0]."</option>";
}
echo "</select><input type=\"submit\" value=\"submit\"></form>";

if (ISSET($_POST['table'])){
$table = $_POST['table'];
$q = mysql_query("SELECT * from $table") or die(mysql_error());
while($row = mysql_fetch_assoc($q)){

foreach($row as $value){
$row2[] = str_replace(","," ",$value);
}
$data[] = implode(",",$row);
unset($row2);
}
$data = implode("\n",$data);
print_r($data);
$filename = $table;
$filename .= "_";
$filename .= date("U");
$filename .= ".csv";
$file = fopen($filename,"w");
if(fwrite($file,$data)){
echo "<br/>Success<br/>
<a href=\"".$filename."\">".$filename."</a>";
}
else {
echo "Failure";
}
}
?>

[Barand]

straight from table to csv

 

SELECT a,b,c INTO OUTFILE "/tmp/result.text"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM test_table;

[jbrill]

Okay I'm working with excel 2003 and i've tried different delimeters and it only likes the delimter of commas and \n so this version strips all , from the data which isn't great, but it will work until you rebuild it into a proper excel format.

<?php
/*
By Cooldude_832: ([email protected])
USE OF THIS WITHOUT THIS IS CONSIDERED STOLEN WORK!!!!!
this will allow you to see all your tables in a given mysql database (make connection first) 
and then select a database by giving $database a value.
Pick a value from the dropdown and hit submit.
Enojy.
Cooldude_832
*/
$database = "";
$sql = "SHOW TABLES FROM $databasel";
$result = mysql_query($sql);
echo "<form action=\"csv.php\" method=\"post\">
<select name=\"table\">";
while ($row = mysql_fetch_row($result)) {
    echo "<option value=\"".$row[0]."\">".$row[0]."</option>";
}
echo "</select><input type=\"submit\" value=\"submit\"></form>";

if (ISSET($_POST['table'])){
$table = $_POST['table'];
$q = mysql_query("SELECT * from $table") or die(mysql_error());
while($row = mysql_fetch_assoc($q)){

foreach($row as $value){
$row2[] = str_replace(","," ",$value);
}
$data[] = implode(",",$row);
unset($row2);
}
$data = implode("\n",$data);
print_r($data);
$filename = $table;
$filename .= "_";
$filename .= date("U");
$filename .= ".csv";
$file = fopen($filename,"w");
if(fwrite($file,$data)){
echo "<br/>Success<br/>
<a href=\"".$filename."\">".$filename."</a>";
}
else {
echo "Failure";
}
}
?>

 

ok, so i got the drop downs working and all but when i hit submit, it goes to the url:

 

http://myurl.com/admin/csv.php

 

where it says

 

"Not Found

The requested URL /admin/csv.php was not found on this server.

 

Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request. "

 

what do i have to do?

 

 

 

[suma237]

in which page you saved the code?.ie the name (*.php)

[jbrill]

the code is saved on admin_options.php

[jbrill]

anyone? need to finish this part of the site tonight. 

[cmgmyr]

great little tutorial: http://www.phpfreaks.com/tutorials/114/0.php

[jbrill]

already looked at that, i think it is almost working, its just that once i select the table i want and hit "Submit" it goes to a page that is not there... and i dont know what it is supposed to do...

[jbrill]

still need to figure this out... i really only want one button, once clicked it backs up every table in the database to my desktop..\

any more suggestions?

[Barand]

see

 

http://dev.mysql.com/doc/refman/4.1/en/backup.html

[jbrill]

still stuck guys, ive been trying everything i cannot seem to get it to work...