0

I use the following way to try export the data to a csv file, the methoid I am using is from the below link Problem in Export csv file in php, but when I try to export the file, the system replys me no such file can be read, so I would like to ask I have to pre-open a result.csv first before I execute the code? Or the file result.csv has already generated, but in a wrong directory, if this is the reason, may I ask how can I define the directory the file should be created in.

$result = mysql_query("SELECT TechName, ClientName, SiteName, Time, Type
                            INTO OUTFILE 'result.csv'
                            FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED by '\"'
                            LINES TERMINATED BY '\n'
                            FROM Tech AS T, Client AS C, Site AS S, Log AS L
                            WHERE T.TechID=L.TechID AND C.ClientID=L.ClientID AND S.SiteID=L.SiteID
                            ORDER BY L.Time DESC");
    $Time = date('Y_m_d_H_i');
    $fileName = "Report_".$Time.".csv";
    header('Content-type: text/csv'); 
    header('Content-Disposition: attachment; filename="'.$fileName.'"'); 
    readfile('result.csv');
Improve this question
9
  • Seems you are trying to "force a download" of the result.csv file here. Commented Jul 14, 2012 at 3:49
  • It seems your query did not successfully produce you an result.csv, so your error gives you, no such file. Try to simplify your query without the outfile part, and see if your query is valid. Also, I think you needed an absolute path, and appropriate permissions for the outfile to be written successfully. Commented Jul 14, 2012 at 3:56
  • ok, let me try to output the data, see if I can retrieve it from the database first Commented Jul 14, 2012 at 4:03
  • I have tried, it could successfully output the data, seems problem comes from the query to output into a csv file Commented Jul 14, 2012 at 4:05
  • Use absolute path to the file ... SELECT... INTO OUTFILE '/absolute/path/result.csv' ... or if you are using windows OUTFILE 'c:\absolute\path\result.csv' . Also use PHPMYADMIN built-in your WAMP to debug your query. Commented Jul 14, 2012 at 4:12

2 Answers 2

Reset to default
1

Try this code may be. Found it here: http://salman-w.blogspot.in/2009/07/export-mysql-data-to-csv-using-php.html

<?php
/*
 * PHP code to export MySQL data to CSV
 * http://salman-w.blogspot.com/2009/07/export-mysql-data-to-csv-using-php.html
 *
 * Sends the result of a MySQL query as a CSV file for download
 */

/*
 * establish database connection
 */

$conn = mysql_connect('MYSQL_HOST', 'MYSQL_USERNAME', 'MYSQL_PASSWORD') or die(mysql_error());
mysql_select_db('MYSQL_DATABASE', $conn) or die(mysql_error($conn));

/*
 * execute sql query
 */

$query = sprintf('SELECT * FROM MYSQL_TABLE');
$result = mysql_query($query, $conn) or die(mysql_error($conn));

/*
 * send response headers to the browser
 * following headers instruct the browser to treat the data as a csv file called export.csv
 */

header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename=export.csv');

/*
 * output header row (if atleast one row exists)
 */

$row = mysql_fetch_assoc($result);
if ($row) {
    echocsv(array_keys($row));
}

/*
 * output data rows (if atleast one row exists)
 */

while ($row) {
    echocsv($row);
    $row = mysql_fetch_assoc($result);
}

/*
 * echo the input array as csv data maintaining consistency with most CSV implementations
 * - uses double-quotes as enclosure when necessary
 * - uses double double-quotes to escape double-quotes 
 * - uses CRLF as a line separator
 */

function echocsv($fields)
{
    $separator = '';
    foreach ($fields as $field) {
        if (preg_match('/\\r|\\n|,|"/', $field)) {
            $field = '"' . str_replace('"', '""', $field) . '"';
        }
        echo $separator . $field;
        $separator = ',';
    }
    echo "\r\n";
}
?>
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I dont know why, but it just prints out those things on the HTML file instead of a csv file, I have specified the filename as the following C:\wamp\www\export.csv, is it matter at all?
0

If you are trying to output the result of your query, simply do so. No need for readfile() at all. All you need are the headers that you already have, and something to output your CSV data.

Also note that you can simply use fputcsv() to format your array as a CSV line for you. http://php.net/manual/en/function.fputcsv.php

Improve this answer

3 Comments

so these line are not necessary, I mean "INTO OUTFILE 'result.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED by '\" LINES TERMINATED BY '\n' ", and how can I output the data, is it by using the fputcsv function here php.net/manual/en/function.fputcsv.php, but it seems the method is completely different from the method I refered to before
@user1502740, Ah, sorry I didn't see where you were outputting the file with MySQL. While you can do it that way, I wouldn't recommend it. At a minimum, you would need to be providing full paths to that file though. The MySQL user would need write access, and the MySQL and web server would have to be on the same server, or at least be able to read/write from the same place. In my opinion, it's far easier to get the data in PHP and output it with fputcsv().
ya, they are using the same server, as I am using wamp server, and for convenience, I just use root accout to login the database, so it should be okay, the way to specify the path is adding the complete path before the file name?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.