Unable to download database as CSV

Question

Im trying to download a whole database but i keep getting the error:

Parse error: syntax error, unexpected '' LINES TERMINATED BY '' (T_CONSTANT_ENCAPSED_STRING) in C:\wamp64\www\cookies\download_db.php on line 6

My code:

<?php 
    require 'db_key.php';

    $conn = mysqli_connect($servername, $username, $password, $dbname);

    $sql_query = mysqli_query($conn, "SELECT * FROM ventas INTO OUTFILE '/tmp/db_downlaod.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'");

    mysqli_query($conn, $sql) or exit(mysqli_error($conn));

    mysqli_close($conn);

    header('Content-type: text/csv');
    header('Content-disposition: attachment; filename=db_downlaod.csv');
    readfile('/tmp/db_downlaod.csv');
    unlink('/tmp/db_downlaod.csv');
    exit();
?>

Any kind of help is greatly appreciated

you're querying twice here. You also have a quotes concatenation issue — Funk Forty Niner, Aug 12 '18 at 16:40
Thanks to both, i assumed it was a quotation error but even tho i can understand what your suggesting without an example i wont be able to apply it. — Minnen, Aug 12 '18 at 16:43
Welcome @Minnen Since you're inside a double quoted query, your `ENCLOSED BY '"'` should be `ENCLOSED BY '\"'` and escaping the double quote. — Funk Forty Niner, Aug 12 '18 at 16:46
also double check your file name. It might be `db_downlaod` but that is actually spelled `db_download`, just saying. — Funk Forty Niner, Aug 12 '18 at 16:48
you'll also be getting another error when you fix it and it will contain "near 1" when you do run this. — Funk Forty Niner, Aug 12 '18 at 16:49
I should have put what I posted in comments in an answer instead. — Funk Forty Niner, Aug 12 '18 at 16:49
@FunkFortyNiner There still would have been the issue with the new lines. — user3783243, Aug 12 '18 at 16:50
@user3783243 Oh I see. Well that part eluded me. I guess I'll just leave well enough alone here and they can use some of my comments to fix this and deal with that also. — Funk Forty Niner, Aug 12 '18 at 16:51

user3783243 · Accepted Answer · 2018-08-12T17:02:46.257

You close the PHP SQL string at the enclosed by bit then start a new string at the next single quote. You also need to double escape the new line since you are in double quotes and want mysql to get it as \n. Try:

$sql_query = mysqli_query($conn, "SELECT * FROM ventas INTO OUTFILE '/tmp/db_downlaod.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\n'");

Visual:

$sql_query = mysqli_query($conn, "SELECT * FROM ventas INTO OUTFILE '/tmp/db_downlaod.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'");
                                                                                                                                 closes PHP>^^<starts a new single quoted PHP string

You also are making 2 queries, the second of which is to a variable that is unassigned, $sql.

Remove:

mysqli_query($conn, $sql) or exit(mysqli_error($conn));

and or possible add that error reporting to the first function call/query execution.

Unable to download database as CSV

1 Answers1