Error 500 Using "into outfile....."

darkdestroyer

Hi All,

This is relatively simpe, or so i thought, i am trying to export a query to CSV using:

into outfile '/$filename.csv' fields terminated by ',' lines terminated by '\r\n'";

this works, but i get lots of formatting problems so i add:

into outfile '/$filename.csv' fields terminated by ',' enclosed by '"' lines terminated by '\r\n'";

As soon as i add the enclosed by or optionally enclosed by.. i get an error 500... what would cause that to happen.
I have also tried using '\n' instead of '\r\n'

Thanks

dalecosp

Does the webserver (I assume you're running PHP in a webserver) have permission to write to "."?

MySQL can also bar you from using "into outfile" with an access denied error. Check your logfiles; that may tell you what's up.

darkdestroyer

Hi Dalecosp,

I can run the query ok directly from MySQL so i dont think that MySQL is the issue.

Does the webserver (I assume you're running PHP in a webserver) have permission to write to "."?

Yes its running PHP, but i have a clue if it has permission to write to "." , i dont even know what that is!!
I'll have to dig a bit more.

Thanks

dalecosp

"." is the current working directory. If your script is in the directory /home/www/mysite, then "." refers to that directory.

I think I've got the problem figured:

into outfile '/$filename.csv'

There's no way you mean that, right? That's into the root of the filesystem. Try something like "/tmp/$filename.csv" ...

darkdestroyer

hi dalescop i tried that, '/CSV/$filename.csv'

it still doesnt like it when i add 'enclosed by '"''

Im stumped!

I run it directly from MySQL it all works fine.
I thought it must be something to do with PHP, but surely if it lets me create/write to a file it doesnt matter if i use the enclosed by statement.

PS thanks for the suggestions so far.

darkdestroyer

i am editing it in PSPad, and whenever i enter the " in the enclosed by '"' the \r\n turns a differnt colour indicating that value isnt valid....

So there must be something in PHP causing this, as PSPad seems to think its illegal also...

dalecosp

Well, it looks like a little bit of a quoting nightmare. If your editor indicates a syntax error, it should be found in your logfiles as well.