Page 1 of 1
PHP and Excel, Excel CSV
Posted: Thu May 08, 2003 12:29 pm
by igoy
I want know how to dump mysql table to excel or excel csv format .. without using any Class or library. .. . (rigid client and server admins

)
can anybody help me . . plzzzzz
Posted: Thu May 08, 2003 12:41 pm
by twigletmac
You can do this via phpMyAdmin:
http://www.phpmyadmin.net
Mac
Posted: Thu May 08, 2003 12:50 pm
by Heavy
Excel can read and convert files. An easy format to generate is a tab delimited file with a new line for each row returned from the database.
For example:
Code: Select all
<?php
// This will force the browser to popup the "Save as..." dialog and prompt you to save the content of $FileStr as a file named tabbed-table-file.txt
$FileStr = "ID\tName\tE-mail
123\tJohn\tdoe@server.com
234\tHomer\tdumb@loser.net";
header("Cache-Control: no-store");
header('Content-Length: '.strlen($FileStr));
header('Content-Type: application/octet');
header('Content-Disposition: attachment; filename="tabbed-table-file.txt"');
echo $FileStr;
?>
Then, if you use excel to open this file, it will detect exactly what you wanted it too. Or at least exactly as I think you want it to.
mySQL table to Excel CSV........
Posted: Thu May 08, 2003 1:10 pm
by igoy
Well I'm myself using PHPmyadmin. . (by the way it's one of the great tools) but i don't think it's a good idea to give client access to such tool...
(more over server guys are very rigid..

)
what i would like to some method where pure php can be used to do so...
I guess heavy have something to say. . which looks like can make my life easier. .
I will post again after trying this code...
Posted: Thu May 08, 2003 3:30 pm
by Heavy
Play with this. It is somewhat off topic, but does a database dump without having to use phpMyAdmin:
Code: Select all
<?php
//Script name: "dump.php"
$DB_NAME = $_GET['db_name']; // The name of the database you wish to dump
$DB_USER = $_GET['db_user'];
$DB_PASSWORD = $_GET['db_passwd'];
/*
TAKE NOTE, THE ABOVE THREE ROWS ARE CONSIDERED A SECURITY HOLE.
DO NOT USE THIS SCRIPT ON A PUBLIC NETWORK WITHOUT REPLACING THEM WITH A REAL LOGIN PROCEDURE.
NEVER SEND DB ACCESS INFO ACROSS THE INTERNET LIKE THIS.
And don't just replace the $_GET vars with the correct working values, because if you do,
anyone can surf to the script and get to see all the content of your database.
You can test the script like this:
Browse: http://localhost/path_to_script/dump.ph ... d=DBPASSWD
*/
// The following assumes you connect to a mysql-server on running localhost:
if (strpos($_SERVER['OS'],"Windows")){
$Command = "c:\mysql\bin\mysqldump.exe -u $DB_USER --password='$DB_PASSWORD' $DB_NAME";
}else{
$Command = "/usr/bin/mysqldump -u $DB_USER --password='$DB_PASSWORD' $DB_NAME";
}
//The following is just about how the client OS handles line breaks:
if (strpos($_SERVER['HTTP_USER_AGENT'],"Windows")){
$LineBreaker = "\r\n";
}else{
// Mac uses \r if my memory is correct.
// assuming unix:
$LineBreaker = "\n";
}
//Here, a database dump is performed:
exec($Command,$ArrData);
$Data = "# SQL Dump for database $DB_NAME";
//Here, The result in $ArrData is joined into a multiline string:
$Data .= $LineBreaker . join($ArrData,$LineBreaker);
//Send it to you:
header("Cache-Control: no-store");
header('Content-Type: text/plain');
header('Content-Length: '.strlen($Data));
//if you uncomment the following line, a "Save As.." dialog will be displayed by the browser:
//header('Content-Disposition: attachment; filename="sqldump-'.$_SERVER['HTTP_HOST'].'-'.strftime('%Y-%M-%d-%H-%M').'.sql"');
echo $Data;
?>
If you use this script as is, and publish it on the Internet, you will send the db access info in plain text over the Internet. Thus, anyone with access to Internet equipment might see what you sent.
That's why you shouldn't do that.
Thank Heavy
Posted: Fri May 09, 2003 4:19 am
by igoy
Damn you are my personal jesus christ man .. .
Thanks a ton for that thing. . I guess I can play around with that stuff and get things rolling. . .
by the way I found this great class yesterday while searching for solution
It works quite well..
check this out...
http://www.phpclasses.org/browse.html/package/644.html
thanks again..
