Convert array to string and write to csv file

[IGGt]

I am looking to write a MySQL query to a csv file.

 

At the minute I:

 

check for the original file, and delete it;

create a new (blank) file;

run the MySQL query and pass it into an array;

write it to the new file;

 

But it is missing something, as it only writes the first line from the query.

 

my code so far is:

 

<?php

//Delete Original File
...

//Set variables
$uName = "root";
$pWord = "password";
$host = "localhost";

$Query = "[i]...MySQL query...[/i]";

$Filename = str_replace('.php', '', basename("Report")) . '_' . date("Ymdhis") . '.csv';

$ReportFiles = "c:\ReportFiles\\";

$Newfile = $ReportFiles.$Filename;

$cr = "\n";

//Create a new (empty) file
echo exec("<nul (set/p z=) > \"$Newfile\"");

//Run MySQL Query
$connection = mysql_connect($host, $uName, $pWord) ;
mysql_select_db("dbName", $connection) ;
$result = mysql_query ($Query, $connection);
while ($row = mysql_fetch_array($result, MYSQL_NUM))
	$String = implode(",",$row);
	{

//Open file and save contents of query to that file
	$fp = fopen($Newfile,"a");
	if($fp){
    		fwrite($fp,$String.$cr);
    		fclose($fp);
    		echo "File saved successfully";
	} else {
    		echo "Error saving file!";
	}
}
?>

 

What am I missing?

[cags]

I'll be honest, I've not looked at your code much because it seems your going about it in the wrong manner. MySQL has built in support for exporting to a CSV. Direct from the manual...

 

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

[IGGt]

I Looked at that, but it didn't work:

 

//Set variables
$uName = "root";
$pWord = "password";
$host = "localhost";

$Query = "SELECT ... INTO OUTFILE  \"c:\ReportFiles\Report_1.csv\" FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM ..;";

//Run MySQL Query
$connection = mysql_connect($host, $uName, $pWord) ;
mysql_select_db("dbName", $connection) ;
$result = mysql_query ($Query, $connection);

[cags]

And if you echo out mysql_error after running the query?

 

Edit: Though if that is actually the query you ran I'm not surprised it didn't work.

[IGGt]

I see,

 

I was missing some "\" (e.g. into outfile c:\\\ReportFiles\\\report.csv)

 

cheers,

 

(and for anybody that may be interested - the answer to the original problem was that it needed a { after $String = implode(",",$row); )

[IGGt]

Cheers,

 

but how can I get the dynamic filename into the query. I tried:

 

$ReportFiles = "c:\\ReportFiles\\";

$Filename = str_replace('.php', '', basename("Report")) . '_' . date("Ymdhis") . '.csv';

$Newfile = $ReportFiles.$Filename;

//MySQL Query
$Query = "SELECT ........

         [color=red]$Query .= "Into outfile ".$Newfile. " lines terminated by etc...."[/color]

        $Query .= "FROM ...........;";

 

but I just got a parse error

[cags]

What error are you getting, check what mysql_error outputs after running the query. Since the filename is a string I suspect it should have single quotes around it. I also believe FROM etc... should be before the INTO OUTFILE.

[IGGt]

I tried the four queries ($Query) below:

The first one was ok, as you would expect.

The second one worked OK as well

The third one though didn't give an error message, but also failed to work. I echoed out the query, and that is fine, but nothing actually happened.

The fourth one was the same as the third one.

 

 

//variables
$ReportFiles = "c:\\ReportFiles\\"";
$Filename = str_replace('.php', '', basename("Report")) . '_' . date("Ymdhis") . '.csv';
$Newfile = $ReportFiles.$Filename;

//$Query .= "INTO OUTFILE  \"c:\\\wamp\\\www\\\ReportFiles\\\Report_1.csv\"";
//$Query .= "INTO OUTFILE  \"c:\\\wamp\\\www\\\ReportFiles\\\ $Filename\"";
//$Query .= "INTO OUTFILE  \"$ReportFiles$Filename\"";
//$Query .= "INTO OUTFILE  \"$Newfile\" ";

 

 

[cags]

Since 3 and 4 produce addresses outside the root of your site, I'd imagine they are out of scope.

[IGGt]

but if I echo out the results I get:

 

//$Query .= "INTO OUTFILE  \"c:\\\ReportFiles\\\Report_1.csv\"";
//$Query .= "INTO OUTFILE  \"c:\\\ReportFiles\\\ $Filename\"";
//$Query .= "INTO OUTFILE  \"$ReportFiles$Filename\"";
//$Query .= "INTO OUTFILE  \"$Newfile\" ";

 

1:"c:\\ReportFiles\\Report_1.csv"

2:"c:\\ReportFiles\\ Report_20091217111456.csv"

3:"c:\ReportFiles\Report_20091217111456.csv"

4:"c:\ReportFiles\Report_20091217111456.csv"

 

so the resulting address is the same for all queries, therefoere presumably it should work?

[cags]

Firstly that code is different to the last post, since in your last post you had wamp\www in the first two links. Secondly they are not the same, your first two echo'd out have double slashes, the first escaping the second so that MySQL doesn't think the slash is an escape character for the R's, the second two only have single quotes so MySQL will think they are to escape the character after them (the R).

[IGGt]

OK, I got there.

 

The lines I needed are:

 

$ReportFiles = "c:\\\wamp\\\www\\\ReportFiles\\\\";

$Filename = str_replace('.php', '', basename("QuadronReport")) . '_' . date("Ymdhis") . '.csv';

$Newfile = $ReportFiles.$Filename;


//$Query .= "INTO OUTFILE  \"$Newfile\"";
//$Query .= "INTO OUTFILE  \"c:\\\wamp\\\www\\\ReportFiles\\\\$Filename\"";
//$Query .= "INTO OUTFILE  \"$ReportFiles$Filename\"";
//$Query .= "INTO OUTFILE  \"c:\\\wamp\\\www\\\ReportFiles\\\Report_1.csv\"";

 

Which then echoes the following results:

1:"c:\\wamp\\www\\ReportFiles\\Report_1.csv"

2:"c:\\wamp\\www\\ReportFiles\\Report_20091217121530.csv"

3:"c:\\wamp\\www\\ReportFiles\\Report_20091217121530.csv"

4:"c:\\wamp\\www\\ReportFiles\\Report_20091217121530.csv"

 

which all work fine when put into MySQL.

 

phew, so many \ !!!

 

[cags]

Yer, it can get confusing. You need one as the escape character for PHP, then one as the escape character for MySQL then the literal slash. I'm not sure why you have 4 in the second one, that shouldn't be needed. It would probably simplify matters slightly if you used single quotes to surround the path, that way your not having to escape the double quotes also.

 

$Query .= "INTO OUTFILE '{$Newfile}'";
$Query .= "INTO OUTFILE 'c:\\\wamp\\\www\\\ReportFiles\\\{$Filename}'";
$Query .= "INTO OUTFILE '{$ReportFiles}{$Filename}'";
$Query .= "INTO OUTFILE 'c:\\\wamp\\\www\\\ReportFiles\\\Report_1.csv'";

[IGGt]

Cheers, I've changed it to use single quotes now, and that makes it easier to see what's happening.

 

The 4th \ seems to be required, to get the filename correct.

 

If I use 3 \ then the filename is '{Report_xxx.csv}', whereas with 4 \ it is correct 'Report_xxx.csv'. But only on the second query:

 

$Query .= "INTO OUTFILE  'c:\\\wamp\\\www\\\ReportFiles\\\{$Filename}'";

 

the others are all fine.

 

I guess this is related to the $Reportfiles variable which is set to:

 

$ReportFiles = "c:\\\wamp\\\www\\\ReportFiles\\\\";

 

because again, anything less than 4 \ at the end and it doesn't work.