...

View Full Version : Create CSV file from a mySql table



mat41
05-12-2004, 04:28 AM
PHPers

The below code is a working example of how I create a CSV file from a mySql table I have been trying to modify it to only include the fields I need, so far no luck, I would apreciate any ideas?

BTW:my database connection is in an include file ("scripts/auditdb.php")
containing:
------start include file contents-----------------
$conn = mysql_connect("localhost", "root", "admin") or die(mysql_error());
mysql_select_db("pdaAuditAlpha",$conn) or die(mysql_error());
------finish include file contents-----------------

<?
if ($_GET['branch'] != "")
{
header("Content-Disposition: attachment; filename=branch.csv");
include("scripts/auditdb.php");
$errorList = array();
$count = 0;

if (sizeof($errorList) == 0)
{
//START FILLING IN THE FIELDNAME ON TOP OF EXCEL SHEET.
$fieldSql = "SHOW FIELDS FROM sitedetails";
$getFieldInfo = mysql_query($fieldSql);
$i = 0;
while ($row = mysql_fetch_array($getFieldInfo))
{
echo $row['Field'] . ",";
}
echo ("\n");

//START FILLING IN THE ROWS IN EXCEL SHEET.
$sql = "SELECT * FROM sitedetails WHERE BSBCode = '".$_GET['bsb']."'";
$getInfo = mysql_query($sql);
while($row = mysql_fetch_array($getInfo, MYSQL_ASSOC))
{
while (list($key, $value) = each($row))
{
echo ("$value" . ",");
}
echo ("\n");
}
mysql_close($conn);
}
else
{
listErrors();
}
}
else
{
echo "need to pass QS values";
}
?>

Thanking you in advance

FJbrian
05-12-2004, 04:49 AM
I dunno mysql or php very well but this is no prob with PHPMyAdmin, that well known free program.

firepages
05-12-2004, 05:28 AM
as FJbrain notes PHPmyadmin can do this for you .. else something similar to the below may do the job



<?
/*e.g. the actual database fieldnames*/
$fields=array('fieldname1','fieldname2','etc');
foreach($fields as $f){
echo $f . ",";
echo ("\n");
}

//START FILLING IN THE ROWS IN EXCEL SHEET.
$sql = "SELECT ".implode(',',$fields)." FROM sitedetails WHERE BSBCode = '".$_GET['bsb']."'";
?>

bcarl314
05-12-2004, 02:07 PM
Looking at the mysqldump man page might help you as well. Although I've never tried it, there is an option to create a tab delimited text file using mysqldump. Looks to be the --tab=file.txt parameter.

More information here:
http://www.hmug.org/man/1/mysqldump.html

Theoritically, the syntax (from the command line) would be:



$> mysqldump --tab=myfile.txt databaseName -u userName -p

FJbrian
05-12-2004, 02:16 PM
PHPMyAdmin is free and as useful as it is popular. In the last few years, I dunno if I've ever been at sourceforge and NOT seen it in the top ten most downloaded scripts.
Wanted to mention it again for a couple reasons:
It's so useful, somewhat powerful, I almost forgot you could do things like these two other responses you got.
The second, it's really cool that there's enough knowledgable folks here that you got 3 useful answers to one question. Quietly says alot about these boards

raf
05-12-2004, 02:54 PM
creating these CSV files shoul no tbe done over the webserver. The commandline is a better option for this (see bcarl314 post)

If it must be done over the webserver, then all that you need is a
select ... INTO OUTFILE ...

--> more info in the manual.
http://dev.mysql.com/doc/mysql/en/SELECT.html
or run a search at the mysql forum. I'm sure i've posted some code there.

About phpMyAdmin : its one of the ugliest, slowest and least userfriendly db-frontends i've ever used. It is realy perplexing how such a webfront can be so widely used by so many capable PHP coders, without being shaped up.
For mySQL db's, MySQLFront is by far a more userfriendly and powerfull front
http://www.mysqlfront.de/
(sadly, it's no longer available as a free download but it's probably worth 25 euro)

On my developmentmachine, i use MySQLFront and i always feel frustrated if i need to do some db-work on a hosted account that uses phpMyAdmin.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum