[Resolved] Output to CSV

[cheese]

Hi All,

I need to output the contents of a query to a CSV file.

Having RTFM (well the bits that seemed to apply!!) I still have a problem.

I found the bit SELECT * INTO OUTFILE but that was about it.

From other resources I patched together

$query = mysql_query("SELECT * INTO OUTFILE 'c:\data.csv' FIELDS TERMINATED BY ',' FROM atable;");

.......but nowt happens.

So you are allowed to laugh and tell me how stupid I am...... as long as I get an answer !!!

Any offers,

Neil.

[Drakla]

Just a quicky reply as I'm off to pub - try putting an ABSOLUTE pathname in for the file, as I remember filling the server mysql directory up with stupid dump files once and getting a bollocking for it!

Cheers!

[cheese]

OK,

So I came up with a solution, although not very clean.

<?php
unlink("/var/www/html/afolder/filename.csv");
$query1 = mysql_query("SELECT * INTO OUTFILE '/var/www/html/afolder/filename.csv' FIELDS TERMINATED BY ',' FROM Addresses;")or die("Could not connect to get CSV from database : " . mysql_error());

header("Location: ../csvfiles/data.csv");
header("Content-Type: application/csv");

?>

So we don't get too many csv files, I am actually using the username in place of filename, so the worst we can get is one file per user.

One thing left to do is to get the column headers to be output in to the file...... offers please.

Cheers,

Neil.

[bradgrafelman]

Perhaps this may be of some use to you:

Quote taken from the MySQL Reference Manual (link)

Posted by Heywood on March 11 2005 3:04pm
When using the SELECT ... INTO OUTFILE syntax, use a UNION to add headers. Here's an example for CSV output:

SELECT 'Fiscal Year','Location','Sales'
UNION
SELECT FY, Loc, Sales INTO OUTFILE 'salesreport.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FROM SalesTable;

Seems like you'd ned to type out all the column names, but perhaps someone more savvy with MySQL UNION's can help here.

[cheese]

Thank you for that.

We have spent all day working on this one while listening to Q107 - Toronto. Rock and Roll works everytime !!

We ended up creating a CSV file that you can chose what you want from groups within the Address table then only select the entries from within the filtered group and give an output to the CSV.

The CSV generation works something like this;

Page 1 uses a simple post to get the number of fields you want to select.

Page 2 gives you the fields to chose in dropdowns.

print "<table class=noborder align=center>"; 
print "<tr><td class=newsheadline valign=top colspan=2>Please fill your " . $_POST['numberoffields'] . " fields with unique merge names.</td>";

$x = 1;
$y = $_POST['numberoffields'];
$y++;
while ($x != $y)
{
	print "<tr><td class=newsheadline valign=top>Field " . $x . "</td><td><select name=field" . $x . "><option value=''>Select</option>";

$query1 = mysql_query('SELECT * FROM Addresses');
$f=4;
while ($f != 51)
{ 
	$selection = mysql_field_name($query1, $f);
	print "<option value='" . $selection . "'>" . $selection . "</option>";
	$f++;
}
print "</select></td></tr>";
$x++;
}


print "</table>";

Page three creates the CSV. There is some junk in here relating to the selection of entries you want from the Address table.

header("Content-Type: application/csv");
$x = 1;
$y = $_POST[numberoffields];
while ($x <= $y)
{
	if ($x == 1)
	{
		$csvoutput = $_POST['field'.$x];
		$queryfields = "`".$_POST['field'.$x]."`";
	}
	else
	{
		$csvoutput .= "," . $_POST['field'.$x];
		$queryfields .= ", `".$_POST['field'.$x]."`";
	}
$x++;
}
$csvoutput .= "\n";
//Create the WHERE ..... OR ...... statement for the query.



$y = count($_SESSION['mailmerge2postinfo']);
$x = 1;
while ($x <= $y)
{
	if ($x == 1)
	{
		$querywhere = "ID = " .$_SESSION['mailmerge2postinfo']['checkbox'.$x];
	}
	else
	{
		$querywhere .= " OR ID = " .$_SESSION['mailmerge2postinfo']['checkbox'.$x];
	}
$x++;
}




$query1 = mysql_query("SELECT " . $queryfields . " FROM Addresses WHERE " . $querywhere . ";")or die("Could not connect to get CSV from database : " . mysql_error());
$i = 0;
while ($row1 = mysql_fetch_array($query1, MYSQL_NUM))
{
	foreach ($row1 as $nameofthebitofthearray => $partofthearray)
	{
		if ($nameofthebitofthearray == 0)
		{
			$csvoutput .= $partofthearray;
		}
		else
		{
			$csvoutput .= "," . $partofthearray;
		}

}
$csvoutput .= "\n";
$i++;
}
echo $csvoutput;

Seems to work enough.

Thanks again for the suggestions - if anyone wants more detail, please email me.

Cheers,

Neil.